【深度长文】MySQL排序内部原理探秘(3)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 【深度长文】MySQL排序内部原理探秘

5.2.2 sort_merge_passes

MySQL手册中对Sort_merge_passes的描述只有一句话

Sort_merge_passes
The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

这段话并没有把sort_merge_passes到底是什么,该值比较大时说明了什么,通过什么方式可以缓解这个问题。

我们把上面MySQL的外部排序算法搞清楚了,这个问题就清楚了。

其实sort_merge_passes对应的就是MySQL做归并排序的次数,也就是说,如果sort_merge_passes值比较大,说明sort_buffer和要排序的数据差距越大,我们可以通过增大sort_buffer_size或者让填入sort_buffer_size的键值对更小来缓解sort_merge_passes归并排序的次数。

对应的,我们可以在源码中看到证据。

上述MySQL外部排序的算法中第5到第7步,是通过sql/filesort.cc文件中merge_many_buff()函数来实现,第5步单次归并使用merge_buffers()实现,源码摘录如下:

int merge_many_buff(Sort_param *param, Sort_buffer sort_buffer,
                    Merge_chunk_array chunk_array,
                    size_t *p_num_chunks, IO_CACHE *t_file)
{
...
    for (i=0 ; i < num_chunks - MERGEBUFF * 3 / 2 ; i+= MERGEBUFF)
    {
      if (merge_buffers(param,                  // param
                        from_file,              // from_file
                        to_file,                // to_file
                        sort_buffer,            // sort_buffer
                        last_chunk++,           // last_chunk [out]
                        Merge_chunk_array(&chunk_array[i], MERGEBUFF),
                        0))                     // flag
      goto cleanup;
    }
    if (merge_buffers(param,
                      from_file,
                      to_file,
                      sort_buffer,
                      last_chunk++,
                      Merge_chunk_array(&chunk_array[i], num_chunks - i),
                      0))
      break;                                    /* purecov: inspected */
...
}

截取部分merge_buffers()的代码如下,

int merge_buffers(Sort_param *param, IO_CACHE *from_file,
                  IO_CACHE *to_file, Sort_buffer sort_buffer,
                  Merge_chunk *last_chunk,
                  Merge_chunk_array chunk_array,
                  int flag)
{
...
  current_thd->inc_status_sort_merge_passes();
...
}

可以看到:每个merge_buffers()都会增加sort_merge_passes,也就是说每一次对MERGEBUFF (7) 个block归并排序都会让sort_merge_passes加一,sort_merge_passes越多表示排序的数据太多,需要多次merge pass。解决的方案无非就是缩减要排序数据的大小或者增加sort_buffer_size。

打个小广告,在我们的qmonitor中就有sort_merge_pass的性能指标和参数值过大的报警设置。

六、trace 结果解释

说明白了三种排序模式和外部排序的方法,我们回过头来看一下trace的结果。

6.1 是否存在磁盘外部排序

"number_of_tmp_files": 0,

number_of_tmp_files表示有多少个分片,如果number_of_tmp_files不等于0,表示一个sort_buffer_size大小的内存无法保存所有的键值对,也就是说,MySQL在排序中使用到了磁盘来排序。

6.2 是否存在优先队列优化排序

由于我们的这个SQL里面没有对数据进行分页限制,所以filesort_priority_queue_optimization并没有启用

"filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            },

而正常情况下,使用了Limit会启用优先队列的优化。优先队列类似于FIFO先进先出队列。

算法稍微有点改变,以回表排序模式为例。

  • sort_buffer_size足够大

如果LIMIT 限制返回N条数据,并且N条数据比sort_buffer_size小,那么MySQL会把sort buffer作为priority queue,在第二步插入priority queue时会按序插入队列;在第三步,队列满了以后,并不会写入外部磁盘文件,而是直接淘汰最尾端的一条数据,直到所有的数据都正常读取完成。

算法如下:

  1. 根据索引或者全表扫描,按照过滤条件获得需要查询的数据
  2. 将要排序的列值和row ID组成键值对,按序存入中priority queue中
  3. 如果priority queue满了,直接淘汰最尾端记录。
  4. 重复上述步骤,直到所有的行数据都正常读取了完成
  5. 最后一轮循环,仅将row ID写入到结果文件中
  6. 根据结果文件中的row ID按序读取用户需要返回的数据。为了进一步优化性能,MySQL会读一批row ID,并将读到的数据按排序字段要求插入缓存区中(内存大小read_rnd_buffer_size)。
  • sort_buffer_size不够大

N条数据比sort_buffer_size大的情况下,MySQL无法直接利用sort buffer作为priority queue,正常的文件外部排序还是一样的,只是在最后返回结果时,只根据N个row ID将数据返回出来。具体的算法我们就不列举了。


这里MySQL到底是否选择priority queue是在sql/filesort.cc的check_if_pq_applicable()函数中确定的,具体的代码细节这里就不展开了。

