MySQL查看表索引的SQL语句|极客笔记

MySQL查看表索引的SQL语句

1. 简介

在数据库中，索引是提高数据检索效率的重要手段之一。通过合理地创建和使用索引，可以显著提高数据库的查询性能。MySQL作为一种常用的关系型数据库管理系统，提供了丰富的功能来创建、管理和查询索引。本文将详细介绍如何使用MySQL提供的SQL语句来查看表索引。

2. 查看表的索引

要查看表的索引信息，可以使用SHOW INDEX语句。该语句的基本语法如下所示：

SHOW INDEX FROM 表名;

其中，表名为要查看索引的表名。

下面是一个示例，查看名为products的表的索引信息：

SHOW INDEX FROM products;

运行结果如下所示：

Table   Non_unique   Key_name   Seq_in_index   Column_name   Collation   Cardinality   Sub_part   Packed   Null   Index_type   Comment   Index_comment 
products  0         PRIMARY      1          id              A         3             NULL      NULL     BTREE      NULL       NULL
products  1         name_unique  1          name            A         3             NULL      NULL     BTREE      NULL       NULL

运行结果中的各个列含义如下：

Table：表名
Non_unique：是否允许重复值，0代表唯一索引，1代表非唯一索引
Key_name：索引名称
Seq_in_index：在索引中的位置
Column_name：索引所在的列名
Collation：排序方式，A代表升序，NULL代表无需排序
Cardinality：索引的基数，也就是索引中不重复的值的数量
Sub_part：索引的长度，对于字符串类型的列，它指定了索引的前缀长度。如果该列为NULL，则表示索引的长度和实际列的长度相同。
Packed：是否压缩索引，NULL代表没有压缩
Null：是否允许列值为空
Index_type：索引的类型，BTREE代表B树索引
Comment：索引的注释
Index_comment：索引的注释

通过SHOW INDEX语句，我们可以快速了解表的索引情况，包括索引名称、索引所在的列名、基数等信息。

3. 查看索引的定义

除了查看表的索引信息外，有时我们还需要查看索引的具体定义，以便更好地理解和优化索引。MySQL提供了SHOW CREATE TABLE语句，可以查看表的结构以及索引的定义。

该语句的基本语法如下：

SHOW CREATE TABLE 表名;

下面是一个示例，查看名为products的表的结构和索引定义：

SHOW CREATE TABLE products;

运行结果如下所示：

Table     Create Table                                               
products  CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

运行结果中的CREATE TABLE语句包含了表的结构定义和索引定义。我们可以根据该语句来查看索引的具体创建方式，以及索引所在的列名等信息。

4. 查看索引的使用情况

除了了解索引的定义，我们还需要知道索引在实际查询中的使用情况。MySQL提供了EXPLAIN语句，可以帮助我们分析查询语句的执行计划，从而判断索引是否得到了有效利用。

EXPLAIN语句的基本语法如下：

EXPLAIN 查询语句;

下面是一个示例，使用EXPLAIN语句分析名为products的表的查询性能：

EXPLAIN SELECT * FROM products WHERE price > 100;

运行结果如下所示：

id   select_type   table     partitions   type    possible_keys   key       key_len   ref    rows   filtered   Extra
1    SIMPLE        products  NULL         ALL     NULL            NULL      NULL      NULL   3      100.00     Using where

运行结果中的各个列含义如下：

id：查询的序号
select_type：查询类型，SIMPLE代表简单的SELECT查询
table：查询涉及的表名
partitions：查询涉及的分区信息
type：访问类型，多种类型包括ALL、index、range等
possible_keys：可能使用的索引
key：实际使用的索引
key_len：使用的索引的字节数
ref：与索引的比较值
rows：扫描的行数
filtered：筛选的行数占比
Extra：额外的信息，如Using where表示查询中使用了WHERE子句

通过分析EXPLAIN的结果，我们可以判断出查询是否使用了索引，以及索引的使用效果。例如，在示例中的运行结果中，使用了WHERE子句进行条件过滤，但是并未使用索引，导致查询使用了全表扫描。

5. 总结

本文介绍了如何使用MySQL提供的SQL语句来查看表索引。通过SHOW INDEX语句，我们可以查看表的索引信息，包括索引名称、所在列名、基数等。通过SHOW CREATE TABLE语句，我们可以查看索引的具体定义。通过EXPLAIN语句，我们可以分析查询语句的执行计划，从而判断索引是否得到了有效利用。

合理地创建和使用索引是提高数据库查询性能的重要手段之一，通过细致地观察和分析索引的使用情况，可以对数据库进行优化，提升查询效率。