当前位置：首页 > news >正文

MySQL索引诊断实战：从SHOW INDEX结果解读到性能优化

news 2026/7/14 11:01:27

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;

报警阈值建议：