MySQL 索引是提升查詢效率的核心機制 , 基于特定的數(shù)據(jù)結(jié)構(gòu)(B+樹,哈希等)對數(shù)據(jù)進(jìn)行預(yù)排序和快速定位 , 避免全表掃描

一.索引使用哪種數(shù)據(jù)結(jié)構(gòu)

1.哈希索引

• 基于哈希表 , 鍵值直接映射到存儲位置 , 查詢的時間復(fù)雜度為 O(1)
• 適合精度匹配(=) , 但不支持范圍查詢 , 排序 , 模糊查詢

2.B+樹索引(最常用)

2.1 B+樹簡介

B+樹是一種經(jīng)常用于數(shù)據(jù)庫和文件系統(tǒng)等場合的平均查找樹 , MySQL 索引采用的數(shù)據(jù)結(jié)構(gòu) , 以 4 階 B+樹為例

在二叉搜索樹的基礎(chǔ)上可以有效控制樹高 , 也就意味著在在相同數(shù)據(jù)量的情況下 可以有效減少 IO 次數(shù) , 從而提升效率

2.2 B+ 樹特點

• 能夠有效保持?jǐn)?shù)據(jù)穩(wěn)定有序 , 插入與修改有較穩(wěn)定的時間復(fù)雜度
• 非葉子結(jié)點僅具有索引的功能 , 不存儲數(shù)據(jù) , 所有葉子結(jié)點保存真實數(shù)據(jù)
• 所有葉子結(jié)點構(gòu)成一個有序鏈表 , 可以按照 Key 排序的次序來依次遍歷全部數(shù)據(jù)

2.3 B+樹與 B 樹的對比

1. 葉子結(jié)點之間有一個相互連接的引用 , 使數(shù)據(jù)連續(xù)去 , 且相互鏈接 , 便于區(qū)間查找和搜索 , 并且在 MySQL中使用的是雙向鏈表
2. 非葉子結(jié)點的值都包含在葉子節(jié)點中 , MySQL 非葉子結(jié)點之保存了對葉子結(jié)點的引用 , 沒有保存真實數(shù)據(jù) , 所有的真實數(shù)據(jù)都保存在葉子結(jié)點中
3. 對于 B+樹而言 , 在相同樹高的情況下 , 查找任意元素的時間復(fù)雜度都是一樣的 , 性能均衡

2.4 B+樹索引的使用

• 所有數(shù)據(jù)都保存在葉子結(jié)點中 , 非葉子結(jié)點僅保存索引鍵 , 形成有序鏈表 , 支持范圍查找
• 適用于范圍查找(>,<,BETWEEN) , 排序(ORDER BY) , 多列聯(lián)合查詢

2.5 B+樹在 MySQL 索引中的應(yīng)用

非葉子結(jié)點保存索引數(shù)據(jù) , 葉子結(jié)點保存真實數(shù)據(jù)

二 . 索引中的頁

1.索引與頁的關(guān)系

MySQL 索引(B+樹)的每一層結(jié)點都是獨立的頁 , 通過頁內(nèi)指針和頁間鏈表組織 , 實現(xiàn)高效數(shù)據(jù)查詢

# B+ 樹索引與頁的對應(yīng)關(guān)系
┌─────────────────────────────────────────────────────┐
│  根節(jié)點頁（16KB） - 非葉子節(jié)點                      │
│  ┌───────────┬───────────┬───────────┬───────────┐  │
│  │ 鍵值:100  │ 鍵值:200  │ 鍵值:300  │ 鍵值:400  │  │
│  │ 指針:頁2  │ 指針:頁3  │ 指針:頁4  │ 指針:頁5  │  │
│  └───────────┴───────────┴───────────┴───────────┘  │
└─────────────────────────────────────────────────────┘
            ↓           ↓           ↓           ↓
┌───────────────┐ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ 子節(jié)點頁2     │ │ 子節(jié)點頁3     │ │ 子節(jié)點頁4     │ │ 子節(jié)點頁5     │
│ （非葉子節(jié)點） │ │ （非葉子節(jié)點） │ │ （非葉子節(jié)點） │ │ （非葉子節(jié)點） │
└───────────────┘ └───────────────┘ └───────────────┘ └───────────────┘
            ↓           ↓           ↓           ↓
