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

1. 排序的兩種方式

MySQL 中想給查詢結(jié)果排序，我們只需要來(lái)一個(gè) order by 即可，SQL 很簡(jiǎn)單，底層實(shí)現(xiàn)起來(lái)整體上來(lái)說(shuō)，有兩種不同的思路：

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

一共就這兩種排序的方式，小伙伴們也發(fā)現(xiàn)了，如果我們的索引設(shè)計(jì)比較合理，最終能夠按照第 2 種方式進(jìn)行排序，那肯定是最好不過(guò)了。

不過(guò)這里需要注意一個(gè)細(xì)節(jié)，第二種排序方式快有一個(gè)前提，那就是不需要回表，如果查詢的過(guò)程中需要回表，那么第二種方式就不一定快了。原因也簡(jiǎn)單：

• 如果不需要回表，也就是我們想要查詢的數(shù)據(jù)都在索引樹(shù)上，索引樹(shù)上的數(shù)據(jù)本身又都是按照順序存儲(chǔ)的，那么查到數(shù)據(jù)直接返回即可，本身就是有序的。
• 如果查詢的時(shí)候，索引樹(shù)上并沒(méi)有我們想要的字段，那么就需要回表，小伙伴們知道，回表基本上都是隨機(jī) IO 了，因?yàn)榛乇淼臅r(shí)候，主鍵值并不一定連續(xù)，此時(shí)效率就會(huì)低一些。那么這個(gè)時(shí)候第二種排序方式的性能就不一定強(qiáng)于第一種了，當(dāng)然，這并無(wú)固定結(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;

這個(gè)表中有一個(gè)聯(lián)合索引，聯(lián)合索引的字段包含 username、age 和 address 三個(gè)。

表中的數(shù)據(jù)如下：

還是假設(shè) username、age、address 三個(gè)字段組成聯(lián)合索引，B+Tree 如下：

小伙伴們就想想，怎么樣查詢，查出來(lái)的結(jié)果是有序的？

給大家 1 分鐘總結(jié)一下。

我們來(lái)梳理下：只有當(dāng)索引的順序和 order by 子句的順序完全一致，并且所有列的排序方向也都一致的情況下，MySQL 才能通過(guò)索引來(lái)對(duì)結(jié)果進(jìn)行排序，同時(shí)，如果是聯(lián)合索引，order by 子句也需要滿足最左匹配原則。

我舉幾個(gè)例子。

2.1 案例一

先來(lái)看如下 SQL：

select address from user order by username;

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

type:index 就說(shuō)明了 MySQL 使用了索引掃描來(lái)進(jìn)行排序的。

2.2 案例二

再來(lái)看下面這條 SQL：

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

這個(gè) SQL 還是查詢 address 字段，是根據(jù) username 和 age 進(jìn)行排序的，其中 username 是按照升序排序，age 則是按照倒序排序，小伙伴們想想，在前面這個(gè)聯(lián)合索引的 B+Tree 中，username 是升序的沒(méi)問(wèn)題，當(dāng) username 相同的時(shí)候，age 也是按照升序排序的，但是 SQL 中卻要一個(gè)升序一個(gè)倒序，顯然從索引樹(shù)中拿到的數(shù)據(jù)無(wú)法滿足這樣的條件，所以這個(gè)查詢并不會(huì)使用索引排序，如下圖：

Extra 中的 Using filesort 就說(shuō)明了這里需要文件排序，無(wú)法通過(guò)索引排序完成需求。

2.3 案例三

再來(lái)看如下 SQL：

select address from user order by username desc

這個(gè) SQL 和 2.1 小節(jié)的 SQL 相比就是排序的順序變了，第一個(gè) SQL 沒(méi)有寫(xiě)順序，默認(rèn)就是升序，這個(gè)里邊寫(xiě)了是按照倒序來(lái)排列。B+Tree 中的 username 是升序，那么這個(gè)能用到索引排序嗎？這個(gè)是可以使用到索引排序的，在 MySQL5.7 中，執(zhí)行計(jì)劃如下：

在 MySQL8.x 中，執(zhí)行計(jì)劃如下：

小伙伴們看到，區(qū)別在于 Extra 中多了一個(gè) Backward index scan。

這是啥意思呢？

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

從 MySQL8 開(kāi)始，索引定義時(shí)候的降序關(guān)鍵字 DESC 將不再被忽略，索引樹(shù)在存儲(chǔ)數(shù)據(jù)的時(shí)候可以降序存儲(chǔ)了，這樣在將來(lái)查詢的時(shí)候掃描索引就可以按照正向掃描了，正向掃描效率相對(duì)于反向掃描效率會(huì)高一些。

這塊我來(lái)舉個(gè)例子說(shuō)明問(wèn)題。假設(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)
);

當(dāng)我在 MySQL5.7 中執(zhí)行如上 SQL 之后，再來(lái)查看表的定義，結(jié)果如下：

