如何给MySQL大表添加/删除索引

TrumanWong
5/9/2024
TrumanWong

通常情况下,MySQL中如果给千万级的表在线添加/删除索引,必然会卡死,因此本文主要介绍如何避免这类事故发生。

步骤1:创建新表

首先,创建一张和原表结构一样的空表:

CREATE TABLE new_table like old_table;

这里的new_table是新表的名称,old_table是旧表名称。

步骤2:向新表中添加/删除索引

此时新表中没有数据,因此添加/删除索引非常快:

# 添加索引
ALTER new_table ADD INDEX idx_name(`column`);
# 删除索引
ALTER new_table DROP INDEX idx_name;

步骤3:向新表表中插入数据

接下来,从旧表往主表里导数据,如果数据太大,建议分批导入(如每批次导入10w条数据)只需确保无重复数据就行,因为导入数据太大,会很占用资源(内存,磁盘io, cpu等),可能会影响旧表在线上的业务:

insert into new_table select * from old_table where id between start_id and end_id;

步骤4:删除旧表

在完成索引添加和数据迁移后,当大部分数据导入后,索引也建立好了,但是旧表数据量还是会因业务的增长而增长,这时候为了确保新旧表的数据一至性和平滑切换,建议写一个脚本,判断当旧表的数据行数与新表一致时,就切换(如:用max(id)来判断)。我们可以将业务切换到新表或者删除旧表,将新表重命名为旧表(这需要先暂停一会业务):

DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;

通过以上步骤,我们成功地为MySQL数据库中的千万级的表添加/删除了索引。