前言

在數(shù)據(jù)庫(kù)開(kāi)發(fā)中，索引是提升查詢性能最核心、最有效的手段。一個(gè)設(shè)計(jì)精良的索引可以將查詢速度提升數(shù)個(gè)數(shù)量級(jí)，而一個(gè)糟糕的索引設(shè)計(jì)，不僅無(wú)法提升性能，反而會(huì)浪費(fèi)磁盤(pán)空間，拖慢數(shù)據(jù)寫(xiě)入速度。

一、在IDEA中圖形化創(chuàng)建索引

IntelliJ IDEA（及其專(zhuān)業(yè)版內(nèi)置的DataGrip）的Database工具窗口功能強(qiáng)大，讓我們無(wú)需手寫(xiě)SQL即可高效地管理數(shù)據(jù)庫(kù)對(duì)象。對(duì)于索引的創(chuàng)建，圖形化界面不僅直觀，還能有效避免因拼寫(xiě)錯(cuò)誤導(dǎo)致的語(yǔ)法問(wèn)題。

我們以一個(gè)電商系統(tǒng)中的商品表 pms_product 為例，該表包含 product_code (商品編碼), category_id (分類(lèi)ID), brand_id (品牌ID), shop_price (商城價(jià)格), product_name (商品名稱(chēng)) 等字段。

1.定位目標(biāo)表：在IDEA右側(cè)的 Database 工具窗口中，展開(kāi)你的數(shù)據(jù)源，找到目標(biāo)數(shù)據(jù)庫(kù)，再找到 pms_product 表。

2.打開(kāi)表結(jié)構(gòu)編輯器：右鍵點(diǎn)擊 pms_product 表，在彈出的菜單中選擇 Modify Object... (或者直接使用快捷鍵 F4)。這將打開(kāi)一個(gè)詳細(xì)的表結(jié)構(gòu)編輯界面。

3.切換到索引標(biāo)簽頁(yè)：在表結(jié)構(gòu)編輯界面的上方，你會(huì)看到 Columns, Keys, Indices, Foreign Keys 等多個(gè)標(biāo)簽頁(yè)。點(diǎn)擊 Indices 標(biāo)簽頁(yè)。

4.新建索引：在 Indices 標(biāo)簽頁(yè)中，點(diǎn)擊工具欄上的 + 號(hào)（或使用快捷鍵 Alt + Insert），從下拉菜單中選擇 Index。此時(shí)，界面下方會(huì)出現(xiàn)一個(gè)新的索引配置行。

5.配置索引屬性：這是最核心的一步，我們需要仔細(xì)填寫(xiě)每一項(xiàng)：

• Name (索引名稱(chēng)): 將默認(rèn)生成的名稱(chēng)（如 index）修改為一個(gè)符合規(guī)范的、有意義的名稱(chēng)。業(yè)界通用的規(guī)范是 idx_字段名[_字段名]。例如，我們可以命名為 idx_category_brand。
• Type (索引類(lèi)型): 保持默認(rèn)的 BTREE。這是MySQL InnoDB引擎中最常用、最通用的索引類(lèi)型，它支持等值查詢、范圍查詢和排序操作。
• Columns (索引字段): 點(diǎn)擊 Columns 區(qū)域下方的 + 號(hào)，會(huì)彈出當(dāng)前表所有字段的列表。
  • 首先選擇 category_id。
  • 再次點(diǎn)擊 + 號(hào)，選擇 brand_id。
  • 關(guān)鍵點(diǎn)：這里的上下順序就是索引的實(shí)際順序，它直接關(guān)系到“最左前綴原則”。
• Unique (唯一索引): 這是一個(gè)復(fù)選框。如果業(yè)務(wù)邏輯要求 category_id 和 brand_id 的組合在表中是唯一的，則勾選它。對(duì)于普通的查詢優(yōu)化，不要勾選。

6.保存并應(yīng)用

配置完成后，點(diǎn)擊窗口右下角的 OK 按鈕。IDEA會(huì)自動(dòng)在后臺(tái)生成并執(zhí)行對(duì)應(yīng)的 CREATE INDEX SQL語(yǔ)句。

二、MySQL索引的常見(jiàn)類(lèi)型與SQL創(chuàng)建方式

雖然圖形化界面很方便，但理解其背后的SQL語(yǔ)句是成為高級(jí)工程師的必經(jīng)之路。索引的類(lèi)型多種多樣，每種都有其特定的應(yīng)用場(chǎng)景。

1.普通索引（Index）

這是最基本的索引類(lèi)型，沒(méi)有任何限制。它的作用是加速對(duì)表中數(shù)據(jù)的查詢。

