什么是降序索引

大家可能對(duì)索引比較熟悉，而對(duì)降序索引比較陌生，事實(shí)上降序索引是索引的子集。

我們通常使用下面的語(yǔ)句來(lái)創(chuàng)建一個(gè)索引：

 

create index idx_t1_bcd on t1(b,c,d); 

上面sql的意思是在t1表中，針對(duì)b,c,d三個(gè)字段創(chuàng)建一個(gè)聯(lián)合索引。

但是大家不知道的是，上面這個(gè)sql實(shí)際上和下面的這個(gè)sql是等價(jià)的：

 

create index idx_t1_bcd on t1(b asc,c asc,d asc); 

asc表示的是升序，使用這種語(yǔ)法創(chuàng)建出來(lái)的索引叫做升序索引。也就是我們平時(shí)在創(chuàng)建索引的時(shí)候，創(chuàng)建的都是升序索引。

可能你會(huì)想到，在創(chuàng)建的索引的時(shí)候，可以針對(duì)字段設(shè)置asc，那是不是也可以設(shè)置desc呢?

當(dāng)然是可以的，比如下面三個(gè)語(yǔ)句：

create index idx_t1_bcd on t1(b desc,c desc,d desc);
create index idx_t1_bcd on t1(b asc,c desc,d desc);
create index idx_t1_bcd on t1(b asc,c asc,d desc);

這種語(yǔ)法在mysql中也是支持的，使用這種語(yǔ)法創(chuàng)建出來(lái)的索引就叫降序索引，關(guān)鍵問(wèn)題是：在Mysql8.0之前僅僅只是語(yǔ)法層面的支持，底層并沒(méi)有真正支持。

我們分別使用Mysql7、Mysql8兩個(gè)版本來(lái)舉例子說(shuō)明一下：

在Mysql7、Mysql8中分別創(chuàng)建一個(gè)表，有a,b,c,d,e五個(gè)字段：

 

create table t1 ( 
a int primary key, 
b int, 
c int, 
d int, 
e varchar(20) 
) engine=InnoDB; 

然后分別創(chuàng)建一個(gè)降序索引：

 

create index idx_t1_bcd on t1(b desc,c desc,d desc); 

創(chuàng)建成功后，我們使用以下sql查看一下索引信息：

 

show index from t1; 

Mysql7中你將得到結(jié)果：

 

 

Mysql8中你將得到結(jié)果：

 

 

image.png

我們只關(guān)心Key_name為idx_t1_bcd的三行記錄，細(xì)心的你應(yīng)該可以發(fā)現(xiàn)，這兩個(gè)結(jié)果中的Collation字段的結(jié)果是不一樣的：

• 在Mysql7中，Collation字段的結(jié)果為A,A,A，表示b,c,d三個(gè)字段的排序方式是asc
• 在Mysql8中，Collation字段的結(jié)果為D,D,D，表示b,c,d三個(gè)字段的排序方式是desc

但是我們?cè)趧?chuàng)建索引的時(shí)候，明明在語(yǔ)法層面已經(jīng)指定了b,c,d三個(gè)字段的排序方式是desc，這就可以看出來(lái)在Mysql7中降序索引只是語(yǔ)法層面的支持，底層并沒(méi)有真正支持，并且固定是升序索引。而在Mysql8中則真正從底層支持了降序索引。

到此為止，大家應(yīng)該對(duì)升序索引和降序索引有了一個(gè)大概的了解，但并沒(méi)有真正理解，因?yàn)榇蠹也⒉恢郎蛩饕c降序索引底層到底是如何實(shí)現(xiàn)的。

升序索引底層實(shí)現(xiàn)

我們知道，索引是用來(lái)提高查詢(xún)速度的，但是為什么索引能提高查詢(xún)速度呢?

給定你一個(gè)數(shù)列，比如[1,3,7,9,2,5,4,6,8]，這是一個(gè)無(wú)序的數(shù)列或數(shù)組，現(xiàn)在如果想提高這個(gè)數(shù)列的查詢(xún)速度，你首先會(huì)做什么? 我相信大部分人都能夠想到先排序，先把這個(gè)無(wú)序的數(shù)列，按從小到大的順序進(jìn)行排序，比如得到[1,2,3,4,5,6,7,8,9]，有了這個(gè)有序的數(shù)列之后，我們就可以利用比如二分法等等算法來(lái)提高這個(gè)數(shù)列的查詢(xún)速度了。

我舉這個(gè)例子想告訴大家的是：想提高數(shù)據(jù)集合的查詢(xún)速度，首先你可以對(duì)這些數(shù)據(jù)進(jìn)行排序。

