MySQL 索引從入門到精通：核心概念、類型與實戰(zhàn)優(yōu)化

一、索引是什么？（核心概念）

可以把索引理解為數(shù)據(jù)庫表的 “目錄”：

沒有索引時，查詢數(shù)據(jù)需要逐行掃描全表（全表掃描），就像找書中某段內(nèi)容要逐頁翻；

有了索引后，數(shù)據(jù)庫會先查索引（目錄），快速定位到數(shù)據(jù)所在位置，大幅提升查詢效率。

索引的本質(zhì)是一種排好序的數(shù)據(jù)結(jié)構(gòu)（MySQL 中最常用的是 B+Tree），它存儲了表中一列 / 多列的值，并指向?qū)?yīng)數(shù)據(jù)行的物理地址。

二、MySQL 中常見的索引類型

1. 按 “數(shù)據(jù)結(jié)構(gòu)” 分類（底層實現(xiàn)）

MySQL 官方的索引數(shù)量限制:

補充：MySQL 8.0+ 對部分限制（如索引長度）有小幅放寬，但 “單表 64 個索引” 是通用上限，且?guī)缀醪粫玫健?/p>

中小型表（數(shù)據(jù)量 ≤ 100 萬）：控制在 5~8 個 以內(nèi)；

大型表（100 萬 < 數(shù)據(jù)量 ≤ 1000 萬）：控制在 8~12 個 以內(nèi)；

超大型表（數(shù)據(jù)量 > 1000 萬）：建議不超過 15 個，且優(yōu)先用 “復(fù)合索引” 替代多個單字段索引（比如用 idx_age_gender(age, gender) 替代 idx_age + idx_gender）。

2. 按 “功能 / 創(chuàng)建方式” 分類（實際開發(fā)常用）

（1）主鍵索引（PRIMARY KEY）

特殊的唯一索引，一張表只能有一個主鍵索引；

主鍵字段值不能為 NULL，且必須唯一；

-- 創(chuàng)建表時指定主鍵索引
CREATE TABLE user (
    id INT NOT NULL,
    name VARCHAR(20),
    PRIMARY KEY (id)  -- 主鍵索引（底層是B+Tree）
);

（2）唯一索引（UNIQUE）

保證索引字段的值唯一，但允許 NULL（多個 NULL 不沖突）；

一張表可以有多個唯一索引；

-- 給user表的phone字段加唯一索引
CREATE UNIQUE INDEX idx_user_phone ON user (phone);

（3）普通索引（INDEX）

最基礎(chǔ)的索引，無唯一性、非空限制，僅用于提升查詢速度；

-- 給user表的name字段加普通索引
CREATE INDEX idx_user_name ON user (name);

（4）復(fù)合索引（聯(lián)合索引）

基于多個字段創(chuàng)建的索引，遵循 “最左前綴原則”；

-- 給user表的age+gender創(chuàng)建復(fù)合索引
CREATE INDEX idx_user_age_gender ON user (age, gender);
-- 最左前綴原則：能命中索引的查詢是 age / age+gender，僅查 gender 則不命中

補：左前原則：就是“最左前綴原則”

當(dāng)你創(chuàng)建復(fù)合索引（比如 idx_age_gender(age, gender)）時，MySQL 會優(yōu)先匹配索引中最左側(cè)的字段，然后依次向右匹配 —— 只有查詢條件包含 “最左前綴”，才能命中該復(fù)合索引；跳過左側(cè)字段直接查右側(cè)字段，索引會失效。

簡單說：復(fù)合索引 (a, b, c) 的有效匹配順序是 a → a+b → a+b+c，而 b、c、b+c 都無法命中該索引。

舉個例子：

表結(jié)構(gòu)為

CREATE TABLE user (
    id INT PRIMARY KEY,
    age INT,
    gender VARCHAR(2),
    city VARCHAR(20)
);
-- 創(chuàng)建復(fù)合索引：age（左1）→ gender（左2）→ city（左3）
CREATE INDEX idx_age_gender_city ON user (age, gender, city);

