MySQL深分页导致CPU飙升解决方法总结

本周同步一张历史数据（大约1亿）表入ES，1000条为一个批次，最开始时按照表的创建时间（有索引）以天为单位进行的数据同步，在同步的过程中联系DBA老师查看数据库负载情况，最开始同步时CPU还算稳定，但是越到后面，CPU就开始飙升的非常高，甚至达到了90%以上，这时候其实出现了MySQL的深分页问题，导致大量的慢SQL，如下图：

数据库CPU监控

优化前sql为：

-- 以创建时间进行范围查询
select * from table where create_time>=#{startDate} and create_time <=#{startDate} order by id asc limit 900000,1000;

以上sql不仅导致CPU飙升，同时效率比较低，耗时较长，存在回表问题，于是将上面的sql进行优化，拆分为以下2个sql来处理，sql如下：

-- 获取范围查询的结束主键ID（endID）
select id from table where id>=#{startID} order by id asc limit 1000,1;
-- 以主键进行范围查询
select * from table where id>=#{startID} and id<#{endID}

优化完之后数据库CPU负载正常，同步时间降为1.5小时（分8个线程）完成。