一次 MySQL 慢查詢事故深度復盤：前綴索引導致「局部全表掃描」

事故現場

• 表：analysis_sessions (百萬級行數)
• 列：cid VARCHAR(50)，建有普通索引 idx_cid (cid)
• 現象：接入微信公眾號後，帶特定前綴 mid-qqwanggou001- 的 cid 查詢變慢 **幾十倍**，普通 cid 查詢正常。

現象分析：EXPLAIN 對比

慢查詢 (微信 cid)

EXPLAIN SELECT * FROM analysis_sessions
WHERE cid = 'sid-a2f9047ddf528d837e5f60843c83aae9';
+----+-------------+-------------------+------+---------------+----------+---------+------+------+-------------+
| id | select_type | table             | type | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | analysis_sessions | ref  | idx_cid       | idx_cid  | 27      | const|    1 | Using where |
+----+-------------+-------------------+------+---------------+----------+---------+------+------+-------------+

• rows=1 → 存儲引擎精準定位到 1 行，極快。

根因溯源：key_len=27 揭示前綴索引

• cid VARCHAR(50)，字符集 gbk (雙字節)。
• InnoDB 對長字符串列自動創建**前綴索引**，默認長度 768 字節 / 字符集字節數 ≈ **256 字符 (utf8mb3) 或 128 字符 (gbk)**，但實際受 innodb_large_prefix、頁面大小影響，本案例實際索引長度為 **27 字節**。
• 推算：27 = 2(字節/字符) × 12(字符) + 2(長度前綴) + 1(NULL標誌) → **僅索引前 12 個字符**。

微信 cid 前綴長度

SHOW INDEX FROM analysis_sessions WHERE Key_name='idx_cid';
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| analysis_sessions |          1 | idx_cid  |            1 | cid         | A         |      1650000|       12 | NULL   | YES  | BTREE      |         |               |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

• Sub_part = 12 → **確認為前綴索引，長度 12 字符**。

解決方案對比

最佳實踐：防患於未然

1. 業務 ID 設計原則：高區分度前綴 (或無前綴)。避免「業務標識前綴 + 隨機後綴」模式，改為「隨機前綴 + 業務標識後綴」或純隨機。
2. 索引定義顯式化：ADD INDEX idx_cid (cid(30)) 顯式指定長度覆蓋業務前綴，避免依賴默認前綴索引。
3. 字符集統一 utf8mb4：gbk 等雙字節字符集加劇前綴索引長度受限，utf8mb4 4 字節/字符雖佔空間但配合 innodb_large_prefix=ON (5.6.3+ 默認 ON) 可支持 3072 字節索引前綴。
4. 上線前 EXPLAIN 驗收：核心查詢必須驗收 key_len 覆蓋業務區分度、rows=1 (或極小)、無 Using filesort/Using temporary。
5. 監控告警：Innodb_rows_read 突增、Handler_read_rnd_next 異常 → 觸發慢查詢告警 → 自動 EXPLAIN 分析入庫。

總結

這次事故的核心教訓：**「索引被用上了」≠「索引用對了」**。
key_len 是看穿前綴索引陷阱的「X 光」，Sub_part 是確診書。
業務 ID 設計應**拒絕低區分度前綴**，索引定義應**顯式覆蓋業務區分度**，上線驗收應**讀懂 EXPLAIN 三大核心列**。
將這三條刻入團隊開發規範，才能從根源上杜絕「局部全表掃描」類性能事故。