樣例：

特殊情況：字段順序不影響（只要包含最左前綴）

MySQL 會自動優(yōu)化查詢條件的字段順序，只要包含最左前綴，即使順序打亂也能命中：

-- 條件字段順序是 gender + age，但包含最左前綴 age → 仍命中索引
SELECT * FROM user WHERE gender = '男' AND age = 20;

底層原因：

復(fù)合索引的 B+Tree 結(jié)構(gòu)是先按左 1 字段排序，再按左 2 字段排序，最后按左 3 字段排序：

葉子節(jié)點先按 age 從小到大排，age 相同的再按 gender 排，gender 相同的再按 city 排；

如果沒有 age 這個 “排序依據(jù)”，數(shù)據(jù)庫無法在索引樹中定位到 gender 或 city 的數(shù)據(jù)，只能全表掃描。

避坑：

不要跳過左側(cè)字段：比如復(fù)合索引 (a,b)，別只查 b，要么加 a，要么給 b 單獨建索引；

合理設(shè)計復(fù)合索引字段順序：把查詢頻率最高、區(qū)分度最高的字段放在最左側(cè)（比如 age 比 gender 區(qū)分度高，放左 1）；

范圍查詢會中斷后續(xù)匹配：如果左 1 字段用 >/</BETWEEN 等范圍查詢，右側(cè)字段無法命中索引：

-- age 用范圍查詢，gender 無法命中索引（僅 age 部分生效）
SELECT * FROM user WHERE age > 20 AND gender = '男';

（5）前綴索引

針對字符串字段（如 VARCHAR、TEXT），僅對字段的前 N 個字符創(chuàng)建索引，節(jié)省存儲空間；

示例：

-- 給address字段的前10個字符創(chuàng)建前綴索引
CREATE INDEX idx_user_address ON user (address(10));

三、索引的優(yōu)缺點

優(yōu)點

大幅提升查詢效率（尤其是大數(shù)據(jù)量表）；

加速排序、分組操作（ORDER BY/GROUP BY）；

約束數(shù)據(jù)唯一性（主鍵 / 唯一索引）。

缺點

增加寫操作開銷（INSERT/UPDATE/DELETE 時，需要同步更新索引，耗時更長）；

占用額外存儲空間（索引文件會占用磁盤空間）；

索引創(chuàng)建 / 維護需要成本（過多索引會拖慢數(shù)據(jù)庫整體性能）。

四、索引使用

適合加索引的場景：

查詢頻繁的字段（如 WHERE 條件、JOIN 關(guān)聯(lián)字段）；

主鍵、唯一約束字段（必須加）；

排序 / 分組字段（ORDER BY/GROUP BY）。

不適合加索引的場景：

數(shù)據(jù)量極小的表（全表掃描比查索引更快）；

頻繁更新的字段（寫操作會頻繁更新索引）；

低基數(shù)字段（如性別（男 / 女），區(qū)分度太低，索引效果差）；

NULL 值占比極高的字段。

避坑要點：

遵循復(fù)合索引的 “最左前綴原則”；

避免索引失效（如 WHERE 中用函數(shù)操作索引字段：WHERE DATE(create_time) = '2026-01-13'）；

不要創(chuàng)建過多索引（一張表建議控制在 5-8 個以內(nèi)）。

五、InnoDB引擎中的索引

這個時候就有老鐵要問了，為什么要單獨拎出來講解InnoDB引擎的索引，

因為InnoDB 是 MySQL 默認(rèn)且最常用的引擎，其索引設(shè)計（聚簇索引 + 二級索引）與 MyISAM 等其他引擎有本質(zhì)區(qū)別，是可以幫助我們理解 MySQL 索引工作原理。

1、一級索引和二級索引

