MySQL 降序索引

---

简介：在本教程中，您将了解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)
);

当您使用SHOW CREATE TABLE在MySQL 5.7，你会发现，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)

从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)

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)
);

其次，使用下面的存储过程来插入行到t表：

DELIMITER $$

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$$  

DELIMITER ;

存储的过程中插入的行数（rowCount）与之间的值low和high到a和b所述列t表。 让我们10,000在t表中插入行，其中随机值介于1和1000之间：

CALL insertSampleData(10000,1,1000);

第三，从t表中查询具有不同排序顺序的数据： 按升序排列a和b列中的值：

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b; -- use index a_asc_b_asc

这是输出：

+----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | a_asc_b_asc | 10      | NULL | 10192 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.03 sec)

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

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b DESC; -- use index a_asc_b_desc

输出是：

+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | a_asc_b_desc | 10      | NULL | 10192 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

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

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b; -- use index a_desc_b_asc

以下说明输出：

+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | a_desc_b_asc | 10      | NULL | 10192 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.42 sec)

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

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b DESC; -- use index a_desc_b_desc

以下显示输出：

+----+-------------+-------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | a_desc_b_desc | 10      | NULL | 10192 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

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

---