MYSQL大数据量分页查询优化 - 十万、百万级优化

1. 常见问题

在mysql大数据量的基础下，分页中随着页码的增加，查询时间也会响应的增加。所以到了百万级别的数据量时，我们就需要优化已有的查询代码进行合理有效的分页。

一般情况下，页码与查询时间成正比

SELECT count(*) FROM `sys_user`
结果： 4344284
> 时间: 1.132s

SELECT * FROM `sys_user` LIMIT 10000,20
> 时间: 0.095s

SELECT * FROM `sys_user` LIMIT 100000,20
> 时间: 0.181s

SELECT * FROM `sys_user` LIMIT 1000000,20
> 时间: 1.302s

SELECT * FROM `sys_user` LIMIT 4000000,20
> 时间: 5.498s

2. 出现的原因

我们先要了解到 limit 4000000,20的意思扫描满足条件的4000020行，扔掉前面的4000000行，返回最后的20行，问题就在这里，其实我们每次都只是需要20行的数据内容，那么到后面每次查询需要扫描超过400W+行，性能肯定大打折扣。

3. 解决方式

对超过特定阈值的页数进行 SQL改写：

SELECT
	a.* 
FROM
	`sys_user` a,
	( SELECT id FROM `sys_user` LIMIT 4000000, 20 ) b 
WHERE
	b.id = a.id
> 时间: 0.699s

将语句抽离成一个可以复用的sql

SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

4. 原理

利用覆盖索引来进行查询操作，避免回表

说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。我们语句中的覆盖索引就使用的就是

SELECT id FROM `sys_user` LIMIT 4000000, 20

这条sql直接拿到我们要查询的20条数据的id，再去表中查询它的具体数据