色综合图-色综合图片-色综合图片二区150p-色综合图区-玖玖国产精品视频-玖玖香蕉视频

您的位置:首頁技術文章
文章詳情頁

MySQL8.0中的降序索引

瀏覽:32日期:2023-10-10 07:32:37

前言

相信大家都知道,索引是有序的;不過,在MySQL之前版本中,只支持升序索引,不支持降序索引,這會帶來一些問題;在最新的MySQL 8.0版本中,終于引入了降序索引,接下來我們就來看一看。

降序索引

單列索引

(1)查看測試表結構

mysql> show create table sbtest1G*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `k` int unsigned NOT NULL DEFAULT ’0’, `c` char(120) NOT NULL DEFAULT ’’, `pad` char(60) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`), KEY `k_1` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=10000001 row in set (0.00 sec)

(2)執行SQL語句order by ... limit n,默認是升序,可以使用到索引

mysql> explain select * from sbtest1 order by k limit 10;+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 10 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

(3)執行SQL語句order by ... desc limit n,如果是降序的話,無法使用索引,雖然可以相反順序掃描,但性能會受到影響

mysql> explain select * from sbtest1 order by k desc limit 10;+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra|+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+| 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 10 | 100.00 | Backward index scan |+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+1 row in set, 1 warning (0.00 sec)

(4)創建降序索引

mysql> alter table sbtest1 add index k_2(k desc);Query OK, 0 rows affected (6.45 sec)Records: 0 Duplicates: 0 Warnings: 0

(5)再次執行SQL語句order by ... desc limit n,可以使用到降序索引

mysql> explain select * from sbtest1 order by k desc limit 10;+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_2 | 4 | NULL | 10 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

多列索引

(1)查看測試表結構

mysql> show create table sbtest1G*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `k` int unsigned NOT NULL DEFAULT ’0’, `c` char(120) NOT NULL DEFAULT ’’, `pad` char(60) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `idx_c_pad_1` (`c`,`pad`)) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=10000001 row in set (0.00 sec)

(2)對于多列索引來說,如果沒有降序索引的話,那么只有SQL 1才能用到索引,SQL 4能用相反順序掃描,其他兩條SQL語句只能走全表掃描,效率非常低

SQL 1:select * from sbtest1 order by c,pad limit 10;

SQL 2:select * from sbtest1 order by c,pad desc limit 10;

SQL 3:select * from sbtest1 order by c desc,pad limit 10;

SQL 4:explain select * from sbtest1 order by c desc,pad desc limit 10;

mysql> explain select * from sbtest1 order by c,pad limit 10;+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_1 | 720 | NULL | 10 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from sbtest1 order by c,pad desc limit 10;+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 950738 | 100.00 | Using filesort |+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from sbtest1 order by c desc,pad limit 10;+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 950738 | 100.00 | Using filesort |+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+1 row in set, 1 warning (0.01 sec)mysql> explain select * from sbtest1 order by c desc,pad desc limit 10;+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra|+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_1 | 720 | NULL | 10 | 100.00 | Backward index scan |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+1 row in set, 1 warning (0.00 sec)

(3)創建相應的降序索引

mysql> alter table sbtest1 add index idx_c_pad_2(c,pad desc);Query OK, 0 rows affected (1 min 11.27 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table sbtest1 add index idx_c_pad_3(c desc,pad);Query OK, 0 rows affected (1 min 14.22 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table sbtest1 add index idx_c_pad_4(c desc,pad desc);Query OK, 0 rows affected (1 min 8.70 sec)Records: 0 Duplicates: 0 Warnings: 0

(4)再次執行SQL,均能使用到降序索引,效率大大提升

mysql> explain select * from sbtest1 order by c,pad desc limit 10;+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_2 | 720 | NULL | 10 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from sbtest1 order by c desc,pad limit 10;+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_3 | 720 | NULL | 10 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from sbtest1 order by c desc,pad desc limit 10;+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_4 | 720 | NULL | 10 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

總結

MySQL 8.0引入的降序索引,最重要的作用是,解決了多列排序可能無法使用索引的問題,從而可以覆蓋更多的應用場景。

以上就是MySQL8.0中的降序索引的詳細內容,更多關于MySQL 降序索引的資料請關注好吧啦網其它相關文章!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 一区二区三区日韩精品 | 亚洲最新网址 | 九九欧美 | 国内三级视频 | 日韩免费一级a毛片在线播放一级 | 依依成人综合网 | 中文一级毛片 | 日韩国产欧美精品综合二区 | 台湾三级毛片 | 黄色片日本人 | a级国产乱理伦片在线观看国 | 手机看片国产欧美日韩高清 | 92精品国产成人观看免费 | 亚洲第一页乱 | 日韩欧美第一页 | 丝袜美腿精品一区二区三 | www.av视频在线观看 | 欧美激情国产一区在线不卡 | 欧洲女同互慰在线视频 | 久久精品国产国产精品四凭 | 91福利国产在线观一区二区 | 免费观看欧美一区二区三区 | 国产91久久久久久久免费 | 色婷婷久久综合中文久久蜜桃 | 看一级特黄a大片国产 | 亚洲第一网站 | 黄色美女网站免费看 | 国产成人在线免费观看 | 国产亚洲福利一区二区免费看 | 国产一区亚洲 | 看全色黄大色黄大片女图片 | 国产成人欧美一区二区三区的 | 欧美人与zoxxxx另类9 | 亚洲日本va午夜中文字幕一区 | 亚洲国产日韩综合久久精品 | 亚洲黄色三级视频 | 久爱午夜精品免费视频 | 大狠狠大臿蕉香蕉大视频 | 亚洲精品综合一区二区 | 亚洲毛片免费看 | 欧美最大成人毛片视频网站 |