mysql show index t 详细解析

发表于 2025-3-15 11:31:13

在 MySQL 中执行 SHOW INDEX FROM staff 会显示表 staff 的索引详细信息，包括索引名称、类型、关联列、唯一性等。以下是对输出结果各列的详细解析，并附示例和优化建议：

示例输出

假设执行 SHOW INDEX FROM staff 的结果如下：

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type
staff	0	PRIMARY	1	id	A	1000	NULL	NULL		BTREE
staff	1	idx_name	1	name	A	500	NULL	NULL	YES	BTREE
staff	1	idx_dept	1	dept_id	A	10	NULL	NULL		BTREE
staff	1	idx_name	2	age	A	500	NULL	NULL	YES	BTREE

各列含义详解

Table
- 含义：索引所属的表名，此处为 staff。
- 用途：确认当前索引属于哪个表。
Non_unique
- 含义：索引是否允许重复值。
  - 0：唯一索引（如主键或 UNIQUE 约束）。
  - 1：非唯一索引，允许重复值。
- 示例：
  - PRIMARY 键的 Non_unique=0，表示主键唯一。
  - idx_name 的 Non_unique=1，表示允许重复的 name 值。
Key_name
- 含义：索引的名称。
  - PRIMARY：主键索引。
  - 其他名称：用户自定义的二级索引。
- 示例：
  - PRIMARY 是主键索引，关联 id 列。
  - idx_name 和 idx_dept 是用户创建的索引。
Seq_in_index
- 含义：列在索引中的位置（从 1 开始）。
- 用途：联合索引中列的顺序。
- 示例：
  - idx_name 是联合索引，包含 name（Seq=1）和 age（Seq=2）。
Column_name
- 含义：索引关联的列名。
- 示例：
  - PRIMARY 索引关联 id 列。
  - idx_dept 关联 dept_id 列。
Collation
- 含义：列的排序规则。
  - A（Ascending）：升序。
  - D（Descending）：降序（MySQL 8.0+ 支持降序索引）。
  - NULL：无排序（如哈希索引）。
- 示例：所有索引均为升序（A）。
Cardinality
- 含义：索引的基数，即索引列中不同值的估算数量。
- 用途：基数越高，索引选择性越好，优化器更可能选择此索引。
- 示例：
  - id 列的基数为 1000（接近表的总行数），说明唯一性高。
  - dept_id 的基数为 10，说明该列重复值多，选择性低。
Sub_part
- 含义：前缀索引的长度（单位为字符）。
  - NULL：未使用前缀索引，索引整个列。
  - 数字（如 10）：仅索引前 10 个字符。
- 示例：所有索引未使用前缀索引（NULL）。
Packed
- 含义：索引是否压缩（如 PACK_KEYS 选项）。
  - NULL：未压缩。
- 示例：所有索引未压缩。
Null
- 含义：列是否允许 NULL 值。
  - YES：允许。
  - 空：不允许。
- 示例：
  - name 列允许 NULL（Null=YES）。
  - id 和 dept_id 列不允许 NULL。
Index_type
- 含义：索引类型。
  - BTREE：B+ 树索引（默认）。
  - HASH：哈希索引（仅 Memory 引擎）。
  - FULLTEXT：全文索引。
  - SPATIAL：空间索引。
- 示例：所有索引为 BTREE。
Comment 和 Index_comment
- 含义：索引的注释信息（通过 COMMENT 选项添加）。
- 示例：未添加注释，均为空。

关键分析点

1. 主键索引（PRIMARY）

关联 id 列，唯一且不允许 NULL。
基数高（1000），适合作为查询条件。

2. 联合索引（idx_name）

包含 name 和 age 列，顺序为 name 在前。
遵循最左前缀原则：仅当查询条件包含 name 时，索引生效。
允许 NULL 值，需注意数据完整性。

3. 低选择性索引（idx_dept）

dept_id 列的基数仅为 10，说明该列重复值较多。
优化建议：若查询中频繁使用 dept_id 且性能差，可考虑删除此索引或结合其他列创建联合索引。

优化建议

删除冗余索引
- 如果存在多个功能重复的索引（如 (name) 和 (name, age)），删除冗余的单列索引。
优化低基数索引
- 对于 dept_id 这类低基数列，避免单独创建索引，可与其他高基数列组成联合索引（如 (dept_id, status)）。
前缀索引权衡
- 若 name 是长字符串（如 VARCHAR(255)），可测试前缀索引以节省空间：
```
CREATE INDEX idx_name_prefix ON staff (name(20));
```
监控索引使用情况
- 通过 sys.schema_unused_indexes 表查看未使用的索引：
```
SELECT * FROM sys.schema_unused_indexes WHERE object_name = 'staff';
```
结合 EXPLAIN 分析查询
- 使用 EXPLAIN 验证索引是否被实际使用：
```
EXPLAIN SELECT * FROM staff WHERE name = 'John';
```

总结

通过 SHOW INDEX FROM staff 可以快速了解表的索引结构，重点关注以下内容：

唯一性（Non_unique）和基数（Cardinality）决定索引的过滤能力。
联合索引顺序（Seq_in_index）影响最左前缀原则的生效。
前缀索引（Sub_part）和 索引类型（Index_type）反映存储和查询优化策略。

合理利用这些信息，可以删除冗余索引、优化低效索引，从而提升查询性能并减少存储开销。