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

一壶清酒 2020-01-16 17:25:26 ⋅ 1228 阅读

从一个问题说起

五年前在腾讯的时候,发现分页场景下,mysql请求速度非常慢。数据量只有10w的情况下,select xx from 单机大概2,3秒。

我就问我师父为什么,他反问“索引场景,mysql中获得第n大的数,时间复杂度是多少?”

答案的追寻

确认场景

假设status上面有索引。select * from table where status = xx limit 10 offset 10000。

会非常慢。数据量不大的情况就有几秒延迟。

小白作答

那时候非常有安全感,有啥事都有师父兜着,反正技术都是组里最差的,就瞎猜了个log(N),心想找一个节点不就是log(N)。自然而然,师父让我自己去研究。

这一阶段,用了10分钟。

继续解答

仔细分析一下,会发现通过索引去找很别扭。因为你不知道前100个数在左子树和右子数的分布情况,所以其是无法利用二叉树的查找特性。

通过学习,了解到mysql的索引是b+树。


看了这个图,就豁然开朗了。可以直接通过叶子节点组成的链表,以o(n)的复杂度找到第100大的树。但是即使是o(n),也不至于慢得令人发指,是否还有原因。

这一阶段,主要是通过网上查资料,断断续续用了10天。

系统学习

这里推荐两本书,一本《MySQL技术内幕 InnoDB存储引擎》,通过他可以对InnoDB的实现机制,如mvcc,索引实现,文件存储会有更深理解。

第二本是《高性能MySQL》,这本书从着手使用层面,但讲得比较深入,而且提到了很多设计的思路。

两本书相结合,反复领会,mysql就勉强能登堂入室了。

这里有两个关键概念:

聚簇索引:包含主键索引和对应的实际数据,索引的叶子节点就是数据节点

辅助索引:可以理解为二级节点,其叶子节点还是索引节点,包含了主键id。


即使前10000个会扔掉,mysql也会通过二级索引上的主键id,去聚簇索引上查一遍数据,这可是10000次随机io,自然慢成哈士奇。

这里可能会提出疑问,为什么会有这种行为,这是和mysql的分层有关系,limit offset 只能作用于引擎层返回的结果集。换句话说,引擎层也很无辜,他并不知道这10000个是要扔掉的。

以下是mysql分层示意图,可以看到,引擎层和server层,实际是分开的。


直到此时,大概明白了慢的原因。这一阶段,用了一年。

触类旁通

此时工作已经3年了,也开始看一些源码。在看完etcd之后,看了些tidb的源码。无论哪种数据库,其实一条语句的查询,是由逻辑算子组成。

逻辑算子介绍

在写具体的优化规则之前,先简单介绍查询计划里面的一些逻辑算子。

DataSource 这个就是数据源,也就是表,select * from t 里面的 t。

Selection 选择,例如 select xxx from t where xx = 5 里面的 where 过滤条件。

Projection 投影, select c from t 里面的取 c 列是投影操作。

Join 连接, select xx from t1, t2 where t1.c = t2.c 就是把 t1 t2 两个表做 Join。

选择,投影,连接(简称 SPJ) 是最基本的算子。其中 Join 有内连接,左外右外连接等多种连接方式。

select b from t1, t2 where t1.c = t2.c and t1.a > 5变成逻辑查询计划之后,t1 t2 对应的 DataSource,负责将数据捞上来。

上面接个 Join 算子,将两个表的结果按 t1.c = t2.c连接,再按 t1.a > 5 做一个 Selection 过滤,最后将 b 列投影。

下图是未经优化的表示:


所以说不是mysql不想把limit, offset传递给引擎层,而是因为划分了逻辑算子,所以导致无法直到具体算子包含了多少符合条件的数据。

怎么解决

《高性能MySQL》提到了两种方案

方案一

根据业务实际需求,看能否替换为下一页,上一页的功能,特别在ios, android端,以前那种完全的分页是不常见的。

这里是说,把limit, offset,替换为>辅助索引(即搜索条件)id的方式。该id再调用时,需要返回给前端。

方案二

正面刚。这里介绍一个概念:索引覆盖:当辅助索引查询的数据,只有id和辅助索引本身,那么就不必再去查聚簇索引。

思路如下:select xxx,xxx from in (select id from table where second_index = xxx limit 10 offset 10000)这句话是说,先从条件查询中,查找数据对应的数据库唯一id值,因为主键在辅助索引上就有,所以不用回归到聚簇索引的磁盘去拉取。再通过这些已经被limit出来的10个主键id,去查询聚簇索引。这样只会十次随机io。

在业务确实需要用分页的情况下,使用该方案可以大幅度提高性能。通常能满足性能要求。

