MySQL8中降序索引的示例分析

133次閱讀

共計 7703 個字符，預計需要花費 20 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 MySQL8 中降序索引的示例分析，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

前言

MySQL 8.0 終于支持降序索引了。其實，從語法上，MySQL 4 就支持了，但正如官方文檔所言，they are parsed but ignored，實際創建的還是升序索引。

無圖無真相，同一個建表語句，看看 MySQL 5.7 和 8.0 的區別。

create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

MySQL 5.7

mysql  show create table slowtech.t1\G
*************************** 1. row ***************************
 Table: t1
Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
row in set (0.00 sec)

雖然 c2 列指定了 desc，但在實際的建表語句中還是將其忽略了。再來看看 MySQL 8.0 的結果。

mysql  show create table slowtech.t1\G
*************************** 1. row ***************************
 Table: t1
Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
row in set (0.00 sec)

c2 列還是保留了 desc 子句。

降序索引的意義

如果一個查詢，需要對多個列進行排序，且順序要求不一致。在這種場景下，要想避免數據庫額外的排序 -“filesort”，只能使用降序索引。還是上面這張表，來看看有降序索引和沒有的區別。

MySQL 5.7

mysql  explain select * from slowtech.t1 order by c1,c2 desc;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
row in set, 1 warning (0.00 sec)

MySQL 8.0

mysql  explain select * from slowtech.t1 order by c1,c2 desc;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
row in set, 1 warning (0.00 sec)

兩者的對比可以看出，MySQL 8.0 因為降序索引的存在，避免了“filesort”。

這其實是降序索引的主要應用場景。如果只對單個列進行排序，降序索引的意義不是太大，無論是升序還是降序，升序索引完全可以應付。還是同樣的表，看看下面的查詢。

MySQL 5.7

mysql  explain select * from slowtech.t1 order by c1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
row in set, 1 warning (0.00 sec)
mysql  explain select * from slowtech.t1 order by c1 desc;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
row in set, 1 warning (0.00 sec)

雖然 c1 是升序索引，但在第二個查詢中，對其進行降序排列時，并沒有進行額外的排序，使用的還是索引。在這里，大家容易產生誤區，以為升序索引就不能用于降序排列，實際上，對于索引，MySQL 不僅支持正向掃描，還可以反向掃描。反向掃描的性能同樣不差。以下是官方對于降序索引的壓測結果，測試表也只有兩列（a，b），建了一個聯合索引 (a desc,b asc)，感興趣的童鞋可以看看，http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

而在 8.0 中，對于反向掃描，有一個專門的詞進行描述“Backward index scan”。

mysql  explain select * from slowtech.t1 order by c1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
row in set, 1 warning (0.00 sec)
mysql  explain select * from slowtech.t1 order by c1 desc;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
row in set, 1 warning (0.00 sec)

終于不再對 group by 進行隱式排序

由于降序索引的引入，MySQL 8.0 再也不會對 group by 操作進行隱式排序。

下面看看 MySQL 5.7 和 8 中的測試情況

create table slowtech.t1(id int);
insert into slowtech.t1 values(2);
insert into slowtech.t1 values(3);
insert into slowtech.t1 values(1);

MySQL 5.7

mysql  select * from slowtech.t1 group by id;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
rows in set (0.00 sec)
mysql  explain select * from slowtech.t1 group by id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
row in set, 1 warning (0.00 sec)

“Using filesort”，代表查詢中有排序操作，從結果上看，id 列確實也是升序輸出。

MySQL 8.0

mysql  select * from slowtech.t1 group by id;
+------+
| id |
+------+
| 2 |
| 3 |
| 1 |
+------+
rows in set (0.00 sec)
mysql  explain select * from slowtech.t1 group by id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
row in set, 1 warning (0.01 sec)

不僅結果沒有升序輸出，執行計劃中也沒有“Using filesort”。

可見，MySQL 8.0 對于 group by 操作確實不再進行隱式排序。

從 5.7 升級到 8.0，依賴 group by 隱式排序的業務可要小心咯。

關于“MySQL8 中降序索引的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。

正文完