日韩欧美国产高清,99久久国产综合色|国产精品,成年无码av片在线

主頁 > 知識(shí)庫 > MySQL8新特性：降序索引詳解

MySQL8新特性：降序索引詳解

前言

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

無圖無真相，同一個(gè)建表語句，看看MySQL 5.7和8.0的區(qū)別。

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，但在實(shí)際的建表語句中還是將其忽略了。再來看看MySQL 8.0的結(jié)果。

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子句。

降序索引的意義

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

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)

兩者的對(duì)比可以看出，MySQL 8.0因?yàn)榻敌蛩饕拇嬖冢苊饬恕癴ilesort”。

這其實(shí)是降序索引的主要應(yīng)用場(chǎng)景。如果只對(duì)單個(gè)列進(jìn)行排序，降序索引的意義不是太大，無論是升序還是降序，升序索引完全可以應(yīng)付。還是同樣的表，看看下面的查詢。

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

而在8.0中，對(duì)于反向掃描，有一個(gè)專門的詞進(jìn)行描述“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)

終于不再對(duì)group by進(jìn)行隱式排序

由于降序索引的引入，MySQL 8.0再也不會(huì)對(duì)group by操作進(jìn)行隱式排序。

下面看看MySQL 5.7和8中的測(cè)試情況

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”，代表查詢中有排序操作，從結(jié)果上看，id列確實(shí)也是升序輸出。

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)

不僅結(jié)果沒有升序輸出，執(zhí)行計(jì)劃中也沒有“Using filesort”。

可見，MySQL 8.0對(duì)于group by操作確實(shí)不再進(jìn)行隱式排序。

從5.7升級(jí)到8.0，依賴group by隱式排序的業(yè)務(wù)可要小心咯。

參考文檔

http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:

MySQL 8.0新特性之隱藏字段的深入講解
MySQL8.0中的降序索引
MySQL8新特性之降序索引底層實(shí)現(xiàn)詳解
MySQL 8中新增的這三大索引隱藏、降序、函數(shù)

標(biāo)簽：揚(yáng)州南陽貴州三門峽新余商丘巴中贛州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL8新特性：降序索引詳解》，本文關(guān)鍵詞 MySQL8,新特性,新,特性,降序,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。