一級索引和二級索引是針對 InnoDB 引擎的索引分類方式（MyISAM 無此劃分），核心區(qū)別是索引葉子節(jié)點存儲的內(nèi)容和在查詢中的作用：

一級索引（Primary Index）：就是聚簇索引，是 InnoDB 表的 “主索引”，葉子節(jié)點直接存儲完整的行數(shù)據(jù)；

二級索引（Secondary Index）：也叫 “輔助索引”，包括普通索引、唯一索引、復(fù)合索引、全文索引等所有非聚簇索引，葉子節(jié)點僅存儲 “索引字段值 + 主鍵值”，不存儲完整行數(shù)據(jù)。

差異：

(1)一級索引（聚簇索引）：

一級索引的創(chuàng)建規(guī)則

InnoDB 會按優(yōu)先級自動確定一級索引：

優(yōu)先使用用戶定義的 PRIMARY KEY（主鍵）作為一級索引；

若無主鍵，找第一個 “非空唯一索引（UNIQUE NOT NULL）” 作為一級索引；

若以上都沒有，InnoDB 會隱式創(chuàng)建一個 6 字節(jié)的自增整型列（GEN_CLUST_INDEX）作為一級索引。

一級索引的查詢邏輯

因為葉子節(jié)點直接存完整行數(shù)據(jù)，所以通過一級索引查詢時，一步就能拿到所有需要的字段，無需任何額外操作。

示例（基于之前的 user 表，id 是主鍵 / 一級索引）：

-- 用一級索引（id）查詢，直接從葉子節(jié)點拿數(shù)據(jù)，無回表
SELECT * FROM user WHERE id = 10;

(2)二級索引（輔助索引）

二級索引的類型

所有手動創(chuàng)建的非主鍵索引都屬于二級索引，比如：

-- 普通索引（二級索引）
CREATE INDEX idx_user_name ON user (name);
-- 唯一索引（二級索引）
CREATE UNIQUE INDEX idx_user_phone ON user (phone);
-- 復(fù)合索引（二級索引）
CREATE INDEX idx_user_age_gender ON user (age, gender);
-- 全文索引（二級索引）
FULLTEXT INDEX idx_article_content ON article (content);

二級索引的查詢邏輯

二級索引的葉子節(jié)點只有 “索引字段 + 主鍵”，所以查詢時分為兩種情況：

覆蓋索引場景：查詢字段僅包含 “索引字段 + 主鍵”→ 直接從二級索引拿數(shù)據(jù)，無需回表；

非覆蓋場景：查詢字段包含其他列 → 先查二級索引拿到主鍵，再用主鍵查一級索引（回表）。

示例 1（覆蓋索引，無回表）：

-- 查詢字段：name（索引字段） + id（主鍵），都在二級索引葉子節(jié)點
SELECT id, name FROM user WHERE name = '張三';

示例 2（非覆蓋場景，需回表）：

-- 查詢字段包含age（不在idx_user_name葉子節(jié)點），需回表查一級索引
SELECT id, name, age FROM user WHERE name = '張三';

(3)一級 / 二級索引的關(guān)聯(lián)（查詢流程示例）

為了讓你更直觀理解，我們拆解一個完整的二級索引查詢流程：

假設(shè) user 表中 id=10 的用戶 name='張三'、age=25，執(zhí)行 SELECT age FROM user WHERE name = '張三'：

數(shù)據(jù)庫先檢索二級索引 idx_user_name，找到 name='張三' 對應(yīng)的主鍵值 id=10；

再用 id=10 檢索一級索引（聚簇索引），從葉子節(jié)點中拿到 age=25；

返回結(jié)果給用戶。

這個過程中，第二步就是 “回表”，本質(zhì)是二級索引依賴一級索引才能獲取完整數(shù)據(jù)。

2、聚簇索引：

(1)聚簇索引是什么？