写在最后

非常感谢我师父在我毕业前三年的指导,给了我很多耐心。在节假日给我布置看书任务,在午休时候考察我学习的进展,通过提问的方式引导我去探索问题,在我从腾讯毕业后,每次见面也给我出了很多主意,传授授业解惑,无一没有做到极致。



全部评论: 0

    我有话说:

    专业解决 MySQL 查询速度与性能差

    什么影响了数据库查询速度?关于数据库性能并不是DBA才关心的事。

    关于MySQL 通用查询日志和查询日志分析

    MySQL中的日志包括:错误日志、二进制日志、通用查询日志、查询日志等等。这里主要介绍比较常用的两个功能:通用查询日志和查询日志。

    什么情况才需要分库表?

    请教一下,我模拟测试,搞了几张大表都是3千万左右的数据,带主键查询秒级响应,jmeter插入能够达到每秒6、700条左右,我业务量每秒就是300条左右插入,这个场景是否不需要考虑分库表的问题?

    为什么说作为程序员分库表的必要性一定要掌握?

      互联网大厂程序员必须掌握海量数据和高并发问题处理技能,期望进入大厂的程序员一定要仔细看这篇! MySQL 分库表是做什么的? 相信很多程序员对 MySQL 都比较熟悉了,目前国内

    Fluid 0.3 正式发布:实现云原生场景通用化数据加速

    简介 为了解决大数据、AI 等数据密集型应用在云原生计算存储分离场景,存在的数据访问延时高、联合分析难、多维管理杂等痛点问题,南京大学 PASALab、阿里巴巴、Alluxio 在 2020 年

    在浏览器输入url到发起http请求,这过程到底发生了什么?

    在浏览器输入url到发起http请求,这过程到底发生了什么?

    「转载」47 张图带 MySQL 进阶!!!

    我们在  138 张图带 MySQL 入门 中主要介绍了基本的 SQL 命令、数据类型和函数,在具备以上知识后,就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员

    MySQL可视化管理后台,phpMyAdmin 4.9.3 和 5.0.0 发布,

    phpMyAdmin是一个非常受欢迎的基于web的MySQL数据库管理工具。

    抖音品质建设 - iOS启动优化《原理篇》

    前言 启动是 App 给用户的第一印象,启动越用户流失的概率就越高,良好的启动速度是用户体验不可缺少的一环。启动优化涉及到的知识点非常多面也很广,一篇文章难以包含全部,所以拆分成两部分:原理和实践

    大数据量 MyBatis PageHelper 查询性能问题的解决办法

    前因 项目一直使用的是PageHelper实现功能,项目前期数据量较少一直没有什么问题。随着业务扩增,数据库扩增PageHelper出现了明显的性能问题。 几十万甚至上百万的单表数据查询性能缓慢

    MySql 8 新特性 - CTE 通用表表达式(先睹为快)

    前言Mysql 8 正式发布了,新增了很多优秀特性,之后我会挑些重点来分享。下面和大家一起熟悉 CTE......

    MySql实战篇:建立高性能的Mysql技巧

    体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库......

    【开源资讯】phpMyAdmin 4.9.7 和 5.0.4 发布,可视化 MySQL 管理后台

    phpMyAdmin 4.9.7 和 5.0.4 发布了。phpMyAdmin 是一个非常受欢迎的 web MySQL 数据库管理工具。它能够创建和删除数据库,创建/删除/修改表格,删除/编辑/新增

    可能不知道的CRUD

      本系列旨在系统学习提升Mysql技能,更完整内容可以参考阿里新零售数据库设计与实战 DB引擎 可能不知道的CRUD INSERT 情况一 Duplicate key 当批量更新,如果

    Mysql优化---订单查询优化(1):视图优化+索引创建

    本文针对电商的订单业务进行的Mysql优化

    Redis为什么了?Redis性能问题排查详述

    Redis 作为优秀的内存数据库,其拥有非常高的性能,单个实例的 OPS 能够达到 10W 左右。但也正因此如此,当我们在使用 Redis 时,如果发现操作延迟变大的情况,就会与我们的预期不符。

    「开源资讯」MySQL 8.0.22 GA发布,有变化

      MySQL 最新版本 8.0.22 于2020年10月19日正式发布。 主要新变化 改进审计日志:对于JSON格式的日志文件,MySQL企业审计支持使用audit_log_read

    MySql实战篇:写一个简单的存储过程,完成订单定时任务

    前言之前我们分享了MySql的性能优化、索引详解等内容,本篇文章主要是针对想要入门MySql存储过程的读者,主要实现的业务是订单库里面的超过30分钟没有支付的订单全部置为失效订单......