数据库超大的分页查询功能

这篇文章其实也是自己的学习历程

 

背景

基本上只要是做后台开发，都会接触到分页这个需求或者功能吧。基本上大家都是会用MySQL的LIMIT来处理，而且我现在负责的项目也是这样写的。但是一旦数据量起来了，其实LIMIT的效率会极其的低，这一篇文章就来讲一下LIMIT子句优化的。

LIMIT优化

很多业务场景都需要用到分页这个功能，基本上都是用LIMIT来实现。

建表并且插入200万条数据：

# 新建一张test表
CREATE TABLE `test` (
 `id` int NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `text` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ix_name` (`name`),
 KEY `ix_test` (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建存储过程插入200万数据
CREATE PROCEDURE test_insert_200w()
BEGIN
 DECLARE i INT;
 SET i=1000000;
 WHILE i<=3000000 DO
 INSERT INTO test(`name`,text) VALUES('optimize1123',concat('text', i));
 SET i=i+1;
 END WHILE;
END;

# 调用存储过程插入200万数据
call t5_insert_200w();

 

 

 

 

 插入的是200万+1条，理由是存储过程的判断边界有问题

 

在翻页比较少的情况下，LIMIT是不会出现任何性能上的问题的。

但是如果用户需要查到最后面的页数呢？

通常情况下，我们要保证所有的页面可以正常跳转，因为不会使用order by xxx desc这样的倒序SQL来查询后面的页数，而是采用正序顺序来做分页查询：

select * from test order by text limit 100000, 10;

 

 

 采用这种SQL查询分页的话，从200万数据中取出这10行数据的代价是非常大的，需要先排序查出前1000010条记录，然后抛弃前面1000000条。我的macbook pro跑出来花了1.87秒。

接下来我们来看一下，上面这条SQL语句的执行计划：

 

 

从执行计划可以看出，在大分页的情况下，MySQL没有走索引扫描，即使text字段我已经加上了索引。

这是为什么呢？

回到MySQL索引（二）如何设计索引中有提及到，MySQL数据库的查询优化器是采用了基于代价的，而查询代价的估算是基于CPU代价和IO代价。

如果MySQL在查询代价估算中，认为全表扫描方式比走索引扫描的方式效率更高的话，就会放弃索引，直接全表扫描。

这就是为什么在大分页的SQL查询中，明明给该字段加了索引，但是MySQL却走了全表扫描的原因。

然后我们继续用上面的查询SQL来验证我的猜想：

 

 以上的实验均在我的mbp上运行的，在5980这个临界点上，MySQL分别采用了索引扫描和全表扫描的查询优化方式。不同的机器这个临界点是不一样的哦

所以可以认为MySQL会根据它自己的代价查询优化器来判断是否使用索引。

由于MySQL的查询优化器的算法核心是我们无法人工干预的，所以我们的优化思路就要着手于如何让分页维持在最佳的的分页临界点。

优化方式

1、使用覆盖索引

如果一条SQL语句，通过索引可以直接获取查询的结果，不再需要回表查询，就称这个索引为覆盖索引。

在MySQL数据库中使用explain关键字查看执行计划，如果extra这一列显示Using index，就表示这条SQL语句使用了覆盖索引。

让我们来对比一下使用了覆盖索引，性能会提升多少吧。

# 没有使用覆盖索引
select * from test order by text limit 1000000, 10;

 

 

 

 

# 使用了覆盖索引
select id, text from test order by text limit 1000000, 10;

 

 

 

 从上面的对比中，超大分页查询中，使用了覆盖索引之后，花了0.2秒，而没有使用覆盖索引花了2.15秒，提高了10倍的效率，这在实际开发中，就是一个大的性能优化了。

2、子查询优化

因为实际开发中，用SELECT查询一两列操作是非常少的，因此上述的覆盖索引的适用范围就比较有限。

所以我们可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升。

select * from test where id>=(select id from test order by text limit 1000000, 1) limit 10;

 

 

但是这种优化方法也有局限性：

• 这种写法，要求主键ID必须是连续的
• Where子句不允许再添加其他条件

3、延迟关联

和上述的子查询做法类似，我们可以使用JOIN，先在索引列上完成分页操作，然后再回表获取所需要的列。

select a.* from test a inner join (select id from test order by text limit 1000000, 10) b on a.id=b.id;

 

 从实验中可以得出，在采用JOIN改写后，上面的两个局限性都已经解除了，而且SQL的执行效率也没有损失。

4、记录上次查询结束的位置

和上面使用的方法都不同，记录上次结束位置优化思路是使用某种变量记录上一次数据的位置，下次分页时直接从这个变量的位置开始扫描，从而避免MySQL扫描大量的数据再抛弃的操作。

 

 

根据以上实验，不难得出，由于使用了主键索引做分页操作，SQL的性能是最快的。

关于教你使用MySQL数据库实现一个超大的分页查询功能问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。

附录

教你使用MySQL数据库实现一个超大的分页查询功能

MySQL百万级数据量分页查询方法及其优化建议

MySQL 百万级数据，怎么做分页查询？

MySQL深度分页的问题及优化方案：千万级数据量如何快速分页