mysql按升序创建索引_MySQL 降序索引

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降序索引来提高查询性能。

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页