mysql排序1 10 11_MySQL中的排序

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

索引排序

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

4b7ec3944483731e7e315de322f84577.png

比如现在查询条件是 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 有索引

b0b4fedcc8e586a8e89a84f4aaf451f3.png

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 有索引

539de852e2f4e7ade35408b65b94c07a.png

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 + 内存临时表来排序。

执行过程:

bccf94e2a39ce847facb38a169bcbb9e.png

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 值返回。

78965c0a72238204790a9c2a51acf706.png

失效场景:因为要拿指定的记录数的排序数据以及rowid去挨个比较,所以如果需要返回的记录数过多,导致所有的字段长度超过了设置的 sort_buffer_size ,那么此算法就会失效。

索引排序案例

问题:有 (city,name) 联合索引,select * from t where city in (“杭州”," 苏州 ") order by name limit 100; 这个 SQL 语句是否需要排序?有什么方案可以避免排序?

答案:需要排序。因为city 的条件有两个,总体上来看就是以 city优先进行排序的。可以优化成下面三步:

1、执行 select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的,客户端用一个长度为 100 的内存数组 A 保存结果。

2、执行 select * from t where city=“苏州” order by name limit 100; 用相同的方法,假设结果被存进了内存数组 B。

3、现在 A 和 B 是两个有序数组,然后你可以用归并排序的思想,得到 name 最小的前 100 值,就是我们需要的结果了。

如果将 " limit 100" 改成 " limit 10000,100 "。可以优化成下面三步:

1、select id,name from t where city="杭州" order by name limit 10100;

2、select id,name from t where city="苏州" order by name limit 10100。

3、用归并排序的方法取得按 name 顺序第 10001~10100 的 name、id 的值,然后拿着这 100 个 id 到数据库中去查出所有记录。

优化总结

优化总体上就是围绕 “尽量不使用额外排序,避免使用临时表” 的原则。

1、尽量使用索引完成排序,如果该查询语句执行的频率比较高,可以为其创建一个联合索引。而如果使用的频率很低,那么就不需要去创建,因为索引的维护需要成本。

2、如果需要额外去排序,那么可以适当调整 sort_buffer_size(sort_buffer) 和 tmp_table_size(内存临时表) ,使排序只在内存中执行。

3、如果 sort_buffer 空间设置足够大,也可以适当调整 max_length_for_sort_data 的值,使用全字段排序。

4、对于一些比较耗时的函数可以自定义算法去实现,避免计算过程在 MySQL 中实现。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值