MySQL SQL Tuning:深入理解Order By

走索引無排序需要滿足2個條件：
①排序欄位和執行計畫中所利用INDEX的索引鍵(或前面幾個索引鍵)完全一致
②表訪問方式為index、ref或range [注釋：explain輸出中的Type可看出]
最快的排序就是不去排序，這也證明，索引的另一個非常重要的優勢：
索引能夠降低排序成本，而排序涉及到的資源有CPU和記憶體，也就是
索引不僅能降低IO開銷，加速查詢，也能極大降低CPU開銷！

如果無法利用索引完成排序操作，則將走filesort，即使完全在記憶體中排序不需要磁碟檔案亦如是
對於filesort有2種演算法：
① two-pass
  
   原理：
   先讀取行指標和排序欄位，進行排序，而後依據排序結果再去讀取所需要的資料
   優點：
   排序的資料量較小，完全可以在記憶體中完成
   缺點：
   第二次讀取時，會發生大量的隨機IO，太昂貴

② single-pass 
   
   原理：
   一次性把sql中涉及到的欄位全部讀出，然後依據排序欄位排序，最後直接返回排序結果
   優點：
   只需一次順序IO，無須任何隨機IO，大大降低IO開銷
   缺點：
   記憶體容不下那麼多的資料，可能會先放在磁碟上，對大資料卻分，單個小塊排序
   排完放回磁碟，待所有單塊排完，最後進行結果集merge，再返回排序結果
   
當查詢所需列和排序列的總和大於max_length_for_sort_data時或者所需列是BLOB/TEXT，則選擇two-pass
single-pass總體而言表現較佳，如果希望使用，則不妨把max_length_for_sort_data加大 
這裡需要注意，計算所需列和排序列長度時，都是按最大長度給定，比如varchar，utf-8
所以，有時候甚至我們會看到排序消耗的臨時儲存空間比磁碟上原表要大多倍啊
另外，在Join時，order by所有列都來自關聯的第一個表時，Extra有using filesort
除此之外的一切Join情況，Extra會有using temporary，using filesort 
因為MySQL會先把所有被關聯的資料讀到暫存資料表，再filesort

如果別無選擇只能filesort，那麼如何加快order by？
①加大sort_buffer_size
②加大read_rnd_buffer_size【注釋：如果是single-pass可以不必考慮】
③只選擇必要的列，只為列選擇適合的資料類型
④加大tmpdir

① http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html
② http://isky000.com/database/mysql_order_by_implement
③ 高效能MySQL(3)版：P220-222，P368-369


By 迦葉
2013-10-1
Good Luck