MySQL Order By索引最佳化方法

儘管 ORDER BY 不是和索引的順序準確匹配，索引還是可以被用到，只要不用的索引部分和所有的額外的 ORDER BY 字段在 WHERE 子句中都被包括了。

使用索引的MySQL Order By
下列的幾個查詢都會使用索引來解決 ORDER BY 或 GROUP BY 部分： 

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; 
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2; 
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2; 
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC; 
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

不使用索引的MySQL Order By
在其他情況下，MySQL無法使用索引來滿足 ORDER BY，儘管它會使用索引來找到記錄來匹配 WHERE 子句。這些情況如下：
* 對不同的索引鍵做 ORDER BY ：
SELECT * FROM t1 ORDER BY key1, key2;
* 在非連續的索引鍵部分上做 ORDER BY：
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* 同時使用了 ASC 和 DESC：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* 用來搜尋記錄的索引鍵和做 ORDER BY 的不是同一個：
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* 有很多表格一起做連接，而且讀取的記錄中在 ORDER BY 中的字段都不全是來自第一個非常數的表中（也就是說，在 EXPLAIN 分析的結果中的第一個表的連接類型不是 const）。
* 使用了不同的 ORDER BY 和 GROUP BY 表達式。
* 表索引中的記錄不是按序儲存。例如，HASH 和 HEAP 表就是這樣。

透過執行 EXPLAIN SELECT ... ORDER BY，就知道MySQL是否在查詢中使用了索引。如果 Extra 欄位的值是 Using filesort，則表示MySQL無法使用索引。詳情請看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。當必須對結果進行排序時，MySQL 4.1以前 它使用了以下 filesort 演算法： 

1. 根据索引键读取记录，或者扫描数据表。那些无法匹配 WHERE 分句的记录都会被略过。 
2. 在缓冲中每条记录都用一个‘对'存储了2个值（索引键及记录指针）。缓冲的大小依据系统变量 sort_buffer_size 的值而定。 
3. 当缓冲慢了时，就运行 qsort（快速排序）并将结果存储在临时文件中。将存储的块指针保存起来（如果所有的‘对'值都能保存在缓冲中，就无需创建临时文件了）。 
4. 执行上面的操作，直到所有的记录都读取出来了。 
5. 做一次多重合并，将多达 MERGEBUFF（7）个区域的块保存在另一个临时文件中。重复这个操作，直到所有在第一个文件的块都放到第二个文件了。 
6. 重复以上操作，直到剩余的块数量小于 MERGEBUFF2 (15)。 
7. 在最后一次多重合并时，只有记录的指针（排序索引键的最后部分）写到结果文件中去。 
8. 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作，MySQL将记录指针读取放到一个大的块里，并且使用它来按序读取记录，将记录放到缓冲中。
缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。

這個逼近演算法的一個問題是，資料庫讀取了2次記錄：一次是估算 WHERE 分句時，第二次是排序時。儘管第一次都成功讀取記錄了（例如，做了一次全表掃描），第二次是隨機的讀取（索引鍵已經排好序了，但是記錄並沒有）。在MySQL 4.1 及更新版本中，filesort 最佳化演算法用於記錄中不只包括索引鍵值和記錄的位置，還包括查詢中要求的欄位。這麼做避免了需要2次讀取紀錄。改進的 filesort 演算法做法大致如下：
1. 跟以前一樣，讀取符合 WHERE 分句的記錄。
2. 相對於每個記錄，都記錄了一個對應的；‘元組'資訊信息，包括索引鍵值、記錄位置、以及查詢中所需要的所有欄位。
3. 依照索引鍵對‘元組'資訊進行排序。
4. 按序讀取記錄，不過是從已經排序過的‘元組'列表中讀取記錄，而非從數據表中再讀取一次。

使用改進後的 filesort 演算法比較原來的，『元組』比『對』需要佔用更長的空間，它們很少正好適合放在排序緩衝中（緩衝的大小是由 sort_buffer_size 的值決定的）。因此，這可能需要有更多的I/O操作，導致改進的演算法更慢。為了避免使之變慢，這種最佳化方法只用於排序‘元組'中額外的字段的大小總和超過系統變量 max_length_for_sort_data 的情況（這個變數的值設定太高的一個表象就是高磁碟負載低CPU負載）。想要提高 ORDER BY 的速度，首先要看MySQL能否使用索引而非額外的排序過程。如果不能使用索引，可以試著遵循以下策略：
* 增加 sort_buffer_size 的值。
* 增加 read_rnd_buffer_size 的值。
* 修改 tmpdir，讓它指向一個有很多剩餘空間的專用檔案系統。
如果使用MySQL 4.1或更新，這個選項允許有多個路徑用循環的格式。各個路徑之間在 Unix 上用冒號（':'）分隔開來，在 Windows，NetWare以及OS/2 上用分號（';'）。可以利用這個特性將負載平均分攤給幾個目錄。注意：這些路徑必須是分佈在不同實體磁碟上的目錄，而非同一個實體磁碟上的不同目錄。