MySQL降序索引

时间:2019-11-20 08:52:15  来源:igfitidea点击:

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

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

从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表中:

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

该存储过程在t表的a和b列中插入了一些行(rowCount),其值在低和高之间。

让我们在t表中插入10,000行,其随机值介于1到1000之间:

CALL insertSampleData(10000,1,1000);

第三,以不同的排序顺序从t表中查询数据:

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

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

这是输出:

按升序对a列中的值进行排序,按降序对b列中的值进行排序:

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

输出为:

按降序对a列中的值进行排序,按升序对b列中的值进行排序:

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

以下说明了输出:

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

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

以下显示输出:

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