場(chǎng)景：product_name 字段經(jīng)常被用于搜索，但允許重復(fù)。

-- 在 product_name 字段上創(chuàng)建普通索引
CREATE INDEX idx_product_name ON pms_product(product_name);

2.唯一索引（Unique Index）

唯一索引與普通索引類(lèi)似，但索引列的值必須唯一，允許有空值。它能保證數(shù)據(jù)列的唯一性。

場(chǎng)景：product_code 是商品的唯一編碼，絕不允許重復(fù)。

-- 在 product_code 字段上創(chuàng)建唯一索引
CREATE UNIQUE INDEX uk_product_code ON pms_product(product_code);

3.聯(lián)合索引（Composite Index）

聯(lián)合索引是在多個(gè)字段上創(chuàng)建一個(gè)索引。它遵循“最左前綴原則”，即查詢條件中必須包含索引定義的最左側(cè)字段，索引才能生效。

場(chǎng)景：經(jīng)常需要根據(jù) category_id 和 brand_id 組合查詢商品。

-- 創(chuàng)建 (category_id, brand_id) 聯(lián)合索引
CREATE INDEX idx_category_brand ON pms_product(category_id, brand_id);

4.全文索引（Fulltext Index）

全文索引主要用于對(duì)大文本字段（如 TEXT, VARCHAR）進(jìn)行關(guān)鍵詞搜索。它比 LIKE '%keyword%' 高效得多。

場(chǎng)景：在 product_description（商品詳情）字段中進(jìn)行全文搜索。

-- 在 product_description 字段上創(chuàng)建全文索引
ALTER TABLE pms_product ADD FULLTEXT INDEX ft_product_desc(product_description);

5.主鍵索引（Primary Key）

主鍵索引是一種特殊的唯一索引，不允許有空值。一個(gè)表只能有一個(gè)主鍵。通常在創(chuàng)建表時(shí)定義。

CREATE TABLE pms_product (
    id BIGINT NOT NULL AUTO_INCREMENT,
    product_code VARCHAR(64),
    -- ... 其他字段
    PRIMARY KEY (id) -- 定義主鍵索引
);

三、核心原理：深入理解最左前綴原則

最左前綴原則是理解聯(lián)合索引的鑰匙，也是面試和實(shí)戰(zhàn)中最常被考察的知識(shí)點(diǎn)。很多開(kāi)發(fā)者誤以為SQL語(yǔ)句中字段的書(shū)寫(xiě)順序必須和索引定義完全一致，或者不理解為何索引會(huì)“莫名其妙”地失效。

什么是“最左前綴”？

聯(lián)合索引 (category_id, brand_id) 在底層的B+樹(shù)數(shù)據(jù)結(jié)構(gòu)中，是按照以下規(guī)則進(jìn)行排序和存儲(chǔ)的：

1. 首先，所有數(shù)據(jù)嚴(yán)格按照 category_id 的值進(jìn)行排序。
2. 在 category_id 值相同的情況下，再按照 brand_id 的值進(jìn)行排序。

這就像一本電話簿，首先是按“姓氏”排序，在姓氏相同的人群內(nèi)部，再按“名字”排序。

場(chǎng)景實(shí)戰(zhàn)分析

基于我們剛剛創(chuàng)建的索引 idx_category_brand (category_id, brand_id)，我們來(lái)看幾種不同的查詢場(chǎng)景：

結(jié)論：SQL語(yǔ)句中 WHERE 子句的書(shū)寫(xiě)順序不會(huì)影響索引的使用。但查詢條件中必須包含聯(lián)合索引定義的最左側(cè)字段（即 category_id），索引才能被激活。如果缺少最左列，索引將完全失效。

四、索引失效的常見(jiàn)“陷阱”

即使建立了索引，如果SQL語(yǔ)句編寫(xiě)不當(dāng)，索引依然會(huì)失效，導(dǎo)致數(shù)據(jù)庫(kù)進(jìn)行低效的全表掃描。以下是生產(chǎn)環(huán)境中最常見(jiàn)的幾種“陷阱”。

1.在索引列上進(jìn)行函數(shù)運(yùn)算或計(jì)算

這是新手最容易犯的錯(cuò)誤，也是最容易被忽略的性能殺手。

錯(cuò)誤寫(xiě)法

-- 對(duì) create_time 字段使用了 YEAR() 函數(shù)
SELECT * FROM pms_product WHERE YEAR(create_time) = 2023;

