前言

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

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

當顧客說："我要最便宜的川菜"。

你會：

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

或者：

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

這就是MySQL優化器的日常決策！

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

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

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

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

給status字段創建了索引idx_status。

給create_time字段創建了索引idx_create_time。

查詢訂單的sql如下：

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

周一執行計劃如下：

使用索引：idx_status（狀態索引）  
掃描行數：500行  
耗時：0.1秒

周二執行計劃如下：

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

周一只掃描了500行數據，而周二卻掃描了50萬行數據。

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

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

讓我們拆解背后的原因。

2.揭秘優化器的"決策三步曲"

MySQL優化器的決策流程如下：

成本計算示例：

根據掃描行數、回表次數、排序成本，計算一個總成本的分數。

優化器會選擇總成本更低的idx_create_time索引。

3.導致索引切換的四大真兇

真兇1：數據分布變化

場景還原：

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

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

可能會影響總成本的分數。

我們可以通過下面的SQL查看數據分布：

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

真兇2：統計信息過期

統計信息過期，就像用去年的地圖導航，新修的路不會出現在地圖上。

MySQL的"地圖"就是統計信息。

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

ANALYZE TABLE orders DELETE STATISTICS;

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

EXPLAIN SELECT...

顯示type: ALL

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

使用ANALYZE TABLE命令，刷新統計信息（相當于更新地圖）：

ANALYZE TABLE orders;

真兇3：索引覆蓋度差異

點餐類比：

• 菜單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：索引碎片化

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

檢查方法：

SHOW TABLE STATUS LIKE 'orders';

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

優化方案：

使用ALTER TABLE命令重建索引。

ALTER TABLE orders ENGINE=INNODB;

4.問題排查四步法

第一步：查看當前執行計劃

使用EXPLAIN查看當前SQL的執行計劃：

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

第二步：檢查統計信息

使用SHOW INDEX命令檢查索引的統計信息：

SHOW INDEX FROM orders;

關注Cardinality字段，值越接近真實數據越好。

第三步：分析數據分布

使用下面的SQL分析數據分布:

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

第四步：追蹤優化器思考過程

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

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

5.三大終極解決方案

方案1：引導優化器選擇

使用FORCE INDEX強制使用指定索引：

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

方案2：創建更優索引

創建更優的聯合索引：

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

方案3：定期維護計劃

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

總結

六個必須檢查的點

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

三條黃金法則

1. 二八定律：20%的索引滿足80%的查詢
2. 數據驅動：定期分析查詢模式調整索引
3. 防御編程：核心查詢明確指定索引