MySQL 5.7 ORDER BY排序的優化

在一些情況下，MySQL可以使用索引而無需額外的排序來執行ORDER BY排序。
即使ORDER BY沒有匹配上指定的索引，也可以使用索引，只要所有未使用索引部分和所有ORDER BY欄位是WHERE語句的常數。

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不會使用索引進行排序

查詢使用多個不同的索引進行排序

SELECT * FROM t1 ORDER BY key1, key2;

查詢在索引的非連續部分上使用排序

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;

查詢使用表示式進行排序，而不是使用索引的欄位名進行排序

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

查詢關聯了許多表，並且ORDER BY使用的欄位不是返回行的第一個非常量表中的欄位（在EXPLAIN執行計劃輸出中第一個表的連線型別不是const）

查詢使用不同的ORDER BY和GROUP BY表示式

在排序中只使用了一個欄位的字首索引

索引沒有按照順序儲存行，例如MEMORY表的HASH索引

如果一個查詢包含GROUP BY，但是你不想將查詢結果排序，可以通過ORDER BY NULL語句來取消排序

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

與提升ORDER BY速度相關的系統引數：

增加sort_buffer_size引數的值。理想狀況下，這個引數的值應該足夠大，可以將整個結果集放入到sort buffer中，避免到磁碟中進行排序。

儲存在緩衝區的列值的大小是由max_sort_length引數決定的。

想要監控排序在磁碟上的合併數量，可以通過觀察Sort_merge_passes這個狀態引數。
mysql> show global status like 'Sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.11 sec)

增加read_rnd_buffer_size引數的值。

定義列的時候，僅分配它們需要的值，這樣每行在排序時可以使用更少的記憶體。例如，如果每列的值不超過16個字元，CHAR(16)比CHAR(200)更好。

改變tmpdir系統變數，將其指向一個專用的大容量的檔案系統目錄。

如果ORDER BY排序沒有使用索引，但是語句中帶有LIMIT語句，優化器可能會避免使用合併檔案而是在記憶體中進行排序。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2122017/，如需轉載，請註明出處，否則將追究法律責任。