優化器為什麼選錯索引：它不是壞掉，它是在算一筆錯的帳

「優化器選錯索引」這句話其實不太準確。優化器沒有選錯——它非常忠實地執行了一個成本計算，只是那個計算的輸入是錯的。把這件事想清楚，後面所有的處理方式才有邏輯。

它實際在做的事

為什麼它不乾脆去實測一次

因為那就跟真的執行一樣貴了。優化本身必須遠比執行便宜，否則優化就沒有意義——一句 SQL 花 3 毫秒選計畫、5 毫秒執行是划算的；花 200 毫秒選計畫就荒謬了。

估算的不精確不是實作缺陷，是這個設計的必然代價。你要做的不是期待它變準，而是知道它什麼時候會不準、以及不準的時候怎麼辦。

它也不保證挑到全域最佳

可行計畫的數量會隨著 JOIN 的表數量爆炸性成長（n 張表的排列有 n! 種）。所以優化器有搜尋深度上限（optimizer_search_depth），表一多就改用貪婪搜尋，只找「夠好」的計畫。

-- 這兩個旋鈕平常不要動，但知道它們存在，才知道「表很多的 JOIN 為什麼計畫怪怪的」
SELECT @@optimizer_search_depth;   -- 預設 62（超過就用啟發式）
SELECT @@optimizer_prune_level;    -- 預設 1（提早剪枝）

所以在多表 JOIN 的場景，「優化器沒選到那個明顯更好的順序」是有可能的，而且它連考慮都沒考慮過。

優化器算出來的成本是一個沒有單位的相對分數，不是毫秒。它只用來比大小。而它的組成很單純，就兩類：

• I/O 成本：要讀幾個頁（block）
• CPU 成本：要比對幾列

成本常數是可以查的

MySQL 8.0 把成本常數放在兩張系統表裡，不是寫死在程式碼裡：

SELECT cost_name, default_value FROM mysql.server_cost;
-- row_evaluate_cost            0.1    每比對一列的 CPU 成本
-- memory_temptable_create_cost 1.0
-- disk_temptable_create_cost  20.0    磁碟暫存表貴 20 倍（ch04 會用到）

SELECT cost_name, default_value FROM mysql.engine_cost;
-- memory_block_read_cost      0.25    從 Buffer Pool 讀一頁
-- io_block_read_cost          1.0     從磁碟讀一頁

看到實際的成本數字

EXPLAIN FORMAT=JSON 會把成本攤開來：

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 1\G

"cost_info": {
  "query_cost": "203847.25"
},
"table": {
  "access_type": "ALL",
  "rows_examined_per_scan": 1998432,
  "filtered": "10.00",
  "cost_info": {
    "read_cost": "3987.25",
    "eval_cost": "199860.00",       ← 1998432 × 0.1
    "prefix_cost": "203847.25"
  }
}

這份輸出的用法不是「看它幾分」，而是拿兩個計畫的分數比大小：想知道優化器為什麼不選你以為的那個索引，就用 FORCE INDEX 逼它選一次，再比較兩邊的 query_cost。差距在哪一項（read_cost 還是 eval_cost）通常一眼就看得出來。

成本模型的三個內建假設

它算得再仔細，前提也只有這幾條，而每一條都可能與現實不符：

• 假設資料不在記憶體——8.0 之後雖然會參考 Buffer Pool 的命中狀況，但仍不精確。一張早就整個被快取住的表，實際讀取成本遠低於估算。
• 假設隨機 I/O 與順序 I/O 的比例是固定的——這在機械硬碟時代是對的，在 NVMe SSD 上差距小很多。（SSD 環境可以考慮調低 io_block_read_cost，但那是全域影響，動之前要有量測。）
• 假設欄位之間互相獨立——這條是最常出問題的。WHERE city = '台北' AND zipcode = '110'，優化器會把兩個條件的選擇度相乘，但這兩個欄位其實高度相關，實際命中列數會遠大於估算。

成本估算的輸入是統計資訊，而統計資訊的核心是每個索引的 cardinality：這個索引裡有幾個不重複的值。

它是採樣出來的

InnoDB 不會為了統計去掃全表，它隨機抓幾頁索引頁，數一數不重複值，再依比例放大到整張表：

SELECT @@innodb_stats_persistent_sample_pages;   -- 預設 20

統計資訊存在哪、看得到嗎

8.0 預設是持久化統計（innodb_stats_persistent=ON），存在系統表裡，重啟不會消失：

SELECT index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE table_name = 'orders';
-- idx_merchant_status  n_diff_pfx01   8213     ← 第 1 個欄位有幾個不重複值
-- idx_merchant_status  n_diff_pfx02   16401    ← 前 2 個欄位的組合有幾個
-- idx_merchant_status  n_leaf_pages   40125

