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

发布于 2022-7-13 16:12
浏览
0收藏

 

我是林晓斌,今天作为【迪B课堂】的客串嘉宾来跟大家分享:当索引存储顺序和order by不一致,该怎么办?

 

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

 

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

 

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

 

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

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

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

 

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

 

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

 

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

 

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

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

 

单字段排序

 

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

我们来看看这个联合索引ab的结构,点击可以查看大图。

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

图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字样,说明这个语句执行过程中,不需要用到排序。

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

图2 order by 不需要排序

 

组合字段排序

 

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

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

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

 

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

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

 

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

  1. InnoDB索引树以任意一个叶节点为起始点,可以向左或向右遍历;
  2. 如果语句需要的order by顺序刚好可以利用索引树的单向遍历,就可以避免排序操作。

 

Descending Indexes

 

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

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

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

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

 图3 order by 需要排序

 

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

 

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

 

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

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

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

 

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

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

 

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

 

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

 

应用优化

 

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

 

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

 

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

 

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

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

 

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

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

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

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

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

 

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

大咖丁奇:索引存储顺序和order by不一致,怎么办?-开源基础软件社区

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

 

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

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

 

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

 

小结

 

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

 

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

 

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

 

探讨了那么多,希望腾讯云数据库实战课程【迪B课堂】可以和大家一起构建MySQL知识网络。有一个问题留给大家讨论:


最后这个算法的一个极端情况是重复的a值太多,如果预估到业务可能出现这种情况,应用的代码应该怎么设计呢?

 

文章转自公众号:腾讯云数据库

分类
标签
已于2022-7-13 16:12:13修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