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

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

文章目录

• • 一般分页查询
  • 使用子查询优化
  • 使用 id 限定优化
  • 使用临时表优化
  • 关于数据表的id说明

一般分页查询

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

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

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

1. 第一个参数指定第一个返回记录行的偏移量，注意从0开始
2. 第二个参数指定返回记录行的最大数目
3. 如果只给定一个参数：它表示返回最大的记录行数目
4. 第二个参数为 -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 查询。 这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

关于数据表的id说明

分库存储的大表 就需要 ID 生成器了

一般情况下，在数据库中建立表的时候，强制为每一张表添加 id 递增字段，这样方便查询。

如果像是订单库等数据量非常庞大，一般会进行分库分表。这个时候不建议使用数据库的 id 作为唯一标识，而应该使用分布式的高并发唯一 id 生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

先通过范围查 id 再通过id 拿数据

使用先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id，然后再 select *。

《大数据量下的分页查询优化》https://segmentfault.com/a/1190000038856674