聚簇索引（Clustered Index）可以理解為：索引的葉子節(jié)點直接存儲了整張表的行數(shù)據(jù)，而非僅僅存儲指向數(shù)據(jù)的指針。

打個更形象的比方：

普通索引（非聚簇索引）像書籍的 “目錄”，目錄里只寫了 “某章節(jié)在第 XX 頁”，需要先查目錄，再翻到對應(yīng)頁碼找內(nèi)容；

聚簇索引則像書籍本身就是 “按目錄排序的”—— 目錄和內(nèi)容合二為一，目錄的最后一頁就是內(nèi)容的對應(yīng)頁，不需要二次查找。

注意：MySQL 的 InnoDB 引擎才支持聚簇索引，MyISAM 引擎沒有聚簇索引的概念（MyISAM 所有索引都是非聚簇的）。

(2)聚簇索引的特點

一張表只能有一個聚簇索引

因為聚簇索引的葉子節(jié)點就是數(shù)據(jù)本身，數(shù)據(jù)行只能以一種物理順序存儲，所以 InnoDB 表最多只能有一個聚簇索引。

主鍵就是默認(rèn)的聚簇索引

InnoDB 會優(yōu)先將主鍵索引作為聚簇索引：

如果你給表定義了主鍵（PRIMARY KEY），InnoDB 就以這個主鍵創(chuàng)建聚簇索引；

如果你沒定義主鍵，InnoDB 會找第一個非空唯一索引作為聚簇索引；

如果連非空唯一索引都沒有，InnoDB 會隱式創(chuàng)建一個名為 GEN_CLUST_INDEX 的自增 6 字節(jié)整型列作為聚簇索引。

聚簇索引的結(jié)構(gòu)（B+Tree）

以主鍵為聚簇索引的結(jié)構(gòu)如下：

                根節(jié)點（主鍵范圍）
               /        \
        分支節(jié)點1      分支節(jié)點2
       /    \         /    \
  葉子節(jié)點1  葉子節(jié)點2 葉子節(jié)點3  葉子節(jié)點4
  （存儲完整行數(shù)據(jù)）  （存儲完整行數(shù)據(jù)）

非葉子節(jié)點：存儲主鍵值和指向子節(jié)點的指針；

葉子節(jié)點：按主鍵順序存儲完整的行數(shù)據(jù)，且葉子節(jié)點之間通過雙向鏈表連接（方便范圍查詢）。

非聚簇索引（二級索引）依賴聚簇索引

InnoDB 中所有的普通索引、唯一索引、復(fù)合索引等，都屬于 “二級索引”，它們的葉子節(jié)點只存儲索引字段值 + 主鍵值，而非完整行數(shù)據(jù)。

舉個例子：

假設(shè)有 user 表，主鍵是 id（聚簇索引），給 name 加了普通索引（二級索引）：

CREATE TABLE user (
    id INT PRIMARY KEY,  -- 聚簇索引
    name VARCHAR(20),
    age INT
);
CREATE INDEX idx_user_name ON user (name);  -- 二級索引

當(dāng)執(zhí)行 SELECT * FROM user WHERE name = '張三' 時，InnoDB 會做兩步操作：

1. 先查 idx_user_name 這個二級索引，找到 name='張三' 對應(yīng)的主鍵值（比如 id=10）；
2. 再用這個主鍵值查聚簇索引，找到 id=10 對應(yīng)的完整行數(shù)據(jù)（回表查詢）。

(3)聚簇索引的優(yōu)缺點

優(yōu)點

查詢效率極高：主鍵查詢時，直接從聚簇索引的葉子節(jié)點拿到完整數(shù)據(jù)，無需回表；

范圍查詢快：葉子節(jié)點是有序的雙向鏈表，按主鍵范圍查詢（如 id BETWEEN 10 AND 20）時，只需遍歷鏈表即可；

數(shù)據(jù)物理存儲有序：行數(shù)據(jù)按主鍵物理排序，減少磁盤 I/O。

