Question

数据库索引问题

一个有1亿数据的系统，现需加一个索引，需要大概两个小时，如何不影响使用，不关闭主机的情况下，完成此项操作？

Answer 1

可以考虑使用在线建索引的工具，或者中间表方式进行更改，下面就两种方法分别做一下说明。

一. pt-online-schema-change在线建索引

1. 下载安装pt-toolkit工具包，里面包含此工具。

2. 安装完成后使用命令在线加索引

pt-online-schema-change -h127.0.0.1  -P 3307 -uroot -p123 --charset=utf8 --alter='ADD INDEX `idxname` (`a`, `b`, `c`) USING BTREE' --execute D=dbname,t=tablename

上面命令主要做了以下工作：

1. 基于原表表结构创建一个临时表

2. 原表创建3个触发器，分别对应insert、update、delete操作

3. 将原表数据拷贝到临时表，如期间有数据更新，通过第二步创建的触发器对临时表也进行更新，保证数据一致。

4. rename原表tablename为tablename_old；然后将临时表rename成tablename

5. 完成在线加索引操作。

注意事项：如表中有外键关联，则命令必须加上--alter-foreign-keys-method参数，否则命令不能执行；执行命令前，原表不能有触发器。

比较熟悉的到这里应该可以看出来了，此工具使用的其实是中间表的方式，如果不想装工具，或者没有安装工具的条件，那么也可以用手动建中间表方式实现软件功能。

二. 中间表方式

假设现在要对表tb1增加索引：

1. 创建tb1的中间表tb1_new

   create table tb1_new like tb1;

2. 将原表数据导入中间表

   insert into tb1_new select * from tb1;

   注意，这里如果表非常大的话，如问题里提到的上亿数据，会非常耗时，很消耗服务器资源，如果服务器配置不高的话，有可能导致系统hang住，这时可考虑使用多次执行分段导入的方式缓解压力：

   insert into tb1_new select * from tb1 where id between 0 and 100000;

   insert into tb1_new select * from tb1 where id between 100001 and 200000;

   .

   .

   insert into tb1_new select * from tb1 where id between 99900001 and 100000000;
   

3. rename原表和中间表

   rename tb1 tb1_old; rename tb1_new tb1;

总结：以上无论是使用在线更改工具还是中间表方式，在修改上亿级大表时都有可能对服务器造成较大压力，导致系统运行缓慢甚至hang住的情况，所以要尽量放在业务非繁忙期窗口进行分段式操作。