大表加索引方案

背景

每个公司针对数据库的设计都有套方案。最近在巡检表的设计，发现之前有张表漏掉了针对更新时间字段updated_at的索引，现在需要加上该索引。

我们都知道，为表增加索引是会对表进行加锁处理的。稍有不慎，可能会导致表被锁后，业务无法进行读写操作而产生事故影响，通常都是报错Waiting for meta data lock。在对表进行修改时，特别是生产上，我们首先要观察对应的表此时是否在高并发读写（选择操作时机）、表的量级信息。

方案

整体思路是：

1. 先创建一张表，结构和原表相同；
2. 在新表上添加索引；
3. rename新表为原表的表名，原表换新的名称；
4. 为原表新增索引；
5. 待索引创建成功后，rename原表为原来的名称，并将新表里的数据导入到原表中

SQL可参考：

-- 假设需要添加索引的表为`fea_moni_res`
-- 1. 新建与表`fea_moni_res`同结构的表
CREATE TABLE fea_moni_res_tmp LIKE fea_moni_res;

-- 2. 新表上添加索引
ALTER TABLE fea_moni_res_tmp ADD INDEX idx_index_name (col_name);

-- 3. *rename*新表为原表的表名，原表换新的名称
RENAME TABLE fea_moni_res TO fea_moni_res_1, fea_moni_res_tmp TO fea_moni_res;

-- 4. 为原表新增索引，此步耗时较长
ALTER TABLE fea_moni_res_1 ADD INDEX idx_index_name (col_name);

-- 5. 待索引创建成功后，rename原表为原来的名称，并将新表里的数据导入到原表中
RENAME TABLE fea_moni_res TO fea_moni_res_tmp, fea_moni_res_1 TO fea_moni_res;
-- 需要根据业务来确定如果导入数据
INSERT INTO fea_moni_res(col_name1, col_name2) SELECT col_name1, col_name2 FROM fea_moni_res_tmp;

总结

本文提供了一种为大表添加索引的方法。当然，此方案不是完美的。譬如，步骤3中的rename操作后，是极有可能会对业务产生影响的，因为业务需要根据历史数据来判断逻辑。此时可能不得不在步骤2之后，先行将原表中的数据导入到新表中，待完全导入后再直接切表操作。此需要根据业务的场景来判断，具体问题具体分析。

此处只是提供了一种方案，涉及到为大表添加字段、删除字段的表锁操作均可参考。