所以，對(duì)Mysql表中的存儲(chǔ)的數(shù)據(jù)也是一樣的，我們?nèi)绻胩岣哌@個(gè)表的查詢(xún)速度，我們可以先對(duì)這個(gè)表里的數(shù)據(jù)進(jìn)行排序，那么表里的某一行數(shù)據(jù)包括了很多字段，我們現(xiàn)在想對(duì)這些數(shù)據(jù)行進(jìn)行排序，我們應(yīng)該根據(jù)哪些字段來(lái)確定這個(gè)順序呢?這就是索引，在創(chuàng)建索引的時(shí)候你所指定的列就是用來(lái)對(duì)表里的數(shù)據(jù)行進(jìn)行排序的。

比如我們?nèi)匀焕蒙厦嫠鶆?chuàng)建的t1表，向t1表里插入8條數(shù)據(jù)：

 

insert into t1 values(4,3,1,1,'d'); 
insert into t1 values(1,1,1,1,'a'); 
insert into t1 values(8,8,8,8,'h'); 
insert into t1 values(2,2,2,2,'b'); 
insert into t1 values(5,2,3,5,'e'); 
insert into t1 values(3,3,2,2,'c'); 
insert into t1 values(7,4,5,5,'g'); 
insert into t1 values(6,6,4,4,'f'); 

那么這些數(shù)據(jù)肯定是存儲(chǔ)在文件中的，所以文件中保存這些數(shù)據(jù)的格式大概如下，順序與插入順序保持一致：

 

4311d 
1111a 
8888h 
2222b 
5235e 
3322c 
7455g 
6644f 

注意，t1是Innodb的存儲(chǔ)引擎，而且a字段是主鍵，所以Innodb存儲(chǔ)引擎在處理這些插入的數(shù)據(jù)時(shí)，會(huì)按主鍵進(jìn)行排序，也就是上面我說(shuō)的文件中保存這些數(shù)據(jù)的格式是不準(zhǔn)確的，因?yàn)椴幌肫L(zhǎng)，所以不去深究，感興趣的同學(xué)可以關(guān)注一波公眾號(hào)：1點(diǎn)25，我會(huì)專(zhuān)門(mén)寫(xiě)一篇文章來(lái)講解Innodb中索引的具體實(shí)現(xiàn)，包括B+樹(shù)到底是如何生成的。

而如果我們基于上面的這種存儲(chǔ)方式，來(lái)查找數(shù)據(jù)，比如查找a=3的這行記錄，查找需要從第一行記錄開(kāi)始查找，那么要查找6次，而如果我們將上面的數(shù)據(jù)按照a字段的大小來(lái)進(jìn)行排序：

 

1111a 
2222b 
3322c 
4311d 
5235e 
6644f 
7455g 
8888h 

排好序之后，如果我們還是查找a=3的這行記錄，我們只需要查3次了。而且這樣還有一個(gè)好處就是，如果我們現(xiàn)在需要查找a=3.5這行數(shù)據(jù)，如果我們基于未排序之前的存儲(chǔ)方式，我們需要查詢(xún)所有8行數(shù)據(jù)最終確定a=3.5這行數(shù)據(jù)不存在，而如果我們利用排好序之后的存儲(chǔ)方式，我們就只需要查4次就好了，因?yàn)楫?dāng)你查到4311d這行記錄時(shí)，你會(huì)發(fā)現(xiàn)4>3.5了，已經(jīng)可以確定a=3.5的這行記錄不存在了。

而如果我們現(xiàn)在對(duì)t1創(chuàng)建一個(gè)索引，就像上面創(chuàng)建索引一樣，如果我們寫(xiě)的是下面的sql:

 

create index idx_t1_bcd on t1(b,c,d); 

這個(gè)sql表示要對(duì)t1創(chuàng)建一個(gè)索引，索引字段是b,c,d，并且是升序的，所以實(shí)際上就是對(duì)原本的數(shù)據(jù)按照b,c,d三個(gè)字段進(jìn)行排序，那么排序之后類(lèi)似：

 

1111a 
2222b 
5235e 
4311d 
3322c 
7455g 
6644f 
8888h 

可以好好看下，上面的記錄是按照b,c,d三個(gè)字段來(lái)對(duì)數(shù)據(jù)行就行排序的，比如1111a中的b,c,d三個(gè)字段的值是111，而2222b中的b,c,d三個(gè)字段的值是222, 111是小于222的，所以對(duì)應(yīng)的行排在前面。

那么數(shù)據(jù)如果這樣排序有什么好處呢?其實(shí)和剛剛按a字段排序之后的好處是類(lèi)似的，比如你現(xiàn)在想來(lái)查找b=4 and c=4 and d=4的數(shù)據(jù)也是能查詢(xún)更快的，實(shí)際上這就是索引的原理： 我們對(duì)某個(gè)表創(chuàng)建一個(gè)索引，就是對(duì)這個(gè)表中的數(shù)據(jù)進(jìn)行排序，而排好序之后的數(shù)據(jù)是能夠提高查詢(xún)速度。

