MySQL索引诊断实战：从SHOW INDEX结果解读到性能优化
1. 为什么需要关注SHOW INDEX结果
当你发现MySQL查询突然变慢时，第一反应可能就是"加个索引试试"。但实际情况往往更复杂——已有的索引可能根本没被使用，或者使用方式不对。我遇到过不少案例，数据库里堆了十几个索引，查询速度却越来越慢。这时候SHOW INDEX就是你的手术刀，能精准解剖索引问题。
上周排查的一个电商系统案例就很典型：订单表有2000万数据，用户反馈历史订单查询要15秒才能返回。打开SHOW INDEX FROM orders一看，发现有个联合索引的Cardinality值异常低，只有个位数。这意味着这个索引的区分度几乎为零，就像用"性别"字段查人一样低效。
2. SHOW INDEX结果深度解析
2.1 关键字段解读指南
执行SHOW INDEX FROM your_table会返回这样的结构（以MySQL 8.0为例）：
mysql> SHOW INDEX FROM employees; +-----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees | 0 | PRIMARY | 1 | emp_no | A | 299512 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name | 1 | first_name | A | 1275 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name | 2 | last_name | A | 2774 | NULL | NULL | YES | BTREE | | | +-----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+需要特别关注的几个黄金指标：
Cardinality（基数）：这个值表示索引列中不重复值的预估数量。基数与表行数的比值越接近1，索引效果越好。比如主键的基数就等于表总行数。
Collation（排序规则）：A表示升序，当你看到
A DESC这样的混合排序时，要注意排序方向是否与你的ORDER BY子句匹配。Sub_part（前缀索引长度）：如果是字符串字段的前缀索引，这里会显示截取的长度。我曾见过用前5个字符做用户名索引导致大量冲突的情况。
2.2 隐藏的陷阱字段
有些字段容易被忽略但很关键：
Null字段：显示为YES时，说明该列允许NULL值。要注意NULL值不会被计入普通索引，这也是为什么
WHERE col IS NULL有时不走索引。Packed字段：显示索引是否被压缩。使用压缩索引虽然节省空间，但会增加CPU开销。
Index_type：最常见的是BTREE，但全文索引会是FULLTEXT。去年我遇到一个性能问题，就是因为开发者在普通字段上误建了FULLTEXT索引。
3. 实战诊断：从SHOW INDEX发现问题
3.1 识别低效索引
通过这个SQL可以快速找出低效索引：
SELECT table_name, index_name, column_name, cardinality, table_rows, ROUND(cardinality/table_rows*100,2) AS selectivity FROM information_schema.statistics JOIN information_schema.tables USING (table_schema, table_name) WHERE table_schema = 'your_db' AND table_rows > 0 ORDER BY selectivity;经验阈值：
- 选择性<5%：考虑删除或重建索引
- 5%-30%：中等效果，看业务场景决定
30%：优质索引
3.2 揪出重复索引
重复索引不仅浪费空间，还会降低写入性能。用这个查询检测：
SELECT a.table_name, a.index_name AS duplicate_index, b.index_name AS dominant_index, a.column_name FROM information_schema.statistics a JOIN information_schema.statistics b ON a.table_schema = b.table_schema AND a.table_name = b.table_name AND a.seq_in_index = b.seq_in_index AND a.column_name = b.column_name WHERE a.table_schema = 'your_db' AND a.index_name != b.index_name AND a.seq_in_index = 1 ORDER BY a.table_name, a.index_name;常见模式是既有单列索引又有包含该列的联合索引，通常可以删除单列索引。
4. 性能优化实战方案
4.1 索引碎片整理
当Cardinality值明显低于预期时，可能是索引碎片导致。执行这两个操作：
-- 更新统计信息 ANALYZE TABLE your_table; -- 重建索引（InnoDB） ALTER TABLE your_table ENGINE=InnoDB;上周处理的一个案例：某表Cardinality从320万降到87万，重建后查询速度从2.1秒降到0.3秒。
4.2 联合索引优化技巧
通过SHOW INDEX的seq_in_index字段，可以分析联合索引的列顺序是否合理。黄金法则：
- 高区分度列放前面
- 等值查询列优先于范围查询列
- 经常排序的列要包含在索引中
比如看到这样的索引：
idx_mixed (gender, birth_date, salary)明显不合理，因为gender区分度太低。应该调整为：
idx_mixed (birth_date, salary, gender)5. 高级技巧：结合执行计划分析
SHOW INDEX只是诊断的第一步，结合EXPLAIN才能完整分析。重点关注：
- possible_keys和key的差异：为什么没走预期的索引？
- key_len：实际使用的索引长度，判断是否用到索引的全部部分
- Extra列：是否出现Using filesort或Using temporary
一个真实案例：某查询执行计划显示用了索引，但key_len只有4（int类型长度），而联合索引总长度应该是8。这说明只用了联合索引的第一列，调整查询条件后性能提升6倍。
6. 自动化监控方案
建议定期运行以下监控脚本：
SELECT table_schema, table_name, index_name, ROUND(data_length/1024/1024,2) AS size_mb, ROUND(data_free/1024/1024,2) AS free_mb, ROUND(data_free/(data_length+data_free)*100,2) AS frag_ratio FROM information_schema.tables JOIN information_schema.statistics USING (table_schema, table_name) WHERE table_schema NOT IN ('mysql','information_schema') AND data_free > 0 ORDER BY frag_ratio DESC LIMIT 10;报警阈值建议：
- 碎片率>30%：需要立即优化
- 碎片率>15%：列入维护计划
- 索引大小超过数据大小50%：检查索引合理性
7. 避坑指南
这些年踩过的坑总结：
前缀索引陷阱：用前N个字符做索引时，一定要测试区分度。曾见过用手机号前7位做索引，结果10万用户中有8万重复。
外键索引遗漏：创建外键不会自动建索引，导致级联更新变慢。
JSON字段索引：MySQL 8.0支持JSON字段索引，但要注意只对完整路径生效。
隐式类型转换：varchar字段用数字查询会导致索引失效，SHOW INDEX能看到但实际用不上。
统计信息过时：大表批量操作后，记得手动执行ANALYZE TABLE更新Cardinality值。