欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

MySQL8.0新特性之降序索引

程序员文章站 2022-09-05 14:51:21
文章目录###简介MySQL从8.0开始终于支持真正的降序索引了，实际上，在以前的版本当中，语法上可以通过desc来指定索引为降序，但实际上创建的仍然是常规的升序索引。如代码中所示，在MySQL5.7中，虽然通过desc关键字指定了索引中的c2字段为降序，但通过show create table语句查看索引并没有真正的降序，c2字段仍然为升序。mysql> select version();+------------+| version() |+------------+| 5.7.2...

MySQL8.0新特性之降序索引

简介

MySQL从8.0开始终于支持真正的降序索引了，实际上，在以前的版本当中，语法上可以通过desc来指定索引为降序，但实际上创建的仍然是常规的升序索引。
如代码中所示，在MySQL5.7中，虽然通过desc关键字指定了索引中的c2字段为降序，但通过show create table语句查看索引并没有真正的降序，c2字段仍然为升序。

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.05 sec)
mysql> CREATE TABLE `t1` (`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,KEY `idx_c1_c2` (`c1`,`c2` desc));
Query OK, 0 rows affected (0.20 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+------------------------------------------------------------------------------------------------
1 row in set (0.06 sec)

但在MySQL8.0中却真正的实现了这一点,如代码中所示，通过show create table语句查看，索引的顺序与我们在建表语句中指定的顺序是一致的，c1字段升序，c2字段降序。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `t1` (`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,KEY `idx_c1_c2` (`c1`,`c2` desc));
Query OK, 0 rows affected (0.63 sec)
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------
| Table | Create Table                                                                  
+-------+-------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------
1 row in set (0.04 sec)

降序索引在执行计划中的表现

环境信息

  • MySQL版本：5.7.22和8.0.15
  • 操作系统版本：Centos6.6
    使用如下存储过程在t1表插入1000万条随机数据，
use test
set sql_log_bin=off;
drop procedure if exists test_insert;
delimiter ;;
create procedure test_insert()
begin
declare i int default 0;
while i<10000000
do
insert into t1 select rand()*10000000,rand()*10000000;
set i=i+1;
end while;
commit;
end;;
delimiter ;
call test_insert();

查看select * from t1 order by c1 , c2 desc limit 10;的执行计划。从执行计划上可以看出，5.7的扫描行数数9364304远远大于8.0的10行，并且使用了filesort。

#MySQL5.7
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (4.40 sec）

mysql> explain select * from t1 order by c1 , c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 9364304 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.08 sec)
#MySQL8.0
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.14 sec)

mysql> explain select * from t1 order by c1 , c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

降序索引最大的应用场景便是如上例这样对多字段排序的场景，这种场景下，能够最大的发挥降序索引的作用。在对单字段排序时，无论是升序还是降序都是可以用到索引的。因为数据库不仅可以对索引进行从前向后的扫描，也可以对索引进行从后向前的扫描。
如图所示，在一条对t1表的查询语句中只对c1字段进行降序排序，无论是MySQL5.7还是MySQL8.0都用到了索引，在8.0中，对于索引的反向扫描，有一个专门的词进行描述“Backward index scan”。

#MySQL5.7
mysql> explain select * from t1 order by c1 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#MySQL8.0
mysql> explain select * from t1 order by c1 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

虽然在对多字段进行排序时，能够最大发会降序索引的作用，但也尤其要注重使用的场景，否则仍然可能适得其反。例如对于如下两条查询语句，降序索引的表现就并没有那么乐观。从执行计划中可以看出，扫描的行数将近1000w，并且使用了filesort。

#MySQL8.0
mysql> explain select * from t1 order by c1,c2 limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 9742403 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 order by c1 desc,c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 9742403 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

但是在MySQL5.7中正常的升序索引对这两条语句的执行却有着上佳的表现，两条查询语句在MySQL5.7中均只扫描了10行。

#MySQL5.7
mysql> explain select * from t1 order by c1,c2 limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from t1 order by c1 desc,c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

为什么降序索引此时失去了它的魔力呢？因为我们执行的这两条查询语句对排序的两个字段要么全部升序，要么全部倒序。而在MySQL5.7中，由于组成联合索引的c1字段和c2字段都是升序排列的，那么在使用order by c1,c2排序时，MySQL可以对索引进行正向扫描，在使用by c1 desc,c2 desc，MySQL对索引进行完全的反向扫描即可。意识到降序索引在这两种排序场景下的缺陷。根据同样的道理，我们也能够发现降序索引在另一种场景下的优势。在文章中的第一个例子中，我们使用的排序顺序为order by c1 , c2 desc，这种场景下，MySQL对索引进行的是正向扫描，那么，让数据库对降序索引进行反向扫描，同样可以发挥降序索引的优势。如下代码所示，MySQL用到了索引，且扫描行数只有10行，并且使用了Backward index scan。

#MySQL8.0
mysql> explain select * from t1 order by c1 desc,c2 limit 10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 10 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

使用条件及限制

  1. 只有InnoDB存储引擎支持降序索引，且具有以下限制：
    • 如果索引包含一个降序索引列或主键包含降序索引列，则辅助索引无法用到change buffer。
    • InnoDB SQL解析器不使用降序索引。对于InnoDB全文搜索，这意味着索引表的FTS_DOC_ID列上所需的索引不能定义为降序索引。
  2. 适用于升序索引的所有数据类型也都支持降序索引。
  3. 普通（非生成）和生成列（VIRTUAL和STORED）都支持降序索引。
  4. DISTINCT可以使用任何一个索引包含的字段，包括索引中降序的字段。
  5. 对于调用了聚合函数但没有GROUP BY子句的这一类查询，索引的降序列不能用于优化MIN()/MAX()这样的聚合函数。
  6. BTREE支持降序索引，但HASH索引、FULLTEXT或SPATIAL索引不支持降序索引。

总结

降序索引的引入，使得在查询中，需要对多字段进行排序，且顺序要求不一致的场景，能够极大的提升查询的效率。但是需要注意的是，降序索引的引入，只是多提供给我们一种索引的使用方法，它并不能做到赢家通吃，无法适用所有的排序场景。因此，在工作中，我们还是要贴合业务的需求，合理的运用索引，这样写出的SQL执行起来才能事半功倍。

本文地址：https://blog.csdn.net/xsh5161/article/details/108995468

相关标签： mysql

上一篇： MYSQL 时间查询

下一篇： com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

推荐阅读