mysql的order by和group by

 

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))

 

Explain的extra應該顯示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

 

 

如果order by的子句只引用了聯接中的第一個表，MySQL會先對第一個表進行排序，然後進行聯接。也就是expain中的Extra的Using Filesort.否則MySQL先把結果儲存到臨時表(Temporary Table),然後再對臨時表的資料進行排序.此時expain中的Extra的顯示Using temporary Using Filesort.