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

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

1.哈希索引

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

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

2.1 B+樹(shù)簡(jiǎn)介

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

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

2.2 B+ 樹(shù)特點(diǎn)

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

2.3 B+樹(shù)與 B 樹(shù)的對(duì)比

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

2.4 B+樹(shù)索引的使用

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

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

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

二 . 索引中的頁(yè)

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

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

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

2.為什么使用頁(yè) ?

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

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

時(shí)間局部性（TemporalLocality）：如果?個(gè)信息項(xiàng)正在被訪問(wèn)，那么在近期它很可能還會(huì)被再 次訪問(wèn)。

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

• 每個(gè)頁(yè)即使沒(méi)有數(shù)據(jù)也會(huì)使用 16KB 空間 , 同時(shí)與索引的 B+樹(shù)種的結(jié)點(diǎn)對(duì)應(yīng)

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

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

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

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

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

page number : 頁(yè)的唯一編號(hào) , 用于定位頁(yè)在磁盤(pán)上的位置

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

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

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

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

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

page level : 當(dāng)前頁(yè)在 B+ 樹(shù)種的層級(jí)

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

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

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

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

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

④User Records（用戶記錄）

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

⑤Free Space（空閑空間）

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

⑥Page Directory（頁(yè)面目錄）

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

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

• 用于校驗(yàn)頁(yè)的完整性，確保頁(yè)在磁盤(pán) I/O 過(guò)程中未損壞。
• 核心字段：

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

5. 三層 B + 樹(shù)的存儲(chǔ)容量計(jì)算

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

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

三 . 索引的分類

1.按功能與約束劃分

① 主鍵索引 ( PRIMARY KEY)

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

② 普通索引 (INDEX)

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

③ 唯一索引 (UNIQUE)

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

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

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

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

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

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

① 聚簇索引(clustered index)

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

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

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

② 二級(jí)索引(secondary index)

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

③ 索引覆蓋

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

四.使用索引

1.查看索引

① 方式 1 : show keys from 表名

② 方式 2 : show index from 表名

③ 方式 3 : 簡(jiǎn)要信息 : desc 表名

④ 查看索引信息

2.自動(dòng)創(chuàng)建

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

3.手動(dòng)創(chuàng)建

① 主鍵索引

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

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

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

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

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

語(yǔ)法 : 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)建表時(shí)創(chuàng)建唯一鍵

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

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

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)建的時(shí)機(jī)分為兩種

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

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);

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

語(yǔ)法 : 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)建語(yǔ)法與創(chuàng)建普通索引相同 , 只不過(guò)指定多個(gè)列 , 列與列之間用逗號(hào)隔開(kāi)

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

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ù)合主鍵
);

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

② 修改表中的列為復(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);

③ 單獨(dú)創(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.刪除索引

① 刪除主鍵索引

語(yǔ)法 : alter table 表名 drop primary key;

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

示例 :

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

② 其他索引

語(yǔ)法 : alter table 表名 drop index 索引名;

示例 :

alter table t_test_index6 drop index index_name;

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

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

8.如何查看自己寫(xiě)的 SQL 走沒(méi)走索引?

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

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

最新評(píng)論