后果：索引失效。數(shù)據(jù)庫(kù)為了判斷每一行是否符合條件，必須取出 create_time 的值并執(zhí)行 YEAR() 函數(shù)。這個(gè)操作破壞了索引列的原始值，導(dǎo)致無(wú)法利用B+樹(shù)的有序性進(jìn)行快速查找，只能進(jìn)行全表掃描。

正確寫(xiě)法

-- 將計(jì)算移到等號(hào)右邊，使用范圍查詢
SELECT * FROM pms_product 
WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2024-01-01 00:00:00';

這種寫(xiě)法直接對(duì)索引列進(jìn)行范圍比較，可以高效地利用索引。

2.隱式類(lèi)型轉(zhuǎn)換

這是一個(gè)非常隱蔽的“陷阱”，往往在上線后才暴露問(wèn)題。

場(chǎng)景：假設(shè) product_code 字段在數(shù)據(jù)庫(kù)中被定義為 VARCHAR(64)，并且已經(jīng)為其建立了索引。

錯(cuò)誤寫(xiě)法

-- 查詢值沒(méi)有加單引號(hào)，被MySQL識(shí)別為數(shù)字類(lèi)型
SELECT * FROM pms_product WHERE product_code = 10001;

后果：MySQL發(fā)現(xiàn)字段是字符串類(lèi)型，而查詢值是數(shù)字類(lèi)型，會(huì)自動(dòng)執(zhí)行一個(gè)隱式轉(zhuǎn)換，相當(dāng)于 WHERE CAST(product_code AS SIGNED) = 10001。這等同于在索引列上使用了函數(shù)，導(dǎo)致索引失效。

正確寫(xiě)法

-- 加上單引號(hào)，確保查詢值的類(lèi)型與字段類(lèi)型一致
SELECT * FROM pms_product WHERE product_code = '10001';

3.模糊查詢以通配符開(kāi)頭

錯(cuò)誤寫(xiě)法

-- 百分號(hào)在最前面
SELECT * FROM pms_product WHERE product_name LIKE '%手機(jī)%';

后果：B+樹(shù)是按照字段值的前綴進(jìn)行排序的。% 開(kāi)頭意味著無(wú)法確定查找的起點(diǎn)，數(shù)據(jù)庫(kù)只能掃描表中所有行的 product_name 字段，導(dǎo)致索引失效。

正確寫(xiě)法

-- 百分號(hào)在后面（前綴匹配）
SELECT * FROM pms_product WHERE product_name LIKE '華為%';

這種前綴匹配可以利用索引快速定位到以“華為”開(kāi)頭的記錄。

4.使用 OR 連接非索引字段

場(chǎng)景：category_id 字段有索引，但 remark（備注）字段沒(méi)有索引。

錯(cuò)誤寫(xiě)法

SELECT * FROM pms_product WHERE category_id = 10 OR remark = '熱銷(xiāo)商品';

后果：只要 OR 連接的任意一個(gè)字段沒(méi)有索引，MySQL優(yōu)化器通常會(huì)認(rèn)為使用索引的成本更高，從而直接放棄索引，選擇全表掃描。

五、進(jìn)階優(yōu)化與注意事項(xiàng)

索引冗余與覆蓋索引

• 避免冗余索引：如果你已經(jīng)創(chuàng)建了聯(lián)合索引 idx_a_b (a, b)，那么不需要再單獨(dú)為字段 a 創(chuàng)建索引。因?yàn)槁?lián)合索引本身就包含了 a 字段的有序信息，單獨(dú)為 a 創(chuàng)建索引是完全多余的，只會(huì)浪費(fèi)存儲(chǔ)空間并降低 INSERT、UPDATE 等寫(xiě)入操作的性能。
• 利用覆蓋索引：如果一個(gè)查詢所需的所有字段都包含在索引中，數(shù)據(jù)庫(kù)引擎無(wú)需再“回表”查詢?cè)紨?shù)據(jù)行，性能極佳。
  • 推薦 (覆蓋索引)：SELECT category_id, brand_id FROM pms_product WHERE category_id = 10;
  • 不推薦 (需要回表)：SELECT * FROM pms_product WHERE category_id = 10;
    后者在查完索引后，還需要拿著主鍵ID回到主索引（聚簇索引）中去獲取其他字段的值，多了一次查找操作。

聯(lián)合索引的字段順序設(shè)計(jì)

在設(shè)計(jì)聯(lián)合索引 (A, B) 時(shí)，字段的順序至關(guān)重要，通常遵循以下兩個(gè)原則：