┌───────────────┐ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ 葉子節(jié)點頁10  │ │ 葉子節(jié)點頁11  │ │ 葉子節(jié)點頁12  │ │ 葉子節(jié)點頁13  │
│ （聚簇索引：  │ │ （聚簇索引：  │ │ （聚簇索引：  │ │ （聚簇索引：  │
│  存儲完整數(shù)據(jù)） │ │  存儲完整數(shù)據(jù)） │ │  存儲完整數(shù)據(jù)） │ │  存儲完整數(shù)據(jù)） │
│  ┌─────────┐  │ │  ┌─────────┐  │ │  ┌─────────┐  │ │  ┌─────────┐  │
│  │ id:1-99 │  │ │  │id:100-199│  │ │  │id:200-299│  │ │  │id:300-399│  │
│  └─────────┘  │ │  └─────────┘  │ │  └─────────┘  │ │  └─────────┘  │
│  前指針:null  │ │  前指針:頁10  │ │  前指針:頁11  │ │  前指針:頁12  │
│  后指針:頁11  │ │  后指針:頁12  │ │  后指針:頁13  │ │  后指針:null  │
└───────────────┘ └───────────────┘ └───────────────┘ └───────────────┘

2.為什么使用頁 ?

在.ibj 文件中最重要的結(jié)構(gòu)體就是頁 , 頁是內(nèi)存與磁盤交互的最小單元 , 默認(rèn)大小是 16 KB , 每次內(nèi)存與磁盤交互至少讀一頁 , 所以在磁盤中每個頁內(nèi)部的地址都是連續(xù)的 , 之所 以這樣做，是因為在使?數(shù)據(jù)的過程中，根據(jù)局部性原理，將來要使?的數(shù)據(jù)?概率與當(dāng)前訪問的 數(shù)據(jù)在空間上是臨近的，所以?次從磁盤中讀取??的數(shù)據(jù)放?內(nèi)存中，當(dāng)下次查詢的數(shù)據(jù)還在這 個?中時就可以從內(nèi)存中直接讀取，從?減少磁盤I/O提?性能

局部性原理： 是指程序在執(zhí)?時呈現(xiàn)出局部性規(guī)律，在?段時間內(nèi)，整個程序的執(zhí)?僅限于程序中的某?部 分。相應(yīng)地，執(zhí)?所訪問的存儲空間也局限于某個內(nèi)存區(qū)域，局部性通常有兩種形式：時間局部 性和空間局部性。

時間局部性（TemporalLocality）：如果?個信息項正在被訪問，那么在近期它很可能還會被再 次訪問。

空間局部性（SpatialLocality）：將來要?到的信息?概率與正在使?的信息在空間地址上是臨 近的

• 每個頁即使沒有數(shù)據(jù)也會使用 16KB 空間 , 同時與索引的 B+樹種的結(jié)點對應(yīng)

3 索引結(jié)點與頁之間的關(guān)系

• 每個 B+ 樹結(jié)點都是一個獨立的 16KB 頁
• 非葉子結(jié)點頁 : 僅存儲 索引鍵值 和 子節(jié)點頁的指針
• 葉子節(jié)點 : 聚簇索引種存儲完整數(shù)據(jù)記錄 , 二級索引中存儲鍵值和主鍵值

4 數(shù)據(jù)頁的基本結(jié)構(gòu)

① 文件頭部 file header(38 字節(jié))

• 存儲頁的元數(shù)據(jù) , 用于標(biāo)識頁的基本信息和關(guān)聯(lián)關(guān)系
• 核心字段:

page number : 頁的唯一編號 , 用于定位頁在磁盤上的位置

prev page / next page : 上一頁/下一頁的編號 , 形成雙向鏈表(方便范圍查詢)

page typr : 頁的類型(索引頁 , 數(shù)據(jù)頁 , undo 日志頁)

checksum : 頁的校驗和 , 用于驗證數(shù)據(jù)的完整性

② 數(shù)據(jù)頁頭 page header(56 字節(jié))

• 存儲頁的狀態(tài)信息 , 描述頁內(nèi)部數(shù)據(jù)的組織情況
• 核心字段 :

page level : 當(dāng)前頁在 B+ 樹種的層級

record count : 頁內(nèi)存儲的用戶記錄數(shù)

free space offset : 空閑空間的起始偏移量(標(biāo)識空閑空間的位置)

directory slot count : page directory 中的槽數(shù)量 (用于快速定位記錄)

③infimum + supremum(最小/最大記錄 , 固定存在)

• 兩個虛擬的記錄 , 不存儲實際數(shù)據(jù) , 用于定義頁內(nèi)記錄的邊界

④User Records（用戶記錄）

• 存儲實際的表數(shù)據(jù)或索引鍵值（聚簇索引頁存完整數(shù)據(jù)，二級索引頁存鍵值 + 主鍵）。
• 記錄按 “行格式” 存儲（如 Compact、Dynamic 格式），包含字段值、隱藏列（如主鍵 ID、事務(wù) ID、回滾指針）。

⑤Free Space（空閑空間）

