索引是雙刃劍：適當索引加速查詢，冗余/低效索引拖慢寫入、佔用空間、誤導優化器。本文提供四條生產可用的 INFORMATION_SCHEMA 掃描 SQL，精準定位「包含主鍵的二級索引」、「重複前綴索引」、「低區分度索引」、「復合主鍵膨脹」四類不良索引。

測試表結構（含典型不良索引）

CREATE TABLE test1 (
  id INT NOT NULL,
  f1 INT DEFAULT NULL,
  f2 INT DEFAULT NULL,
  f3 INT DEFAULT NULL,
  PRIMARY KEY (id),
  KEY k1 (f1, id),      -- 含主鍵
  KEY k2 (id, f1),      -- 含主鍵且主鍵在前
  KEY k3 (f1),          -- 被 k1/k4/k5 前綴覆蓋
  KEY k4 (f1, f3),      -- 被 k5 前綴覆蓋
  KEY k5 (f1, f3, f2)   -- 最大前綴
) ENGINE=InnoDB;

CREATE TABLE test2 (
  id1 INT NOT NULL DEFAULT 0,
  id2 INT NOT NULL DEFAULT 0,
  b INT DEFAULT NULL,
  PRIMARY KEY (id1, id2),  -- 復合主鍵
  KEY k1 (b)               -- 低區分度
) ENGINE=InnoDB;

1. 掃描「包含主鍵列的二級索引」

原理：InnoDB 聚簇索引葉子節點即數據行，二級索引葉子節點存儲主鍵值。顯式在二級索引中包含主鍵列屬於冗余（除非為了覆蓋索引避免回表，但通常不建議）。

SELECT c.*, pk 
FROM (
  SELECT table_schema, table_name, index_name,
         CONCAT('|', GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR '|'), '|') AS cols
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE index_name != 'PRIMARY' AND table_schema != 'mysql'
  GROUP BY table_schema, table_name, index_name
) c
JOIN (
  SELECT table_schema, table_name,
         CONCAT('|', GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR '|'), '|') AS pk
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE index_name = 'PRIMARY' AND table_schema != 'mysql'
  GROUP BY table_schema, table_name
) p
  ON c.table_schema = p.table_schema 
  AND c.table_name = p.table_name
  AND c.cols LIKE CONCAT('%', p.pk, '%');

輸出示例：

+--------------+------------+------------+---------+------+
| table_schema | table_name | index_name | cols    | pk   |
+--------------+------------+------------+---------+------+
| test         | test1      | k1         | |f1|id| | |id| |
| test         | test1      | k2         | |id|f1| | |id| |
+--------------+------------+------------+---------+------+

處理：評估是否為覆蓋索引優化，否則 DROP INDEX k1 ON test1;

2. 掃描「重複前綴索引」（左前綴完全包含）

原理：索引 (a,b,c) 已包含 (a)、(a,b) 的查詢能力，後兩者可刪除。

SELECT c1.table_schema, c1.table_name, c1.index_name, c1.cols, c2.index_name, c2.cols
FROM (
  SELECT table_schema, table_name, index_name,
         CONCAT('|', GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR '|'), '|') AS cols
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema != 'mysql' AND index_name != 'PRIMARY'
  GROUP BY table_schema, table_name, index_name
) c1
JOIN (
  SELECT table_schema, table_name, index_name,
         CONCAT('|', GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR '|'), '|') AS cols
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema != 'mysql' AND index_name != 'PRIMARY'
  GROUP BY table_schema, table_name, index_name
) c2
  ON c1.table_schema = c2.table_schema
  AND c1.table_name = c2.table_name
  AND c1.cols LIKE CONCAT(c2.cols, '%')
  AND c1.index_name != c2.index_name;

輸出示例：

+--------------+------------+------------+------------+------------+---------+
| table_schema | table_name | index_name | cols       | index_name | cols    |
+--------------+------------+------------+------------+------------+---------+
| test         | test1      | k1         | |f1|id|    | k3         | |f1|    |
| test         | test1      | k4         | |f1|f3|    | k3         | |f1|    |
| test         | test1      | k5         | |f1|f3|f2| | k3         | |f1|    |
| test         | test1      | k5         | |f1|f3|f2| | k4         | |f1|f3| |
+--------------+------------+------------+------------+------------+---------+

處理：保留最長前綴（k5），刪除 k1、k3、k4。注意：若 k1 用於覆蓋索引查詢 SELECT id FROM test1 WHERE f1=? 則保留。

3. 掃描「低區分度索引」（Cardinality / Rows < 10%）

原理：優化器預估掃描行數超過表行數一定比例（通常 20%-30%）會放棄索引改走全表掃描。區分度極低的索引幾乎不會被使用，純屬負擔。

SELECT p.table_schema, p.table_name, c.index_name, c.car, p.car AS total
FROM (
  SELECT table_schema, table_name, index_name, MAX(cardinality) AS car
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE index_name != 'PRIMARY'
  GROUP BY table_schema, table_name, index_name
) c
JOIN (
  SELECT table_schema, table_name, MAX(cardinality) AS car
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE index_name = 'PRIMARY' AND table_schema != 'mysql'
  GROUP BY table_schema, table_name
) p
  ON c.table_schema = p.table_schema 
  AND c.table_name = p.table_name
  AND p.car > 0
  AND c.car / p.car < 0.1;  -- 閾值 10%，可調整

輸出示例：

+--------------+------------+------------+------+-------+
| table_schema | table_name | index_name | car  | total |
+--------------+------------+------------+------+-------+
| test         | test2      | k1         | 4    | 49    |
+--------------+------------+------------+------+-------+

處理：結合業務查詢模式確認，若確無用則刪除；若用於 WHERE b=? 且返回極少行，可能仍有價值。

4. 掃描「復合主鍵」（主鍵列數 > 1）

風險：InnoDB 二級索引葉子節點存儲完整主鍵值。復合主鍵導致每個二級索引條目膨脹，增加存儲、內存、I/O 開銷，降低緩存命中率。

SELECT table_schema, table_name,
       GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ',') AS cols,
       MAX(seq_in_index) AS len
FROM INFORMATION_SCHEMA.STATISTICS
WHERE index_name = 'PRIMARY' AND table_schema != 'mysql'
GROUP BY table_schema, table_name
HAVING len > 1;

輸出示例：

+--------------+------------+-----------+------+
| table_schema | table_name | cols      | len  |
+--------------+------------+-----------+------+
| test         | test2      | id1,id2   | 2    |
+--------------+------------+-----------+------+

建議：

• 業務主鍵改為單列自增 BIGINT（代理鍵）
• 原復合主鍵列加 UNIQUE INDEX 保證業務唯一性
• 若業務強依賴復合主鍵（如多租戶分區），評估收益大於成本再保留

自動化巡檢腳本建議

#!/bin/bash
# 每日定時執行，發現異常發送告警
mysql -uroot -p${PWD} -e "
  -- 1. 含主鍵二級索引
  -- 2. 重複前綴索引
  -- 3. 低區分度索引 (閾值 0.1)
  -- 4. 復合主鍵
" | mail -s "MySQL 索引健康巡檢 $(date +%F)" dba@company.com

索引治理流程

1. 掃描發現：定期執行上述 SQL
2. 影響評估：pt-index-usage 分析慢查詢日誌，確認索引是否被真實使用
3. 變更窗口：低峰期 DROP INDEX / ADD INDEX（5.6+ Online DDL）
4. 效果驗證：對比變更前後 QPS、延遲、磁盤佔用
5. 知識庫沉澱：記錄索引治理案例，避免重複建立不良索引