可以看到，雖然我在執(zhí)行的時(shí)候定了索引字段的順序，但是這個(gè)順序?qū)嶋H上是被忽略了。

再來(lái)看看 MySQL8 中執(zhí)行之后的結(jié)果：

可以看到，在 MySQL8 中，索引定義時(shí)字段的順序被保留了。這印證了我們前面所說(shuō)的沒(méi)有問(wèn)題。

最后，回到我們的問(wèn)題，Backward index scan 表示優(yōu)化器在查詢的時(shí)候?qū)⒛軌蚴褂媒敌蛩饕?/p>

2.4 案例四

再來(lái)看如下 SQL：

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

這個(gè) SQL 中已經(jīng)給 username 指定了具體的值了，在前面的 B+Tree 中，當(dāng) username 已經(jīng)確定的時(shí)候，那么接下來(lái)就是按照 age 排序的，如果 age 相同則是按照 address 排序，所以上面這個(gè) SQL 是可以通過(guò)索引排序的：

2.5 案例五

再來(lái)看如下 SQL：

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

這個(gè) SQL 中 username 也是給指定了具體的值了，但是排序卻是按照 address 排序的，小伙伴們知道，當(dāng) username 確定后，首先是按照 age 排序，其次才是按照 address 排序，所以，對(duì)于上面這個(gè) SQL，從索引樹(shù)中讀取出來(lái)的數(shù)據(jù)，順序并不一定是按照 address 排的，所以上面這個(gè) SQL 無(wú)法用到索引排序：

2.6 案例六

再來(lái)看下面這個(gè) SQL：

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

這個(gè) SQL 中的查詢條件 username 是范圍搜索，當(dāng) username 是范圍搜索的時(shí)候，就無(wú)法保證相應(yīng)的 age 是有序的了，所以這個(gè) SQL 也無(wú)法使用索引排序：

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

2.7 案例七

再來(lái)看下面這個(gè) SQL：

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

這個(gè)雖然 username 也是按照范圍搜索，但是最終排序的時(shí)候卻是按照 username 和 age 排序的，按照范圍搜索拿出來(lái)的 username 和 age 本身就是有序的，所以這里也可以使用索引排序：

2.8 案例八

再來(lái)看下面這個(gè) SQL：

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

這個(gè) SQL 就不用多說(shuō)了，排序字段中出現(xiàn)了索引之外的列，那肯定沒(méi)法使用索引排序了：

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

現(xiàn)在我們?cè)賮?lái)回過(guò)頭看一下一開(kāi)始的結(jié)論，大家這個(gè)時(shí)候應(yīng)該就好理解了：

只有當(dāng)索引的順序和 order by 子句的順序完全一致，并且所有列的排序方向也都一致的情況下，MySQL 才能通過(guò)索引來(lái)對(duì)結(jié)果進(jìn)行排序，同時(shí)，如果是聯(lián)合索引，order by 子句也需要滿足最左匹配原則。

3. 其他情況

3.1 多表聯(lián)查

當(dāng)我們?cè)诓樵兊臅r(shí)候是多表連接查詢時(shí)，如果用到了排序，那么 order by 子句中涉及到的字段，必須全部在第一個(gè)表中，此時(shí)才會(huì)用到索引排序。

松哥舉一個(gè) TienChin 項(xiàng)目中的例子，TienChin 中有一個(gè)活動(dòng)渠道表 tienchin_channel，還有一個(gè)活動(dòng)表 tienchin_activity，活動(dòng)表中引用到了渠道表的 id，我們來(lái)做如下一個(gè)多表聯(lián)合查詢：

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

我們來(lái)看下這個(gè) SQL 的執(zhí)行計(jì)劃：

可以看到，在這個(gè)查詢中，優(yōu)化器將 ta 表作為了第一張表，tc 表作為了第二張表，那么根據(jù)前面的結(jié)論，如果使用第一個(gè)表中的索引排序，就會(huì)用到索引排序，第二張表的則用不了，我們來(lái)驗(yàn)證一下。

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

3.2 order by null

還有一種特殊的情況就是 order by null，不知道有沒(méi)有小伙伴見(jiàn)到過(guò)有人這樣寫(xiě)？

在 MySQL8 之前，默認(rèn)會(huì)按照 group by 的字段進(jìn)行排序，此時(shí)加上 order by null 就是告訴 MySQL，不用幫我排序了，直接返回結(jié)果就行了，因?yàn)槿绻患?nbsp;order by null，則可能會(huì)進(jìn)行 filesort 排序，降低查詢效率。

不過(guò)從 MySQL8 開(kāi)始，默認(rèn)已經(jīng)不會(huì)按照 group by 字段排序了，所以這句現(xiàn)在其實(shí)可以不用寫(xiě)了。

4. 小結(jié)

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