還有一點(diǎn)需要注意的是，排序有很多中方式，或者所可以利用一些數(shù)據(jù)結(jié)構(gòu)，比如二叉樹(shù)、紅黑樹(shù)、B+樹(shù)，這些數(shù)據(jù)結(jié)構(gòu)實(shí)際上就是對(duì)數(shù)據(jù)進(jìn)行排序，只是排序的形式各不相同而已，每種數(shù)據(jù)結(jié)構(gòu)有它各自的特點(diǎn)，而大家應(yīng)該都知道，Mysql中用得最多的就是B+樹(shù)了。

相信，看到這里，大家應(yīng)該對(duì)索引重新有了認(rèn)識(shí)，只不過(guò)我們上面舉的幾個(gè)例子都是升序排序，而且排好序之后的數(shù)據(jù)不僅可以提高查詢(xún)速度，而且對(duì)于order by也是管用的，比如我們?nèi)绻F(xiàn)在想對(duì)t1進(jìn)行order by b asc,c asc,d asc;對(duì)于這個(gè)排序，如果已經(jīng)在t1表建立了b,c,d的升序索引，那么就代表對(duì)t1表中的數(shù)據(jù)已經(jīng)提前按照b,c,d排好序了，所以對(duì)于order by語(yǔ)句可以直接使用已經(jīng)排好序的數(shù)據(jù)了，不用利用filesort再次進(jìn)行排序了。

而且如果我們的order by是order by b desc, c desc, d desc，同樣可以利用b,c,d的升序索引，因?yàn)槿绻莖rder by b asc,c asc,d asc就從上往下遍歷即可，如果是order by b desc, c desc, d desc就從下往上遍歷即可。

那么，如果是order by b asc, c desc, d desc呢?這個(gè)order by是不是就沒(méi)有辦法利用b,c,d的升序索引了。

這個(gè)時(shí)候就需要降序索引了。

降序索引底層實(shí)現(xiàn)

我們花了較大篇幅介紹了升序索引的實(shí)現(xiàn)原理，總結(jié)來(lái)說(shuō)就是對(duì)表中的數(shù)據(jù)按照指定的字段比較大小進(jìn)行升序排序。

升序是什么?是數(shù)據(jù)進(jìn)行大小比較后，是小的在上，大的在下，或者如果是B+樹(shù)的話(huà)就是小的在左，大的在右。而降序就是大的在上，小的在下，或者如果是B+樹(shù)的話(huà)就是大的在左，小的在右。

所以，對(duì)于上面的那份原始數(shù)據(jù)：

 

4311d 
1111a 
8888h 
2222b 
5235e 
3322c 
7455g 
6644f 
復(fù)制代碼 

如果我們將這份數(shù)據(jù)按照a desc進(jìn)行排序就是：

 

8888h 
7455g 
6644f 
5235e 
4311d 
3322c 
2222b 
1111a 

非常簡(jiǎn)單吧，那如果我們將這份數(shù)據(jù)按照b desc, c desc, d desc排序就是：

 

8888h 
6644f 
7455g 
3322c 
4311d 
5235e 
2222b 
1111a 

也非常簡(jiǎn)單，那如果我們要將這份數(shù)據(jù)按照b desc, c asc, d desc排序呢?這是不是就有點(diǎn)懵了?

其實(shí)不難，排序其實(shí)就是對(duì)數(shù)據(jù)比較大小，我們用下面三行數(shù)據(jù)來(lái)模擬一下：

 

3322c 
7455g 
4311d 

首先，按照b desc, c desc, d desc來(lái)排序，得到結(jié)果如下：

 

7455g 
3322c 
4311d 

按照b desc, c asc, d desc來(lái)排序，得到結(jié)果如下：

 

7455g 
4311d 
3322c 

可能一部分大佬已經(jīng)能理解，實(shí)際上b desc所表達(dá)的意思就是b字段數(shù)據(jù)大者在上，數(shù)據(jù)小者在下，數(shù)據(jù)相等的話(huà)則開(kāi)始比較c字段，而c字段是按升序排的，也就是c字段數(shù)據(jù)小者在下，數(shù)據(jù)大者在上。所以就得到了上面的結(jié)果。

這就是降序索引。

總結(jié)

實(shí)際上升序索引和降序索引是不同的排序方式而已，Mysql8中正在實(shí)現(xiàn)了降序索引后，我們?cè)趧?chuàng)建索引時(shí)更加靈活，可以根據(jù)業(yè)務(wù)需要的排序規(guī)則來(lái)創(chuàng)建合適的索引，這樣能使你的查詢(xún)更快。

當(dāng)然本文只講了原理，大家一定要知道Mysql中排序利用的B+樹(shù)，而不是上面我舉例的那種很簡(jiǎn)單的方式，但是就算用B+樹(shù)原理也是一樣的，比較數(shù)據(jù)的大小而已。

還有一點(diǎn)，現(xiàn)在只有Innodb存儲(chǔ)引擎支持降序索引。