SHOW INDEX FROM orders;   -- Cardinality 欄位就是同一份資料

n_diff_pfx01、pfx02 這種前綴統計，正是優化器判斷「複合索引用到第幾個欄位還有多少過濾力」的依據。

什麼時候會重算

• 自動：innodb_stats_auto_recalc=ON（預設），當表變動的列數超過總列數的 10% 時，背景執行緒會重算。
• 手動：ANALYZE TABLE orders;
• 其他觸發點：ALTER TABLE、加索引、SHOW TABLE STATUS 之類的操作也可能帶動更新。

採樣越多越準，但不是免費的

-- 針對特定大表提高採樣頁數（只影響這張表）
ALTER TABLE orders STATS_SAMPLE_PAGES = 200;
ANALYZE TABLE orders;

代價是每次重算要讀更多頁，時間變長。對「資料分佈極不平均、又常出問題」的大表值得調高；不要全庫一起調——這是第二條主線「代價守恆」的第一次現身：你買到的每一分準確度，都在別的地方付了錢。

統計資訊最後會被換算成一個很簡單的比值——選擇度（selectivity）：

選擇度 = 不重複值的數量 / 總列數

user_id（200 萬個不同值 / 2000 萬列）    = 0.1      高
created_at（幾乎每列不同）               ≈ 1.0      最高
status（5 個值 / 2000 萬列）             = 0.00000025  極低
is_deleted（2 個值）                     ≈ 0         最低

推導：為什麼低選擇度的索引沒有用

假設 status=1 佔了全表的 40%，也就是 800 萬列。走 idx_status 的話：

掃索引葉節點找出 800 萬個主鍵     → 讀約 12,000 頁
拿每個主鍵回表                    → 800 萬次隨機 I/O
合計 ≈ 8,012,000 次 I/O

全表順序掃描                      → 65,000 頁順序讀（還有預讀加持）

差了兩個數量級，而且是索引比較慢。優化器選全表掃描是完全正確的判斷。

那 status 這種欄位就不能建索引嗎

可以，但要建在對的地方——放進複合索引，而且不是放第一個：

-- ❌ 幾乎沒用
KEY idx_status (status)

-- ✅ 有用：高選擇度的欄位在前，先把範圍縮小到幾十列，
--    status 只是在這幾十列裡再篩一次
KEY idx_m_s_c (merchant_id, status, created_at)

還有一種例外值得記住：資料極度傾斜的欄位。例如 status=9（處理失敗）只佔全部的 0.01%，那查 status=9 走索引是非常划算的——問題是同一個索引，查 status=1 該用全表掃描，查 status=9 該走索引。這正是「同一句 SQL 換個參數值就該換計畫」的來源，也是下一節直方圖要解決的事。

cardinality 只回答了「有幾個不重複值」，它假設每個值出現的次數一樣多。現實中幾乎從來不是這樣：訂單狀態裡「已完成」可能佔 95%，「待退款」佔 0.01%。

MySQL 8.0 引進直方圖（histogram）來補這一塊：它記錄的是值的實際分佈。

怎麼建

ANALYZE TABLE orders UPDATE HISTOGRAM ON status, order_type WITH 32 BUCKETS;

-- 看內容
SELECT histogram FROM information_schema.column_statistics
WHERE table_name = 'orders' AND column_name = 'status'\G

-- 不要了
ANALYZE TABLE orders DROP HISTOGRAM ON status;

桶數（buckets）預設 100、最大 1024。不重複值少於桶數時會用 singleton（每個值一個桶，最精確）；否則用 equi-height（每桶列數相近）。

它改變的是 filtered，不是 rows

這是最需要弄清楚的一點。回到 ch01 的欄位定義：rows 是這一步要掃幾列，filtered 是掃完之後估計剩幾成。直方圖影響的是 filtered。

-- 沒有直方圖：優化器假設均勻分佈，5 個狀態 → filtered ≈ 20.00
EXPLAIN SELECT * FROM orders WHERE status = 9;
-- rows=1998432  filtered=20.00  → 估計 40 萬列

-- 有直方圖：它知道 status=9 只佔 0.01%
-- rows=1998432  filtered=0.01   → 估計 200 列

估算從 40 萬變成 200，優化器對後續的每一個決策都會跟著改——要不要走索引、JOIN 的驅動表選誰、要不要建暫存表。這在 JOIN 場景的影響尤其大（ch03）。

代價：它不會自己更新

