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

0、导读

在MySQL里,什么情况下会发生排序。MySQL内部的排序怎么完成的,怎么合理分配内存,怎么避免发生额外的磁盘I/O,这里都有解,看过来!

本文由沃趣科技数据库工程师罗小波撰写,二次排版时,个别文字老叶略有调增。

友情提醒:建议阅读时间20分钟,请先找好蹲坑。


一、本文想解决什么问题

二、如何识别需要排序

三、如何利用索引优化排序

四、排序的几种模式

4.1、实际trace结果

4.2、排序模式概览

4.2.1、回表排序模式

4.2.2、不回表排序模式

4.2.3、打包数据排序模式

4.2.4、三种模式比较

五、外部排序

5.1、普通外部排序

5.1.1、两路外部排序

5.1.2、多路外部排序

5.2、MySQL外部排序

5.2.1、MySQL外部排序算法

5.2.2、关于sort_merge_passes

六、optimizer trace解读

6.1、是否存在磁盘外部排序

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

七、MySQL其他相关排序参数

7.1、max_sort_length

7.2、innodb_disable_sort_file_cache

7.3、innodb_sort_buffer_size

八、MySQL排序优化总结

九、参考文献


一、主要内容简介

MySQL排序是个老生长谈的话题,这次我们想由浅入深详细说说MySQL的几种排序模式,怎么选择不同排序模式,以及如何优化排序。

同时也希望通过本文能解决大家的几个疑问:

  1. MySQL什么时候做排序,怎么判断需要进行排序;
  2. MySQL有几种排序模式,有什么方法让MySQL选择不同的排序模式;
  3. MySQL排序跟 read_rnd_buffer_size 有啥关系,在哪些情况下增加 read_rnd_buffer_size 能优化排序效率;
  4. 怎么判断MySQL使用了磁盘排序,怎么避免或者优化磁盘排序;
  5. 排序时变长字段(varchar)数据在内存是怎么存储的,5.7有哪些改进;
  6. 加了LIMIT后,排序模式有哪些改进;
  7. sort_merge_pass到底是什么鬼,该状态值过大说明了什么问题,可以通过什么方法解决;
  8. 迫不得已要进行排序的话,有什么优化手段让排序更快;

二、如何识别需要排序?

利用EXPLAIN查看执行计划候时,如果在Extra列中显示Using filesort,其实这种情况就说明MySQL需要进行排序。

Using filesort经常出现在order by、group by、distinct、join等情况下。

三、利用索引优化排序

需要排序时,首先想到的一般是:能否利用索引来优化。

InnoDB默认采用的是B+tree索引,B+tree索引本身就是有序的,以下面的查询为例:

select * from film where actor_name='苍老师' order by prod_time;

只需创建多列索引(actor_name, prod_time),就能利用B+tree的特性来避免额外排序。

如下图所示:【深度长文】MySQL排序内部原理探秘(1)

通过B+tree查找到actor_name=’苍老师’的数据后,只需要按序往右继续扫描即可,无需额外排序操作。

下面都是其他可以利用索引优化排序的情形:

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = 1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

从以上例子里面我们也可以看到,如果要让MySQL使用索引优化排序应该怎么建组合索引。

四、排序模式

4.1 实际trace结果

但是还是有非常多的SQL没法使用索引进行排序,例如

select * from film where Producer like '东京热%'  and prod_time>'2015-12-01' order by actor_age;

我们想查询’东京热’出品的,从去年12月1号以来,并且按照演员的年龄排序的电影信息。

(好吧,假设我这里有一个每一位男DBA都想维护的数据库:)

这种情况下,使用索引已经无法避免排序了,那MySQL排序到底会怎么做列。
笼统的来说,它会按照:

  1. 依据“Producer like ‘东京热%’  and prod_time>’2015-12-01’  ”过滤数据,查找需要的数据;
  2. 对查找到的数据按照“order by actor_age”进行排序,并按照“select *”将必要的数据按照actor_age依序返回给客户端。

空口无凭,我们可以利用MySQL的optimize trace来查看是否如上所述。

如果通过optimize trace看到更详细的MySQL优化器trace信息,可以查看阿里印风的博客初识5.6的optimizer trace

trace结果如下:

  • 依据“Producer like ‘东京热%’  and prod_time>’2015-12-01’  ”过滤数据,查找需要的数据
  "attaching_conditions_to_tables": {
              "original_condition": "((`film`.`Producer` like '东京热%') and (`film`.`prod_time` > '2015-12-01'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`film`",
                  "attached": "((`film`.`Producer` like '东京热%') and (`film`.`prod_time` > '2015-12-01'))"
                }
              ]
            }
  • 对查找到的数据按照“order by actor_age”进行排序,并 按照“select *”将必要的数据按照actor_age依序返回给客户端
"join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`film`",
                "field": "actor_age"
              }
            ],
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "rows": 1,
              "examined_rows": 5,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 261872,
              "sort_mode": "<sort_key, packed_additional_fields>"
            }
          }
        ]
      }

这里,我们可以明显看到,MySQL在执行这个select的时候执行了针对film表.actor_age字段的asc排序操作。

"filesort_information": [
              {
                "direction": "asc",
                "table": "`film`",
                "field": "actor_age"
              }



上一篇:宜搭的典型模板案例分享


下一篇:Spread for Windows Forms快速入门(13)---数据排序