处理一次物流系统mysql大并发全表扫描SQL增加索引的过程

1.查出与表相关的会话，并杀之
select concat("kill ", id,";") from information_schema.processlist where user='hlsprod' and info like '%task_detail%'  into outfile '/tmp/1.sql';
source '/tmp/1.sql';
2.建立新表结构
create table task_detail_new like task_detail;
3.新表上增加索引
a.在线增加
ALTER TABLE `task_detail_new`ADD INDEX `idx4` (`referenceId`);
b.pt-osc增加
cd /apps/sh/tool/percona-toolkit/bin
./pt-online-schema-change --host=192.168.1.2 -u root -p xxx --alter='add key idx4(referenceId)' --print --execute D=wmsb,t=task_detail_new
4.将原表的数据插入到新表
insert into task_detail_new select * from task_detail;
5.将原表改名
ALTER TABLE task_detail RENAME to  task_detail_old;
6.将新表改回原表名
ALTER TABLE task_detail_new RENAME to  task_detail;
7.最后一步需要检查表结构和数据是否跟原表一致
select count(*) from task_detail;
select count(*) from task_detail_old;
show create table task_detail\G
show create table task_detail_old\G