前言

最近星球中有小伙伴問(wèn)我：加了索引，SQL查詢(xún)效率一定會(huì)提升嗎？

答案是否定的。

讓我想起了幾年前查詢(xún)訂單的場(chǎng)景。

優(yōu)化前下面這條SQL的查詢(xún)耗時(shí)是0.5s。

SELECT * FROM orders WHERE user_id = 10086;

添加了下面的索引：

CREATE INDEX idx_user ON orders(user_id);

再次執(zhí)行上面的查詢(xún)SQL語(yǔ)句，此時(shí)的耗時(shí)卻是5.2s。

出現(xiàn)了非常神奇的一幕：加了索引，SQL查詢(xún)性能反而慢了10倍。

作為一個(gè)踩過(guò)無(wú)數(shù)數(shù)據(jù)庫(kù)性能坑的老司機(jī)，今天跟大家一起聊聊那些“加了索引反而更慢”的詭異場(chǎng)景。

1. 索引失效

加了索引之后，你以為它在工作，其實(shí)它在摸魚(yú)，因?yàn)樗赡芤呀?jīng)失效了。

1.1 最左前綴原則

如果查詢(xún)條件的順序不對(duì)，努力白費(fèi)。

復(fù)合索引 (a, b, c) 生效的關(guān)鍵在于最左前綴匹配。

用戶(hù)表創(chuàng)建了名稱(chēng)、年齡和城市這三個(gè)字段的復(fù)合索引：

CREATE INDEX idx_user ON user(name, age, city);

? 有效：使用最左列 name

SELECT * FROM user WHERE name = '蘇三';

? 有效：使用最左前綴 (name, age)

SELECT * FROM user WHERE name = '蘇三' AND age = 30;

? 失效：跳過(guò)了最左列 name

SELECT * FROM user WHERE age = 30;

? 失效：未使用最左前綴

SELECT * FROM user WHERE city = '北京';

原理：復(fù)合索引的存儲(chǔ)結(jié)構(gòu)類(lèi)似于電話(huà)簿（先按姓排序，再按名排序）。

如果跳過(guò)“姓”直接查“名”，索引就失效了。

1.2 函數(shù)操作

函數(shù)操作會(huì)讓索引瞬間失憶。

對(duì)索引列做計(jì)算、函數(shù)轉(zhuǎn)換或類(lèi)型轉(zhuǎn)換，會(huì)導(dǎo)致索引失效：

? 失效：對(duì)索引列使用函數(shù)：

SELECT * FROM user WHERE YEAR(create_time) = 2023;

? 失效：隱式類(lèi)型轉(zhuǎn)換 (phone 是 varchar)

SELECT * FROM user WHERE phone = 13800138000;

? 有效：避免函數(shù)操作

SELECT * FROM user WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

可以使用BETWEEN...AND查詢(xún)時(shí)間范圍。

原理：索引存儲(chǔ)的是列的原始值。對(duì)值進(jìn)行修改后，數(shù)據(jù)庫(kù)無(wú)法在索引樹(shù)中定位原始值。

1.3 范圍查詢(xún)

范圍查詢(xún)（>、<、BETWEEN）會(huì)截?cái)?/span>復(fù)合索引中后續(xù)列的匹配：

索引 (age, salary)

SELECT * FROM employee 
WHERE age > 25        -- ? age 范圍查詢(xún)
  AND salary = 10000; -- ? salary 無(wú)法使用索引

會(huì)導(dǎo)致salary的索引失效。

原理：age>25 匹配到的是一系列值（非精確值），數(shù)據(jù)庫(kù)無(wú)法高效地對(duì) salary 進(jìn)行索引過(guò)濾。

2. 索引維護(hù)成本

天下沒(méi)有免費(fèi)的午餐，索引是有額外的維護(hù)成本的。

2.1 DML 操作變慢

每一次寫(xiě)入都可能在負(fù)重前行。

每次 INSERT、UPDATE、DELETE 操作，數(shù)據(jù)庫(kù)不僅要修改數(shù)據(jù)，還要維護(hù)相關(guān)索引。

無(wú)索引表插入 100w 行：1.2 秒。

有 5 個(gè)索引的表插入 100w 行：15.8 秒 (實(shí)測(cè)差距 10 倍以上)。

場(chǎng)景：在寫(xiě)多讀少的高并發(fā)場(chǎng)景下（如流水記錄），索引反而會(huì)成為性能瓶頸。

2.2 索引占用空間

索引可能會(huì)給磁盤(pán)和內(nèi)存帶來(lái)雙重壓力。

每個(gè)索引都是一棵 B+ 樹(shù)，存儲(chǔ)完整的索引列值（或組合值）。大表的索引輕松占據(jù)幾十GB空間。

MySQL中可以通過(guò)下面的SQL查看表索引大小：

SELECT 
  table_name AS `Table`,
  index_name AS `Index`,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) `Size(MB)`