• 頁內(nèi)未使用的連續(xù)空間，呈動態(tài)變化。
  插入記錄時，從空閑空間分配內(nèi)存；刪除記錄時，釋放的空間會合并到空閑空間。
• 當(dāng)空閑空間不足時，會觸發(fā)頁分裂（Page Split）。

⑥Page Directory（頁面目錄）

• 類似 “書籍目錄”，用于快速定位頁內(nèi)記錄，避免全頁掃描。
  存儲部分記錄的偏移量（按主鍵排序），每個偏移量稱為一個 “槽”（Slot）。
• 查找記錄時，先通過二分法定位槽，再在槽對應(yīng)的記錄區(qū)間內(nèi)遍歷查找。

⑦File Trailer（文件尾部，8 字節(jié)）

• 用于校驗頁的完整性，確保頁在磁盤 I/O 過程中未損壞。
• 核心字段：

Checksum：與 File Header 中的校驗和一致，用于比對驗證。
LSN（Log Sequence Number）：頁的日志序列號，關(guān)聯(lián)事務(wù)日志，確保數(shù)據(jù)持久化

5. 三層 B + 樹的存儲容量計算

三層 B + 樹結(jié)構(gòu)為 “根節(jié)點索引頁 → 二級節(jié)點索引頁 → 葉子節(jié)點數(shù)據(jù)頁”因此總記錄數(shù)為：根節(jié)點索引記錄數(shù) × 二級節(jié)點索引記錄數(shù) × 葉子節(jié)點數(shù)據(jù)記錄數(shù) = 1170 × 1170 × 16 = 21,902,400條

三層樹高的 B + 樹可存儲約 2190 萬條記錄，且檢索時只需三次 I/O（對應(yīng)樹的三層訪問），體現(xiàn)了 B + 樹在大規(guī)模數(shù)據(jù)存儲與高效檢索上的優(yōu)勢

三 . 索引的分類

1.按功能與約束劃分

① 主鍵索引 ( PRIMARY KEY)

• 當(dāng)在一個表中定義一個主鍵時 , 自動創(chuàng)建 , 唯一且非空 , 一個表只能有一個主鍵索引 , 索引值是主鍵的值 , InnoDB 中使用它為聚簇索引

② 普通索引 (INDEX)

• 無任何約束 , 僅用于加速查詢 , 允許列值重復(fù) , 是最基礎(chǔ)的索引類型
• 可能為多列創(chuàng)建組合索引，稱為復(fù)合索引或組全索引

③ 唯一索引 (UNIQUE)

• 當(dāng)在一個表上定義一個唯一鍵時 , 自動創(chuàng)建唯一索引
• 與普通索類似 , 區(qū)別是 唯一索引的列不允許有重復(fù)值

④ 聯(lián)合索引(多列索引)

• 由多個列組合創(chuàng)建的索引 , 遵循'最左前綴匹配原則' : 查詢序包含最左列才能命中索引
• 適用于多條件查詢 , 需要合理安排排列順序(區(qū)分高度的列放在左側(cè))

⑤ 全文索引(按數(shù)據(jù)結(jié)構(gòu)劃分)

• 基于文本列(CHAR , VARCHAR 或 TEXT)上創(chuàng)建 , 以加快對這些類中包含的數(shù)據(jù)查詢和 DML 操作
• 用于全文搜索 , 僅 MYSQAM 和 InnoDB 引擎支持

2.按存儲方式劃分 (InnoDB 引擎)

① 聚簇索引(clustered index)

• 主鍵索引默認(rèn)時聚簇索引 , 如果沒有為表定義主鍵 , InnDB 使用第一個 唯一鍵和非空列作為聚集索引
• 如果表中沒有主鍵或合適的唯一鍵索引 , InnoDB 會為新插入的行生成一個行號并用 6 字節(jié) 的 row_id 字段記錄 , row_id 單調(diào)遞增 , 并使用 row_id 作為索引(隱藏列)

索引與數(shù)據(jù)存儲在一起 , 葉子結(jié)點即數(shù)據(jù)記錄 , 僅主鍵索引是聚簇 索引

優(yōu)勢 查詢主鍵時無需徽標(biāo) , 效率高 ; 劣勢 主鍵更新會導(dǎo)致數(shù)據(jù)移動 , 影響性能

② 二級索引(secondary index)

• 聚集索引以外的索引稱為 非聚集索引或而二級索引
• 二級索引中的每條記錄都包含該行的主鍵列 , 以及二級索引指定的列
• InnoDB 使用這個主鍵值來搜索聚集索引中的行 , 這個過程稱為回表查詢

③ 索引覆蓋

• 查詢字段均在索引中無需回表的優(yōu)化方式

四.使用索引

1.查看索引

① 方式 1 : show keys from 表名

