MySQL8.0中的降序索引
更新時間：2020年10月30日 11:32:01 作者：brightdeng@DBA
這篇文章主要介紹了MySQL8.0中的降序索引的相關知識，幫助大家更好的理解和使用MySQL8.0的新特性，感興趣的朋友可以了解下
前言
相信大家都知道，索引是有序的；不過，在MySQL之前版本中，只支持升序索引，不支持降序索引，這會帶來一些問題；在最新的MySQL 8.0版本中，終于引入了降序索引，接下來我們就來看一看。
降序索引
單列索引
（1）查看測試表結構
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create 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=1000000
1 row in set (0.00 sec)
（2）執(zhí)行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）執(zhí)行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）創(chuàng)建降序索引
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）再次執(zhí)行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 sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create 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=1000000
1 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）創(chuàng)建相應的降序索引
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: 0 mysql> 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: 0 mysql> 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）再次執(zhí)行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中的降序索引的詳細內(nèi)容，更多關于MySQL 降序索引的資料請關注腳本之家其它相關文章！
相關文章
本篇文章是對SQL SERVER 日期格式轉換進行了詳細的分析介紹，需要的朋友參考下2013-06-06
mysql中inner join和left join使用詳解
本文主要介紹了mysql中inner join和left join使用詳解，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-03-03
mybatis將本地的數(shù)據(jù)傳入到mysql數(shù)據(jù)庫服務器的時候,服務器會對數(shù)據(jù)進行檢測,會把date類型的數(shù)據(jù)自動轉換為mysql服務器所對應的時區(qū),即0時區(qū),所以會相差8小時,本文給大家介紹了MySQL時區(qū)差8小時的問題解決方法,需要的朋友可以參考下2024-01-01
淺談MySQL數(shù)據(jù)庫中日期中包含零值的問題
下面小編就為大家?guī)硪黄獪\談MySQL數(shù)據(jù)庫中日期中包含零值的問題。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
MySQL數(shù)據(jù)庫執(zhí)行效率對程序的執(zhí)行速度有很大的影響，有效的處理優(yōu)化數(shù)據(jù)庫是非常有用的。尤其是大量數(shù)據(jù)需要處理的時候2018-03-03
mysql遇到load data導入文件數(shù)據(jù)出現(xiàn)1290錯誤的解決方案
這篇文章主要介紹了mysql遇到load data導入文件數(shù)據(jù)出現(xiàn)1290錯誤的解決方案,非常的簡單實用，有需要的小伙伴可以參考下2018-07-07
mysql8.0.20安裝與連接navicat的方法及注意事項
這篇文章主要介紹了mysql8.0.20安裝與連接navicat的方法及注意事項，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-05-05
這篇文章主要幫助大家深入理解MySQL變量和條件，感興趣的小伙伴們可以參考一下2016-03-03
這篇文章主要介紹了Mysql中的NULL和Empty String知識,需要的朋友可以參考下2017-12-12
本文主要介紹了MySQL常用登錄命令小結，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-05-05