• 區(qū)分度高的字段放前面：區(qū)分度是指字段中不重復(fù)值的數(shù)量與總行數(shù)的比值。區(qū)分度越高，篩選能力越強(qiáng)。例如，user_id 的區(qū)分度遠(yuǎn)高于 gender。將高區(qū)分度的字段放在前面，可以更快地縮小數(shù)據(jù)范圍。
• 范圍查詢的字段放最后：如果查詢中既有等值查詢（=），又有范圍查詢（>、<、BETWEEN），必須將范圍查詢的字段放在聯(lián)合索引的最后。
  • 例如：WHERE category_id = 10 AND shop_price > 100。
  • 索引應(yīng)建為：(category_id, shop_price)。如果反過(guò)來(lái) (shop_price, category_id)，那么查詢時(shí)只能用到 shop_price 的索引，category_id 將無(wú)法利用索引進(jìn)行過(guò)濾。

如何驗(yàn)證索引是否生效？

不要靠猜測(cè)，一定要使用 EXPLAIN 命令。在你的 SELECT 語(yǔ)句前加上 EXPLAIN，重點(diǎn)關(guān)注結(jié)果中的以下兩列：

• key：顯示實(shí)際被優(yōu)化器選中使用的索引名稱(chēng)。如果為 NULL，則說(shuō)明沒(méi)有使用任何索引。
• type：顯示表的連接類(lèi)型，代表了查詢的效率。
  • 高效：const, eq_ref, ref, range。
  • 低效（需優(yōu)化）：index (全索引掃描), ALL (全表掃描)。

六、常見(jiàn)疑難問(wèn)答

1.最左前綴原則只會(huì)出現(xiàn)在聯(lián)合索引中嗎？

是的，最左前綴原則是聯(lián)合索引的專(zhuān)屬特性。對(duì)于單列索引（只有一個(gè)字段的索引），數(shù)據(jù)庫(kù)要么用，要么不用，不存在“用一半”的情況，所以談不上“最左”前綴。

2.我可以在一張表中創(chuàng)建一個(gè) INDEX idx_form_task (form_id, task_id)，再創(chuàng)建一個(gè) INDEX idx_task_form (task_id, form_id) 嗎？

技術(shù)上完全允許，但在實(shí)際開(kāi)發(fā)中往往沒(méi)必要，甚至屬于資源浪費(fèi)。

假設(shè)你創(chuàng)建了聯(lián)合索引：INDEX idx_form_task (form_id, task_id)：

• 查詢 WHERE form_id = ?：能用上索引（最左前綴）。
• 查詢 WHERE form_id = ? AND task_id = ?：能用上索引（完整匹配）。
• 查詢 WHERE task_id = ?：用不上（跳過(guò)最左）。
  此時(shí)，如果你再創(chuàng)建一個(gè) INDEX idx_task_form (task_id, form_id)：
  它是專(zhuān)門(mén)為了優(yōu)化 WHERE task_id = ? 這種查詢的。
  只有當(dāng)你有大量 單獨(dú)查詢 task_id 的需求時(shí)，才需要建 (task_id, form_id)。
  如果你既有 WHERE form_id = ? 的需求，又有 WHERE task_id = ? 的需求。
• 錯(cuò)誤做法：只建一個(gè)聯(lián)合索引 (form_id, task_id)。（因?yàn)椴?task_id 會(huì)失效）
• 正確做法：建兩個(gè)單列索引，或者建一個(gè)聯(lián)合索引 + 一個(gè)單列索引。
• 冗余做法：同時(shí)建 (form_id, task_id) 和 (task_id, form_id)。這通常浪費(fèi)空間，除非你的查詢經(jīng)常涉及 ORDER BY form_id, task_id 和 ORDER BY task_id, form_id 這種截然不同的排序。

3.能既創(chuàng)建聯(lián)合索引，又創(chuàng)建單個(gè)索引嗎？

可以，但要注意冗余。

假設(shè)你創(chuàng)建了聯(lián)合索引：INDEX idx_union (A, B)

情況一：你再給 A 建單列索引

• 操作：INDEX idx_union (A, B) + INDEX idx_single (A)
• 結(jié)果：完全冗余，浪費(fèi)資源！
• 原因：根據(jù)最左前綴原則，聯(lián)合索引 (A, B) 已經(jīng)包含了 A 的索引功能。數(shù)據(jù)庫(kù)在查詢 WHERE A = ? 時(shí)，會(huì)直接使用聯(lián)合索引。再單獨(dú)給 A 建索引，就像是在字典里給“姓”排了一次序，又單獨(dú)拿個(gè)小本本把“姓”排了一次序，純屬多此一舉。
• 建議：刪掉單列索引 idx_single (A)。

情況二：你再給 B 建單列索引

