当前位置：首页 > 问答 > 正文

MySQL里改表结构时那些关于索引的事儿，怎么加怎么删还得注意点啥

盈壮
问答
2026-01-08 20:01:31
4

在MySQL里折腾表结构,尤其是索引，是个技术活，加对了事半功倍，搞错了轻则拖慢速度，重则服务停摆，咱们就聊聊怎么加、怎么删，还有那些容易踩的坑，内容主要基于MySQL官方文档的实践总结和常见运维经验。

给表加索引

加索引最常用的命令是 ALTER TABLE 和 CREATE INDEX，它俩在功能上很多时候是等价的，但细微之处有差别。

基本语法：
- ALTER TABLE 表名 ADD INDEX 索引名 (列名); – 这是最常用的方式。
- CREATE INDEX 索引名 ON 表名 (列名); – 这个语句更直观，但不能用来创建主键。
加索引的注意事项：
1. 锁表问题（最要命的一点）：
  - 在MySQL 5.6之前的版本，或者即使到了5.6/5.7但使用某些存储引擎（如MyISAM）时，直接给大表加索引会导致锁表，这意味着在索引创建过程中，整个表会处于只读状态（read lock），任何写操作（INSERT, UPDATE, DELETE）都会被阻塞，直到索引创建完成，对于在线业务来说，这可能是灾难性的，会导致服务超时甚至不可用。
  - 怎么避免？ 尽量使用MySQL 5.6及以上版本，并为表使用InnoDB存储引擎，在InnoDB下，可以通过 ALGORITHM=INPLACE, LOCK=NONE 选项来尝试在线加索引，但这不是万能的，根据官方文档，只有在索引创建过程中不需要重建表的情况下，才能实现真正的无锁（LOCK=NONE），如果MySQL判断必须重建表，它仍然会使用更高级别的锁，执行前最好先测试一下语句是否会锁表，可以使用 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 试试，如果报错说明无法在线完成。
2. 索引命名要有意义：
  - 别用 idx_1, idx_2 这种名字，时间一长你自己都忘了是干嘛的，建议的命名规则是 idx_字段名，如果是联合索引（多个列），可以叫 idx_字段1_字段2，比如为 user_name 字段加索引，就叫 idx_user_name，这样一看名字就知道索引建在哪儿了。
3. 联合索引的顺序是关键：
  - 当你需要为多个列一起加索引时（联合索引），列的顺序至关重要，MySQL的联合索引遵循最左前缀原则，意思是，索引就像电话簿，先按姓排序，姓相同的再按名排序，如果你查的时候只知道名不知道姓，这个电话簿（索引）就帮不上太大忙。
  - 例子： 你建了一个联合索引 idx_city_age (city, age)。
    - WHERE city='北京' – 索引有效（用了姓）。
    - WHERE city='北京' AND age=25 – 索引有效（姓和名都用了）。
    - WHERE age=25 – 索引可能无效（因为没提供姓，直接找名，需要全表扫描）。
  - 要把区分度高（唯一值多）的、查询最常用的列放在联合索引的左边。
4. 选择在业务低峰期操作：
  
  即使MySQL声称可以在线操作,给一个有几亿行记录的大表加索引依然是一个重量级操作，会消耗大量CPU和I/O资源，可能会影响同服务器上其他数据库的性能，务必选择在深夜或流量最低的时间段进行操作。

删除表里的索引

删索引通常比加索引快得多,风险也相对小，但也不能掉以轻心。

基本语法：
- ALTER TABLE 表名 DROP INDEX 索引名; – 标准做法。
- DROP INDEX 索引名 ON 表名; – 另一种写法。
删索引的注意事项：
1. 删之前，搞清楚这个索引是干嘛的：
  - 这是最重要的！随手删掉一个索引，可能导致某个关键查询的速度从毫秒级暴跌到秒级，直接拖垮整个应用，在删除前，一定要确认：
    - 这个索引真的不再使用了吗？可以通过MySQL的慢查询日志（slow query log）或者性能模式（Performance Schema）来查看索引的使用情况。
    - 这个索引是不是某个唯一约束或外键约束的一部分？如果是，删除索引可能会破坏约束，导致删除失败或数据完整性问题。
2. 删除主键索引（PRIMARY KEY）要特别小心：
  - 每个InnoDB表都必须有一个主键,如果你试图删除现有的主键，MySQL会做两件事：
    - 如果表上有另一个非空的唯一索引（UNIQUE INDEX），MySQL会自动选择其中一个作为新的主键，这个选择可能不符合你的预期。
    - 如果表上没有其他合适的唯一索引,MySQL会自动创建一个隐藏的主键，但这个隐藏的主键对你是不可见的，而且基于它的查询性能可能不佳，除非你打算立即创建一个新的主键，否则不要随意删除旧的主键。
3. 删除索引也会短暂锁表：
  
  虽然删除操作很快,但依然会获取一个元数据锁（metadata lock），以确保在删除过程中没有并发的DDL操作，这个锁定的时间极短，通常感知不到，但在高并发DDL环境下也可能引发问题。