mysql 为大表添加索引,导致超时的解决办法
简单的创建索引语句 : create unique index inxName on table A('Col') 。
如果表数据量不大,没有问题,但是数据超过千万,可能你等了半天,却告知你超时了。
网上查到解决方案:
1. 复制表A 的数据结构 , 不复制数据
create table B like A;
2. 表B加上你需要的索引
3. 把原有数据导入新表
4. 修改表A 的名称为A_old , 修改B表的 表名为A.
我的问题出在步骤3.
使用如下语句导入A表数据到B :
insert into B select * from A;
执行了半天,又提示错误 table x is full。
网上找到另一种导入方式,即把A表数据导入文件,然后load 进B表
select * from A into outfile '/var/money.txt';
load data infile '/var/money.txt' into table B; -- 这个路径需要和show variables like '%secure%'; 一致
我执行的时候提示没有权限,需要开启secure_file_priv 相关权限 ,我没有root用户权限,所以就放弃该方案了。
解决:
insert into B select * from A; 错误提示 table x is full , 那就是一次导入太大。 那我改小点就是了,我们数据2000万条。
我按照日期 分组 (注意表A 原有按日期的索引),分组后发现最大的一天有700万条数据。
把导入语句改为
insert into B select * from A where trans_date = '20190923'; -- 执行时间1735s,近30分钟,还好没有报错。
100多万的 46s 搞定,其他数据更少的日期基本几秒可以搞定,最终1个半小时完成数据复制