什么是降序索引

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

我們通常使用下面的語句來創(chuàng)建一個索引：

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

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

但是大家不知道的是，上面這個sql實際上和下面的這個sql是等價的：

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

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

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

當然是可以的，比如下面三個語句：

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);

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

我們分別使用Mysql7、Mysql8兩個版本來舉例子說明一下：

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

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

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


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

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


show index from t1;

Mysql7中你將得到結果：

Mysql8中你將得到結果：

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

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

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

到此為止，大家應該對升序索引和降序索引有了一個大概的了解，但并沒有真正理解，因為大家并不知道升序索引與降序索引底層到底是如何實現(xiàn)的。

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

我們知道，索引是用來提高查詢速度的，但是為什么索引能提高查詢速度呢？

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

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

所以，對Mysql表中的存儲的數(shù)據(jù)也是一樣的，我們如果想提高這個表的查詢速度，我們可以先對這個表里的數(shù)據(jù)進行排序，那么表里的某一行數(shù)據(jù)包括了很多字段，我們現(xiàn)在想對這些數(shù)據(jù)行進行排序，我們應該根據(jù)哪些字段來確定這個順序呢？這就是索引，在創(chuàng)建索引的時候你所指定的列就是用來對表里的數(shù)據(jù)行進行排序的。

比如我們仍然利用上面所創(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ù)肯定是存儲在文件中的，所以文件中保存這些數(shù)據(jù)的格式大概如下，順序與插入順序保持一致：

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

注意，t1是Innodb的存儲引擎，而且a字段是主鍵，所以Innodb存儲引擎在處理這些插入的數(shù)據(jù)時，會按主鍵進行排序，也就是上面我說的文件中保存這些數(shù)據(jù)的格式是不準確的，因為不想篇幅太長，所以不去深究，感興趣的同學可以關注一波公眾號：1點25，我會專門寫一篇文章來講解Innodb中索引的具體實現(xiàn)，包括B+樹到底是如何生成的。

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

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

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

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

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

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

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

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

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

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

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

而且如果我們的order by是order by b desc, c desc, d desc，同樣可以利用b,c,d的升序索引，因為如果是order by b asc,c asc,d asc就從上往下遍歷即可，如果是order by b desc, c desc, d desc就從下往上遍歷即可。

那么，如果是order by b asc, c desc, d desc呢？這個order by是不是就沒有辦法利用b,c,d的升序索引了。

這個時候就需要降序索引了。

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

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

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

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

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

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

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

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

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

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

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

3322c
7455g
4311d

首先，按照b desc, c desc, d desc來排序，得到結果如下：

7455g
3322c
4311d

按照b desc, c asc, d desc來排序，得到結果如下：

7455g
4311d
3322c

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

這就是降序索引。

總結

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

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

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

到此這篇關于MySQL8新特性之降序索引底層實現(xiàn)詳解的文章就介紹到這了,更多相關MySQL8 降序索引內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論