大数据量下的分页查询优化技巧

原创

码不停Ti 2022-01-19 09:32:41 博主文章分类：面试复习 ©著作权

©著作权归作者所有：来自51CTO博客作者码不停Ti的原创作品，请联系作者获取转载授权，否则将追究法律责任

上个月负责公司人群包的查询毕竟主产品上亿的注册量，分页查询查到10W后就会很慢，
上次写了一个 sql 语句导出一个中低活人群包就整了两个小时可以见得数据之大普通的sql 语句就肯定需要尽可能优化优化
最后是用限定 id 做了查询优化，
看了几篇博客刚好现在可以整理一下数据库分页查询的优化技巧
基本上全网都是这么写的我简化了一下保留我想记住的内容
《大数据量下的分页查询优化》

一般分页查询

这个就是大家在初学SQL 语句的时候都会学习的 limit 语句基础用法

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：

第一个参数指定第一个返回记录行的偏移量，注意从0开始
第二个参数指定返回记录行的最大数目
如果只给定一个参数：它表示返回最大的记录行数目
第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行
初始记录行的偏移量是 0(而不是 1)

还是用最常用的学生表举例子吧

select * from students where class_id=8 order by id limit 10000,10;

这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

使用子查询优化

使用 select id 代替 select * 速度增加了3倍

这种方式假设数据表的id是连续递增的

select * from students where class_id=8limit 100000,1;

select id from students where class_id=8 limit 100000,1;

select * from students where class_id=8 and 
id>=(select id from students where class_id=8 limit 100000,1) 
limit 100;

select * from students where class_id=8 limit 100000,100;

使用 id 限定优化

这种方式假设数据表的id是连续递增的

限于明确知道id 但是速度会更大的优化

between… and.有局限性，不能增加过滤条件，否则很容易造成id不连续，这样得到的结果只会是正确结果的子集

写法一

select * from students where class_id=8
and id between 1000000 and 1000100 limit 100;

写法二

select * from students where class_id=8 and id >= 1000001 limit 100;

这种 in 查询的方式要注意：某些 mysql 版本不支持在 in 子句中使用 limit。

使用临时表优化

这个不太懂目前也没用到过

这种方式已经不属于查询优化，这儿附带提一下。
对于使用 id 限定优化中的问题，需要 id 是连续递增的，
但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，
使用分页的id来进行 in 查询。
这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。