MySQL索引查看语句show index详解

一、概述

show index语句是MySQL中用于查看表索引信息的语句。它提供了有关表中索引的详细信息，包括索引名称、索引类型、关联的列等。

二、语法与概要描述

2.1 基本语法

SHOW INDEX语句的语法如下：

show index from table_name [from db_name] [where condition];

参数说明：

table_name：需要查询索引的表名。
db_name：（可选）数据库名。如果你已经在某个数据库上下文中，可以省略此参数。
condition：（可选）可以添加where子句来筛选索引信息，比如根据索引名称、索引类型等进行筛选。

2.2 常用示例

-- 查看当前库中users表的所有索引
show index from users;

-- 查看test库中orders表的唯一索引
show index from orders from test where non_unique = 0;

三、字段说明

show index语句返回以下信息：

table：表名。
non_unique：是否允许重复值。如果值为1，表示允许重复值；如果值为0，表示不允许重复值（唯一索引）。
key_name：索引名称。主键索引名通常为primary。
seq_in_index：索引中的列的序号。对于组合索引，这表示列在索引中的位置。
column_name：列名，索引涉及的列。
collation：排序规则。a表示升序，null表示不可排序。
cardinality：索引的基数。这是一个估算值，表示索引中唯一值的数量。这个值对于查询优化器选择索引非常重要。
sub_part：索引的前缀长度。对于部分索引，这表示索引的前缀长度。
packed：索引是否被压缩。如果索引未被压缩，该列的值为null。
null：列是否允许包含null值。
index_type：索引类型。常见的类型有btree、hash、fulltext等。
comment：索引的备注。

四、示例

下面是一个名为examples的表，其中包含各种类型的索引：主键索引、唯一索引、普通索引、前缀索引和联合索引。

create table examples (
    id          int auto_increment,
    name        varchar(255),
    description text,
    category    varchar(255),
    price       decimal(10, 2),
    primary key (id),
    unique key idx_name (name),
    key idx_category (category),
    key idx_category_price (category, price),
    key idx_name_prefix (name(10))
) engine = InnoDB;

在这个示例中，我们创建了一个名为examples的表，包含以下类型的索引：

主键索引(primary key)：id列是主键索引。主键索引要求唯一且不允许null值。
唯一索引(unique key)：name列是唯一索引。唯一索引要求唯一，但允许null值。
普通索引(key)：category列是普通索引。普通索引允许重复值和null值。
联合索引(key)：category和price列组成了一个联合索引。这允许根据这两个列的组合进行更快的查询。
前缀索引(key)：name列的前10个字符被用作前缀索引。前缀索引允许在索引较长的字符串列时节省存储空间和提高查询速度，但可能会影响查询准确性。

现在，使用show index语句查询examples表的索引信息：

show index from examples;

输出结果（以表格形式表示）：

+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| examples |          0 | PRIMARY            |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     |       NULL |
| examples |          0 | idx_name           |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     |       NULL |
| examples |          1 | idx_category       |            1 | category    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     |       NULL |
| examples |          1 | idx_category_price |            1 | category    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     |       NULL |
| examples |          1 | idx_category_price |            2 | price       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     |       NULL |
| examples |          1 | idx_name_price     |            1 | name        | A         |           0 |       10 |   NULL | YES  | BTREE      |         |               | YES     |       NULL |
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

在show index的输出中，你可以看到examples表的各种类型索引：

主键索引(primary key)：在key_name列中，primary关键字表示该行对应的是主键索引。non_unique列的值为0，说明主键索引的值必须是唯一的。在此例中，主键索引是id列。
唯一索引(unique key)：在key_name列中，自定义的索引名称（例如idx_name）表示这是一个唯一索引。non_unique列的值为0，说明唯一索引的值必须是唯一的，但允许null值。在此例中，唯一索引是name列。
普通索引(key)：在key_name列中，自定义的索引名称（例如idx_category）表示这是一个普通索引。non_unique列的值为1，说明普通索引允许重复值和null值。在此例中，普通索引是category列。
前缀索引(key)：前缀索引的识别方法与普通索引相同，但在sub_part列中有一个值，表示使用列值的前多少个字符作为前缀索引。在此例中，idx_name_prefix是一个前缀索引，它将name列的前10个字符作为索引。
联合索引(key)：联合索引可以通过key_name列中的相同索引名称以及不同的seq_in_index值来识别。在seq_in_index列中，数字表示列在联合索引中的顺序。在此例中，idx_category_price是一个联合索引，包括category和price两个列。在这两行中，key_name列的值都是idx_category_price，表明它们属于同一个索引。seq_in_index列的值分别为1和2，表示category和price这两个列在联合索引中的顺序。

注意，show index语句及返回的结果列可能会因MySQL版本的不同而有所差异。本解释基于MySQL 8.0版本。在其他版本中，返回的结果列可能有所不同。要获取与您的MySQL版本对应的详细信息，请查阅官方文档。

五、拓展

5.1 索引重复检测

重复索引会浪费存储空间并降低写入性能，可通过以下SQL排查：

-- 快速定位重复索引
select 
    table_name,
    group_concat(column_name order by seq_in_index) AS columns
from information_schema.statistics
where table_schema = '你的数据库名'
group by table_name
having count(*) > 1;

5.2 分析索引有效性

通过Cardinality判断索引是否值得保留

-- 计算索引选择性（越接近1越好）
select 
    table_name,
    key_name,
    column_name,
    cardinality,
    (cardinality / (select count(*) from 表名)) as selectivity
from information_schema.statistics
where table_schema = '你的数据库名'
  and table_name = '你的表名';

5.3 索引碎片率计算

当索引碎片率过高（>30%）时，查询性能会下降

-- 计算索引碎片率（>30%建议优化）
select 
    table_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;

5.4 强制更新统计信息

当Cardinality值明显偏离实际时（如刚批量插入数据后）：

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

六、总结

show index不仅是查看索引信息的工具，更是索引优化的"导航系统"。通过解读其输出的Non_unique、Seq_in_index、Cardinality等核心字段，我们能精准识别索引类型、判断索引有效性、发现重复或冗余索引。

配合文中的索引检测和优化脚本，可让索引真正成为数据库性能的"加速器" 而非"负担"。建议定期（如每周）对核心业务表执行show index分析，防患于未然。

posted @ 2025-10-20 13:10 夏尔_717 阅读(110) 评论(0) 收藏举报

刷新页面返回顶部

ciel_717