MySQL 修改和删除索引

 一、删除索引的两种方法

方法 1: 使用 `DROP INDEX` 语句 (标准做法)

这是最直接、最常用的专门用于删除索引的语句。

语法：

DROP INDEX <索引名> ON <表名>;

   `<索引名>`：要删除的索引的名称。

   `<表名>`：该索引所在的表名。

示例：

 删除表 `employees` 上名为 `idx_email` 的索引

DROP INDEX idx_email ON employees;

方法 2: 使用 `ALTER TABLE ... DROP INDEX` 语句

`ALTER TABLE` 语句功能强大，也可以用于删除索引。

语法：

ALTER TABLE <表名> DROP INDEX <索引名>;

   参数含义与 `DROP INDEX` 完全相同。

示例：

 效果与上一句完全相同

ALTER TABLE employees DROP INDEX idx_email;

二、删除特殊类型的索引

1. 删除主键 (PRIMARY KEY)

由于主键约束名总是 `PRIMARY`，删除语法固定。

语法：

ALTER TABLE <表名> DROP PRIMARY KEY;

注意： 如果主键列具有 `AUTO_INCREMENT` 属性，在某些MySQL版本中，直接删除主键可能会报错。可能需要先修改列去掉 `AUTO_INCREMENT`，再删除主键。

2. 删除外键 (FOREIGN KEY)

删除外键时，需要指定外键约束的名称（`fk_symbol`），而不是索引名。但请注意，删除外键并不会自动删除其对应的索引。通常需要分别删除。

语法：

 首先，删除外键约束

ALTER TABLE <子表名> DROP FOREIGN KEY <外键约束名>;

 然后，再手动删除为该外键创建的索引（如果不再需要）

ALTER TABLE <子表名> DROP INDEX <索引名>;

三、关键注意事项与最佳实践

1.  权限要求：用户必须对目标表具有 `INDEX` 权限。

2.  索引不存在错误：如果尝试删除不存在的索引，语句会执行失败并报错 (`ERROR 1091 (42000): Can't DROP ...; check that column/key exists`)。

       建议：在自动化脚本中，可以考虑先检查索引是否存在，或者使用某些工具的特性来避免此错误。

3.  性能影响：

       删除操作本身：是一个相对快速的元数据操作，通常瞬间完成，但表越大，可能需要一点时间。

       删除后的影响：会提高写操作的速度，因为数据库无需再维护该索引。但会降低以该索引为条件的查询速度。

4.  验证结果：使用 `SHOW CREATE TABLE <表名>;` 或 `SHOW INDEX FROM <表名>;` 来确认索引已被成功删除。

5.  修改索引的实现方式：正如您提到的，MySQL 没有直接修改索引的语句。修改索引的“标准”方法是：

     1. 先删除旧索引

 DROP INDEX old_index_name ON table_name;

     2. 再创建新索引

CREATE INDEX new_index_name ON table_name (column1, column2);

 四、方法对比与选择

 特性  `DROP INDEX ... ON ...`  `ALTER TABLE ... DROP INDEX`

语义清晰度 高。一看就知道是专门用来删除索引的。  中。是修改表结构的一个子功能。

功能  只能删除普通索引和唯一索引。 功能更强。可以删除索引、主键、外键。

 常用场景  最常用于删除普通索引和唯一索引。  需要删除主键或外键时必须使用。也可用于删除普通索引。

总结：

   对于删除普通索引或唯一索引，两种方法任选其一，`DROP INDEX` 更为直观。

   对于删除主键或外键，必须使用 `ALTER TABLE`语句。

操作示例与验证

1. 删除普通索引并验证

 删除索引

DROP INDEX height ON tb_stu_info;

 验证索引是否已删除（查看表结构）

SHOW CREATE TABLE tb_stu_info\G

输出确认： 在 `CREATE TABLE` 的输出语句中，之前存在的 `KEY `height` (`height`)` 项已经消失。

2. 删除唯一索引 (使用 ALTER TABLE)

 删除唯一索引

ALTER TABLE tb_stu_info2 DROP INDEX height;

 验证

SHOW INDEX FROM tb_stu_info2;

输出确认： 查询结果集中不再有 `Key_name` 为 `height` 的记录。

始终记住，删除索引是一个需要谨慎进行的操作，务必确保该索引确实不再被任何重要查询使用。