所以建直方圖的同時就要決定「誰負責重建」：跟著批次流程走、或排一個週期性維護。不維護的直方圖比沒有直方圖更危險，因為沒有直方圖時優化器至少知道自己在猜。

這是實務上最常見、也最反直覺的一個計畫翻車場景。先看現象：

-- 這句：8 秒
SELECT * FROM orders WHERE merchant_id = 8821 AND status = 1
ORDER BY created_at DESC LIMIT 20;

-- 把 LIMIT 拿掉或改大：0.03 秒
SELECT * FROM orders WHERE merchant_id = 8821 AND status = 1
ORDER BY created_at DESC;

優化器的推理過程

它不是隨機犯錯的，它的推理其實很有道理：

它錯在哪

第 3 步的「應該很快湊滿」建立在一個假設上：符合條件的資料在時間軸上是均勻分佈的。

怎麼確認是這個問題

• EXPLAIN 顯示 key 是排序欄位的索引而不是過濾欄位的索引，Extra 沒有 Using filesort（它「成功」避開了排序）。
• 把 LIMIT 拿掉，計畫換回過濾索引，而且變快——這個對照實驗基本上就能定案。
• EXPLAIN ANALYZE 看那個節點的 actual rows：估算幾百，實際上千萬。

三種解法，從治本到止血

-- ① 治本：一個索引同時滿足過濾與排序（WHERE 等值在前，ORDER BY 欄位在後）
ALTER TABLE orders ADD KEY idx_m_s_c (merchant_id, status, created_at);
-- 代價：多一個索引 → 寫入變慢、空間變大（代價守恆）

-- ② 明確告訴優化器不要為了排序換索引（8.0.21+）
SET SESSION optimizer_switch = 'prefer_ordering_index=off';

-- ③ 針對單句下 hint（8.0）
SELECT /*+ NO_INDEX(orders idx_created_at) */ ...

優先做 ①。②③ 是還沒辦法改結構時的止血手段——它們讓這一句好了，但同一張表其他查詢的同類問題還在。

徵狀

商家後台的訂單列表 API，平常穩定在 30 毫秒。某天早上九點開始，監控顯示 p99 衝到 8 秒，客服電話進來了。

• 程式碼沒有部署，索引沒有異動，DB 參數沒有改。
• 只有一件事發生過：凌晨三點的批次匯入了 300 萬列歷史訂單（表原本 1,700 萬列）。
• 更怪的是——某些商家很快，某些商家很慢，而慢的那些用同一個 API、同一句 SQL。

診斷

照 ch01 的順序走。先拿線上真正慢的那組參數去看計畫（這點很關鍵，不同參數值可以走出不同計畫）：

-- 快的商家
EXPLAIN SELECT * FROM orders WHERE merchant_id = 101 AND status = 1
        ORDER BY created_at DESC LIMIT 20;
-- key=idx_merchant_status  type=ref  rows=180  Extra=Using where; Using filesort

-- 慢的商家（同一句 SQL，只換參數）
EXPLAIN SELECT * FROM orders WHERE merchant_id = 8821 AND status = 1
        ORDER BY created_at DESC LIMIT 20;
-- key=idx_created_at  type=index  rows=214  Extra=Using where

兩件事同時發生了：換了索引，而且估算只有 214 列。用 EXPLAIN ANALYZE 對照實測：

-> Limit: 20 row(s)  (cost=1250 rows=20) (actual time=8402..8402 rows=20 loops=1)
   -> Filter: (merchant_id = 8821 and status = 1)
      (cost=1250 rows=214) (actual time=8402..8402 rows=20 loops=1)
      -> Index scan on orders using idx_created_at (reverse)
         (rows=214) (actual rows=14203871 loops=1)   ← 估 214，實掃 1,420 萬

根因（三件事疊在一起）

修法（依序）

怎麼提早知道，而不是等客服電話

-- 這句 SQL 最近用過哪些索引、各自跑了多久
SELECT * FROM sys.statements_with_full_table_scans WHERE db = 'shop'\G

-- 每支查詢的平均延遲與呼叫次數（ch07 主講）
SELECT digest_text, count_star, avg_timer_wait/1e9 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;

更直接的一招：把關鍵查詢的執行計畫寫進整合測試，斷言 key 必須等於預期的索引。計畫是可以被測試的資產，不是只能事後追查的黑盒。

知道優化器會選錯之後，最直覺的反應是：那我直接叫它用哪個索引不就好了。可以，但這是一筆有利息的交易。

三種介入強度

-- 建議（優化器仍可能不理）
SELECT * FROM orders USE INDEX (idx_m_s_c) WHERE ...;

