您好,登錄后才能下訂單哦!
這篇文章將為大家詳細(xì)講解有關(guān)MySQL8中降序索引的示例分析,小編覺(jué)得挺實(shí)用的,因此分享給大家做個(gè)參考,希望大家閱讀完這篇文章后可以有所收獲。
前言
MySQL 8.0終于支持降序索引了。其實(shí),從語(yǔ)法上,MySQL 4就支持了,但正如官方文檔所言,"they are parsed but ignored",實(shí)際創(chuàng)建的還是升序索引。
無(wú)圖無(wú)真相,同一個(gè)建表語(yǔ)句,看看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í)際的建表語(yǔ)句中還是將其忽略了。再來(lái)看看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ù)庫(kù)額外的排序-“filesort”,只能使用降序索引。還是上面這張表,來(lái)看看有降序索引和沒(méi)有的區(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)行排序,降序索引的意義不是太大,無(wú)論是升序還是降序,升序索引完全可以應(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í),并沒(méi)有進(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é)果沒(méi)有升序輸出,執(zhí)行計(jì)劃中也沒(méi)有“Using filesort”。
可見(jiàn),MySQL 8.0對(duì)于group by操作確實(shí)不再進(jìn)行隱式排序。
從5.7升級(jí)到8.0,依賴group by隱式排序的業(yè)務(wù)可要小心咯。
關(guān)于“MySQL8中降序索引的示例分析”這篇文章就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,使各位可以學(xué)到更多知識(shí),如果覺(jué)得文章不錯(cuò),請(qǐng)把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。