MySQL Cardinality 異常導致索引失效：一條 ANALYZE TABLE 救活業務

MySQL Cardinality 異常導致索引失效：一條 ANALYZE TABLE 救活業務

早高峰突發報警：PHP-FPM 進程數飆升，根因是一條 SQL 突然走全表掃描。排查發現 Cardinality 統計值異常（約為真實行數 2 倍），導致優化器誤判索引代價。執行 ANALYZE TABLE 重算統計後秒級恢復。本文還原排查全程並總結 Cardinality 機制與維護策略。

現場還原

-- 慢 SQL
SELECT id FROM feed_comment_info_id_0000 WHERE obj_id=101 AND type=1;

-- 索引信息
SHOW INDEX FROM feed_comment_info_id_0000;
+---------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| feed_comment_info_id_0000 | 0          | PRIMARY  | 1            | id          | A         | 6216        | NULL     | NULL   |      | BTREE      |         |
| feed_comment_info_id_0000 | 1          | obj_type | 1            | obj_id      | A         | 6216        | NULL     | NULL   |      | BTREE      |         |
| feed_comment_info_id_0000 | 1          | obj_type | 2            | type        | A         | 6216        | NULL     | NULL   | YES  | BTREE      |         |
| feed_comment_info_id_0000 | 1          | user_id  | 1            | user_id     | A         | 6216        | NULL     | NULL   |      | BTREE      |         |
+---------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Cardinality = 6216 但實際表行數約 3000，值約為真實 2 倍。EXPLAIN 顯示走主鍵掃描而非 obj_type 索引。

Cardinality 核心機制

• 定義：索引中不重複值的估算數量。MyISAM 為精確值，InnoDB 為統計估算值（每次 SHOW INDEX 可能不同）。
• 影響：Cardinality 越大 → 區分度越高 → 優化器越傾向使用該索引。
• 初始值：建索引時 MyISAM 為 NULL，InnoDB 約為表行數。
• 更新方式：ANALYZE TABLE tbl 或 mysqlcheck -Aa 全庫更新。
• 觸發自動更新：InnoDB 表修改超過 10% 或 innodb_stats_auto_recalc=ON 時後台異步重算。

根因分析

1. 大批量寫入/刪除導致統計信息過期，Cardinality 偏離真實分佈。
2. 優化器根據錯誤統計認為主鍵掃描代價更低，放棄二級索引。
3. 全表掃描導致查詢耗時從毫秒級飆升至秒級，阻塞連接池，引發 PHP-FPM 進程堆積。

解決與預防

即時修復

ANALYZE TABLE feed_comment_info_id_0000;

執行後 Cardinality 恢復正常，索引重新生效，業務秒級恢復。

長效機制

1. 開啟自動重算：SET GLOBAL innodb_stats_auto_recalc=ON;（默認已開）。
2. 調整采樣頁數：innodb_stats_persistent_sample_pages（默認 20，大表可調大提高精度）。
3. 持久化統計：innodb_stats_persistent=ON（默認開），避免重啟丟失統計。
4. 定時任務兜底：核心業務表每日低峰期跑 ANALYZE TABLE 或 mysqlcheck -Aa。
5. 監控告警：監控 Cardinality 與真實行數偏差比，超過閾值（如 2 倍）報警。

總結

Cardinality 是優化器選擇索引的關鍵輸入。InnoDB 統計為估算值，大批量變更易失真。建立「自動重算+定時兜底+偏差監控」三層防線，避免統計失真引發索引失效風暴。