MySQL Descending Index

摘要：在本教程中，您将了解 MySQL 降序索引以及如何利用它来提高查询性能。

MySQL降序索引简介

降序索引是按降序存储键值的索引。在 MySQL 8.0 之前，您可以在索引定义中指定DESC 。然而，MySQL 忽略了它。同时，MySQL可以逆序扫描索引，但成本很高。

以下语句创建一个带有索引的新表：

CREATE TABLE t(
    a INT NOT NULL,
    b INT NOT NULL,
    INDEX a_asc_b_desc (a ASC, b DESC)
);
Code language: SQL (Structured Query Language) (sql)

当你在MySQL 5.7中使用SHOW CREATE TABLE时，你会发现DESC被忽略，如下所示：

mysql> SHOW CREATE TABLE t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `a_asc_b_desc` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

从MySQL 8.0开始，如果在索引定义中使用DESC关键字，键值将按降序存储。当查询中请求降序排列时，查询优化器可以利用降序索引。

MySQL 8.0的表结构如下：

mysql> SHOW CREATE TABLE t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `a_asc_b_desc` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

MySQL 降序索引示例

首先，重新创建具有不同顺序的四个索引的t表：

DROP TABLE t;

CREATE TABLE t (
    a INT,
    b INT,
    INDEX a_asc_b_asc (a ASC , b ASC),
    INDEX a_asc_b_desc (a ASC , b DESC),
    INDEX a_desc_b_asc (a DESC , b ASC),
    INDEX a_desc_b_desc (a DESC , b DESC)
);
Code language: SQL (Structured Query Language) (sql)

其次，使用以下存储过程将行插入到t表中：

CREATE PROCEDURE insertSampleData(
    IN rowCount INT, 
    IN low INT, 
    IN high INT
)
BEGIN
    DECLARE counter INT DEFAULT 0;
    REPEAT
        SET counter := counter + 1;
        -- insert data
        INSERT INTO t(a,b)
        VALUES(
            ROUND((RAND() * (high-low))+high),
            ROUND((RAND() * (high-low))+high)
        );
    UNTIL counter >= rowCount
    END REPEAT;
END$$    
Code language: SQL (Structured Query Language) (sql)

该存储过程将值介于low和high之间的多行 ( rowCount ) 插入到t表的a和b列中。

让我们向t表中插入10,000行，随机值在 1 到 1000 之间：

CALL insertSampleData(10000,1,1000);
Code language: SQL (Structured Query Language) (sql)

三、从t表中查询不同排序顺序的数据：

按升序对 a 列和 b 列中的值进行排序：

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b; -- use index a_asc_b_asc
Code language: SQL (Structured Query Language) (sql)

这是输出：

对a列中的值按升序排序，对b列中的值按降序排序：

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b DESC; -- use index a_asc_b_desc
Code language: SQL (Structured Query Language) (sql)

输出是：

将a列中的值按降序排序，将b列中的值按升序排序：

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b; -- use index a_desc_b_asc
Code language: SQL (Structured Query Language) (sql)

下图说明了输出：

按降序对a列和b列中的值进行排序：

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b DESC; -- use index a_desc_b_desc
Code language: SQL (Structured Query Language) (sql)

下面显示了输出：

在本教程中，您学习了如何使用MySQL降序索引来提高查询性能。