本文共享自华为云社区《大数据量功能优化之分页查询》，作者： JavaEdge。

刷帖子翻页需求分页查询，查找商品也需分页查询。当遇到上千万、上亿数据量，怎么快速拉取全量数据呢？比方：

大商家拉取每月千万级别的订单数量到自己独立的ISV做财务计算
拥有百万千万粉丝的大v，给悉数粉丝推送音讯

案例

常见错误写法

SELECT *
FROM table
where kid = 1342
  and type = 1
order id asc
limit 149420,20;

典型的排序+分页查询：

order by col limit N,OFFSET M

MySQL 执行此类SQL时：先扫描到N行，再取 M行。N越大，MySQL需扫描更多数据定位到详细的N行，这会耗费大量的I/O本钱和时刻本钱。

为什么上面的SQL写法扫描数据会慢？

t是个索引安排表，key idx_kid_type(kid,type)

契合kid=3 and type=1 的记载有很多行,咱们取第 9，10行。

select * from t where kid =3 and type=1 order by id desc 8,2;

对于Innodb，依据 idx_kid_type 二级索引里面包括的主键去查找对应行。对百万千万级记载，索引巨细或许和数据巨细相差无几，cache在内存中的索引数量有限，并且二级索引和数据叶子节点不在同一物理块存储，二级索引与主键的相对无序映射联系，也会带来大量随机I/O请求，N越大越需遍历大量索引页和数据叶，需求耗费的时刻就越久。

由于上面大分页查询耗时长，是否真的有必要完全遍历“无效数据”？若需求：

limit 8，2

越过前面8行无关数据页的遍历，可直接经过索引定位到第9、10行，这样是不是更快？

这便是推迟相关的中心思想：经过运用掩盖索引查询回来需求的主键，再依据主键相关原表取得需求的数据，而非经过二级索引获取主键再经过主键遍历数据页。

经过如上分析可得，经过惯例办法进行大分页查询慢的原因，也知道了提高大分页查询的详细办法。

一般分页查询

简单的 limit 子句。limit 子句声明如下：

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

limit 子句用于指定 select 句子回来的记载数，注意：

offset 指定第一个回来记载行的偏移量，默许为0 初始记载行的偏移量是0，而非1
rows 指定回来记载行的最大数量 rows 为 -1 表明检索从某个偏移量到记载集的结束所有的记载行。

若只给定一个参数：它表明回来最大的记载行数目。

实例：

select * from orders_history where type=8 limit 1000,10;

从 orders_history 表查询offset: 1000开始之后的10条数据，即第1001条到第1010条数据（1001 <= id <= 1010）。

数据表中的记载默许运用主键（id）排序，上面成果等价于：

select * from orders_history where type=8
	order by id limit 10000,10;

三次查询时刻分别为：

3040 ms
3063 ms
3018 ms

针对这种查询办法，下面测验查询记载量对时刻的影响：

select * from orders_history where type=8 limit 10000,1;
select * from orders_history where type=8 limit 10000,10;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 10000,1000;
select * from orders_history where type=8 limit 10000,10000;

三次查询时刻：

查询1条记载：3072ms 3092ms 3002ms
查询10条记载：3081ms 3077ms 3032ms
查询100条记载：3118ms 3200ms 3128ms
查询1000条记载：3412ms 3468ms 3394ms
查询10000条记载：3749ms 3802ms 3696ms

在查询记载量低于100时，查询时刻根本无差距，随查询记载量越来越大，消耗时刻越多。

针对查询偏移量的测验：

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;

三次查询时刻如下：

查询100偏移：25ms 24ms 24ms
查询1000偏移：78ms 76ms 77ms
查询10000偏移：3092ms 3212ms 3128ms
查询100000偏移：3878ms 3812ms 3798ms
查询1000000偏移：14608ms 14062ms 14700ms

随着查询偏移的增大，尤其查询偏移大于10万今后，查询时刻急剧增加。

这种分页查询办法会从DB的第一条记载开始扫描，所以越往后，查询速度越慢，并且查询数据越多，也会拖慢总查询速度。

优化

前端加缓存、查找，减少落到库的查询操作比方海量商品可以放到查找里面，运用瀑布流的办法展现数据.
优化SQL 访问数据的办法直接快速定位到要访问的数据行。推荐运用”推迟相关”的办法来优化排序操作，何谓”推迟相关” ：经过运用掩盖索引查询回来需求的主键,再依据主键相关原表取得需求的数据。
运用书签办法，记载上次查询最新/大的id值，向后追溯 M行记载

推迟相关

优化前

explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+
| id  | select_type | table           | type  | possible_keys | key            | key\_len | ref  | rows   | Extra   |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+
| 1  | SIMPLE      | relation    | range | ind\_endtime   | ind\_endtime | 9       | NULL | 349622 | Using                                                                                                                       where; Using filesort |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+

