13.4 查看和删除索引

      在创建索引之后，有时需要查看表中的索引信息，或者因为不再需要、影响写入性能而删除索引。MySQL 提供了简单易用的命令来查看和删除索引。

一、查看索引

查看表中的索引可以帮助你了解当前有哪些索引、索引的列、索引类型以及是否唯一等信息。常用的命令是 SHOW INDEX FROM。

基本语法

SHOW INDEX FROM 表名;

或者使用等效的

SHOW KEYS FROM 表名;

示例

假设我们有一张 users 表，并且已经创建了一些索引。执行以下命令查看索引：

SHOW INDEX FROM users;

输出结果示例：

重要字段解释：

• Table：表名。

• Non_unique：是否为唯一索引（0 表示唯一索引，1 表示非唯一）。

• Key_name：索引的名称。如果索引是主键，则名称通常为 PRIMARY。

• Seq_in_index：在复合索引中的顺序（从 1 开始）。

• Column_name：索引的列名。

• Collation：列的排序方式，A 表示升序，NULL 表示未排序。

• Cardinality：索引中唯一值的估计数量。值越大，索引的选择性越好。

• Sub_part：如果索引是前缀索引（只索引列的前几个字符），这里显示前缀长度。如果是完整列，则为 NULL。

• Null：列是否允许为 NULL，YES 表示允许。

• Index_type：索引类型（BTREE、HASH、FULLTEXT 等）。

• Comment：备注信息。

二、查看特定数据库中的索引

如果表不在当前数据库中，可以指定数据库名：

SHOW INDEX FROM 数据库名.表名;

三、删除索引

当索引不再需要，或者因为索引过多影响写入性能时，可以将其删除。删除索引也有两种常用方法。

方法一：使用 DROP INDEX 语句

DROP INDEX 索引名 ON 表名;

示例：删除 users 表上的 idx_email 索引。

DROP INDEX idx_email ON users;

方法二：使用 ALTER TABLE 语句

ALTER TABLE 表名 DROP INDEX 索引名;

示例：同样删除 idx_email 索引。

ALTER TABLE users DROP INDEX idx_email;

删除主键索引

主键索引的删除略有不同，因为主键是一种特殊的索引。如果表有主键，可以使用：

ALTER TABLE 表名 DROP PRIMARY KEY;

注意：删除主键前，必须确保该列没有 AUTO_INCREMENT 属性（如果有，需先修改列定义去掉自增），或者先删除自增属性。

删除复合索引

复合索引的删除与普通索引相同，只需指定索引名即可。例如，删除复合索引 idx_user_date：

DROP INDEX idx_user_date ON orders;

注意事项

1. 删除索引前确认：在执行删除操作前，建议先用 SHOW INDEX 确认索引名称和所属表，避免误删。

2. 主键索引的特殊性：主键索引名始终为 PRIMARY，删除时不能使用 DROP INDEX PRIMARY 语法，必须用 ALTER TABLE ... DROP PRIMARY KEY。

3. 外键约束与索引：在 InnoDB 中，外键列会自动创建索引。如果删除该索引，可能会影响外键约束的性能，但外键约束本身不会因此被删除。通常建议保留外键列的索引。

4. 唯一索引：删除唯一索引不会影响列的唯一性约束，唯一性不再被强制（除非该列还有其它唯一约束）。

5. 删除不存在的索引：如果尝试删除一个不存在的索引，MySQL 会报错。可以使用 IF EXISTS 吗？MySQL 不支持 DROP INDEX IF EXISTS 语法（但某些版本或分支可能支持）。为安全起见，可以先检查索引是否存在。

6. 对正在运行的系统的影响：删除索引通常很快，但可能会短暂影响正在执行的查询（如果该查询使用了该索引）。建议在业务低峰期执行。

四、小结

• 使用 SHOW INDEX FROM 表名 查看表中的索引信息，了解索引结构。

• 使用 DROP INDEX 索引名 ON 表名 或 ALTER TABLE 表名 DROP INDEX 索引名 删除索引。

• 主键索引需用 ALTER TABLE 表名 DROP PRIMARY KEY 删除。

• 在删除索引前应确认其必要性，并考虑对查询性能的影响。

掌握了索引的查看和删除，你就能更好地管理和优化数据库的索引结构了。