② 方式 2 : show index from 表名

③ 方式 3 : 簡要信息 : desc 表名

④ 查看索引信息

2.自動創(chuàng)建

• 當(dāng)我們?yōu)橐粡埍砑又麈I約束 , 外鍵約束 , 唯一約束時 , MySQL 回為對應(yīng)的列自動創(chuàng)建一個索引
• 如果表不指定任何約束時 , MySQL 會自動為每一列生成一個索引并用 ROW_ID 進(jìn)行標(biāo)識

3.手動創(chuàng)建

① 主鍵索引

• 方式 1 : 創(chuàng)建表時創(chuàng)建主鍵

create table t_test_pk(
  id bigint primary key auto_increment,
  name varchar(20)
);

• 方式 2 : 創(chuàng)建表時單獨指定主鍵列

create table t_test_pk1(
  id bigint auto_increment,
  name varchar(20),
  primary key(id)
);

• 方式 3 : 修改表中的列為主鍵索引

語法 : alter table 表名 [add | modify | drop] 要修改的內(nèi)容

示例 : alter table t_test_pk2 modify id bigint auto_increment;

create table t_test_pk2(
  id bigint,
  name varchar(20)
);
alter table t_test_pk2 add primary key(id);#為表中添加主鍵 , 并指定ID

② 唯一索引

• 方式 1 : 創(chuàng)建表時創(chuàng)建唯一鍵

create table t_test_uk(
  id bigint primary key auto_increment,
  name varchar(20) unique
);

• 方式 2 : 創(chuàng)建表時單獨指定唯一列

create table t_test_uk1(
  id bigint primary key auto_increment,
  name varchar(20),
  unique (name)
);

• 方式 3 : 修改表中的列為唯一索引

create table t_test_uk2(
  id bigint primary key auto_increment,
  name varchar(20)
);
alter table t_test_uk2 add unique (name);

4.普通索引

按創(chuàng)建的時機分為兩種

① 創(chuàng)建表時指定索引列

create table t_test_index(
  id bigint primary key auto_increment,
  name varchar(20) unique,
  sno varchar(10),
  index(sno)
);

② 修改表中的列為普通索引

create table t_test_index1(
  id bigint primary key auto_increment,
  name varchar(20),
  sno varchar(10)
);
alter table t_test_index1 add index(sno);

③ 單獨創(chuàng)建索引并指定索引名

語法 : create index 索引名(一般為 index_ 表名 _ 列名) on 表名(列名);

create table t_test_index2(
  id bigint primary key auto_increment,
  name varchar(20),
  sno varchar(10) 
);
create index index_name on t_test_index2(sno);

5.創(chuàng)建復(fù)合索引

創(chuàng)建語法與創(chuàng)建普通索引相同 , 只不過指定多個列 , 列與列之間用逗號隔開

① 創(chuàng)建表時指定索引列

create table t_test_index4(
  id bigint primary key auto_increment,
  name varchar(20),
  sno varchar(10),
  class_id bigint,
  index(sno,class_id) # 復(fù)合主鍵
);

此時索引名默認(rèn)是第一個鍵的列名

② 修改表中的列為復(fù)合索引

create table t_test_index5(
  id bigint primary key auto_increment,
  name varchar(20),
  sno varchar(10),
  class_id bigint
);
alter table t_test_index5 add index(sno,class_id);

③ 單獨創(chuàng)建索引并指定索引名

create table t_test_index6(
  id bigint primary key auto_increment,
  name varchar(20),
  sno varchar(10),
  class_id bigint
);
create index index_name on t_test_index6(sno,class_id);

6.刪除索引

① 刪除主鍵索引

語法 : alter table 表名 drop primary key;

注意 : 如果主鍵是自增列 , 需要先改為非自增 , 再刪去主鍵

示例 :

alter table t_test_index6 modify id bigint;
alter table t_test_index6 drop primary key;

② 其他索引

語法 : alter table 表名 drop index 索引名;

示例 :

alter table t_test_index6 drop index index_name;

7.創(chuàng)建索引注意事項

• 索引應(yīng)該創(chuàng)建再高頻查詢的列上
• 索引需要占用額外的存儲空間
• 對表進(jìn)行插入,更新,刪除操作時 , 同時也會修改索引 , 可能會影響性能
• 常見過多或不合理的索引會導(dǎo)致性能下降 , 需要謹(jǐn)慎選擇和規(guī)劃索引

8.如何查看自己寫的 SQL 走沒走索引?

可以查看執(zhí)行計劃 , explain+查詢語句

到此這篇關(guān)于MySQL數(shù)據(jù)庫頁與索引示例詳解的文章就介紹到這了,更多相關(guān)mysql頁和索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論