mysql大数据量下的limit m , n分页查询缓慢的优化办法

一、日常开发中,有些业务需要，我们使用mysql来实现分页功能的时候,总会用到mysql的limit语法

1.返回符合条件的前10条语句

select * from abc where xxx limit 10;

2.返回符合条件的第11-20条数据

select * from abc where xxx limit 10,20;

也可以说limit n 等价于limit 0,n;

二、实际使用中，在分页的越往后面，加载会变的非常慢

业务中涉及到需要同步查询的表，数量级在千万左右，假如每次查询同步50条记录，越往后查询，分页越大…

select * from abc where xxx limit 1000000,50;
...
select * from abc where xxx limit 9000000,50;

这样的查询非常慢，本地使用navicat模拟时，一般要花费几秒到十几秒不等，sql语句根本不能这样写，因为limit 1000000,50的语法实际上是mysql查找到前1000050条数据，之后丢弃前面的1000000行，这个步骤其实是浪费掉的，服务估计会直接挂掉.

三、优化

1.看到网上有部分使用覆盖索引 ，连接查询优化:

“mysql的查询完全命中索引的时候,称为覆盖索引,是非常快的,因为查询只需要在索引上进行查找,之后可以直接返回,而不用再回数据表拿数据.因此我们可以先查出索引的ID,然后根据Id拿数据.”

select * from abc where id >=   
 (select id from abc where xxx limit 10000, 1) limit 10  

或者

select * from (select id from abc where xxx limit 1000000,50) a left join abc b on a.id = b.id;

这种子查询分页大的时候依然很慢…使用本地navicat，在有大数据量的表中试了一下，也要几秒到十几秒不等(也有可能和电脑性能，网络有关)

select id from abc where xxx limit 1000000,50 -- 3.865s
select id from abc where xxx limit 2000000,100 -- 13.400s
select * from (select id from abc where xxx limit 3000000,50) a left join abc b on a.id = b.id; -- 6.37s

3.可配合业务需要共同优化sql(也是大量数据中分批查询)，这种查询最快

 Long startId = 0L;
 List<AbcEntity> abcList = abcService.findListByXxx(startId, xxx);
	for (AbcEntity entity : abcList) {
	 // ......业务逻辑
      startId = entity.getId();
    }

对应的sql语句

select * from abc where xxx and id > #{startId} LIMIT 50;
-- 同上相同数量级的表中测试结果
select * from abc where id > 1000000 LIMIT 50; -- 0.386s
select * from abc where id > 9000000 LIMIT 50;  -- 0.247s