最近大后台查看一些数据统计的时候,很慢,甚至会有超时情况,前端设置的超时时间是20秒。

后来通过查看日志和慢查询,发现一条sql语句执行时间超过18秒,基本都19秒左右。

select count(*) from tb_name where create_time > xxx;

最终得知是因为这个表数据行数已经超过 一千万了,然后create_time字段又没有索引 。

那解决办法肯定是加索引喽。

但是这个表是一直在线上运行,很重要和业务部分。如果给千万级的大表在线加索引 ,肯定会卡死。

然后就搜罗了一大筐解决方案,比如 在线无锁加索引使用 

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
后来才发现,这个特性是Mysql 5.6以后才支持,然而我们的mysql用的是5.5版本

最后在 《高性能Mysql》一书中看到,可在通过 “影子拷贝”来解决,

就是 先创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表;

#操作步骤:
#1、创建一张和原表结构一样的空表,只是表名不一样
    create table tb_name_tmp like tb_name;

#2、把新建的空表非主键索引都删掉,因为这样在往新表导数据的时候效率会很快(因为除了必要的主键以外,不用再去建立其它索引数据了)
    alter tb_name_tmp drop index index_name;

#3、从旧表往主表里导数据,如果数据太大,建议分批导入,只需确保无重复数据就行,因为导入数据太大,会很占用资源(内存,磁盘io, cpu等),可能会影响旧表在线上的业务。我是每批次100万条数据导入,基本上每次都是在 20s左右
    insert into tb_name_tmp select * from tb_name where id between start_id and end_id;

#4、数据导完后,再对新表进行添加索引
     create index index_name on tb_name_tmp(column_name);

#5、当大部分数据导入后,索引也建立好了,但是旧表数据量还是会因业务的增长而增长,这时候为了确保新旧表的数据一至性和平滑切换,建议写一个脚本,判断当旧表的数据行数与新表一致时,就切换。我是以 max(id)来判断的。
    rename table tb_name to tb_name_tmp1;
    rename table tb_name_tmp to tb_name;

当给新表加完索引后,最上面那条查询直接就是0.0002s


写在最后:

1.按照如上操作,最后一批数据仍然可能有误差,这时候可以视业务场景设置旧表为只读,保证两表数据一致

2.mysql在线改表工具pt-osc天然支持上述操作,可以尝试使用

3.数据量大加索引可能导致锁表甚至拖垮数据库,应在业务量少的时候操作