FROM mysql.innodb_index_stats 
WHERE table_name = 'your_table';

后果：索引過(guò)大導(dǎo)致內(nèi)存中緩存命中率降低，物理 I/O 增加。

3. 優(yōu)化器的“錯(cuò)誤”選擇

有時(shí)候，可能會(huì)出現(xiàn)聰明反被聰明誤，加了索引可能會(huì)導(dǎo)致性能變慢。

3.1 統(tǒng)計(jì)信息過(guò)時(shí)

導(dǎo)航用了舊地圖。

優(yōu)化器依賴(lài)統(tǒng)計(jì)信息（如索引區(qū)分度、數(shù)據(jù)分布）來(lái)選擇索引。

如果統(tǒng)計(jì)信息過(guò)期，優(yōu)化器可能選擇性能更差的索引。

MySQL中我們可以通過(guò)下面的命令強(qiáng)制更新表統(tǒng)計(jì)信息：

ANALYZE TABLE user;

場(chǎng)景：當(dāng)表中數(shù)據(jù)發(fā)生劇烈變化（如大批量刪除/導(dǎo)入）后，統(tǒng)計(jì)信息未及時(shí)更新。

3.2 回表代價(jià)高昂

索引再好也怕“繞路”。

假如給user表給age創(chuàng)建了索引：

SELECT name, email FROM user 
WHERE age > 25;

即使 age 索引被使用，數(shù)據(jù)庫(kù)仍需根據(jù)索引中的主鍵 ID 回表查詢(xún)name、email 字段。

當(dāng)符合條件的數(shù)據(jù)量很大時(shí)，回表 I/O 可能遠(yuǎn)超索引掃描本身。

優(yōu)化方案：使用覆蓋索引（Covering Index），讓索引包含查詢(xún)所需的所有列。

創(chuàng)建覆蓋索引 (包含 age, name, email)：

CREATE INDEX idx_age_covering ON user(age, name, email);

查詢(xún)可直接從索引獲取數(shù)據(jù)，無(wú)需回表

SELECT name, email FROM user WHERE age > 25;

4. 索引過(guò)多

當(dāng)表上存在多個(gè)索引時(shí)，優(yōu)化器需要評(píng)估每個(gè)索引的成本，選擇越多，決策時(shí)間越長(zhǎng)。

下面的查詢(xún)可能使用索引 A 或索引 B

SELECT * FROM orders 
WHERE user_id = 1001 
  AND status = 'completed';

優(yōu)化建議：

• 刪除重復(fù)或冗余索引
• 合并可組合的索引
• 使用工具分析索引使用率（如 sys.schema_unused_indexes）

5. 鎖與并發(fā)

鎖的競(jìng)爭(zhēng)是一個(gè)看不見(jiàn)的戰(zhàn)場(chǎng)。

5.1 行鎖升級(jí)

在事務(wù)中通過(guò)索引檢索并鎖定行時(shí)，如果鎖數(shù)量過(guò)多（超過(guò)閾值），數(shù)據(jù)庫(kù)可能將鎖升級(jí)為表鎖，嚴(yán)重降低并發(fā)性能。

場(chǎng)景：全表更新或刪除大量數(shù)據(jù)時(shí)，索引的存在可能導(dǎo)致鎖升級(jí)。

5.2 索引分裂的阻塞

B+樹(shù)索引在插入數(shù)據(jù)時(shí)可能發(fā)生頁(yè)分裂。

這個(gè)過(guò)程需要加鎖，在高并發(fā)寫(xiě)入場(chǎng)景下可能導(dǎo)致短暫阻塞。

總結(jié)

索引是把雙刃劍，用對(duì)場(chǎng)景是關(guān)鍵。

• 理解原理：掌握最左前綴、索引失效條件、覆蓋索引等核心機(jī)制
• 權(quán)衡成本：在寫(xiě)密集場(chǎng)景謹(jǐn)慎添加索引，評(píng)估維護(hù)代價(jià)
• 精準(zhǔn)設(shè)計(jì)：按實(shí)際查詢(xún)模式設(shè)計(jì)復(fù)合索引，避免冗余
• 關(guān)注統(tǒng)計(jì)信息：定期更新統(tǒng)計(jì)信息，確保優(yōu)化器決策準(zhǔn)確
• 監(jiān)控分析：使用 EXPLAIN、慢查詢(xún)?nèi)罩镜裙ぞ叱掷m(xù)跟蹤索引效果

某電商平臺(tái)曾因在流水表上盲目添加索引，導(dǎo)致高峰時(shí)段寫(xiě)入延遲飆升。

后經(jīng)分析，移除兩個(gè)非核心索引，寫(xiě)入速度提升8倍，而相關(guān)查詢(xún)僅增加20毫秒——這正是索引取舍的藝術(shù)。

記?。?/span>索引不是越多越好，而是越準(zhǔn)越好。

真正的高手，懂得在索引的利刃上優(yōu)雅行走。