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

最新推荐文章于 2024-03-13 15:05:06 发布

小颜-

最新推荐文章于 2024-03-13 15:05:06 发布

阅读量2k

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库 java 后端 sql

本文链接：https://blog.csdn.net/weixin_63566550/article/details/128436504

版权

MySQL 专栏收录该内容

24 篇文章 15 订阅

订阅专栏

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

文章目录

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

一：文件排序出现的情况

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操作，反而得不偿失。