执行时刻：

20 rows in set (1.05 sec)

优化后:

explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end\_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows   | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| 1  | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL | 20     |       |
| 1  | PRIMARY     | a           | eq_ref | PRIMARY       | PRIMARY | 8       | b.id | 1      |       |
| 2  | DERIVED     | relation    | index  | ind_endtime   | PRIMARY | 8       | NULL | 733552 |       |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+

执行时刻:

20 rows in set (0.36 sec)

优化后执行时刻为原来的1/3 。

书签

首要获取契合条件的记载的最大 id和最小id(默许id是主键)

select max(id) as maxid ,min(id) as minid
	from t where kid=2333 and type=1;

依据id 大于最小值或许小于最大值进行遍历。

select xx,xx from t where kid=2333 and type=1
	and id >=min_id order by id asc limit 100;
select xx,xx from t where kid=2333 and type=1 
	and id <=max_id order by id desc limit 100;

案例

当遇到推迟相关也不能满足查询速度的要求时

SELECT a.id as id, client_id, admin_id, kdt_id, type, token, created_time, update_time, is_valid, version FROM t1 a, (SELECT id FROM t1 WHERE 1 and client_id = 'xxx' and is_valid = '1' order by kdt_id asc limit 267100,100 ) b WHERE a.id = b.id;

100 rows in set (0.51 sec)

运用推迟相关查询数据510ms ，运用依据书签模式的解决办法减少到10ms以内绝对是一个质的腾跃。

SELECT * FROM t1 where client_id='xxxxx' and is_valid=1 and id<47399727 order by id desc LIMIT 100;

100 rows in set (0.00 sec)

小结

依据主键定位数据的办法直接定位到主键起始位点，然后过滤所需求的数据。

相对比推迟相关的速度更快，查找数据时少了二级索引扫描。但优化办法没有银弹，比方：order by id desc 和 order by asc 的成果相差70ms ，生产上的案例有limit 100 相差1.3s ，这是为啥？

还有其他优化办法，比方在运用不到组合索引的悉数索引列进行掩盖索引扫描的时候运用 ICP 的办法也可以加速大分页查询。

子查询优化

先定位偏移位置的 id，然后往后查询，适于 id 递加场景：

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

4条句子的查询时刻如下：

第1条句子：3674ms
第2条句子：1315ms
第3条句子：1327ms
第4条句子：3710ms

1 V.S 2：select id 代替 select *，速度快3倍
2 V.S 3：速度相差不大
3 V.S 4：得益于 select id 速度增加，3的查询速度快了3倍

这种办法相较于原始一般的查询办法，将会增快数倍。

运用 id 约束优化

假设数据表的id是接连递加，则依据查询的页数和查询的记载数可以算出查询的id的范围，可运用 id between and：

select *
from order_history
where c = 2
  and id between 1000000 and 1000100
limit 100;

查询时刻：

15ms
12ms
9ms

这可以极大地优化查询速度，根本可以在几十毫秒之内完成。约束是只能运用于明确知道id。

另一种写法：

select *
from order_history
where id >= 1000001
limit 100;

还可以运用 in，这种办法经常用在多表相关时进行查询，运用其他表查询的id调集，来进行查询：

select *
from order_history
where id in
      (select order_id from trade_2 where goods = 'pen')
limit 100;

暂时表

现已不属于查询优化，这儿顺便提一下。

对于运用 id 约束优化中的问题，需求 id 是接连递加的，但是在一些场景下，比方运用历史表的时候，或许出现过数据缺失问题时，可以考虑运用暂时存储的表来记载分页的id，运用分页的id来进行 in 查询。这样可以极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

数据表的id

一般在DB建立表时，强制为每一张表增加 id 递加字段，方便查询。

像订单库等数据量很大，一般会分库分表。这时不推荐运用数据库的 id 作为仅有标识，而应该运用分布式的高并发仅有 id 生成器，并在数据表中运用另外的字段来存储这个仅有标识。

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

参阅

segmentfault.com/a/119000003…

点击关注，第一时刻了解华为云新鲜技术~

亿级数据量场景下，如何优化数据库分页查询方法

案例

一般分页查询

实例：

优化

推迟相关

书签

案例

小结

子查询优化

运用 id 约束优化

暂时表

数据表的id

参阅

作者信息

推广

亿级数据量场景下，如何优化数据库分页查询方法

案例

一般分页查询

实例：

优化

推迟相关

书签

案例

小结

子查询优化

运用 id 约束优化

暂时表

数据表的id

参阅

相关文章

精选版：用Java扩展Nginx（nginx-clojure 入门）

Android进阶宝典 — Kotlin协程分析（创建、取消、超时）

zookeeper+kafka

使用 Postman 的预请求脚本实现自动刷新接口凭证

作者信息

推广