mysql亿万级大表在线添加索引

步骤：

create table t_sys_test_temp like t_sys_test;

alter table t_sys_test_temp add index age_index(age);

INSERT into t_sys_test_temp(id,name,age,card_id,address,create_time,update_time,manager_id,deleted,sex) select id,name,age,card_id,address,create_time,update_time,manager_id,deleted,sex from t_sys_test rename table t_sys_test to temp2 ,t_sys_test_temp to t_sys_test ;

drop table temp2;

 

1先创建临时表t_sys_test_temp
2向临时表t_sys_test_temp 添加索引
3将目标表的数据查询出来插入到临时表，再将目标表t_sys_test重命名为temp2,
将临时表t_sys_test_temp 重命名为结果表t_sys_test
4删除临时表temp2