ITPub博客

首页 > 数据库 > MySQL > mysql的order by和group by

mysql的order by和group by

MySQL 作者:wzgchen 时间:2015-08-19 15:51:48 0 删除 编辑


 

Mysql下的order by会导致排序,根据是否使用到索引和内部临时表,可分为:

Using where—索引排序

using filesort—文件排序

using temporary—临时表排序

 

同通过explain查看其extra列获取相应信息

 

索引排序

下面的例子很好的解释了索引排序使用的场景

1

Order by 使用了最左前缀的列 或者 N列都为const

2

多表连接时order by的列均为outter table的最左前缀列

 

create table rental (

...

primary key (rental_id),

unique key rental_date (rental_date,inventory_id,customer_id),

key idx_fk_inventory_id (inventory_id),

key idx_fk_staff_id (staff_id)),

...

)

select rental_id, staff_id from rental where rental_date ='2005-05-25' order by inventory_id, customer_id\G--使用索引排序,引导列为=

where rental_date ='2005-05-25' order by inventory_id desc--索引排序,最左前缀2

where rental_date ='2005-05-25' order by inventory_id desc, customer_id asc--文件排序,使用了两种排序方向

where rental_date ='2005-05-25' order by inventory_id, staff_id;--文件排序,引用了不在索引中的列

where rental_date ='2005-05-25' order by customer_id;--文件排序,没能形成最左前缀

where rental_date ='2005-05-25' order by inventory_id, customer_id;--文件排序,引导列为范围查询,而非等值

where rental_date ='2005-05-25' and inventory_id in (1,2) order by customer_id;--文件排序,inventory_id有多个等于条件,相当于范围查询

select actor_id, title from film_actor inner join file using(film_id) order by actor_id;--文件排序,因为film_actor表解析时位于Inner

 

文件排序

一般的order by,不使用索引排序就会用到文件排序,而文件排序并非一定使用磁盘,有可能完全在内存中完成;

Mysql目前支持两种文件排序算法

 

双路排序:

读取行指针和order by列进行排序,在sort_buffer_size中进行,当buffer满了运行快速排序并将结果存储于临时文件中,重复直至完成所有行;

扫描排序后的列表,按照其key值从表中读取行,存于read_rn_buffer_size

用到2次排序且第2次可能为随机读取;

 

单路排序:

读取所有列,按order by列排序,读取的列更多,可能需要更大的sort_buffer_size和额外的IO

扫描排序后的列表并输出;

4.1后引入,只用到1次排序

 

为了加速order by,尽量使用索引排序,如果无法做到这一点则临时调整如下参数

Sort_buffer_size/read_rnd_buffer_size

 

如何选择排序算法

 max_length_for_sort_data决定,其官方文档定义为The cutoff on the size of index values that determines which filesort algorithm to use

当所有返回字段长度总和小于此参数时,使用单路排序,否则为双路排序

 

临时表排序

http://space.itpub.net/?uid-15480802-action-viewspace-itemid-757553

 

 

group by

最常见的方法是扫描整个表记录然后创建一个临时表,根据临时表的数据应用聚集函数;

采用流聚合,而非散列聚合(oracle支持hash group by),如果一个sql包含的group by数据多于1行,则先进行排序;

使用group by时如果不想对结果额外排序(默认会排序),则添加order by null

order by 一样,group by也可以利用索引避免额外排序,有2种方法:松散索引扫描和紧凑索引扫描

松散索引扫描

适用条件:

1

针对单表操作

2

Group by使用索引的最左前缀列

3

只支持聚集函数min()/max()

4

Where条件出现的列必须为=constant操作   没出现在group by中的索引列必须使用constant

5

不支持前缀索引,即部分列索引 ,如index(c1(10))

 

Explainextra应该显示using index for group-by

 

假定表t1有个索引idx(c1,c2,c3)

SELECT c1, c2 FROM t1 GROUP BY c1, c2;

SELECT DISTINCT c1, c2 FROM t1;

SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;

SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2

SELECT c1, c3 FROM t1 GROUP BY c1, c2;--无法使用松散索引

SELECT c1, c3 FROM t1  where c3= const GROUP BY c1, c2;则可以

 

紧凑索引扫描

扫描索引时 须读取所有满足条件的索引键,

要么是全索引扫描,要么是范围索引扫描

当无法满足松散扫描条件时,此方法依旧可以避免使用临时表进行额外排序

Group by的索引列不连续;或者不是从最左前缀开始,但是where条件里出现最左列

 

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

 

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

 

http://www.cnblogs.com/ggjucheng/archive/2012/11/18/2776449.html 

 

http://dinglin.iteye.com/blog/1560634

 

使用order by / group by时,mysql可能会用到多个临时文件,可用到的最大上限=( length(排序列) + sizeof(行指针)) * 候选行数量 * 2

 

 

http://www.open-open.com/lib/view/open1328763772874.html

如果order by的子句只引用了联接中的第一个表,MySQL会先对第一个表进行排序,然后进行联接。也就是expain中的ExtraUsing Filesort.否则MySQL先把结果保存到临时表(Temporary Table),然后再对临时表的数据进行排序.此时expain中的Extra的显示Using temporary Using Filesort.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-1776416/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-05-01

  • 博文量
    383
  • 访问量
    179220