【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方式效率较低
  • 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