MySQL索引添加与删除方法实际示例
在MySQL数据库中，索引是提升查询效率的核心机制。合理地使用索引可以将数据的检索速度提升数个数量级，但不当的索引设计也会导致写入性能下降及存储空间浪费。因此，熟练掌握索引的添加与删除操作，是数据库开发与运维人员的必备技能。
本文将结合实际示例，详细讲解MySQL中各类索引的添加与删除方法，并深入探讨操作过程中的注意事项。
一、准备测试数据
为了更直观地展示索引操作，我们先创建一张示例表 users，包含主键ID、用户名、邮箱、年龄和状态字段。
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `age` int(11) DEFAULT NULL, `status` tinyint(4) DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
二、添加索引的方法
MySQL提供了两种添加索引的语法：ALTER TABLE 语句和 CREATE INDEX 语句。推荐使用 ALTER TABLE，因为它支持所有类型的索引创建，而 CREATE INDEX 无法用于创建主键索引。
1. 添加普通索引 (INDEX)
普通索引是最基础的索引类型，没有任何约束限制，纯粹用于加速查询。
-- 方式一：使用 ALTER TABLE ALTER TABLE `users` ADD INDEX idx_username (`username`); -- 方式二：使用 CREATE INDEX CREATE INDEX idx_email ON `users`(`email`);
2. 添加唯一索引 (UNIQUE INDEX)
唯一索引与普通索引类似，但要求索引列的值必须唯一，允许存在NULL值。常用于邮箱、手机号等需要保证唯一性的字段。
ALTER TABLE `users` ADD UNIQUE INDEX idx_email_unique (`email`);
3. 添加组合索引 (COMPOSITE INDEX)
组合索引是在多个字段上建立的索引。遵循“最左前缀原则”，即在查询条件中使用了组合索引的最左列或最左列的连续几列，索引才会生效。
ALTER TABLE `users` ADD INDEX idx_age_status (`age`, `status`);
4. 添加前缀索引 (PREFIX INDEX)
对于BLOB、TEXT或很长的VARCHAR字段，直接建立完整索引会消耗大量空间。此时可以截取字符串的前几个字符建立前缀索引，在查询性能与存储空间之间取得平衡。
ALTER TABLE `users` ADD INDEX idx_username_prefix (`username`(10));
三、删除索引的方法
当索引不再使用或成为系统负担时，应及时删除。同样有两种语法：ALTER TABLE ... DROP INDEX 和 DROP INDEX。
1. 删除普通索引/唯一索引/组合索引
-- 方式一：使用 ALTER TABLE ALTER TABLE `users` DROP INDEX idx_username; -- 方式二：使用 DROP INDEX DROP INDEX idx_email ON `users`;
2. 删除主键索引
主键索引的删除方式略有不同，且要求主键列没有自增（AUTO_INCREMENT）属性，否则需要先修改列属性再删除。
ALTER TABLE `users` DROP PRIMARY KEY;
四、索引操作的注意事项
在实际生产环境中操作索引，仅仅知道语法是不够的，还需要注意以下关键点：
1. 避免冗余与重复索引
如果已经存在组合索引 idx_a_b(a, b)，再单独创建索引 idx_a(a) 就是冗余的，因为组合索引的最左前缀已经可以满足对 a 列的查询需求。重复索引是指对同一个列以相同方式建立了多次索引，这会白白增加写入时的开销。
2. 业务低峰期操作
在数据量大的表上添加或删除索引，MySQL需要拷贝全表数据并重建表，这会导致锁表并严重影响线上业务。在MySQL 5.6及以上版本支持 Online DDL，但大表操作仍可能造成主从延迟。建议在业务低峰期执行，或使用 pt-online-schema-change 等工具进行无锁变更。更多数据库高可用架构下的DDL操作案例可以参考 www.ipipp.com 提供的性能测试报告。
3. 关注索引的选择性
索引的选择性是指索引列中不重复的值与表记录数的比值。选择性越高，索引效果越好。例如在“性别”字段上建立索引几乎没有意义，因为选择性极低，MySQL优化器通常会选择全表扫描而非走索引。
4. EXPLAIN 验证
添加索引后，务必使用 EXPLAIN 关键字验证SQL语句是否真正使用了新创建的索引，避免创建了索引但优化器并未采用的情况。
五、总结
MySQL索引的添加与删除虽然语法简单，但背后的设计逻辑与对系统的影响却不容忽视。通过 ALTER TABLE 和 DROP INDEX 可以灵活管理索引结构，但在实际应用中，需结合业务查询模式、数据量大小及区分度，遵循最左前缀原则，避免冗余索引，并在合适的时机执行DDL操作，才能真正发挥索引的价值，保障数据库高效稳定运行。