11 月 19 - 20 日 Apache Pulsar 社区年度盛会来啦,立即报名! 了解详情
写点什么

性能优化之分页查询

  • 2020-03-11
  • 本文字数:2797 字

    阅读完需:约 9 分钟

性能优化之分页查询

一、背景

大部分开发和 DBA 同行都对分页查询非常非常了解,看帖子翻页需要分页查询,搜索商品也需要分页查询。那么问题来了,遇到上千万或者上亿的数据量怎么快速的拉取全量,比如大商家拉取每月千万级别的订单数量到自己独立的 ISV 做财务统计;或者拥有百万千万粉丝的公众大号,给全部粉丝推送消息的场景,该怎么处理呢?本文讲讲个人的优化分页查询的经验,抛砖引玉。


二、分析

在讲如何优化之前我们先来看看一个比较常见错误的写法:


SELECT * FROM tablewhere kid=1342 and type=1 order id asc limit 149420,20;
复制代码


该 SQL 是一个非常典型的排序+分页查询:


order by col limit N, M
复制代码


MySQL 执行此类 SQL 时需要先扫描到 N 行,然后再去取 M 行。对于此类操作,取前面少数几行数据会很快,但是扫描的记录数越多,SQL 的性能就会越差,因为 N 越大,MySQL 需要扫描越多的数据来定位到具体的 N 行,这样耗费大量的 IO 成本和时间成本。一图胜千言,我们使用简单的图来解释为什么 上面的 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;
复制代码


MySQL 是如何执行上面的 sql 的?对于 Innodb 表,系统是根据 idx_kid_type 二级索引里面包含的主键去查找对应的行。对于百万千万级别的记录而言,索引大小可能和数据大小相差无几,cache 在内存中的索引数量有限,而且二级索引和数据叶子节点不在同一个物理块儿上存储,二级索引与主键的相对无序映射关系,也会带来大量的随机 IO 请求,N 值越大越需要遍历大量索引页和数据叶,需要耗费的时间就越久。



鉴于上面的大分页查询耗费时间长的原因,我们思考一个问题,是否需要完全遍历“无效的数据”?如果我们需要 limit 8,2;我们跳过前面 8 行无关的数据页遍历,可以直接通过索引定位到第 9,第 10 行,这样操作是不是更快了?依然是一图胜千言,通过这其实也是 延迟关联的 核心思思:通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,而不是通过二级索引获取主键再通过主键去遍历数据页。



通过上面的原理分析,我们知道通过常规方式进行大分页查询慢的原因,也知道了提高大分页查询的具体方法 ,下面我们讨论一下在线上业务系统中常用的解决方法。


三、实践出真知

针对 limit 优化有很多种方式:


  1. 前端加缓存、搜索,减少落到库的查询操作。比如海量商品可以放到搜索里面,使用瀑布流的方式展现数据,很多电商网站采用了这种方式。

  2. 优化 SQL 访问数据的方式,直接快速定位到要访问的数据行。

  3. 使用书签方式 ,记录上次查询最新/大的 id 值,向后追溯 M 行记录。 对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作,何谓"延迟关联" :通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。


3.1 延迟关联

优化前


root@xxx 12:33:48>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 |+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+1 row in set (0.00 sec)
复制代码


其执行时间:



优化后:


root@xxx 12:33:43>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 |    |+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+3 rows in set (0.36 sec)
复制代码


执行时间:



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


3.2 使用书签的方式

首先要获取复合条件的记录的最大 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;
复制代码



使用延迟关联查询数据 510ms ,使用基于书签模式的解决方法减少到 10ms 以内绝对是一个质的飞跃。SELECT * FROM t1 where client_id=‘xxxxx’ and is_valid=1 and id<47399727 order by id desc LIMIT 100;



四、小结

从我们的优化经验和案例上来讲,根据主键定位数据的方式直接定位到主键起始位点,然后过滤所需要的数据 相对比延迟关联的速度更快些,查找数据的时候少了二级索引扫描。但是 优化方法没有银弹,没有一劳永逸的方法。比如下面的例子



order by id desc 和 order by asc 的结果相差 70ms ,生产上的案例有 limit 100 相差 1.3s ,这是为什么呢?留给大家去思考吧。


最后,其实我相信还有其他优化方式,比如在使用不到组合索引的全部索引列进行覆盖索引扫描的时候使用 ICP 的方式 也能够加快大分页查询。


以上是我在优化分页查询方面的经验总结,抛砖引玉,有兴趣的朋友可以多交流,分享你们的优化经验案例。


2020-03-11 22:191045

评论 2 条评论

发布
用户头像
厉害啊
2020-04-14 20:10
回复
没有更多了
发现更多内容

架构师训练营第 1 期 - 第 12 周 - 命题作业

wgl

架构师训练营第 1 期

我能加入写作训练营,一切都因为...

李忠良

个人成长 驱动力量 28天写作

架构师训练营大作业(一)

曾彪彪

「架构师训练营第 1 期」

架构师训练营大作业(二)

曾彪彪

「架构师训练营第 1 期」

生产环境全链路压测建设历程 24:FAQ 5、6负载均衡、如何不影响正常业务?

数列科技杨德华

28天写作

第2周总结-架构中的设计模式

潘涛

架构师训练营 4 期

三只猫

Flink 自定义Avro序列化(Source/Sink)到kafka中

大数据老哥

大数据 flink hadoop

关系中的密码:麻烦

熊斌

个人成长 28天写作 亲密关系

职业成长就是一个逐渐“变帅”的过程

俊毅

【计算机内功修炼】三:一文彻底理解IO多路复用

码农的荒岛求生

epoll

28天瞎写的第二百一二天:一次删库没跑路的故事

树上

28天写作 删库

写在再次学习python之前-why篇

赵开忠

Python 28天写作

SafePoint 与 Stop The World 全解(基于OpenJDK 11版本)

AI乔治

Java 架构 jdk JVM

第2周课后练习-OOD的五大原则

潘涛

架构师训练营 4 期

序言 基层管理者技能修炼的九把刀

一笑

管理 28天写作

解读《Java开发手册(泰山版)》- 会当凌绝顶,一览众山小

xcbeyond

Java Java开发手册 28天写作

【Node.js】静态页面和简单的路由

德育处主任

大前端 Node 28天写作

区块链的前世今生(1)

抗哥

CSS10 - 盒子模型&常用无序列表样式

Mr.Cactus

html/css

28天带你玩转Kubernetes--第一天(课程介绍)

Java全栈封神

Kubernetes 云原生 k8s入门 28天写作 k8s教程

我们为什么要学习Springboot?

武哥聊编程

Java springboot SpringBoot 2 28天写作

学创业,读毛选 Jan 9, 2021

王泰

28天写作 读毛选,学创业

记一次JVM OOM 实战优化

AI乔治

Java 架构 JVM OOM

自下而上的问题清单

将军-技术演讲力教练

28天写作

视频号发展简史&第一天数据 | 视频号28天(02)

赵新龙

28天写作

小心!你可能搞了个假的头脑风暴!

Justin

团队协作 28天写作 头脑风暴 群体迷思 创造性思维

架构师训练营第 1 期 - 大作业1

Anyou Liu

架构师训练营第 1 期

区块链挖矿系统APP软件开发

系统开发

kill -9 导致 kafka 重启失败的惨痛经历!

AI乔治

Java kafka 架构

HDFS SHELL详解(1)

罗小龙

hadoop 28天写作 hdfs shell

性能优化之分页查询_文化 & 方法_有赞技术_InfoQ精选文章