缺點

插入速度受主鍵順序影響大：

如果主鍵是自增整型（如 id INT AUTO_INCREMENT），新數(shù)據(jù)會追加到葉子節(jié)點末尾，插入效率高；

如果主鍵是無序值（如 UUID），插入時需要移動數(shù)據(jù)來維持有序性，會導(dǎo)致大量磁盤 I/O，性能下降。

更新主鍵代價高：更新主鍵會改變數(shù)據(jù)的物理存儲位置，同時所有二級索引的主鍵值也需要同步更新；

頁分裂問題：當(dāng)一個葉子節(jié)點存滿數(shù)據(jù)，插入新數(shù)據(jù)時會觸發(fā)頁分裂，增加額外開銷。

(4)四聚簇索引 vs 非聚簇索引（核心區(qū)別）

3、什么是回表？

(1)先明確：什么是回表？

回表是 InnoDB 引擎特有的操作，本質(zhì)是：

當(dāng)你通過非聚簇索引（二級索引，如普通索引、唯一索引） 查詢數(shù)據(jù)時，如果需要的字段不在該二級索引的葉子節(jié)點中，數(shù)據(jù)庫會先查二級索引拿到主鍵值，再用主鍵值去聚簇索引中查找完整行數(shù)據(jù)的過程。

簡單說：回表 = 查二級索引（拿主鍵） + 查聚簇索引（拿完整數(shù)據(jù)），是兩次索引查詢的組合。

(2)回表發(fā)生的核心條件

回表的發(fā)生需要同時滿足兩個條件：

1. 查詢依賴的是二級索引（不是聚簇索引）；
2. 查詢需要的字段不全在二級索引的葉子節(jié)點中（葉子節(jié)點只有 “索引字段 + 主鍵”）。

(3)回表發(fā)生的具體場景（附示例）

以下所有示例均基于這個表結(jié)構(gòu)（InnoDB 引擎）：

CREATE TABLE user (
    id INT PRIMARY KEY,  -- 聚簇索引（葉子節(jié)點存完整行數(shù)據(jù)）
    name VARCHAR(20),
    age INT,
    gender VARCHAR(2),
    phone VARCHAR(11)
);
-- 創(chuàng)建二級索引：普通索引
CREATE INDEX idx_user_name ON user (name);  -- 葉子節(jié)點：name + id
CREATE INDEX idx_user_age_gender ON user (age, gender);  -- 葉子節(jié)點：age + gender + id

場景 1：查詢字段包含非索引字段（最常見）

-- 條件用二級索引字段name，但查詢字段包含age（不在idx_user_name的葉子節(jié)點）
SELECT id, name, age FROM user WHERE name = '張三';

第一步：查 idx_user_name 二級索引，找到 name='張三' 對應(yīng)的主鍵 id；

第二步：用 id 查聚簇索引，拿到 age 字段 → 觸發(fā)回表。

場景 2：查詢 *（所有字段）且條件用二級索引

-- 查詢所有字段，條件用二級索引字段age
SELECT * FROM user WHERE age = 20;

* 包含 phone 等不在 idx_user_age_gender 葉子節(jié)點的字段，必須回表查聚簇索引才能拿到完整數(shù)據(jù) → 觸發(fā)回表。

場景 3：復(fù)合索引不滿足 “覆蓋”，需額外字段

-- 條件用復(fù)合索引的age，但查詢字段包含phone（不在索引中）
SELECT id, age, phone FROM user WHERE age = 20;

復(fù)合索引 idx_user_age_gender 的葉子節(jié)點只有 age + gender + id，沒有 phone → 觸發(fā)回表。

(4)不會發(fā)生回表的場景（對比理解）

場景 1：直接用聚簇索引（主鍵）查詢

-- 條件用主鍵（聚簇索引），無論查什么字段都不回表
SELECT * FROM user WHERE id = 10;

