| |
|
开发:
C++知识库
Java知识库
JavaScript
Python
PHP知识库
人工智能
区块链
大数据
移动开发
嵌入式
开发工具
数据结构与算法
开发测试
游戏开发
网络协议
系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程 数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁 |
-> 大数据 -> 你的分页查询是怎样实现的? -> 正文阅读 |
|
[大数据]你的分页查询是怎样实现的? |
在日常工作中,对于大数据量的展示问题,通常会采用分页显示,对于分页查询的实现,sql查询提供了一种标准化的实现方式,就是利用limit关键字。我们知道limit关键字,可以实现获取目标结果集中的前n行数,如:
除了limit n外,limit 还支持两个参数的查询:limit x,y。分页查询就是使用双参数的limit 实现的。limit x,y表示获取结果集中,从x行开始的y行数据,常见的实现分页查询的方式如下:
然而,这种标准的实现方式,却是有性能问题的。尤其是在高页码分页的场景下,下面,我们一起来分析一下。 一条sql语句的执行流程为了方便下文描述,我们建立如下表结构:
建立表名为 t_limit 的表,其中id为主键索引,age为二级索引。同时使用存储过程 pt_limit 向表中插入10000行数据。 当我们执行如下查询语句:
有经验的读者都知道,这条语句使用索引’ix_age’进行查询,因为age字段在索引 'ix_age’上天然是有序的。这样查询效率会更高。事实上mysql在执行查询的时候,的确使用了’ix_age’索引。可以通过查看这个sql语句的执行计划进行验证: 但是,这里小编要强调一点:一条sql的执行计划,通常情况下有很多种,mysql会从这些执行计划中选择最优的一个来执行。对于上面的那个查询语句,mysql可以通过全表扫描的方式进行查询,也可以通过索引‘ix_age’来完成,不过对于上面的查询条件,通过索引‘ix_age’查询效率更高,于是就选择使用‘ix_age’来完成查询。 虽然我们都知道使用‘ix_age’索引,查询的效率比较高,但是整个查询的执行过程是怎样的呢? 其实上面查询的执行过程大概分以下几个步骤: 2.Innodb在索引’ix_age’上取出值最大的一个叶子节点,也就是最大age值对应的主键id,回表到主键索引上取出完整的数据行,并将数据行返回给server层。 3.server层收到数据后,根据sql的limit 1,判断出只需要一行数据即可,于是将查询的数据返回给客户端,并结束查询。 分页查询执行流程上面的查询语句很好理解,执行结果也符合我们的预期。但是,当我们使用双参数limit实现分页功能时,查询的执行计划又是怎样的呢? 下面我们可以执行以下查询语句进行验证
这条查询语句的语义表示:将表t_limit中的数据按照age字段进行排序,然后在排序后的数据集中,从第10行开始,取出1行数据,简单来说,就是取出满足条件的第11行数据。这条查询语句的执行计划如下: 这条sql在执行过程中,使用了索引’ix_age’,扫描的数据行数也比较少。 不过在上文中我们提到,这种查询方式,在高页码的情况下,查询性能会下降很多。这里我们可以对高页码的查询进行以下实验,实验的查询语句如下:
这条查询语句和上面低页码查询语句的差异,就在于获取结果集的起始位置不同。接下来,我们再来看一下高页码查询的执行计划: 从执行计划可以看出,这条查询语句没有使用索引’ix_age’,而是使用了全表扫描,而且执行计划中还出现了 filesort,有经验的小伙伴都知道,排序是一个比较消耗空间和性能的操作,对mysql排序不太熟悉的小伙伴可以查看如何优化sql中的order by ?。 是不是很奇怪,语义相同的查询语句,因为查询条件的不同,导致执行计划差别很大。要想了解其中的原因,就不得不了解一下双参数limit的执行流程了。 双参数limit执行流程大致如下,这里我们先假设使用索引’ix_age’来完成查询: 1.server层向innodb获取第一条记录,Innodb收到请求后,从’ix_age’索引树上取出第一条索引记录,然后回表获取完整的数据行,并返回给server层。 2.server层收到数据后,开始对limit 5000,1进行处理,也就是符合条件记录中的第5001条,才会返回给客户端。所以在server层,要对这个查询维护一个计数变量,这里我们假设变量为limit_count,来统计从满足条件记录的个数。 3.重复步骤2,直到变量limit_count的值为5000时,server层才会将查询到的数据返回给客户端。 通过上面的分析可以看出,因为limit关键字的处理,是在server层完成。使用索引’ix_age’查询的话,查询语句要进行5001次回表,才能实现整个查询。server层分析下来,感觉这个查询计划,还没有全表扫描+filesort 来的快(当然这个感觉不一定准确)。所以就选择了使用全表扫描的方式,这也就是高页码查询时,性能比较差的根本原因。 如何优化分页查询对于高页码查询性能低的问题,小编根据工作经验总结了两种优化方案:子查询优化和索引定位。 子查询优化在优化limit高页码情况下,查询性能低的问题前,我们需要先了解,这个查询性能低的原因是什么?通过上面的分析,我们知道,双参数limit场景中,第一个参数比较大时,在使用二级索引的情况下,会导致回表次数随着增多,所以mysql选择使用了全表扫描的方式,进而导致整个查询性能比较低。 既然查询性能低,是由回表过多间接导致的。那么,我们可以朝着向"避免或者减少回表次数"的方向来优化查询。我们知道二级索引覆盖了主键id,因此我们可以通过如下sql来实现高页码查询。
子查询
可以实现只扫描索引’ix_age’实现高页码查询,而不需要回表。下图为优化后sql的执行计划: 索引定位如果我们可以利用索引直接定位到,双参数limit中第一个参数的位置,那么接下来只需要在索引树上,再向后或者向前扫描pagesize行数据即可。查询优化方式如下:
这种查询方式,可以直接使用索引’ix_age’,只需要扫描一行数据,回表一次即可完成语句的查询。 具体执行计划,以及扫描行数可以通过以下语句进行查看
执行计划如下: 可以使用下面语句来确认一条sql语句,在Innodb中扫描的数据行数
总结对于分页查询的性能问题,除了上面提到的优化方案外,你还有其他的优化方法吗?除了上文提到的分页查询优化方案外,这里小编还要告诉小伙伴,有时候mysql自己选择的执行计划并不一定是最优,对于查询性能低的查询语句,我们要能够根据执行计划,进行一定的优化,还有就是,执行计划中显示的扫描行数,只是一个估计值,要想精确得到一个查询语句在Innodb中扫描行数,可以使用上文中小编提供的小技巧。 |
|
|
上一篇文章 下一篇文章 查看所有文章 |
|
开发:
C++知识库
Java知识库
JavaScript
Python
PHP知识库
人工智能
区块链
大数据
移动开发
嵌入式
开发工具
数据结构与算法
开发测试
游戏开发
网络协议
系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程 数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁 |
360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 | -2024/11/24 2:29:17- |
|
网站联系: qq:121756557 email:121756557@qq.com IT数码 |