Mysql group by,order by,dinstict最佳化

標籤：操作   optimize   記錄   區別   大於   code   直接   order by   pad   

1.order by最佳化

實現方式：

1. 根據索引欄位排序，利用索引取出的資料已經是排好序的，直接返回給用戶端；

2. 沒有用到索引，將取出的資料進行一次排序操作後返回給用戶端。

1	EXPLAIN SELECT m.id,m.subject,c.content FROM group_message m,group_message_content c WHERE m.group_id = 1 AND m.id = c.group_msg_id ORDER BY m.user_id\G;

optimizer對query進行了最佳化，它會按照m.user_id上的索引順序來訪問資料，這樣擷取的資料已經是排好序的。

這種利用索引實現資料排序的方法是 MySQL 中實現結果集排序的最佳做法，利用已有的索引避免實際的排序計算所帶來的資源消耗。

 

如果沒有用到索引，mysql就會將取出的資料按照一定的排序演算法進行排序，然後再把排好序的資料返回給用戶端。mysql中主要使用兩種排序演算法：

1. 取出用於排序的條件欄位和指向相應資料行的指標，在sort buffer中對條件進行排序，排好序之後利用指標取出資料行中的請求資料，然後返回給用戶端；

2. 取出用於排序的條件欄位和其它所有請求資料，將不用於排序的欄位存放在一塊記憶體中，然後在sort buffer中對條件欄位進行排序，排好序後利用行指標將在記憶體中的資料進行匹配合并結果集，然後將排好序的資料返回給用戶端。（減少資料的二次訪問，節省了IO操作）

3.當用於排序的欄位存在在多個表中，或者在排序之前要先經過join操作，mysql必須先把join的結果集放入一個暫存資料表，之後再把暫存資料表中的資料取到sort buffer裡進行排序。

最佳化方式：

1. 加大max_length_for_sort_data參數的設定。當需要取出的所有資料長度小於這個參數的值的時候，mysql將採用第二重排序演算法，否則，使用第一種演算法，所以只要記憶體充足就可以設定足夠大的值來讓mysql採用改進的排序演算法。

2. 去掉不必要的欄位。

3. 增大sort_buffer_size參數的值。當mysql對條件欄位進行排序時，如果需要排序欄位的總長度大於該參數的值的時候，mysql就會對需要排序的欄位使用暫存資料表進行分段，這樣也會有效能的消耗。

 

2.group by最佳化

group by的實現過程除了要使用排序操作外，還要進行分組操作，如果使用到一些彙總函式，還要進行相應的彙總計算。

實現方式：

1. 使用鬆散（Loose）索引掃描實現group by，所謂的鬆散索引掃描，就是mysql不需要掃描所有滿足條件的索引鍵即可完成group by操作

2. 使用緊湊(Tight)索引掃描實現group by，緊湊索引與鬆散索引最主要的區別就是在需要掃描索引的時候，緊湊索引讀取所有滿足條件的索引鍵，然後再來使用group by操作得到相應的結果。

3.當optimizer無法找到合適的索引可以利用的時候，就會選擇將讀取的資料放入暫存資料表中來完成group by操作。group by操作想要利用索引，必須滿足group by欄位必須同時存放於同一個索引中，且該索引是一個有序索引，而且，使用不同的彙總函式也會影響是否使用索引來實現group by操作。

最佳化方式：

1. 儘可能利用索引並且是鬆散索引來完成group by操作，這的依靠調整索引或者調整query來實現；

2. 當無法利用索引的時候，必須要提供足夠的sort_buffer_size來供mysql完成排序操作，之前介紹過，不然mysql會將需要排序的欄位進行分段排序，會影響效能。除此之外盡量不要對大結果集進行group by操作，因為一旦資料量超過系統最大暫存資料表大小時，mysql會將暫存資料表裡的資料copy到磁碟上然後再進行操作，效能會成數量級的下降。

 

3.Dinstinct 最佳化

實現方式：

     distinct的實現原理同group by類似，實現過程只是在group by之後只取出每一組中的第一條記錄，所以distinct同樣可以利用鬆散或者緊湊索引來實現，不同的是，當無法利用索引實現distinct時，mysql同樣會將資料取出放進一個暫存資料表，不過不會對暫存資料表進行排序操作。

最佳化方式：

     盡量使用索引，無法使用索引的時候，確保不要在大結果集上進行distinct操作，磁碟上的IO操作和記憶體中的IO操作效能完全不是一個數量級的差距。

Mysql group by,order by,dinstict最佳化