關(guān)于在 MySQL 排序中使用索引這件事！
前面跟小伙伴們分享的索引相關(guān)的內(nèi)容，基本上都是在 where 子句中使用索引，實際上，索引也還有另外一個大的用處，那就是在排序中使用索引，今天我們就來聊聊這個話題。
1. 排序的兩種方式
MySQL 中想給查詢結(jié)果排序，我們只需要來一個 order by 即可，SQL 很簡單，底層實現(xiàn)起來整體上來說，有兩種不同的思路：
- filesort，有時候我們也將之稱為文件排序，這個名字有時候會給我們一些誤解，讓人以為是在磁盤上進行排序的，然而實際上并不一定，數(shù)據(jù)量比較小的時候，直接在內(nèi)存中進行排序就行了，只有當在內(nèi)存中無法完成排序的時候，才會用到磁盤文件。
- 索引排序，由于 InnoDB 中的索引是按照 B+Tree 的形式將數(shù)據(jù)組織在一起的，B+Tree 中數(shù)據(jù)本身就是有序的，所以如果能夠利用好索引，排序的事情就會事半功倍。
一共就這兩種排序的方式，小伙伴們也發(fā)現(xiàn)了，如果我們的索引設(shè)計比較合理，最終能夠按照第 2 種方式進行排序，那肯定是最好不過了。
不過這里需要注意一個細節(jié)，第二種排序方式快有一個前提，那就是不需要回表，如果查詢的過程中需要回表，那么第二種方式就不一定快了。原因也簡單：
- 如果不需要回表，也就是我們想要查詢的數(shù)據(jù)都在索引樹上，索引樹上的數(shù)據(jù)本身又都是按照順序存儲的，那么查到數(shù)據(jù)直接返回即可，本身就是有序的。
- 如果查詢的時候，索引樹上并沒有我們想要的字段，那么就需要回表，小伙伴們知道，回表基本上都是隨機 IO 了，因為回表的時候，主鍵值并不一定連續(xù)，此時效率就會低一些。那么這個時候第二種排序方式的性能就不一定強于第一種了，當然，這并無固定結(jié)論，還是要結(jié)合具體情況分析，這里我只是告訴小伙伴們有各種可能的情況。
2. 索引排序
如果我們想用上索引排序，那么需要滿足哪些條件呢？
還是以我們上篇文章的數(shù)據(jù)為例，假設(shè)我有如下表結(jié)構(gòu)：
CREATE TABLE `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int DEFAULT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`gender` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_prop_index` (`username`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;這個表中有一個聯(lián)合索引，聯(lián)合索引的字段包含 username、age 和 address 三個。
表中的數(shù)據(jù)如下：
id(主鍵) | username | age | address | gender |
1 | ab | 99 | 深圳 | 男 |
2 | bw | 95 | 天津 | 男 |
3 | cx | 93 | 深圳 | 男 |
4 | bc | 80 | 上海 | 女 |
5 | bg | 85 | 重慶 | 女 |
6 | ac | 98 | 廣州 | 男 |
7 | bw | 99 | 海口 | 女 |
8 | ck | 90 | 深圳 | 男 |
9 | cc | 92 | 武漢 | 男 |
10 | af | 88 | 北京 | 女 |
還是假設(shè) username、age、address 三個字段組成聯(lián)合索引，B+Tree 如下：
小伙伴們就想想，怎么樣查詢，查出來的結(jié)果是有序的？
給大家 1 分鐘總結(jié)一下。
我們來梳理下：只有當索引的順序和 order by 子句的順序完全一致，并且所有列的排序方向也都一致的情況下，MySQL 才能通過索引來對結(jié)果進行排序，同時，如果是聯(lián)合索引，order by 子句也需要滿足最左匹配原則。
我舉幾個例子。
2.1 案例一
先來看如下 SQL：
select address from user order by username;這個是查詢 address 字段，根據(jù) username 進行排序。很明顯，我們想要的 address 字段就存在于這個聯(lián)合索引的 B+Tree 上，并且這個聯(lián)合索引的 B+Tree 就是按照 username 進行升序排序的，所以這個 SQL 就可以通過索引進行排序，如下圖：
type:index 就說明了 MySQL 使用了索引掃描來進行排序的。
2.2 案例二
再來看下面這條 SQL：
select address from user order by username asc,age desc\G這個 SQL 還是查詢 address 字段，是根據(jù) username 和 age 進行排序的，其中 username 是按照升序排序，age 則是按照倒序排序，小伙伴們想想，在前面這個聯(lián)合索引的 B+Tree 中，username 是升序的沒問題，當 username 相同的時候，age 也是按照升序排序的，但是 SQL 中卻要一個升序一個倒序，顯然從索引樹中拿到的數(shù)據(jù)無法滿足這樣的條件，所以這個查詢并不會使用索引排序，如下圖：
Extra 中的 Using filesort 就說明了這里需要文件排序，無法通過索引排序完成需求。
2.3 案例三
再來看如下 SQL：
select address from user order by username desc這個 SQL 和 2.1 小節(jié)的 SQL 相比就是排序的順序變了，第一個 SQL 沒有寫順序，默認就是升序，這個里邊寫了是按照倒序來排列。B+Tree 中的 username 是升序，那么這個能用到索引排序嗎？這個是可以使用到索引排序的，在 MySQL5.7 中，執(zhí)行計劃如下：
在 MySQL8.x 中，執(zhí)行計劃如下：
小伙伴們看到，區(qū)別在于 Extra 中多了一個 Backward index scan。
這是啥意思呢？
在 MySQL8 之前，索引是可以被反向掃描的，但是反向掃描效率會低一些，所以小伙伴們看到，在 MySQL5.7 中用到了索引排序，而且也沒說其他的，這其實就是索引反向掃描了。
從 MySQL8 開始，索引定義時候的降序關(guān)鍵字 DESC 將不再被忽略，索引樹在存儲數(shù)據(jù)的時候可以降序存儲了，這樣在將來查詢的時候掃描索引就可以按照正向掃描了，正向掃描效率相對于反向掃描效率會高一些。
這塊我來舉個例子說明問題。假設(shè)我有如下創(chuàng)建表的 SQL：
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);當我在 MySQL5.7 中執(zhí)行如上 SQL 之后，再來查看表的定義，結(jié)果如下：
可以看到，雖然我在執(zhí)行的時候定了索引字段的順序，但是這個順序?qū)嶋H上是被忽略了。
再來看看 MySQL8 中執(zhí)行之后的結(jié)果：
可以看到，在 MySQL8 中，索引定義時字段的順序被保留了。這印證了我們前面所說的沒有問題。
最后，回到我們的問題，Backward index scan 表示優(yōu)化器在查詢的時候?qū)⒛軌蚴褂媒敌蛩饕?/p>
2.4 案例四
再來看如下 SQL：
select gender from user where username='ab' order by age這個 SQL 中已經(jīng)給 username 指定了具體的值了，在前面的 B+Tree 中，當 username 已經(jīng)確定的時候，那么接下來就是按照 age 排序的，如果 age 相同則是按照 address 排序，所以上面這個 SQL 是可以通過索引排序的：
2.5 案例五
再來看如下 SQL：
select gender from user where username='ab' order by address這個 SQL 中 username 也是給指定了具體的值了，但是排序卻是按照 address 排序的，小伙伴們知道，當 username 確定后，首先是按照 age 排序，其次才是按照 address 排序，所以，對于上面這個 SQL，從索引樹中讀取出來的數(shù)據(jù)，順序并不一定是按照 address 排的，所以上面這個 SQL 無法用到索引排序：
2.6 案例六
再來看下面這個 SQL：
select gender from user where username like 'a%' order by age這個 SQL 中的查詢條件 username 是范圍搜索，當 username 是范圍搜索的時候，就無法保證相應(yīng)的 age 是有序的了，所以這個 SQL 也無法使用索引排序：
另外需要注意的是，像查詢條件中的 IN 和 BETWEEN 這樣的關(guān)鍵字，也算是范圍搜索，如果 where 子句中出現(xiàn)這些關(guān)鍵字，也是有可能導(dǎo)致無法使用索引排序的。
2.7 案例七
再來看下面這個 SQL：
select gender from user where username like 'a%' order by username,age這個雖然 username 也是按照范圍搜索，但是最終排序的時候卻是按照 username 和 age 排序的，按照范圍搜索拿出來的 username 和 age 本身就是有序的，所以這里也可以使用索引排序：
2.8 案例八
再來看下面這個 SQL：
select gender from user where username like 'a%' order by username,gender這個 SQL 就不用多說了，排序字段中出現(xiàn)了索引之外的列，那肯定沒法使用索引排序了：
總之，就是當我們根據(jù) where 子句中的條件從 B+Tree 中定位到數(shù)據(jù)之后，定位到的這個數(shù)據(jù)究竟是否有序？如果有序且是 SQL 中要求的順序，就能使用索引排序，否則就不可以。
現(xiàn)在我們再來回過頭看一下一開始的結(jié)論，大家這個時候應(yīng)該就好理解了：
只有當索引的順序和 order by 子句的順序完全一致，并且所有列的排序方向也都一致的情況下，MySQL 才能通過索引來對結(jié)果進行排序，同時，如果是聯(lián)合索引，order by 子句也需要滿足最左匹配原則。
3. 其他情況
3.1 多表聯(lián)查
當我們在查詢的時候是多表連接查詢時，如果用到了排序，那么 order by 子句中涉及到的字段，必須全部在第一個表中，此時才會用到索引排序。
松哥舉一個 TienChin 項目中的例子，TienChin 中有一個活動渠道表 tienchin_channel，還有一個活動表 tienchin_activity，活動表中引用到了渠道表的 id，我們來做如下一個多表聯(lián)合查詢：
select ta.name from tienchin_activity ta inner join tienchin_channel tc using(`channel_id`)我們來看下這個 SQL 的執(zhí)行計劃：
可以看到，在這個查詢中，優(yōu)化器將 ta 表作為了第一張表，tc 表作為了第二張表，那么根據(jù)前面的結(jié)論，如果使用第一個表中的索引排序，就會用到索引排序，第二張表的則用不了，我們來驗證一下。
可以看到，如果是第一張表的索引，就用到了索引排序；如果是第二張表的索引，就沒有用到索引排序，如果兩張表的索引都用了，也不會使用索引排序。
3.2 order by null
還有一種特殊的情況就是 order by null，不知道有沒有小伙伴見到過有人這樣寫？
在 MySQL8 之前，默認會按照 group by 的字段進行排序，此時加上 order by null 就是告訴 MySQL，不用幫我排序了，直接返回結(jié)果就行了，因為如果不加 order by null，則可能會進行 filesort 排序，降低查詢效率。
不過從 MySQL8 開始，默認已經(jīng)不會按照 group by 字段排序了，所以這句現(xiàn)在其實可以不用寫了。
4. 小結(jié)
好啦，關(guān)于 MySQL 中的索引排序就和小伙伴們聊這么多，希望大家都有所收獲～
