“AI 技术+人才”如何成为企业增长新引擎?戳此了解>>> 了解详情
写点什么

数据库大咖丁奇:MySQL 索引存储顺序和 order by 不一致,怎么办?

  • 2019-11-07
  • 本文字数:2546 字

    阅读完需:约 8 分钟

数据库大咖丁奇:MySQL索引存储顺序和order by不一致,怎么办?

今天与大家分享:当索引存储顺序和 order by 不一致,该怎么办?


根据指定的字段排序来显示结果,是我们写应用时最常见的需求之一了,比如一个交易系统中,按照交易时间倒序显示交易记录。


相信你听说过这样的建议:如果有 order by 的需求,给需要排序的字段加上索引,就可以避免数据库排序操作。


所谓数据库排序操作,是指数据库在执行过程中,先将满足条件的数据全部读出来,放入内存中,再执行快排,这个内存就是 sort_buffer。


如果临时数据量比 sort_buffer 大, 就要把数据放入临时文件,然后做外部排序。


这个排序过程的消耗是比较大的。


所谓避免数据库排序操作,是指执行过程中不需要快排或外部排序。


为什么加上索引就可以避免排序呢?如果索引存储顺序和 order by 不一致,还需要排序吗? 如果是联合索引呢?


今天我们就来说一说,建了索引以后,order by 是怎么执行的以及怎么优化。


为了便于说明,我创建一个简单的表,这个表里,除了主键索引 id 外,还有一个联合索引 ab。你可以在文稿中看到这个表的定义。



我们来看看不同的业务需求下,SQL 语句怎么写,以及在 MySQL 里是怎么执行的。

单字段排序

一个简单的需求是将这个表的数据,按照 a 的大小倒序返回。你的 SQL 语句可以这么写:



我们来看看这个联合索引 ab 的结构。



图 1 索引(a,b)示意图


可以看到,在这个索引上,数据存储顺序是:按照 a 值递增,对于 a 值相同的情况,按照 b 值递增。


因此上面这个语句的执行流程就是:


1.从索引 ab 上,取最右的一个记录,取出主键值 ID_Z;


2.根据 ID_Z 到主键索引上取整行记录,作为结果集的第一行;


3.在索引 ab 上取上一个记录的左边相邻的记录;


4.每次取到主键 id 值,再到主键索引上取到整行记录,添加到结果集的下一行;


5.重复步骤 3、4,直到遍历完整个索引。


可以看到,这个流程中并不涉及到排序操作。我们也可以用 explain 语句来验证这个结论。


图 2 是这个语句的 explain 的结果,可以看到,Extra 字段中没有 Using filesort 字样,说明这个语句执行过程中,不需要用到排序。



图 2 order by 不需要排序

组合字段排序

有了上面的分析,我们再来看看下面这个语句:



这个语句的意思是,按照 a 值倒序,当 a 的值相同时按照 b 值倒序。


你一定发现了,这个语句的执行逻辑和执行结果,跟前面的语句是一模一样的,因此也不需要排序。


倒序不需要排序,正序呢?正序的语句是这么写的:



显然,这个语句也是不需要排序的,执行流程上,只需要先取 ab 索引树最左边的节点,然后向右遍历即可。


到这里我们可以小结一下:


1.InnoDB 索引树以任意一个叶节点为起始点,可以向左或向右遍历;


2.如果语句需要的 order by 顺序刚好可以利用索引树的单向遍历,就可以避免排序操作。

Descending Indexes

接下来我们来看一种不满足”单向遍历“的场景。



这个语句要求查询结果中的记录排序顺序是:按照 a 值正序,对于相同的 a 值,按照 b 值倒序。


由于不满足单向遍历的要求,因此只能选择使用排序操作。


图 3 是这个语句 explain 的结果。



图 3 order by 需要排序


extra 字段中 Using filesort 表示使用了排序。


你一定想到了,如果可以让 InnoDB 在构建索引 ab 的时候,相同的 a 里面,b 能够从大到小排序,就又可以满足单向遍历的要求了。


在 MySQL5.7 及之前的版本是不支持这么创建索引的,在 8.0 版本中支持了这个功能,官方名称是 Descending Indexes。


在 8.0 版本中,我们可以把索引 ab 的定义做个修改。



我们将索引 ab 的定义做了修改,在字段 b 后面加上 desc,表示对于相同的 a 值,字段 b 按照倒序存储。


这个表对应的索引 ab 的结构图如下,点击可以查看大图。




图 4 索引(a, b desc) 示意图 和 explain 的结果


这样从左到右遍历这个索引的时候,就刚好满足 a 正序,然后 b 逆序的要求。


Descending Indexes 可以避免这种情况下的排序操作,语句的执行性能自然就提升了。

应用优化

前面说过,Descending Indexes 这个功能是在 MySQL 8.0 才支持的。那如果你的生产环境上使用的还是低于 8.0 的版本,有没有不需要排序的方法呢?


答案是有的,接下来我给大家介绍一种应用端协作的优化方案。


假设我们现在的需求就是在 MySQL 5.7 版本下,要求按照”a 值正序,然后 b 值逆序”的顺序,返回所有行 a 和 b 的值。


