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