文章目录
- MySQL8.0 降序索引
- 数据准备
- group by 聚合后,不在对数据排序
- 降序索引使用条件以及限制
- 总结
- 参考文档
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
, andSPATIAL
indexes 这些索引不支持 降序的索引的,如果使用ASC
andDESC
将会出现错误BTREE
支持降序索引,但不支持HASH
索引。对于 "FULLTEXT "或 "SPATIAL "索引不支持递减索引。- 降序索引 仅适用于 InnoDB 存储引擎
总结
降序索引最大的应用场景便是如上例这样对多字段排序的场景,这种场景下,能够最大的发挥降序索引的作用。在对单字段排序时,无论是升序还是降序都是可以用到索引的。因为数据库不仅可以对索引进行从前向后的扫描,也可以对索引进行从后向前的扫描。
降序索引的引入,使得在查询中,需要对多字段进行排序,且顺序要求不一致的场景,能够极大的提升查询的效率。在工作中,我们还是要贴合业务的需求,合理的运用索引,这样写出的SQL执行起来才能事半功倍。
参考文档
mysql-8-0-labs-descending-indexes-in-mysql