8.2.1.15 ORDER BY Optimization
在有些情況下,MySQL 可以使用index 來返祖order by 子句而不做任何額外的排序:
index 可以用于使用,即使 ORDER BY 不比對index exactly,
隻要所有的索引的不使用的部分和所有的額外的ORDER BY 子句是常量在WHERE 子句裡,
下面的查詢使用index 來拒絕排序:
SELECT * FROM t1
ORDER BY key_part1,key_part2,… ;
SELECT * FROM t1
WHERE key_part1 = constant
ORDER 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;
SELECT * FROM t1
WHERE key_part1 > constant
ORDER BY key_part1 ASC;
SELECT * FROM t1
WHERE key_part1 < constant
ORDER BY key_part1 DESC;
SELECT * FROM t1
WHERE key_part1 = constant1 AND key_part2 > constant2
ORDER BY key_part2;
在一些情況下, MySQL 不能使用index 來解決ORDER BY ，盡管 它仍舊使用索引來找到比對WHERE 條件的記錄,
這種情況如下:
1.你使用ORDER BY 在不同的keys上
SELECT * FROM t1 ORDER BY key1, key2;
2.你使用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;
4.索引用于提取記錄和用于排序的不一樣:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
5.SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
你關聯了很多表, ORDER BY 列 不是從第一個非常量表來用于檢索記錄
(這是第一個表在EXPLAIN 輸出,沒有常量關聯類型)
你有不同的ORDER BY 或者GROUP BY 表達式:
你的索引隻有一個字首列在ORDER BY子句,在這種情況下, index 不能用于完整的剞劂排序順序。
比如,如果你有一個CHAR(20）列, 但是索引隻有10個位元組,索引不能區分 傳遞的第10個位元組和一個排序需要
表索引類型使用不能用于按順序的存儲記錄,比如, 對于一個HASH index 在一個MEMORY table.
一個索引用于排序的有效性可能受别名的使用影響。假設column t1.a 是有索引,
在這個語句裡, 在查詢清單裡 列的名字是a. 它指向t1.a，是以關聯到a在ORDER BY ,index 可以被使用:
SELECT a FROM t1 ORDER BY a;
在這個語句, SELECT語句裡的列仍舊是a， 但是它是alias name.
但是它是别名,它指向ABS(a), 是以涉及a在ORDER BY ，索引不能被使用:
SELECT ABS(a) AS a FROM t1 ORDER BY a;
在下面的語句,ORDER BY 指向一個name，但不是SELECT 清單裡的列名。
但是有一個列在t1 叫做a， 是以ORDER BY 使用這個, 索引被使用
SELECT ABS(a) AS b FROM t1 ORDER BY a;
預設情況下, MySQL 排序所有的GROUP BY col1,col2 查詢如果你指定col1,col2 在查詢裡。
如果你包含一個明确的ORDER BY 子句 包含相同的列,
MySQL 優化總是沒有任何速度害處，盡管排序仍舊發生。如果一個查詢包含GROUP BY ,
但是比需要避免排序結果的開銷,你可以阻止排序通過指定ORDER BY NULL。
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
注意:
依賴隐含的GROUP BY 排序在MySQL 5.6 是過時的,為了完成一個特定的排序順序,
最好是使用一個顯示的ORDER 通過子句。GROUP BY 排序是一個MySQL 擴充,可能會在未來的版本中改變。
例如, 有可能使優化器來排序分區。
EXPLAIN SELECT … ORDER BY,
你可以檢查是否MySQL 可以使用索引來解決查詢,
它不能如果你看到使用filesort 在額外的列。
See Section 8.8.1, “Optimizing Queries with EXPLAIN”.
Filesort 使用一個固定長度行存儲格式類似于MEMORY 記憶體引擎的使用
MySQL 有2個filesort 算法用于排序和檢索結果, 原始的方法是隻使用ORDER BY 列。
修改的方法不隻是使用ORDER BY 子句,而是所有涉及查詢的列。
優化器選擇 哪個filesort 算法來使用, 它通常使用修改的算法出了當BLOB或者TEXT 列被涉及,
在這種情況下,利用原來的算法。對于兩種算法,sort buffer size 是sort_buffer_size 系統變量控制:
原始的filesort 算法工作如下:
- 讀取所有的記錄根據索引或者表掃描,跳過不比對的行:
- 對于每一行, 存儲一對值(sort key value和row ID）在sort buffer：
- 如果所有的對被放入到sort buffer, 沒有臨時檔案産生。否則, 當sort buffer 變滿,
運維一個qsort(quicksort)在記憶體裡,寫入到臨時檔案,保留一個指針指向排序塊。
- 重複前面的步驟,直到所有的行被讀取
- 做一個multi-merge 到MERGEBUFF (7) 區域到一個block 在另外的一個臨時檔案,
重複直到所有的塊從第一個檔案都到第2個檔案。
5.重複下面的直到 有很少的相比 MERGEBUFF2 (15) blocks留下
6.在最後一個 multi-merge, 隻有row ID（ 最後部分的對值)是寫到一個結果檔案)
這種方法的一個問題是它讀取行要2次, 一次在WHERE 子句執行時, 另外一個在排序值後。
即使第一次通路記錄成功(比如,一個表掃描是被做)
第2次是随機通路的(sort keys是有序的,但是記錄位置不是)
修改filesort 算法采用一個優化用于避免讀取2次, 它記錄sort key value，代替row ID，
它記錄查詢相關的列。 修改後的filesort 算法工作如下:
讀取比對WHERE 條件的記錄:
1.對于每一行, 記錄一個值的tuple 有sort key 值組成和查詢涉及的列
- 當sort buffer 變滿, 排序tuples 通過sort key value 在記憶體裡和寫到temporary 檔案
3.在 merge-sorting the temporary file, 檢索從排序的order裡檢索資料,
但是讀需要的列直接從存儲的tuples 相比第2次通路表
使用修改後的filesort 算法,tuples 是比原來使用的方法長, 它們中的少數放入到sort buffer.
其結果是,它是可能的對于額外的I/O 來使修改後的方法更慢,而不是更快。
為了避免變慢,優化器使用修改算法隻有在額外的列的總大小在sort tuple 不超過max_length_for_sort_data 的值。
如果一個filesort 被執行, EXPLAIN 輸出包括 使用filesort in the Extra column.
優化的trace 輸出包括一個filesort_summary block. For example:
“filesort_summary”: {
“rows”: 100,
“examined_rows”: 100,
“number_of_tmp_files”: 0,
“sort_buffer_size”: 25192,
“sort_mode”: “