MySQL8.0 降序索引

MySQL8.0 降序索引

Mysql8.0 开始真正支持降序索引

• 只有 InnoDB 存储引擎支持降序索引，只支持BTREE 降序索引

• MySQL8.0 不再对 GROUP BY 操作 进行隐式排序

数据准备

-- 创建一个表， c1 字段升序索引， c2 降序索引
-- Create Table: 
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


-- 插入测试数据
insert into t2(c1,c2) values(1,100), (2,200),(3,150),(4,50),(5,300);

再来查询一下 ，根据建立的索引，进行查询 是可以使用到索引的

mysql> explain select * from t2 order by c1,c2 desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_c1_c2
      key_len: 10
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

并且可以反向查找，此时使用的是 反向索引扫描。

mysql> explain select * from t2 order by c1 desc,c2 asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_c1_c2
      key_len: 10
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Backward index scan; Using index
1 row in set, 1 warning (0.00 sec)

此时 也可以使用到索引， 此时叫 Backward index scan 反向扫描

group by 聚合后，不在对数据排序

在MySQL8.0 后， group by 聚合后， 不在对数据排序， 如果需要排序，需要显示 指定 排序字段 order by column

group by 后 不再默认排序数据

mysql> select count(*) , c2 from t2 group by c2;
+----------+------+
| count(*) | c2   |
+----------+------+
|        1 |  100 |
|        1 |  200 |
|        1 |  150 |
|        1 |   50 |
|        1 |  300 |
+----------+------+
5 rows in set (0.01 sec)

手动指定字段排序

mysql> select count(*) , c2 from t2 group by c2 order by c2 desc;
+----------+------+
| count(*) | c2   |
+----------+------+
|        1 |  300 |
|        1 |  200 |
|        1 |  150 |
|        1 |  100 |
|        1 |   50 |
+----------+------+
5 rows in set (0.00 sec)

降序索引使用条件以及限制

• 只有InnoDB 存储引擎支持降序索引

• 能够使用 升序索引的列， 一样支持降序索引

• 普通列 和自动生成的列 都是可以支持降序索引的

• HASH, FULLTEXT, and SPATIAL indexes 这些索引不支持 降序的索引的，如果使用ASC and DESC 将会出现错误

• BTREE支持降序索引，但不支持HASH索引。对于 "FULLTEXT "或 "SPATIAL "索引不支持递减索引。

• 降序索引 仅适用于 InnoDB 存储引擎

总结

降序索引最大的应用场景便是如上例这样对多字段排序的场景，这种场景下，能够最大的发挥降序索引的作用。在对单字段排序时，无论是升序还是降序都是可以用到索引的。因为数据库不仅可以对索引进行从前向后的扫描，也可以对索引进行从后向前的扫描。

降序索引的引入，使得在查询中，需要对多字段进行排序，且顺序要求不一致的场景，能够极大的提升查询的效率。在工作中，我们还是要贴合业务的需求，合理的运用索引，这样写出的SQL执行起来才能事半功倍。

参考文档

descending-indexes

mysql-8-0-labs-descending-indexes-in-mysql

MySQL 8.0新特性：降序索引

分享快乐,留住感动. '2022-10-09 20:39:32' --frank