• 操作：INDEX idx_union (A, B) + INDEX idx_single (B)
• 結(jié)果：合理，且經(jīng)常需要這樣做。
• 原因：聯(lián)合索引 (A, B) 無(wú)法優(yōu)化 WHERE B = ? 的查詢。如果你經(jīng)常需要根據(jù) B 單獨(dú)查詢，就必須給 B 單獨(dú)建一個(gè)索引。
• 建議：保留。

4.我創(chuàng)建了聯(lián)合索引 INDEX idx_form_task (form_id, task_id)，SQL是 WHERE task_id = '5' AND form_id = '101'。這種有違反最左前綴原則嗎？

完全沒(méi)有違反，索引依然會(huì)生效。

這其實(shí)是數(shù)據(jù)庫(kù)開(kāi)發(fā)中一個(gè)非常經(jīng)典的問(wèn)題，很多新手都會(huì)誤以為 SQL 里的字段順序必須和索引順序一模一樣。

核心結(jié)論：MySQL 的查詢優(yōu)化器非常聰明，它會(huì)自動(dòng)分析你的 SQL 語(yǔ)句。無(wú)論你寫(xiě)成：

WHERE task_id = '5' AND form_id = '101'

還是：

WHERE form_id = '101' AND task_id = '5'

數(shù)據(jù)庫(kù)在執(zhí)行時(shí)，都會(huì)識(shí)別出這兩個(gè)條件，并根據(jù)你建立的索引 INDEX idx_form_task (form_id, task_id)，自動(dòng)調(diào)整匹配順序。它會(huì)先利用 form_id 定位，再利用 task_id 過(guò)濾。

所以，SQL 語(yǔ)句中 WHERE 條件的書(shū)寫(xiě)順序，不影響索引的使用。

“最左前綴原則”里的順序，指的是索引定義時(shí)的順序以及查詢條件是否包含最左邊的列，而不是 SQL 語(yǔ)句的書(shū)寫(xiě)順序。

你的索引定義：(form_id, task_id)

你的查詢：WHERE task_id = '5' AND form_id = '101'

分析：

EXPLAIN SELECT * FROM fc_form_data WHERE task_id = '5' AND form_id = '101';

在結(jié)果中，你會(huì)看到：

• 優(yōu)化器看到了 form_id = '101'，發(fā)現(xiàn)它是索引的第一列（最左列），匹配成功。
• 優(yōu)化器看到了 task_id = '5'，發(fā)現(xiàn)它是索引的第二列，匹配成功。
• 結(jié)果：兩個(gè)字段都用上了索引，效率最高。

怎么驗(yàn)證？

• 你可以使用 EXPLAIN 命令來(lái)查看執(zhí)行計(jì)劃：
• key 字段顯示使用了 idx_form_task。
• key_len 字段顯示的長(zhǎng)度應(yīng)該是兩個(gè)字段長(zhǎng)度之和（說(shuō)明兩個(gè)字段都參與了索引查找）。
  總結(jié)：只要你的 SQL 語(yǔ)句中包含了聯(lián)合索引的最左前綴字段（即 form_id），哪怕你把 task_id 寫(xiě)在前面，或者把 form_id 寫(xiě)在最后面，索引都能正常工作。

5.MySQL可以對(duì)相同字段創(chuàng)建不同索引嗎？

可以。MySQL允許對(duì)同一個(gè)字段創(chuàng)建多個(gè)名稱(chēng)不同但結(jié)構(gòu)相同的索引，但這是一種極其糟糕的實(shí)踐，會(huì)造成嚴(yán)重的資源浪費(fèi)。

例如，以下兩條SQL語(yǔ)句都可以成功執(zhí)行：

ALTER TABLE test ADD INDEX idx_test02 USING BTREE(UPDATED);
ALTER TABLE test ADD INDEX idx_test03 USING BTREE(UPDATED);

從效果上看，這兩個(gè)索引保留一個(gè)即可。因?yàn)樗鼈冎皇敲Q(chēng)不同，索引字段相同，實(shí)際上就是相同的索引。創(chuàng)建重復(fù)索引會(huì)：

• 浪費(fèi)磁盤(pán)空間。
• 降低 INSERT、UPDATE、DELETE 等寫(xiě)入操作的性能，因?yàn)槊看螖?shù)據(jù)變動(dòng)都需要更新多個(gè)相同的索引樹(shù)。

因此，應(yīng)嚴(yán)格避免創(chuàng)建重復(fù)索引。

6.不同表的索引命名相同會(huì)沖突嗎？