-- 排除某個索引，其餘讓它自己選 —— 副作用最小的一種
SELECT * FROM orders IGNORE INDEX (idx_created_at) WHERE ...;

-- 強制（幾乎等同於「不准全表掃描」）
SELECT * FROM orders FORCE INDEX (idx_m_s_c) WHERE ...;

-- MySQL 8.0 的 optimizer hint，寫在註解裡
SELECT /*+ INDEX(orders idx_m_s_c) */ ...;
SELECT /*+ NO_INDEX(orders idx_created_at) */ ...;
SELECT /*+ JOIN_ORDER(o, u) */ ...;
SELECT /*+ MAX_EXECUTION_TIME(2000) */ ...;

hint 為什麼比 FORCE INDEX 好一點

• 不支援時被當成註解忽略，不會讓語句直接失敗——版本升級、換 DB 時比較安全。
• 粒度更細：可以只干預 JOIN 順序、只排除某個索引、只設定這一句的逾時。
• 可以指定作用在哪張表、哪個查詢區塊，不像 FORCE INDEX 綁在表名後面。

代價（這是重點）

• ② 索引改名或刪除，查詢直接報錯。FORCE INDEX 指定的索引不存在時是 ERROR 1176 (42000): Key 'idx_xxx' doesn't exist in table——不是退回自動選擇，是整句失敗。有人清理「沒用到的索引」時就會踩到（因為它在監控上看起來確實沒被用到多少）。
• ③ 它會掩蓋真正的問題。統計資訊過期是可以修的；加了 hint 之後症狀消失，那個根因就留在系統裡，換一句 SQL 再爆一次。
• ④ 在 ORM 裡很難維護。要用 hint 通常得改寫成 native query，於是就失去了型別安全與可組合性（回扣 ch01 那個 nativeQuery 的失敗場景——那正是這樣來的）。

什麼時候該用

它是止血手段，不是解法。合理的使用情境只有這幾種：

• 線上正在燒，需要五分鐘內恢復——先加 hint，事後補治本。
• 已經確認資料分佈就是極度傾斜，而且短期內不會改變，統計資訊再怎麼調都救不了。
• 加了 hint 的同時，在程式碼裡留下註解寫清楚「為什麼、什麼條件下可以拿掉」。沒有這行註解，三個月後沒有人敢動它。

這一章給了你一整排旋鈕：採樣頁數、直方圖、optimizer_switch、hint、成本常數。而這一節要說的是什麼時候不要碰它們。

三個「先不要動」

• 只有一句 SQL 慢的時候，不要動全域參數。調 io_block_read_cost、關 optimizer_switch 的某個開關，影響的是整個實例上的每一句查詢。為了一句 SQL 讓其他一千句的計畫重新洗牌，是把一個局部問題換成一個全域風險。
• 沒有量測就不要調成本常數。「我們用 SSD，所以隨機讀沒那麼貴」聽起來合理，但你要能拿出改前改後的關鍵查詢延遲對照，否則就只是換一組沒有根據的猜測。
• 建了直方圖就要有人維護。它不會自動更新（上面說過）。沒有維護計畫的直方圖，是一個會慢慢腐爛的錯誤來源。

統計資訊維護本身的代價

• ANALYZE TABLE 通常很快（它只採樣不掃全表），但它需要取得 metadata lock。如果當下有長交易占著這張表，ANALYZE 會排隊等待——而排在它後面的所有查詢也會跟著卡住。線上執行前先看一眼 SHOW PROCESSLIST 有沒有長交易。
• 提高 STATS_SAMPLE_PAGES 會讓每次重算讀更多頁；對超大表要衡量重算頻率。
• 統計資訊更新之後，計畫可能立刻改變——通常是變好，但也可能是換一種翻車。所以在正式環境跑 ANALYZE 之後，要記得回頭確認關鍵查詢的計畫。

優化器完全處理不了的三件事

有些問題不管統計資訊多準都不會變好，硬要調優化器只是浪費時間：

• SQL 本身的寫法就要求了昂貴的操作——例如 LIMIT 1000000, 10 的深分頁。無論走哪個索引都得先跳過一百萬列（ch04）。
• 索引根本不存在。優化器只能從現有的索引裡挑，它不會幫你變出一個。Using join buffer 就是這種訊號（ch03）。
• 問題不在單句 SQL。鎖等待、連線池排隊、N+1、複本延遲——計畫再完美也沒用（ch05、ch07、ch08）。

把這一章收成三句

下一章往上走一層：當查詢牽涉到兩張以上的表，這裡的每一個估算誤差都會被乘上被驅動表的查詢次數——測試環境 20 毫秒的 JOIN，正式環境跑 40 秒就是這樣來的。