另外,我们也没有讨论limit m,n的情况,如果是Limit m,n, 上面对应的“N个row ID”就是“M+N个row ID”了,MySQL的limit m,n 其实是取m+n行数据,最后把M条数据丢掉。


从上面我们也可以看到sort_buffer_size足够大对limit数据比较小的情况,优化效果是很明显的。

七、MySQL其他相关排序参数

7.1 max_sort_length

这里需要区别max_sort_length 和max_length_for_sort_data。

max_length_for_sort_data是为了让MySQL选择”< sort_key, rowid >”还是”< sort_key, additional_fields >”的模式。

而max_sort_length是键值对的大小无法确定时(比如用户要查询的数据包含了 SUBSTRING_INDEX(col1, ‘.’,2))MySQL会对每个键值对分配max_sort_length个字节的内存,这样导致内存空间浪费,磁盘外部排序次数过多。

7.2 innodb_disable_sort_file_cache

innodb_disable_sort_file_cache设置为ON的话,表示在排序中生成的临时文件不会用到文件系统的缓存,类似于O_DIRECT打开文件。

7.3 innodb_sort_buffer_size

这个参数其实跟我们这里讨论的SQL排序没有什么关系。innodb_sort_buffer_size设置的是在创建InnoDB 索引时,使用到的sort buffer的大小。

以前写死为1M,现在开放出来,允许用户自定义设置这个参数了。

八、MySQL排序优化总结

最后整理一下优化MySQL排序的手段

  1. 排序和查询的字段尽量少。只查询你用到的字段,不要使用select * ;使用limit查询必要的行数据;
  2. 要排序或者查询的字段,尽量不要用不确定字符函数,避免MySQL直接分配max_sort_length,导致sort buffer空间不足;
  3. 使用索引来优化或者避免排序;
  4. 增加sort_buffer_size大小,避免磁盘排序;
  5. 不得不使用original 排序算法时,增加read_rnd_buffer_size;
  6. 字段长度定义合适就好(避免过长);
  7. tmpdir建议独立存放,放在高速存储设备上。

写到这里,大家可以回顾一下文章开头的那八个问题,如果回答不了这些问题,说明其实你没有真正的理解透MySQL的排序,或者说我们的这篇文章写的太乱了~

九、参考文献

https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

http://coding-geek.com/how-databases-work/

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
存储 算法 关系型数据库
MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
|
6月前
|
存储 关系型数据库 MySQL
Mysql学习--深入探究索引和事务的重点要点与考点
Mysql学习--深入探究索引和事务的重点要点与考点
|
6月前
|
存储 缓存 算法
作者推荐 | 【深入浅出MySQL】「底层原理」探秘缓冲池的核心奥秘,揭示终极洞察
MySQL作为一个存储系统,有着一个关键的优化机制——缓冲池(buffer pool),它极大地提高了数据的访问效率,避免了频繁的磁盘IO操作。通过将常用的数据存储在内存中,MySQL可以快速响应查询请求,减少耗时的磁盘访问。这一优化机制在提升数据库性能方面起到了重要的作用。
379 7
作者推荐 | 【深入浅出MySQL】「底层原理」探秘缓冲池的核心奥秘,揭示终极洞察
|
存储 SQL 缓存
探秘MySQL底层架构:设计与实现流程一览
Mysql,作为一款优秀而广泛使用的数据库管理系统,对于众多Java工程师来说,几乎是日常开发中必不可少的一环。无论是存储海量数据,还是高效地检索和管理数据,Mysql都扮演着重要的角色。然而,除了使用Mysql进行日常开发之外,我们是否真正了解它的底层架构以及设计实现的流程呢?本篇博客将带您深入探索Mysql底层架构的设计与实现流程,帮助您更好地理解和应用这个强大的数据库系统。让我们一同揭开Mysql底层的神秘面纱,探寻其中的奥秘。
36690 14
探秘MySQL底层架构:设计与实现流程一览
|
机器学习/深度学习 存储 关系型数据库
史上最简单的 MySQL 教程(十四)「关系」
史上最简单的 MySQL 教程(十四)「关系」
109 0
|
存储 缓存 监控
Redis监控步骤get!Google精髓的四大法则直接掌握
Redis也是对外服务,所以Google四个黄金指标同样适用,还从延迟、流量、错误、饱和度分析Redis关键指标。
199 0
|
SQL 关系型数据库 MySQL
重温《MySQL必知必会》学习到的一些关键点(一)
重温《MySQL必知必会》学习到的一些关键点(一)
129 0
重温《MySQL必知必会》学习到的一些关键点(一)
|
存储 SQL 搜索推荐
【深度长文】MySQL排序内部原理探秘(1)
【深度长文】MySQL排序内部原理探秘
198 0
【深度长文】MySQL排序内部原理探秘(1)
下一篇
无影云桌面