mysql排序1 10 11_MySQL中的排序

最新推荐文章于 2024-08-16 16:22:51 发布

茅十七

最新推荐文章于 2024-08-16 16:22:51 发布

阅读量2.2k

点赞数

文章标签： mysql排序1 10 11

本文链接：https://blog.csdn.net/weixin_32687283/article/details/113212343

版权

在编写SQL 语句时常常会用到 order by 进行排序，那么排序过程是什么样的？为什么有些排序执行比较快，有些排序执行很慢？又该如何去优化？

索引排序

索引排序指的是在通过索引查询时就完成了排序，从而不需要再单独进行排序，效率高。索引排序是通过联合索引实现的。因为联合索引是从最左边的列开始起按大小顺序进行排序，如下图。

比如现在查询条件是 where sex=1 order by name，那么查询过程就是会找到满足 sex=1 的记录，而符合这条的所有记录一定是按照 name 排序的，所以也不需要额外进行排序。而如果是 where sex >1 order by name，那么根据 sex>1 得到的记录 sex 值并不是固定值，所以得到的记录是按照 sex，其次才是 name 进行排列的。也就没有实现索引排列。

额外排序

额外排序就是需要额外进行排序，也就是在 explain 的 extra 列中出现 using filesort。可以分别两种方式来看待。

按执行位置划分

1、Sort_Buffer

MySQL 为每个线程各维护了一块内存区域 sort_buffer ，用于进行排序。sort_buffer 的大小可以通过 sort_buffer_size 来设置。如果加载的记录字段总长度(可能是全字段也可能是 rowid排序的字段)小于 sort_buffer_size 便使用 sort_buffer 排序；如果超过则使用 sort_buffer + 临时文件进行排序。

2、Sort_Buffer + 临时文件

MySQL 会使用临时文件搭配 Sort_Buffer 进行排序。主要是使用归并算法来得出最终排序后的结果。

临时文件种类：

临时表种类由参数 tmp_table_size 与临时表大小决定，如果内存临时表大小超过 tmp_table_size ，那么就会转成磁盘临时表。因为磁盘临时表在磁盘上，所以使用内存临时表的效率是大于磁盘临时表的。

1、内存临时表

2、磁盘临时表磁盘临时表默认使用的是 InnoDB，如果想要切换执行引擎，可以修改参数internal_tmp_disk_storage_engine。

按执行方式划分

执行方式是由 max_length_for_sort_data 参数与用于排序的单条记录字段长度决定的，如果用于排序的单条记录字段长度 <= max_length_for_sort_data ，就使用全字段排序；反之则使用 rowid 排序。

1、全字段排序

全字段排序就是将查询的所有字段全部加载进来进行排序。

优点：查询快，执行过程简单

缺点：需要的空间大。

例子(不考虑临时文件)：select city,name,age from t where city='杭州' order by name limit 1000 ; city 有索引

1、初始化 sort_buffer，确定放入两个字段，即 name 和 id；

2、从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

3、到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；

4、从索引 city 取下一个记录的主键 id；

5、重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；

6、对 sort_buffer 中的数据按照字段 name 进行排序；

7、遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

2、rowid 排序

rowid 表示位置信息，如果整张表有主键那么 rowid 就是主键，如果没有主键就会自动创建一个 6 字节的唯一标识。所以 rowid 排序就表示只加载用于排序的字段以及 rowid ，然后进行排序，然后根据排序好的 rowid 去表中回表查询所要的结果。

缺点：会产生更多次数的回表查询，查询可能会慢一些。

优点：所需的空间更小。

例子(不考虑临时文件)：select city,name,age from t where city='杭州' order by name limit 1000 ; city 有索引

1、初始化 sort_buffer，确定放入两个字段，即 name 和 id；

2、从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

3、到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；

4、从索引 city 取下一个记录的主键 id；

5、重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；

6、对 sort_buffer 中的数据按照字段 name 进行排序；

7、遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

执行案例分析

rand() 执行

select word from words order by rand() limit 3; 　　表数据有10000行　　　　SQL是从10000行记录中随机获取3条记录返回。

分析：这里查询的字段只有一个，所以使用全字段查询。加上记录数过多，但是单条记录的字段长度不长，所以会使用 sort_buffer + 内存临时表。所以总结来看这条语句会使用全字段查询 + sort_buffer + 内存临时表来排序。

执行过程：

1、从缓冲池依次读取记录，每次读取后都调用 rand() 函数生成一个 0-1 的数存入内存临时表，W 是 word 值，R 是 rand() 生成的随机数。到这扫描了 10000 行。

2、初始化 sort_buffer，从内存临时表中将 rowid(这张表自动生成的) 以及排序数据 R 存入 sort_buffer。到这因为要遍历内存临时表所以又扫描了 10000 行。

3、在 sort_buffer 中根据 R 排好序，然后选择前三个记录的 rowid 逐条去内存临时表中查到 word 值返回。到这因为取了三个数据去内存临时表去查找所以又扫描了 3 行。总共 20003 行。

rand() 优化

通过上面的例子可以看出当要从表中随机获取几条记录使用 rand() 函数是非常消耗资源的，同时触发了 Using temporary(使用了临时表) 和 Using filesort(使用了额外排序)。并且进行了 20003 行记录的扫描，非常消耗资源。所以我们可以自己去计算一个随机值，避免使用 rand() 函数。

查询随机的一条记录：

1、取得整个表的行数，并记为 C。

2、取得 Y = floor(C * rand())。floor 函数在这里的作用，就是取整数部分。

3、再用 limit Y,1 取得一行。

select count(*) into @C from t;

set @Y = floor(@C * rand());

set @sql = concat("select * from t limit ", @Y, ",1");

prepare stmt from @sql;

execute stmt;

DEALLOCATE prepare stmt;

如果查询多条，只要将第二步执行多次，然后依次执行就可以了。

使用这样的方式就可以避免 MySQL 去使用临时表以及 filesort 排序，提高执行效率。

优先队列排序算法

在 5.6 中对排序算法进行一些优化，之前使用的是搭配临时表使用归并排序算法，这种方式会对所有的记录都进行排序，消耗了不必要的资源例如有一个 20000 行记录的表，执行 select word from words order by rand() limit 3;

因为这条语句只取三条记录，对这剩余的 19993 行进行排序比较浪费CPU资源且耗时。所以在 5.6 中提出了使用优先队列排序算法。还是以这个例子为例，因为查询的字段只有一个，且查询的行数很多，所以还是使用全字段查询 + sort_buffer + 内存临时表。

过程：先读取前三行记录并为其分别通过 rand() 函数为其设置一个0-1的随机数，取这三条记录的 rowid、随机数组成一个堆，然后依次设置随机数并与当前堆中的随机数比较。如果这个随机数比堆中某个记录的随机数小，就替换，然后移除，如果没有小的就直接移除，取下一个。最后根据堆中的 rowid 去临时表中读取对应的 word 值返回。