MySQL8新特性：降序索引詳解
發(fā)布日期：2022-03-22 08:57 | 文章來源：腳本之家
MySQL 8.0終于支持降序索引了。其實，從語法上，MySQL 4就支持了，但正如官方文檔所言，"they are parsed but ignored"，實際創(chuàng)建的還是升序索引。
無圖無真相，同一個建表語句，看看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，但在實際的建表語句中還是將其忽略了。再來看看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子句。
降序索引的意義
如果一個查詢，需要對多個列進行排序，且順序要求不一致。在這種場景下，要想避免數(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)
兩者的對比可以看出，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是升序索引，但在第二個查詢中，對其進行降序排列時，并沒有進行額外的排序，使用的還是索引。在這里，大家容易產(chǎn)生誤區(qū)，以為升序索引就不能用于降序排列，實際上，對于索引，MySQL不僅支持正向掃描，還可以反向掃描。反向掃描的性能同樣不差。以下是官方對于降序索引的壓測結(jié)果，測試表也只有兩列（a，b），建了一個聯(lián)合索引(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”，代表查詢中有排序操作，從結(jié)果上看，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)
不僅結(jié)果沒有升序輸出，執(zhí)行計劃中也沒有“Using filesort”。
可見，MySQL 8.0對于group by操作確實不再進行隱式排序。
從5.7升級到8.0，依賴group by隱式排序的業(yè)務可要小心咯。
參考文檔
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對本站的支持。
版權(quán)聲明：本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信