前言

想象你是一家餐廳的服務(wù)員，面前有兩個菜單：

• 菜單A：按菜品分類排列（前菜、主菜、甜點(diǎn)）
• 菜單B：按價格從低到高排列

當(dāng)顧客說："我要最便宜的川菜"。

你會：

• 先用菜單B找到所有低價菜
• 從中篩選川菜

或者：

• 先用菜單A找到所有川菜
• 再按價格排序

這就是MySQL優(yōu)化器的日常決策！

明明是同一條SQL，有時候走的索引a，而有時候走的索引b，就是它的鍋。

今天這篇文章跟大家一起聊聊，MySQL選錯索引的問題，希望對你會有所幫助。

1.一個讓程序員崩潰的案例

現(xiàn)在有個需求：查詢今年開始已付款的前100個訂單。

給status字段創(chuàng)建了索引idx_status。

給create_time字段創(chuàng)建了索引idx_create_time。

查詢訂單的sql如下：

SELECT * FROM orders 
WHERE status = 'paid'      -- 狀態(tài)條件
AND create_time > '2025-01-01' -- 時間條件
ORDER BY amount DESC 
LIMIT 100;

周一執(zhí)行計劃如下：

使用索引：idx_status（狀態(tài)索引）  
掃描行數(shù)：500行  
耗時：0.1秒

周二執(zhí)行計劃如下：

使用索引：idx_create_time（時間索引）  
掃描行數(shù)：50萬行  
耗時：8秒

周一只掃描了500行數(shù)據(jù)，而周二卻掃描了50萬行數(shù)據(jù)。

周一耗時0.1秒，而周二耗時卻又8秒。

同一SQL在不同時間性能差異80倍！

讓我們拆解背后的原因。

2.揭秘優(yōu)化器的"決策三步曲"

MySQL優(yōu)化器的決策流程如下：

成本計算示例：

根據(jù)掃描行數(shù)、回表次數(shù)、排序成本，計算一個總成本的分?jǐn)?shù)。

優(yōu)化器會選擇總成本更低的idx_create_time索引。

3.導(dǎo)致索引切換的四大真兇

真兇1：數(shù)據(jù)分布變化

場景還原：

• 周一數(shù)據(jù)：已支付訂單5萬條，其中2025年的5萬條
• 周二數(shù)據(jù)：已支付訂單50萬條，其中2025年的50萬條

這個例子中數(shù)據(jù)分布變化很大，周二的數(shù)據(jù)，比周一的數(shù)據(jù)一下子多了45萬。

可能會影響總成本的分?jǐn)?shù)。

我們可以通過下面的SQL查看數(shù)據(jù)分布：

SELECT 
  COUNT(*) AS total,
  SUM(status='paid') AS paid_count,
  SUM(create_time>'2023-01-01') AS new_orders 
FROM orders;

真兇2：統(tǒng)計信息過期

統(tǒng)計信息過期，就像用去年的地圖導(dǎo)航，新修的路不會出現(xiàn)在地圖上。

MySQL的"地圖"就是統(tǒng)計信息。

我們可以通過ANALYZE TABLE ... DELETE STATISTICS命令刪除統(tǒng)計信息：

ANALYZE TABLE orders DELETE STATISTICS;

這時候查詢可能變成全表掃描：

EXPLAIN SELECT...

顯示type: ALL

那么，如何解決這個問題呢？

使用ANALYZE TABLE命令，刷新統(tǒng)計信息（相當(dāng)于更新地圖）：

ANALYZE TABLE orders;

真兇3：索引覆蓋度差異

點(diǎn)餐類比：

• 菜單A能直接看到菜品價格 → 無需問廚師（覆蓋索引）
• 菜單B只能看到菜品名 → 需要問廚師詳情（回表查詢）

下面的SQL會走idx_status（需要回表）：

SELECT * FROM orders WHERE status='paid';

下面的SQL會走idx_create_time（覆蓋索引）：

SELECT create_time FROM 
orders WHERE create_time>'2023-01-01';

真兇4：索引碎片化

索引碎片化就像書本的目錄頁被撕破，找內(nèi)容變得困難。

檢查方法：

SHOW TABLE STATUS LIKE 'orders';

查看Data_free字段，值越大碎片越多。

優(yōu)化方案：

使用ALTER TABLE命令重建索引。

ALTER TABLE orders ENGINE=INNODB;

4.問題排查四步法

第一步：查看當(dāng)前執(zhí)行計劃

使用EXPLAIN查看當(dāng)前SQL的執(zhí)行計劃：

EXPLAIN 
SELECT * FROM orders 
WHERE status='paid' 
AND create_time>'2023-01-01';

第二步：檢查統(tǒng)計信息

使用SHOW INDEX命令檢查索引的統(tǒng)計信息：

SHOW INDEX FROM orders;

關(guān)注Cardinality字段，值越接近真實(shí)數(shù)據(jù)越好。

第三步：分析數(shù)據(jù)分布

使用下面的SQL分析數(shù)據(jù)分布:

SELECT 
  COUNT(*) AS total,
  AVG(LENGTH(status)) AS status_avg_len 
FROM orders;

第四步：追蹤優(yōu)化器思考過程

SET optimizer_trace="enabled=on";
SELECT * FROM orders WHERE ...;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

開啟optimizer_trace，然后通過INFORMATION_SCHEMA.OPTIMIZER_TRACE表查看追蹤優(yōu)化器思考過程。

5.三大終極解決方案

方案1：引導(dǎo)優(yōu)化器選擇

使用FORCE INDEX強(qiáng)制使用指定索引：

SELECT * FROM orders FORCE INDEX(idx_status) WHERE ...;

方案2：創(chuàng)建更優(yōu)索引

創(chuàng)建更優(yōu)的聯(lián)合索引：

ALTER TABLE orders 
ADD INDEX idx_status_create_time(status,create_time);

方案3：定期維護(hù)計劃

1. 定期統(tǒng)計信息更新
2. 定期碎片率檢查
3. 定期索引重建

總結(jié)

六個必須檢查的點(diǎn)

• WHERE條件字段是否有合適索引
• ORDER BY/GROUP BY是否利用索引排序
• 統(tǒng)計信息是否最新（尤其大表每天更新）
• 是否存在索引碎片（每月檢查一次）
• 是否出現(xiàn)索引合并（INDEX_MERGE）
• 是否使用覆蓋索引（減少回表）

三條黃金法則

1. 二八定律：20%的索引滿足80%的查詢
2. 數(shù)據(jù)驅(qū)動：定期分析查詢模式調(diào)整索引
3. 防御編程：核心查詢明確指定索引