不會(huì)。MySQL對(duì)索引名稱(chēng)的作用域限制在單個(gè)表內(nèi)。也就是說(shuō)，同一數(shù)據(jù)庫(kù)中不同表可以擁有相同名稱(chēng)的索引而不會(huì)產(chǎn)生沖突。

然而，同一張表內(nèi)的索引名稱(chēng)必須唯一，不能重復(fù)。

盡管如此，在實(shí)際開(kāi)發(fā)中，建議采用統(tǒng)一規(guī)范為索引命名，比如結(jié)合表名和字段名來(lái)定義索引名稱(chēng)。這樣不僅有助于區(qū)分不同表的索引，還能提高代碼可讀性和維護(hù)性。例如，對(duì)于用戶表user的id字段索引，可以命名為idx_user_id；訂單表order的日期字段date索引則命名為idx_order_date。

七、拓展：索引底層原理深度剖析——為什么加了索引會(huì)更快？

我們?cè)谇懊鎸W(xué)會(huì)了如何創(chuàng)建索引、如何避免索引失效，但你是否思考過(guò)：為什么加了索引，查詢速度就能從幾秒甚至幾分鐘縮短到幾毫秒？這背后到底發(fā)生了什么？

要理解這個(gè)問(wèn)題，我們需要深入到MySQL的存儲(chǔ)引擎（以最常用的InnoDB為例）的底層，從數(shù)據(jù)結(jié)構(gòu)和磁盤(pán)I/O兩個(gè)維度來(lái)揭開(kāi)索引的神秘面紗。

1. 索引的本質(zhì)：空間換時(shí)間

索引的本質(zhì)，其實(shí)就是一種數(shù)據(jù)結(jié)構(gòu)。

如果把數(shù)據(jù)庫(kù)表比作一本書(shū)，那么索引就是書(shū)的目錄。

• 沒(méi)有索引（全表掃描）：就像你要找書(shū)中關(guān)于“MySQL原理”的內(nèi)容，如果沒(méi)有目錄，你只能從第一頁(yè)翻到最后一頁(yè)，逐字逐句地找。數(shù)據(jù)量小的時(shí)候無(wú)所謂，如果書(shū)有幾百萬(wàn)頁(yè)（幾千萬(wàn)行數(shù)據(jù)），這將是災(zāi)難性的。
• 有了索引：你可以直接查目錄，找到對(duì)應(yīng)的頁(yè)碼，翻過(guò)去就能找到內(nèi)容。

在計(jì)算機(jī)領(lǐng)域，這是一種典型的**“空間換時(shí)間”**的策略。索引文件需要占用額外的磁盤(pán)空間來(lái)存儲(chǔ)，但它能極大地減少查詢時(shí)需要掃描的數(shù)據(jù)量，從而換取查詢時(shí)間的縮短。

2. 為什么MySQL選擇B+樹(shù)？（數(shù)據(jù)結(jié)構(gòu)層面的降維打擊）

MySQL InnoDB引擎默認(rèn)使用的索引結(jié)構(gòu)是B+樹(shù)。為什么不是數(shù)組、鏈表或者二叉樹(shù)？這完全是為了適應(yīng)磁盤(pán)存儲(chǔ)的特性。

磁盤(pán)I/O的瓶頸：計(jì)算機(jī)的內(nèi)存速度極快，但數(shù)據(jù)是持久化存儲(chǔ)在磁盤(pán)上的。磁盤(pán)的讀寫(xiě)速度（I/O）比內(nèi)存慢幾十萬(wàn)倍。因此，數(shù)據(jù)庫(kù)性能優(yōu)化的核心目標(biāo)就是：盡量減少磁盤(pán)I/O的次數(shù)。

磁盤(pán)讀取數(shù)據(jù)是按“頁(yè)”（Page）為單位的，InnoDB中默認(rèn)一頁(yè)的大小是16KB。每次I/O，至少讀取一頁(yè)。

二叉樹(shù)的缺陷（樹(shù)太高了）：

如果使用二叉樹(shù)（每個(gè)節(jié)點(diǎn)最多兩個(gè)分叉），數(shù)據(jù)量一大，樹(shù)的高度就會(huì)變得非常高（瘦高型）。

查找一個(gè)數(shù)據(jù)，可能需要從根節(jié)點(diǎn)遍歷到葉子節(jié)點(diǎn)，經(jīng)過(guò)幾十層。每一層節(jié)點(diǎn)如果不在內(nèi)存中，就需要一次磁盤(pán)I/O。如果樹(shù)高20，就需要20次I/O，這在數(shù)據(jù)庫(kù)領(lǐng)域是不可接受的慢。

