前面跟小伙伴們分享的索引相關的內容，基本上都是在 where 子句中使用索引，實際上，索引也還有另外一個大的用處，那就是在排序中使用索引，今天我們就來聊聊這個話題。

1. 排序的兩種方式

MySQL 中想給查詢結果排序，我們只需要來一個 order by 即可，SQL 很簡單，底層實現起來整體上來說，有兩種不同的思路：

1. filesort，有時候我們也將之稱為文件排序，這個名字有時候會給我們一些誤解，讓人以為是在磁盤上進行排序的，然而實際上并不一定，數據量比較小的時候，直接在內存中進行排序就行了，只有當在內存中無法完成排序的時候，才會用到磁盤文件。
2. 索引排序，由于 InnoDB 中的索引是按照 B+Tree 的形式將數據組織在一起的，B+Tree 中數據本身就是有序的，所以如果能夠利用好索引，排序的事情就會事半功倍。

一共就這兩種排序的方式，小伙伴們也發現了，如果我們的索引設計比較合理，最終能夠按照第 2 種方式進行排序，那肯定是最好不過了。

不過這里需要注意一個細節，第二種排序方式快有一個前提，那就是不需要回表，如果查詢的過程中需要回表，那么第二種方式就不一定快了。原因也簡單：

• 如果不需要回表，也就是我們想要查詢的數據都在索引樹上，索引樹上的數據本身又都是按照順序存儲的，那么查到數據直接返回即可，本身就是有序的。
• 如果查詢的時候，索引樹上并沒有我們想要的字段，那么就需要回表，小伙伴們知道，回表基本上都是隨機 IO 了，因為回表的時候，主鍵值并不一定連續，此時效率就會低一些。那么這個時候第二種排序方式的性能就不一定強于第一種了，當然，這并無固定結論，還是要結合具體情況分析，這里我只是告訴小伙伴們有各種可能的情況。

2. 索引排序

如果我們想用上索引排序，那么需要滿足哪些條件呢？

還是以我們上篇文章的數據為例，假設我有如下表結構：

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;

這個表中有一個聯合索引，聯合索引的字段包含 username、age 和 address 三個。

表中的數據如下：

還是假設 username、age、address 三個字段組成聯合索引，B+Tree 如下：

小伙伴們就想想，怎么樣查詢，查出來的結果是有序的？

給大家 1 分鐘總結一下。

我們來梳理下：只有當索引的順序和 order by 子句的順序完全一致，并且所有列的排序方向也都一致的情況下，MySQL 才能通過索引來對結果進行排序，同時，如果是聯合索引，order by 子句也需要滿足最左匹配原則。

我舉幾個例子。

2.1 案例一

先來看如下 SQL：

select address from user order by username;

這個是查詢 address 字段，根據 username 進行排序。很明顯，我們想要的 address 字段就存在于這個聯合索引的 B+Tree 上，并且這個聯合索引的 B+Tree 就是按照 username 進行升序排序的，所以這個 SQL 就可以通過索引進行排序，如下圖：

type:index 就說明了 MySQL 使用了索引掃描來進行排序的。

2.2 案例二

再來看下面這條 SQL：

select address from user order by username asc,age desc\G

這個 SQL 還是查詢 address 字段，是根據 username 和 age 進行排序的，其中 username 是按照升序排序，age 則是按照倒序排序，小伙伴們想想，在前面這個聯合索引的 B+Tree 中，username 是升序的沒問題，當 username 相同的時候，age 也是按照升序排序的，但是 SQL 中卻要一個升序一個倒序，顯然從索引樹中拿到的數據無法滿足這樣的條件，所以這個查詢并不會使用索引排序，如下圖：

Extra 中的 Using filesort 就說明了這里需要文件排序，無法通過索引排序完成需求。

2.3 案例三

再來看如下 SQL：

select address from user order by username desc

這個 SQL 和 2.1 小節的 SQL 相比就是排序的順序變了，第一個 SQL 沒有寫順序，默認就是升序，這個里邊寫了是按照倒序來排列。B+Tree 中的 username 是升序，那么這個能用到索引排序嗎？這個是可以使用到索引排序的，在 MySQL5.7 中，執行計劃如下：

在 MySQL8.x 中，執行計劃如下：

小伙伴們看到，區別在于 Extra 中多了一個 Backward index scan。

這是啥意思呢？

在 MySQL8 之前，索引是可以被反向掃描的，但是反向掃描效率會低一些，所以小伙伴們看到，在 MySQL5.7 中用到了索引排序，而且也沒說其他的，這其實就是索引反向掃描了。

