MyBatis百万级Limit分页优化，不加索引性能最低提升6倍 _MyBatis

文章插图
前言在实际开发中分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。DBA的同事给出的解决方法就是加“索引/组合索引” ，例如在name,create_time,status等字段上加组合索引，这样根据这些条件查询的时候能够有效的利用索引，性能迅速的就提高了。
以上做法对 select * from table limit 0,10 这个没有问题，但是当 limit 1000000,10 的时候数据读取就很慢了。我只想要10条数据但是每次查询都扫描了100多万行，在高频访问下堵的死死的。那么有什么办法解决这个问题呢？
方案1—最大ID前提：数据库主键ID为自增
正常分页SQL语句为：
select id,name,content,create_time from users order by id asc limit 1000000,10优化后SQL语句为：
select id,name,content,create_time from users where id > 1000000 order by id asc limit 10看到这里大家应该明白了吧？进行查询的时候可以将上一页的最大值ID当成参数作为查询条件的，那么当次查询只需要扫描最大ID开始的20行数据即可。
方案2—入库时间那么有的同学会问如果设计的表主键不是自增又该怎么办？这里还有一种方法根据数据的入库时间，具体方法和上面雷同。
select id,name,content,create_time from users where id > '2020-01-01 12:29:23' order by id asc limit 10这样查询的时间基本固定，并且也不会随着数据量的增长而发生变化。
测评如下图所示，小编准备了300w+的数据，这张表共有28个字段。

文章插图

正常分页所需时长：

文章插图
【MyBatis百万级Limit分页优化，不加索引性能最低提升6倍】
从200w开始获取10条数据共花费12s多。
根据入库时间进行分页：

文章插图

这里的查询时间是上面SQL查询出来结果的create_time ，可以看到花费了2s多。性能提升了约6倍！
备注：create_time 字段没有增加索引。

总结当一个数据库表过于庞大， LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by ，并且order by字段需要建立索引。
如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件， where会过滤数据，使数据失去连续性。
如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field ，则可以通过建立子查询。
SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0 ，这个主键最好是int类型并且auto_increment
SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;这条语句，大意如下:
SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10; 如果limit的offset值过大，用户也会翻页疲劳，你可以设置一个offset最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户。