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 ENGINEInnoDB;上周处理的一个案例某表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只有4int类型长度而联合索引总长度应该是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_lengthdata_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值。