從 MySQL8 開始，索引定義時候的降序關鍵字 DESC 將不再被忽略，索引樹在存儲數據的時候可以降序存儲了，這樣在將來查詢的時候掃描索引就可以按照正向掃描了，正向掃描效率相對于反向掃描效率會高一些。

這塊我來舉個例子說明問題。假設我有如下創建表的 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 中執行如上 SQL 之后，再來查看表的定義，結果如下：

可以看到，雖然我在執行的時候定了索引字段的順序，但是這個順序實際上是被忽略了。

再來看看 MySQL8 中執行之后的結果：

可以看到，在 MySQL8 中，索引定義時字段的順序被保留了。這印證了我們前面所說的沒有問題。

最后，回到我們的問題，Backward index scan 表示優化器在查詢的時候將能夠使用降序索引。

2.4 案例四

再來看如下 SQL：

select gender from user where username='ab' order by age

這個 SQL 中已經給 username 指定了具體的值了，在前面的 B+Tree 中，當 username 已經確定的時候，那么接下來就是按照 age 排序的，如果 age 相同則是按照 address 排序，所以上面這個 SQL 是可以通過索引排序的：

2.5 案例五

再來看如下 SQL：

select gender from user where username='ab' order by address

這個 SQL 中 username 也是給指定了具體的值了，但是排序卻是按照 address 排序的，小伙伴們知道，當 username 確定后，首先是按照 age 排序，其次才是按照 address 排序，所以，對于上面這個 SQL，從索引樹中讀取出來的數據，順序并不一定是按照 address 排的，所以上面這個 SQL 無法用到索引排序：

2.6 案例六

再來看下面這個 SQL：

select gender from user where username like 'a%' order by age

這個 SQL 中的查詢條件 username 是范圍搜索，當 username 是范圍搜索的時候，就無法保證相應的 age 是有序的了，所以這個 SQL 也無法使用索引排序：

另外需要注意的是，像查詢條件中的 IN 和 BETWEEN 這樣的關鍵字，也算是范圍搜索，如果 where 子句中出現這些關鍵字，也是有可能導致無法使用索引排序的。

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 就不用多說了，排序字段中出現了索引之外的列，那肯定沒法使用索引排序了：

總之，就是當我們根據 where 子句中的條件從 B+Tree 中定位到數據之后，定位到的這個數據究竟是否有序？如果有序且是 SQL 中要求的順序，就能使用索引排序，否則就不可以。

現在我們再來回過頭看一下一開始的結論，大家這個時候應該就好理解了：

只有當索引的順序和 order by 子句的順序完全一致，并且所有列的排序方向也都一致的情況下，MySQL 才能通過索引來對結果進行排序，同時，如果是聯合索引，order by 子句也需要滿足最左匹配原則。

3. 其他情況

3.1 多表聯查

當我們在查詢的時候是多表連接查詢時，如果用到了排序，那么 order by 子句中涉及到的字段，必須全部在第一個表中，此時才會用到索引排序。

松哥舉一個 TienChin 項目中的例子，TienChin 中有一個活動渠道表 tienchin_channel，還有一個活動表 tienchin_activity，活動表中引用到了渠道表的 id，我們來做如下一個多表聯合查詢：

select ta.name from tienchin_activity ta inner join tienchin_channel tc using(`channel_id`)

我們來看下這個 SQL 的執行計劃：

可以看到，在這個查詢中，優化器將 ta 表作為了第一張表，tc 表作為了第二張表，那么根據前面的結論，如果使用第一個表中的索引排序，就會用到索引排序，第二張表的則用不了，我們來驗證一下。

可以看到，如果是第一張表的索引，就用到了索引排序；如果是第二張表的索引，就沒有用到索引排序，如果兩張表的索引都用了，也不會使用索引排序。

3.2 order by null

還有一種特殊的情況就是 order by null，不知道有沒有小伙伴見到過有人這樣寫？

在 MySQL8 之前，默認會按照 group by 的字段進行排序，此時加上 order by null 就是告訴 MySQL，不用幫我排序了，直接返回結果就行了，因為如果不加 order by null，則可能會進行 filesort 排序，降低查詢效率。

不過從 MySQL8 開始，默認已經不會按照 group by 字段排序了，所以這句現在其實可以不用寫了。

4. 小結

好啦，關于 MySQL 中的索引排序就和小伙伴們聊這么多，希望大家都有所收獲～