前言

最近星球中有小伙伴問我：加了索引，SQL查詢效率一定會提升嗎？

答案是否定的。

讓我想起了幾年前查詢訂單的場景。

優化前下面這條SQL的查詢耗時是0.5s。

SELECT * FROM orders WHERE user_id = 10086;

添加了下面的索引：

CREATE INDEX idx_user ON orders(user_id);

再次執行上面的查詢SQL語句，此時的耗時卻是5.2s。

出現了非常神奇的一幕：加了索引，SQL查詢性能反而慢了10倍。

作為一個踩過無數數據庫性能坑的老司機，今天跟大家一起聊聊那些“加了索引反而更慢”的詭異場景。

1. 索引失效

加了索引之后，你以為它在工作，其實它在摸魚，因為它可能已經失效了。

1.1 最左前綴原則

如果查詢條件的順序不對，努力白費。

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

用戶表創建了名稱、年齡和城市這三個字段的復合索引：

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;

? 失效：跳過了最左列 name

SELECT * FROM user WHERE age = 30;

? 失效：未使用最左前綴

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

原理：復合索引的存儲結構類似于電話簿（先按姓排序，再按名排序）。

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

1.2 函數操作

函數操作會讓索引瞬間失憶。

對索引列做計算、函數轉換或類型轉換，會導致索引失效：

? 失效：對索引列使用函數：

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

? 失效：隱式類型轉換 (phone 是 varchar)

SELECT * FROM user WHERE phone = 13800138000;

? 有效：避免函數操作

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

可以使用BETWEEN...AND查詢時間范圍。

原理：索引存儲的是列的原始值。對值進行修改后，數據庫無法在索引樹中定位原始值。

1.3 范圍查詢

范圍查詢（>、<、BETWEEN）會截斷復合索引中后續列的匹配：

索引 (age, salary)

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

會導致salary的索引失效。

原理：age>25 匹配到的是一系列值（非精確值），數據庫無法高效地對 salary 進行索引過濾。

2. 索引維護成本

天下沒有免費的午餐，索引是有額外的維護成本的。

2.1 DML 操作變慢

每一次寫入都可能在負重前行。

每次 INSERT、UPDATE、DELETE 操作，數據庫不僅要修改數據，還要維護相關索引。

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

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

場景：在寫多讀少的高并發場景下（如流水記錄），索引反而會成為性能瓶頸。

2.2 索引占用空間

索引可能會給磁盤和內存帶來雙重壓力。

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

MySQL中可以通過下面的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';

后果：索引過大導致內存中緩存命中率降低，物理 I/O 增加。

3. 優化器的“錯誤”選擇

有時候，可能會出現聰明反被聰明誤，加了索引可能會導致性能變慢。

3.1 統計信息過時

導航用了舊地圖。

優化器依賴統計信息（如索引區分度、數據分布）來選擇索引。

如果統計信息過期，優化器可能選擇性能更差的索引。

MySQL中我們可以通過下面的命令強制更新表統計信息：

ANALYZE TABLE user;

場景：當表中數據發生劇烈變化（如大批量刪除/導入）后，統計信息未及時更新。

3.2 回表代價高昂

索引再好也怕“繞路”。

假如給user表給age創建了索引：

SELECT name, email FROM user 
WHERE age > 25;

即使 age 索引被使用，數據庫仍需根據索引中的主鍵 ID 回表查詢name、email 字段。

當符合條件的數據量很大時，回表 I/O 可能遠超索引掃描本身。

優化方案：使用覆蓋索引（Covering Index），讓索引包含查詢所需的所有列。

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

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

查詢可直接從索引獲取數據，無需回表

SELECT name, email FROM user WHERE age > 25;

4. 索引過多

當表上存在多個索引時，優化器需要評估每個索引的成本，選擇越多，決策時間越長。

下面的查詢可能使用索引 A 或索引 B

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

優化建議：

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

5. 鎖與并發

鎖的競爭是一個看不見的戰場。

5.1 行鎖升級

在事務中通過索引檢索并鎖定行時，如果鎖數量過多（超過閾值），數據庫可能將鎖升級為表鎖，嚴重降低并發性能。

場景：全表更新或刪除大量數據時，索引的存在可能導致鎖升級。

5.2 索引分裂的阻塞

B+樹索引在插入數據時可能發生頁分裂。

這個過程需要加鎖，在高并發寫入場景下可能導致短暫阻塞。

總結

索引是把雙刃劍，用對場景是關鍵。

• 理解原理：掌握最左前綴、索引失效條件、覆蓋索引等核心機制
• 權衡成本：在寫密集場景謹慎添加索引，評估維護代價
• 精準設計：按實際查詢模式設計復合索引，避免冗余
• 關注統計信息：定期更新統計信息，確保優化器決策準確
• 監控分析：使用 EXPLAIN、慢查詢日志等工具持續跟蹤索引效果

某電商平臺曾因在流水表上盲目添加索引，導致高峰時段寫入延遲飆升。

后經分析，移除兩個非核心索引，寫入速度提升8倍，而相關查詢僅增加20毫秒——這正是索引取舍的藝術。

記住：索引不是越多越好，而是越準越好。

真正的高手，懂得在索引的利刃上優雅行走。