B+樹(shù)的優(yōu)勢(shì)（矮胖子）：

B+樹(shù)是一種多路平衡查找樹(shù)。

• 多路：一個(gè)節(jié)點(diǎn)可以有非常多的分叉（InnoDB中一個(gè)節(jié)點(diǎn)可以存儲(chǔ)上千個(gè)鍵值）。這意味著樹(shù)非常“矮胖”。
• 高度極低：對(duì)于千萬(wàn)級(jí)數(shù)據(jù)的表，B+樹(shù)的高度通常只有2到3層。這意味著，查找任意一條數(shù)據(jù)，最多只需要2到3次磁盤(pán)I/O。

3. B+樹(shù)是如何實(shí)現(xiàn)的？（硬核原理解析）

讓我們通過(guò)一個(gè)簡(jiǎn)單的計(jì)算，來(lái)看看B+樹(shù)到底有多快。

假設(shè)：

• InnoDB頁(yè)大小為16KB。
• 主鍵是BIGINT類(lèi)型，占8字節(jié)。
• 指針占6字節(jié)。
• 那么一個(gè)鍵值對(duì)（Key+Pointer）大約14字節(jié)。

一個(gè)非葉子節(jié)點(diǎn)能存多少個(gè)鍵值？

16KB / 14B ≈ 1170個(gè)。

這意味著，B+樹(shù)的一個(gè)節(jié)點(diǎn)（一頁(yè)）可以指向1170個(gè)子節(jié)點(diǎn)。

B+樹(shù)的存儲(chǔ)能力：

• 樹(shù)高為1：只能存一頁(yè)數(shù)據(jù)（很少見(jiàn)）。
• 樹(shù)高為2：根節(jié)點(diǎn)指向1170個(gè)葉子節(jié)點(diǎn)。假設(shè)每頁(yè)存10行數(shù)據(jù)，能存 1170 * 10 ≈ 1萬(wàn)行。
• 樹(shù)高為3：根節(jié)點(diǎn) -> 1170個(gè)中間節(jié)點(diǎn) -> 1170 * 1170個(gè)葉子節(jié)點(diǎn)。能存 1170 * 1170 * 10 ≈ 1300萬(wàn)行數(shù)據(jù)！

結(jié)論：幾千萬(wàn)行數(shù)據(jù)的表，B+樹(shù)高度僅為3。也就是說(shuō)，無(wú)論數(shù)據(jù)量多大，InnoDB主鍵索引查詢最多只需要3次磁盤(pán)I/O。這就是為什么加了索引會(huì)快的根本原因——它將O(N)的全表掃描復(fù)雜度降低到了O(log N)，且常數(shù)極小。

4. 聚簇索引與非聚簇索引（數(shù)據(jù)到底存在哪？）

在InnoDB中，索引不僅僅是“目錄”，它和數(shù)據(jù)是綁定在一起的。

聚簇索引（主鍵索引）：B+樹(shù)的葉子節(jié)點(diǎn)直接存儲(chǔ)了整行數(shù)據(jù)。

當(dāng)你通過(guò)主鍵查詢時(shí)，一旦在B+樹(shù)中定位到葉子節(jié)點(diǎn)，數(shù)據(jù)就已經(jīng)拿到了，不需要再做任何操作。這就是為什么主鍵查詢最快。

非聚簇索引（二級(jí)索引/普通索引）：我們?cè)?code>form_id上建立的索引就是二級(jí)索引。

它的B+樹(shù)葉子節(jié)點(diǎn)不存整行數(shù)據(jù)，只存索引列的值和主鍵值。

5. 回表（Table Lookup）：為什么查主鍵最快？

這就引出了一個(gè)重要的概念——回表。

假設(shè)你執(zhí)行了這樣一條SQL：

SELECT * FROM fc_form_data WHERE form_id = '101';

因?yàn)槟悴榈氖?code>SELECT *（所有字段），而form_id索引樹(shù)上只有form_id和id（主鍵）。

1. 第一步：先在form_id的二級(jí)索引樹(shù)中找到form_id = '101'的記錄，拿到主鍵id（假設(shè)是1001）。
2. 第二步：拿著主鍵id = 1001，去**主鍵索引樹(shù)（聚簇索引）**中再查找一遍，獲取完整的行數(shù)據(jù)（如task_id, create_time等）。

這個(gè)過(guò)程叫回表?；乇硪馕吨嗔艘淮蜝+樹(shù)查詢（多幾次I/O）。

6. 覆蓋索引（Covering Index）：高手的優(yōu)化技巧

如果你執(zhí)行的是：

SELECT id, form_id FROM fc_form_data WHERE form_id = '101';

