全网最硬核的MySQL索引解析！SHOW INDEX FROM 每个字段都给你说明白！

今天咱们来点实实在在的干货！数据库索引优化是老生常谈的话题，但90%的开发者连索引的体检报告都不会看。不废话，直接上硬菜——手把手教你用 SHOW INDEX FROM 表名 把索引扒个底朝天！

一、命令核心作用（先看疗效）

当你发现SQL查询慢得跟蜗牛一样时，这个命令就是你的听诊器：

1. 透视索引结构：联合索引顺序、索引类型全掌握

2. 揪出问题索引：重复索引、失效索引无处遁形

3. 诊断索引健康：碎片化程度、基数高低一目了然

4. 优化依据：决定是否需要重建索引或调整字段顺序

二、实战演示：从建表到查看索引

1. 先搞个测试表（带各种索引）

CREATE TABLE `user` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `mobile` CHAR(11) UNIQUE COMMENT '手机号',
  `name` VARCHAR(20) NOT NULL,
  `age` TINYINT DEFAULT 0,
  `address` VARCHAR(100),
  INDEX `idx_age_name` (`age`,`name`),
  INDEX `idx_address` (`address`(10)) 
ENGINE=InnoDB;

2. 执行核心命令

SHOW INDEX FROM user;

3. 输出结果（重点来了！）

三、逐字段解剖（建议收藏）

1. Table

• 含义：索引所属的表名

• 注意点：当使用JOIN查询多个表时，可快速区分来源

2. Non_unique

• 含义：是否允许重复值

  • 0：唯一索引（主键、UNIQUE约束）

  • 1：普通索引（允许重复）

• 实战技巧：
  发现Non_unique=0的重复索引可直接删除

3. Key_name

• 含义：索引名称

  • 主键固定为PRIMARY

  • 联合索引多个字段共享同一名称

• 避坑指南：
  通过此字段可快速发现类似idx_age和idx_age_name的冗余索引

4. Seq_in_index

• 含义：字段在索引中的顺序（从1开始）

• 经典场景：
  联合索引idx_age_name中：

  • age顺序=1，name顺序=2

  • 查询时必须先使用age才能触发索引！

5. Column_name

• 含义：索引涉及的字段名

• 高阶用法：
  函数索引会显示表达式，如(CAST(content AS CHAR(30)))

6. Collation

• 含义：字段在索引中的排序方式

  • A：升序（默认）

  • D：降序

  • NULL：非排序类型（如FULLTEXT索引）

• MySQL 8.0+特性：
  支持混合排序索引，如INDEX (a DESC, b ASC)

7. Cardinality

• 含义：索引基数（估算的不同值数量）

• 核心作用：

  • 值越大，索引过滤性越好

  • 计算公式：Cardinality / 表总行数 → 区分度

  • 经验值：
    区分度 < 10% 考虑删除索引

8. Sub_part

• 含义：前缀索引的长度

  • NULL：未使用前缀索引

  • 数字：截取字段前N个字符

• 示例解析：
  idx_address索引的Sub_part=10 → 只对address前10个字符建索引

9. Packed

• 含义：是否压缩索引

  • NULL：未压缩

  • 其他值：显示压缩方式

• 适用场景：
  MEMORY引擎的哈希索引会显示压缩信息

10. Null

• 含义：字段是否允许NULL

  • YES：允许

  • ''：不允许

• 索引设计铁律：
  频繁查询的字段尽量设为NOT NULL，可提升索引效率

11. Index_type

• 含义：索引类型

  • BTREE：B+树索引（默认）

  • HASH：哈希索引

  • FULLTEXT：全文索引

  • SPATIAL：空间索引

• 存储引擎差异：
  InnoDB不支持HASH索引（但会自动转换自适应哈希）

12. Comment

• 含义：索引备注信息

  • 创建索引时通过COMMENT添加

• 实用场景：
  记录索引创建目的，如COMMENT '用于首页年龄筛选'

13. Index_comment

• 含义：索引注释（同COMMENT）

• 版本差异：
  MySQL 5.5+开始支持该字段

四、私藏技巧

1. 索引重复检测（SQL直接查）

-- 快速定位重复索引
SELECT 
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.statistics
WHERE table_schema = '你的数据库名'
GROUP BY table_name, columns
HAVING COUNT(*) > 1;

2. 索引碎片率计算

-- 计算索引碎片率（>30%建议优化）
SELECT 
    table_name,
    index_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    ROUND((data_free) / 1024 / 1024, 2) AS free_mb,
    ROUND((data_free / (data_length + index_length + data_free)) * 100, 2) AS frag_ratio
FROM information_schema.tables
WHERE table_schema = '你的数据库名'
AND data_free > 0;

3. 强制更新统计信息

-- 解决Cardinality不准问题
ANALYZE TABLE user;

五、必看避坑指南

1. 乱用前缀索引：

   • 会导致排序无法使用索引

   • 需确保前缀长度足够区分数据（如身份证前6位）

2. 盲目追求高基数：

   • 文本型字段（如status）基数低但查询频繁仍需索引

3. 忽视联合索引顺序：

   • 错误顺序：INDEX (city, age)

   • 正确顺序：INDEX (age, city)（先过滤再排序）

4. 过度索引的代价：

   • 每增加一个索引，写操作开销增加约10%

六、结语

掌握SHOW INDEX命令，相当于拿到了数据库调优的CT扫描仪。建议大家结合EXPLAIN执行计划分析，定期进行索引健康检查。遇到具体问题欢迎在评论区留言讨论！

你的每个索引设计，都可能影响百万级查询的性能。且用且珍惜！