1. 首页
  2. IT资讯

从 MySQL 执行原理告诉你:为什么分页场景下,请求速度非常慢?

“u003Ch1u003Eu003Cstrongu003E从一个问题说起u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cpu003E五年前在腾讯的时候,发现分页场景下,mysql请求速度非常慢。数据量只有10w的情况下,select xx from 单机大概2,3秒。u003Cu002Fpu003Eu003Cpu003E我就问我师父为什么,他反问“索引场景,mysql中获得第n大的数,时间复杂度是多少?”u003Cu002Fpu003Eu003Ch1u003Eu003Cstrongu003E答案的追寻u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cpu003Eu003Cstrongu003E确认场景u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003E假设status上面有索引。select * from table where status = xx limit 10 offset 10000。u003Cu002Fpu003Eu003Cpu003E会非常慢。数据量不大的情况就有几秒延迟。u003Cu002Fpu003Eu003Cpu003Eu003Cstrongu003E小白作答u003Cu002Fstrongu003Eu003Cu002Fpu003Eu003Cpu003E那时候非常有安全感,有啥事都有师父兜着,反正技术都是组里最差的,就瞎猜了个log(N),心想找一个节点不就是log(N)。自然而然,师父让我自己去研究。u003Cu002Fpu003Eu003Cpu003E这一阶段,用了10分钟。u003Cu002Fpu003Eu003Ch1u003Eu003Cstrongu003E继续解答u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cpu003E仔细分析一下,会发现通过索引去找很别扭。因为你不知道前100个数在左子树和右子数的分布情况,所以其是无法利用二叉树的查找特性。u003Cu002Fpu003Eu003Cpu003E通过学习,了解到mysql的索引是b+树。u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002F944e523b4e2e41cdad78cdea8ff9e7c2″ img_width=”1080″ img_height=”548″ alt=”从 MySQL 执行原理告诉你:为什么分页场景下,请求速度非常慢?” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E看了这个图,就豁然开朗了。可以直接通过叶子节点组成的链表,以o(n)的复杂度找到第100大的树。但是即使是o(n),也不至于慢得令人发指,是否还有原因。u003Cbru002Fu003Eu003Cu002Fpu003Eu003Cpu003E这一阶段,主要是通过网上查资料,断断续续用了10天。u003Cu002Fpu003Eu003Ch1u003Eu003Cstrongu003E系统学习u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cpu003E这里推荐两本书,一本《MySQL技术内幕 InnoDB存储引擎》,通过他可以对InnoDB的实现机制,如mvcc,索引实现,文件存储会有更深理解。u003Cu002Fpu003Eu003Cpu003E第二本是《高性能MySQL》,这本书从着手使用层面,但讲得比较深入,而且提到了很多设计的思路。u003Cu002Fpu003Eu003Cpu003E两本书相结合,反复领会,mysql就勉强能登堂入室了。u003Cu002Fpu003Eu003Cpu003E这里有两个关键概念:u003Cu002Fpu003Eu003Cblockquoteu003Eu003Cpu003E聚簇索引:包含主键索引和对应的实际数据,索引的叶子节点就是数据节点u003Cu002Fpu003Eu003Cpu003E辅助索引:可以理解为二级节点,其叶子节点还是索引节点,包含了主键id。u003Cu002Fpu003Eu003Cu002Fblockquoteu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002Feb88dd9ca5ce4d7e8fa2546cd428c872″ img_width=”658″ img_height=”916″ alt=”从 MySQL 执行原理告诉你:为什么分页场景下,请求速度非常慢?” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E即使前10000个会扔掉,mysql也会通过二级索引上的主键id,去聚簇索引上查一遍数据,这可是10000次随机io,自然慢成哈士奇。u003Cbru002Fu003Eu003Cu002Fpu003Eu003Cpu003E这里可能会提出疑问,为什么会有这种行为,这是和mysql的分层有关系,limit offset 只能作用于引擎层返回的结果集。换句话说,引擎层也很无辜,他并不知道这10000个是要扔掉的。u003Cu002Fpu003Eu003Cpu003E以下是mysql分层示意图,可以看到,引擎层和server层,实际是分开的。u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002F8788be50683546e2bbc44236523cd9d9″ img_width=”640″ img_height=”383″ alt=”从 MySQL 执行原理告诉你:为什么分页场景下,请求速度非常慢?” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E直到此时,大概明白了慢的原因。这一阶段,用了一年。u003Cu002Fpu003Eu003Cpu003E触类旁通u003Cu002Fpu003Eu003Cpu003E此时工作已经3年了,也开始看一些源码。在看完etcd之后,看了些tidb的源码。无论哪种数据库,其实一条语句的查询,是由逻辑算子组成。u003Cu002Fpu003Eu003Ch1u003Eu003Cstrongu003E逻辑算子介绍u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cpu003E在写具体的优化规则之前,先简单介绍查询计划里面的一些逻辑算子。u003Cu002Fpu003Eu003Cblockquoteu003Eu003Cpu003EDataSource 这个就是数据源,也就是表,select * from t 里面的 t。u003Cu002Fpu003Eu003Cpu003ESelection 选择,例如 select xxx from t where xx = 5 里面的 where 过滤条件。u003Cu002Fpu003Eu003Cpu003EProjection 投影, select c from t 里面的取 c 列是投影操作。u003Cu002Fpu003Eu003Cpu003EJoin 连接, select xx from t1, t2 where t1.c = t2.c 就是把 t1 t2 两个表做 Join。u003Cu002Fpu003Eu003Cu002Fblockquoteu003Eu003Cpu003E选择,投影,连接(简称 SPJ) 是最基本的算子。其中 Join 有内连接,左外右外连接等多种连接方式。u003Cu002Fpu003Eu003Cpu003Eselect b from t1, t2 where t1.c = t2.c and t1.a > 5变成逻辑查询计划之后,t1 t2 对应的 DataSource,负责将数据捞上来。u003Cu002Fpu003Eu003Cpu003E上面接个 Join 算子,将两个表的结果按 t1.c = t2.c连接,再按 t1.a > 5 做一个 Selection 过滤,最后将 b 列投影。u003Cu002Fpu003Eu003Cpu003E下图是未经优化的表示:u003Cu002Fpu003Eu003Cdiv class=”pgc-img”u003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002Ffac483d3b26343059ed662b5e81d6fe0″ img_width=”960″ img_height=”1072″ alt=”从 MySQL 执行原理告诉你:为什么分页场景下,请求速度非常慢?” inline=”0″u003Eu003Cp class=”pgc-img-caption”u003Eu003Cu002Fpu003Eu003Cu002Fdivu003Eu003Cpu003E所以说不是mysql不想把limit, offset传递给引擎层,而是因为划分了逻辑算子,所以导致无法直到具体算子包含了多少符合条件的数据。u003Cu002Fpu003Eu003Ch1u003Eu003Cstrongu003E怎么解决u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cblockquoteu003Eu003Cpu003E《高性能MySQL》提到了两种方案u003Cu002Fpu003Eu003Cu002Fblockquoteu003Eu003Ch1u003Eu003Cstrongu003E方案一u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cpu003E根据业务实际需求,看能否替换为下一页,上一页的功能,特别在ios, android端,以前那种完全的分页是不常见的。u003Cu002Fpu003Eu003Cpu003E这里是说,把limit, offset,替换为>辅助索引(即搜索条件)id的方式。该id再调用时,需要返回给前端。u003Cu002Fpu003Eu003Ch1u003Eu003Cstrongu003E方案二u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cpu003E正面刚。这里介绍一个概念:索引覆盖:当辅助索引查询的数据,只有id和辅助索引本身,那么就不必再去查聚簇索引。u003Cu002Fpu003Eu003Cpu003E思路如下:select xxx,xxx from in (select id from table where second_index = xxx limit 10 offset 10000)这句话是说,先从条件查询中,查找数据对应的数据库唯一id值,因为主键在辅助索引上就有,所以不用回归到聚簇索引的磁盘去拉取。再通过这些已经被limit出来的10个主键id,去查询聚簇索引。这样只会十次随机io。u003Cu002Fpu003Eu003Cpu003E在业务确实需要用分页的情况下,使用该方案可以大幅度提高性能。通常能满足性能要求。u003Cu002Fpu003Eu003Ch1u003Eu003Cstrongu003E写在最后u003Cu002Fstrongu003Eu003Cu002Fh1u003Eu003Cpu003E非常感谢我师父在我毕业前三年的指导,给了我很多耐心。在节假日给我布置看书任务,在午休时候考察我学习的进展,通过提问的方式引导我去探索问题,在我从腾讯毕业后,每次见面也给我出了很多主意,传授授业解惑,无一没有做到极致。u003Cu002Fpu003Eu003Cpu003E来源:juejin.imu002Fpostu002F5c4db295e51d4503834d9c43u003Cu002Fpu003E”

原文始发于:从 MySQL 执行原理告诉你:为什么分页场景下,请求速度非常慢?

主题测试文章,只做测试使用。发布者:程序员,转转请注明出处:http://www.cxybcw.com/26352.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code