你會(huì)發(fā)現(xiàn)，查詢需要的字段id和form_id在form_id的索引樹(shù)上全都有！這時(shí)候，MySQL就不需要去查主鍵索引樹(shù)了，直接返回索引樹(shù)上的數(shù)據(jù)即可。

這就叫覆蓋索引。覆蓋索引避免了回表，是性能優(yōu)化的重要手段。

總結(jié)

索引之所以快，是因?yàn)椋?/p>

1. 使用了B+樹(shù)數(shù)據(jù)結(jié)構(gòu)，將樹(shù)高控制在極低水平（2-3層），極大地減少了磁盤(pán)I/O次數(shù)。
2. 利用了數(shù)據(jù)的有序性，讓數(shù)據(jù)庫(kù)能像查字典一樣通過(guò)二分查找快速定位，而不是逐行掃描。
3. 通過(guò)覆蓋索引等機(jī)制，避免了額外的數(shù)據(jù)讀取操作。

八、可視化圖解：B+樹(shù)結(jié)構(gòu)與回表流程

為了更直觀地理解索引的運(yùn)作機(jī)制，我們使用 Mermaid 流程圖來(lái)展示 InnoDB 中 B+ 樹(shù)的結(jié)構(gòu)以及“回表”查詢的全過(guò)程。

假設(shè)我們有一張表 user，包含字段 id (主鍵), name (普通索引), age。

1. B+樹(shù)結(jié)構(gòu)圖解

InnoDB 中，主鍵索引（聚簇索引）的葉子節(jié)點(diǎn)存儲(chǔ)整行數(shù)據(jù)，而普通索引（二級(jí)索引）的葉子節(jié)點(diǎn)只存儲(chǔ)主鍵值。

圖解說(shuō)明：

1. 左側(cè)（普通索引）：當(dāng)我們執(zhí)行 SELECT * FROM user WHERE name = 'B' 時(shí)，首先在 name 的索引樹(shù)中找到記錄。
2. 中間（獲取主鍵）：在 name 索引的葉子節(jié)點(diǎn)中，我們只找到了主鍵值 id=5。
3. 右側(cè)（回表）：拿著 id=5 回到主鍵索引樹(shù)（聚簇索引）中查找，最終獲取到完整的行數(shù)據(jù)（name, age 等）。這個(gè)過(guò)程就是回表。

2. 覆蓋索引流程圖

如果 SQL 優(yōu)化為 SELECT id, name FROM user WHERE name = 'B'，則不需要回表。

九、深度對(duì)比：B+樹(shù)、B樹(shù)與哈希索引

MySQL 之所以選擇 B+ 樹(shù)作為默認(rèn)索引結(jié)構(gòu)，是因?yàn)樗诖疟P(pán) I/O、范圍查詢和穩(wěn)定性之間取得了最佳平衡。以下是三種主流索引結(jié)構(gòu)的詳細(xì)對(duì)比。

核心總結(jié)：

1. B+ 樹(shù)勝在“范圍查詢”與“I/O友好”：B+ 樹(shù)的內(nèi)部節(jié)點(diǎn)不存數(shù)據(jù)，這使得它比 B 樹(shù)更“矮胖”，同樣的磁盤(pán)頁(yè)能容納更多索引鍵，大大降低了樹(shù)的高度，減少了磁盤(pán) I/O 次數(shù)。同時(shí)，葉子節(jié)點(diǎn)的鏈表設(shè)計(jì)讓它成為了范圍查詢（BETWEEN, >, <）和排序（ORDER BY）的王者。
2. 哈希索引勝在“精準(zhǔn)打擊”：雖然哈希索引在 = 查詢上速度無(wú)敵，但它是個(gè)“偏科生”。一旦遇到 >、< 或者 ORDER BY，它就完全失效。因此，InnoDB 僅在內(nèi)存中提供“自適應(yīng)哈希索引”來(lái)輔助熱點(diǎn)數(shù)據(jù)的等值查詢，而不會(huì)將其作為默認(rèn)的持久化索引結(jié)構(gòu)。
3. B 樹(shù)的“中庸之道”：B 樹(shù)雖然也能工作，但由于其內(nèi)部節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)導(dǎo)致樹(shù)高較高，且范圍查詢效率不如 B+ 樹(shù)，因此在現(xiàn)代關(guān)系型數(shù)據(jù)庫(kù)中逐漸被 B+ 樹(shù)取代。

以上就是一文帶你搞懂MySQL如何創(chuàng)建索引的詳細(xì)內(nèi)容，更多關(guān)于MySQL創(chuàng)建索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論