聚簇索引的葉子節(jié)點直接存完整行數(shù)據(jù)，一步就能拿到所有字段 → 無回表。

場景 2：查詢字段僅包含 “二級索引字段 + 主鍵”（覆蓋索引）

-- 查詢字段：name（索引字段） + id（主鍵），都在idx_user_name的葉子節(jié)點
SELECT id, name FROM user WHERE name = '張三';

無需查聚簇索引，直接從二級索引拿到所有需要的字段 → 無回表（這就是 “覆蓋索引” 的核心價值）。

場景 3：復(fù)合索引覆蓋所有查詢字段

-- 查詢字段：age + gender + id，都在idx_user_age_gender的葉子節(jié)點
SELECT id, age, gender FROM user WHERE age = 20;

復(fù)合索引的葉子節(jié)點包含所有查詢字段 → 無回表。

(5)如何避免回表？（實用優(yōu)化方案）

回表會增加一次索引查詢，大數(shù)據(jù)量下會顯著降低性能，可通過以下方式避免：

使用覆蓋索引（最推薦）：

調(diào)整查詢語句，只查 “二級索引字段 + 主鍵”，不查額外字段；

或創(chuàng)建包含所需字段的復(fù)合索引（比如將常用查詢字段加入復(fù)合索引）：

-- 原查詢需要name + age，給name和age建復(fù)合索引
CREATE INDEX idx_user_name_age ON user (name, age);
-- 此時查詢 SELECT id, name, age FROM user WHERE name = '張三' 無需回表

優(yōu)先用主鍵查詢：如果業(yè)務(wù)允許，先通過其他方式拿到主鍵，再用主鍵查數(shù)據(jù)（比如先查 name 拿到 id，再用 id 查 *）。

合理設(shè)計索引：將高頻查詢的字段加入復(fù)合索引，讓索引覆蓋更多查詢場景。

總結(jié)

索引是 MySQL 提升查詢效率的核心手段，本質(zhì)是 “有序數(shù)據(jù)結(jié)構(gòu)（B+Tree 為主）”，可類比為 “表的目錄”；

常用索引類型包括主鍵索引、唯一索引、普通索引、復(fù)合索引，其中復(fù)合索引需遵循 “最左前綴原則”；

，大數(shù)據(jù)量下會顯著降低性能，可通過以下方式避免：

使用覆蓋索引（最推薦）：

調(diào)整查詢語句，只查 “二級索引字段 + 主鍵”，不查額外字段；

或創(chuàng)建包含所需字段的復(fù)合索引（比如將常用查詢字段加入復(fù)合索引）：

-- 原查詢需要name + age，給name和age建復(fù)合索引
CREATE INDEX idx_user_name_age ON user (name, age);
-- 此時查詢 SELECT id, name, age FROM user WHERE name = '張三' 無需回表

優(yōu)先用主鍵查詢：如果業(yè)務(wù)允許，先通過其他方式拿到主鍵，再用主鍵查數(shù)據(jù)（比如先查 name 拿到 id，再用 id 查 *）。

合理設(shè)計索引：將高頻查詢的字段加入復(fù)合索引，讓索引覆蓋更多查詢場景。

總結(jié)

索引是 MySQL 提升查詢效率的核心手段，本質(zhì)是 “有序數(shù)據(jù)結(jié)構(gòu)（B+Tree 為主）”，可類比為 “表的目錄”；

常用索引類型包括主鍵索引、唯一索引、普通索引、復(fù)合索引，其中復(fù)合索引需遵循 “最左前綴原則”；

索引并非越多越好，要平衡查詢效率和寫操作開銷，僅給高頻查詢、低更新的字段加索引。

到此這篇關(guān)于MySQL 索引從入門到精通示例詳解(核心概念、類型與實戰(zhàn)優(yōu)化)的文章就介紹到這了,更多相關(guān)mysql索引類型內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論