首先,为了避免数据库排序,我们直接执行下面这个语句:



当然,这个语句返回的结果集是不满足业务要求的,但是我们知道,对于相同的 a 值,b 值是有序递增的,我们要把这个数据特点利用起来。


执行这个语句后,应用端的逻辑改造如下:


1.构造一个空栈(stack),栈中的节点可以保存数据行;


2.读入第一行,入栈;


3.读入下一行,


a.如果新一行中 a 值与上一行相同,将新一行入栈;


b.如果新一行中 a 值与上一行不同,则将栈中的所有数据行依次出栈并输出,直到栈清空;然后新一行入栈。


4.重复步骤 3 直到遍历完整个索引,将栈中的所有数据行依次出栈并输出,直到栈清空。


下图 5 是用图 1 中的示例数据,执行上面的流程的效果图。



图 5 应用端优化的执行流程


可以看到,这个过程中数据库端没有使用排序,在应用端也没有使用排序。


这个过程需要在应用端构造一个栈,需要临时内存。当然这个内存并不是凭空多出来的,因为如果不使用这个方法,就只能在 MySQL 端排序,这个内存就会在 MySQL 里创建,也就是 sort_buffer。


相比之下,使用应用端的内存还是比使用 MySQL 的内存好些,也算是这个方案的另一个优点。

小结

接下来,总结一下今天的主要内容。


今天介绍了 MySQL 在有索引的情况下,处理 order by 请求的执行过程,也介绍了 Descending Indexes 的应用背景。


Descending Indexes 是 MySQL 8.0 才支持的特性。在数据库不支持一些特性的时候,也可以考虑通过应用端的协作来实现业务需求。方案优化并不一定只是数据库的优化,综合考虑系统中各个模块的特性,可以增强我们解决问题的灵活性。


作者介绍:


林晓斌,网名丁奇,腾讯云数据库负责人,数据库领域资深技术专家。作为活跃的 MySQL 社区贡献者,丁奇专注于数据存储系统、MySQL 源码研究和改进、MySQL 性能优化和功能改进,在业务场景分析、系统瓶颈分析、性能优化方面拥有丰富的经验。其创作的《MySQL 实战 45 讲》专栏受众已逾 2 万人。


本文转载自公众号云加社区(ID:QcloudCommunity)。


原文链接:


https://mp.weixin.qq.com/s/rYE5lQ9usMPlFe9roqUYlg


2019-11-07 17:583789

评论 1 条评论

发布
用户头像
为什么order by a,b desc 的时候,mysql 干脆整个(a,b)索引都不走了,不是至少a可以用一下吗
2022-02-28 14:49
回复
没有更多了
发现更多内容

html创建表格有那些小技巧,表单中真的有这么多功能吗

你好bk

html html5 大前端 html/css JavaScrip

【架构训练营】模块六作业

zclau

模块六作业 - 电商系统微服务

babos

#架构实战营

你知道 JavaScript 中的 Arguments 对象都有哪些用途吗?

编程三昧

JavaScript 大前端 函数 8月日更 Arguments

Rust从0到1-高级特性-类型进阶

rust 高级特性 类型 Types

架构实战营 模块六 作业

脉醉

#架构实战营

微信业务架构&学生系统管理系统设计

Geek_dae

架构实战营

如何找到靠谱的工长?

escray

生活记录 8月日更 装修记

模块六

秀聪

架构训练营

模块6作业

柱林

模块6 作业

Geek_35a345

Lua 入门到精通( 01 Lua 简介以及软件安装)《做一个脚本高手》

陈皮的JavaLib

lua Linux 运维 脚本语言 8月日更

架构训练营 模块六

小卷儿

❤ 超详细《软件测试全栈技能》思维导图详解 ❤(建议收藏) ​

程序员阿沐

程序员 软件测试 自动化测试 经验分享 技能图谱

百度地图开发-在地图上实现路线导航 09

Andy阿辉

android Android 小菜鸟 Android端 8月日更

电商系统微服务拆分-模块6

小牧ah

架构实战营

拆分电商系统为微服务

木云先森

架构实战营

情窦初开,原来喜欢这么可爱的

4ye

Python 后端 8月日更 词云

模块六作业

seawolflin

架构实战营

架构实战营模块6作业

zlz

在线JSON转Schema工具

入门小站

架构实战营模块 6 作业

蔸蔸

Spark 源码阅读 01:环境搭建

程序员赤小豆

spark 技术 Spark 源码

把知识玩起来:Ansible(一)快乐的入门

南冥

Linux之fgrep命令

入门小站

Linux

java毕设开发经典选题

清风

毕业设计

架构训练营模块六作业

喻高咏        

架构实战营

kafka核心技术与实战学习笔记(一)

追风少年

kafka

模块六作业

Mr.He

架构实战营

架构实战营第一期--模块六作业

clay

架构实战营

Spark 源码阅读 02:从 Spark-Submit 到 Driver 启动

程序员赤小豆

spark 技术 Spark 源码

数据库大咖丁奇:MySQL索引存储顺序和order by不一致,怎么办?_数据库_林晓斌_InfoQ精选文章