專注於Java領域優質技術，歡迎關注

文章轉載自yangyidba ， 作者 楊奇龍

一 前言

為什麼是再說呢？因為前面已經寫過 《order by 原理以及優化》 ，介紹 order by 的基本原理以及優化。如果覺得對 order by 原理了解不透徹可以參考其他同行的文章《MySQL排序內部原理探秘》.本文是基於官網文檔的二刷(基本翻譯+測試驗證),看完本文大部分開發同學可以瞭解到什麼樣的select + order by 語句可以使用索引，什麼樣的不能利用到索引排序。

二 分析

2.1 官方標準介紹

對於select order by語句如何能夠利用到索引，官方表述如下:

"The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause."

翻譯一下就是

即使ORDER BY語句不能精確匹配(組合)索引列也能使用索引,只要WHERE條件中的所有未使用的索引部分和所有額外的ORDER BY列為常數就行。如何理解這句話呢？我們通過具體用例來解釋。

2.2 準備工作

2.3 能夠利用索引的例子分析

官方的文檔 中介紹有7個例子可以使用索引進行排序。如果使用explain/desc工具查看執行計劃中的extra中出現了Using filesort則說明sql沒有用到排序優化。

案例一

SELECT * FROM t1 ORDER BY key_part1,key_part2,...;

分析:

顯然上述sql沒有利用到索引排序. type=ALL Extra=Using filesort,因為where字句沒有條件，優化器選擇全表掃描和內存排序。

分析:

從type=index，extra=Using index 可以看出當select 的字段包含在索引中時，能利用到索引排序功能，進行覆蓋索引掃描。使用select * 則不能利用覆蓋索引掃描且由於where語句沒有具體條件MySQL選擇了全表掃描且進行了排序操作。

案例二

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;

使用組合索引中的一部分做等值查詢 ，另一部分作為排序字段。更嚴謹的說法是where條件使用組合索引的左前綴等值查詢，使用剩餘字段進行order by排序。

分析:

where 條件字句可以基於 shid 進行索引查找並且利用(shid,gid)中gid的有序性避免額外的排序工作。我們基於本例解釋"即使ORDER BY語句不能精確匹配(組合)索引列也能使用索引,只要WHERE條件中的所有未使用的索引部分和所有額外的ORDER BY列為常數就行。"

該語句的order by gid 並未精確匹配到組合索引(shid,gid)，where條件 shid利用了組合索引的最左前綴且為等值常量查詢，對order by 而言shid就是額外的字段，沒有出現在order by子句中卻是組合索引的一部分。這樣的條件既可以使用索引來排序。

案例三

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

其實和案例一 類似，只是選擇了倒序。該sql不能利用索引的有序性，需要server層進行排序。

案例四

SELECT * FROM t1 WHERE keypart1 = 1 ORDER BY keypart1 DESC, key_part2 DESC;

本例和案例二類似，只是order by 字句中包含所有的組合索引列。

分析:

where shid=4 可以利用shid的索引定位數據記錄，select * 有不在索引裡面的字段，所以回表訪問組合索引列之外的數據，利用了gid索引的有序性避免了排序工作。

案例五

SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;

SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;

分析:

表總共24行,其中大於5的有16行，大於13的2行，導致MySQL優化器選擇了不同的執行計劃。這個測試說明和shid的區分度有關。

案例六

SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;

利用組合索引前綴索引進行ref等值查詢，其他字段進行範圍查詢，order by 非等值的字段。

分析:

利用shid=6的進行索引查詢記錄到了MySQL的ICP特性，無排序操作。

2.4 不能利用索引排序的分析

案例一 order by語句使用了多個不同的索引

SELECT * FROM t1 ORDER BY key1, key2;

分析:

因為sql使用了不同的索引列，在存儲上順序存在不一致的可能性，MySQL會選擇排序操作。

特例因為所有的輔助索引裡面都包含主鍵id，當where 字段加上order by字段溝通完整的索引時 ，可以避免filesort的。

案例二當查詢條件使用了與order by不同的其他的索引，且值為常量，但排序字段是另一個聯合索引的非連續部分時

SELECT * FROM t1 WHERE key2=constant ORDER BY keypart1, keypart3;

分析:

與案例一一致，key2 的順序語句key1(key_part1)存儲排序不一樣的情況下，MySQL 都會選擇filesort 。

案例三order by 語句使用了和組合索引默認不同的排序規則

SELECT * FROM t1 ORDER BY keypart1 DESC, keypart2 ASC;

官方文檔中提示使用混合索引排序規則會導致額外排序，其實我們創建索引的時候可以做 (keypart1 DESC, keypart2 ASC)

案例四當where 條件中利用的索引與order by 索引不同時，與案例二有相似性。

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

分析:

案例的sql 利用了idxtype 索引，但是order 使用了shid，gid 字段，沒有包含在idxtype 索引裡面，故不能利用idx_type索引排序。

案例五order by 字段使用了表達式

SELECT * FROM t1 ORDER BY ABS(key);

SELECT * FROM t1 ORDER BY -key;

分析:order by 的字段使用函數，和在where條件中使用函數索引一樣 ，MySQL都無法利用到索引。

案例六

The query joins many tables,and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows.(This is the first table in the EXPLAIN output that does not have a const join type.)

當查詢語句是多表連接，並且ORDER BY中的列並不是全部來自第1個用於搜索行的非常量表.(這是EXPLAIN輸出中的沒有使用const聯接類型的第1個表)

分析:

出現join的情況下不能利用索引其實有很多種，只要對a的訪問不滿足上面說的可以利用索引排序的情況都會導致額外的排序動作。但是當where + order 複合要求，order by 有包含了其他表的列就會導致額外的排序動作。

案例七sql中包含的order by 列與group by 列不一致 。

group by 本身會進行排序的操作,我們可以顯示的注讓group by不進行額外的排序動作。

案例八索引本身不支持排序存儲 比如，hash索引。

分析

hash 索引本身不支持排序存儲，故不能利用到排序特性，將錶轉化為innodb再次查詢，避免了filesort。

案例九order by的索引使用部分字符串 比如 key idx_name(name(2))

三 老生常談的優化策略

為了提高order by 查詢的速度，儘可能的利用索引的有序性進行排序，如果不能利用索引排序的功能，那麼我們只能退而求其次優化order by相關的緩存參數。

1. 增加 sort_buffer_size 大小，建議sort_buffer_size要足夠大能夠避免磁盤排序和合並排序次數。
2. 增加 read_rnd_buffer_size 大小。
3. 使用合適的列大小存儲具體的內容，比如對於city字段 varchar(20)比varchar(200)能獲取更好的性能。
4. 將tmpdir 目錄指定到os上面有足夠空間的具有比較高iops能力的存儲上。

---

最近發現了一個非常適合小白人工智能入門的教程，不僅通俗易懂而且還很風趣幽默。忍不住分享一下給大家。點下面鏈接可以跳轉到教程。