【MySQL进阶】索引排序内部逻辑——单路排序和双路排序详解


文章目录

  • 【MySQL进阶】索引排序内部逻辑——单路排序和双路排序详解
  • 一:文件排序出现的情况
  • 1:什么是索引排序和文件排序?
  • 2:文件排序出现的几种情况:
  • 二:文件排序(FileSort)详解
  • 1:单路排序
  • 2:双路排序
  • 3:对比
  • 4:什么时候使用单路 / 双路?
  • 5:结论及引申出的问题
  • 6:优化策略
  • 7:提高Order By的速度


一:文件排序出现的情况

1:什么是索引排序和文件排序?

MySQL排序支持两种filesort排序index排序

  • using index
    mysql扫描索引本身完成排序(底层存放在b+树中,默认顺序排列),查询不需要额外的排序,操作效率较高
  • using filesort
    将数据查询到内存中,在内存中在做一次排序,效率非常低;

2:文件排序出现的几种情况:

举个例子,下面有一段sql:

select * from user where name = "blblccc" order by age
  • order by 字段不是索引字段
  • order by 字段是索引字段,但是 select 中没有使用覆盖索引,如:select * from staffs order by age asc;
  • order by 中同时存在 ASC 升序排序和 DESC 降序排序,如:select a, b from staffs order by a desc, b asc;
  • order by 多个字段排序时,不是按照索引顺序进行 order by,即不是按照最左前缀法则,如:select a, b from staffs order by b asc, a asc;

二:文件排序(FileSort)详解

1:单路排序

一次取出所有字段进行排序,内存不够用的时候会使用磁盘

单路排序过程:

  • 从索引name找到第一个满足 name = ‘blblccc’ 条件的主键 id
  • 根据主键 id 取出整行,取出所有字段的值,存入 sort_buffer(排序缓存)中
  • 从索引name找到下一个满足 name = ‘blblccc’ 条件的主键 id
  • 重复步骤 2、3 直到不满足 name = ‘blblccc’
  • 对 sort_buffer 中的数据按照字段 age 进行排序
  • 返回结果给客户端

2:双路排序

取出排序字段进行排序,排序完成后再次回表查询所需要的其他字段

双路排序过程:

  • 从索引 name 找到第一个满足 name = ‘blblccc’ 的主键id
  • 根据主键 id 取出整行,把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中
  • 从索引 name 取下一个满足 name = ‘blblccc’ 记录的主键 id
  • 重复 3、4 直到不满足 name = ‘blblccc’
  • 对 sort_buffer 中的字段 age 和主键 id 按照字段 age进行排序
  • 遍历排序好的 id 和字段 age ,按照 id 的值回到原表中取出 所有字段的值返回给客户端

3:对比

  • 其实对比两个排序模式,单路排序会把所有需要查询的字段都放到 sort buffer 中,而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序,然后再通过主键回到原表查询需要的字段。
  • 单路排序从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出, 它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间, 因为它把每一行都保存在内存中了。

4:什么时候使用单路 / 双路?

关键配置:

  • sort_buffer可供排序的内存缓冲区大小
  • max_length_for_sort_data 单行所有字段总和限制,超过这个大小启动双路排序

如果查询的列字段大于max_length_for_sort_data变量,则会使用双路排序,反之则会使用单路排序

5:结论及引申出的问题

在sort_buffer中,单路比多路要多占用很多空间,因为单路是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量大小,再排……从而多次I/O。

单路本来想省一次I/O操作,反而导致了大量的I/O操作,反而得不偿失。

6:优化策略

  • 增大sort_buffer_size参数的设置
  • 增大max_length_for_sort_data参数的设置
  • 减少select 后面的查询的字段。 禁止使用select *

7:提高Order By的速度

  • ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序
  • MySQL支持二种方式的排序,FileSort和Index,Index效率较高,FileSort方式效率较低
  • 尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