• 售前

  • 售后

热门帖子
入门百科

MySQL8新特性:降序索引详解

[复制链接]
二级传播盅 显示全部楼层 发表于 2021-10-26 13:49:36 |阅读模式 打印 上一主题 下一主题
前言
MySQL 8.0终于支持降序索引了。其实,从语法上,MySQL 4就支持了,但正如官方文档所言,"they are parsed but ignored",现实创建的照旧升序索引。

无图无原形,同一个建表语句,看看MySQL 5.7和8.0的区别。
  1. create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
复制代码
MySQL 5.7
  1. mysql> show create table slowtech.t1\G
  2. *************************** 1. row ***************************
  3. Table: t1
  4. Create Table: CREATE TABLE `t1` (
  5. `c1` int(11) DEFAULT NULL,
  6. `c2` int(11) DEFAULT NULL,
  7. KEY `idx_c1_c2` (`c1`,`c2`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  9. row in set (0.00 sec)
复制代码
虽然c2列指定了desc,但在现实的建表语句中照旧将其忽略了。再来看看MySQL 8.0的结果。
  1. mysql> show create table slowtech.t1\G
  2. *************************** 1. row ***************************
  3. Table: t1
  4. Create Table: CREATE TABLE `t1` (
  5. `c1` int(11) DEFAULT NULL,
  6. `c2` int(11) DEFAULT NULL,
  7. KEY `idx_c1_c2` (`c1`,`c2` DESC)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  9. row in set (0.00 sec)
复制代码
c2列照旧保存了desc子句。
降序索引的意义

假如一个查询,须要对多个列举行排序,且序次要求不一致。在这种场景下,要想克制数据库额外的排序-“filesort”,只能使用降序索引。照旧上面这张表,来看看有降序索引和没有的区别。

MySQL 5.7
  1. mysql> explain select * from slowtech.t1 order by c1,c2 desc;
  2. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |
  4. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
  5. | 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |
  6. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
  7. row in set, 1 warning (0.00 sec)
复制代码
MySQL 8.0
  1. mysql> explain select * from slowtech.t1 order by c1,c2 desc;
  2. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  5. | 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
  6. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  7. row in set, 1 warning (0.00 sec)
复制代码
两者的对比可以看出,MySQL 8.0由于降序索引的存在,克制了“filesort”。

这其实是降序索引的重要应用场景。假如只对单个列举行排序,降序索引的意义不是太大,无论是升序照旧降序,升序索引完全可以应付。照旧同样的表,看看下面的查询。

MySQL 5.7
  1. mysql> explain select * from slowtech.t1 order by c1;
  2. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  5. | 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
  6. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  7. row in set, 1 warning (0.00 sec)
  8. mysql> explain select * from slowtech.t1 order by c1 desc;
  9. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  10. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  11. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  12. | 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
  13. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  14. row in set, 1 warning (0.00 sec)
复制代码
虽然c1是升序索引,但在第二个查询中,对其举行降序排列时,并没有举行额外的排序,使用的照旧索引。在这里,各人轻易产生误区,以为升序索引就不能用于降序排列,现实上,对于索引,MySQL不但支持正向扫描,还可以反向扫描。反向扫描的性能同样不差。以下是官方对于降序索引的压测结果,测试表也只有两列(a,b),建了一个团结索引(a desc,b asc),感爱好的童鞋可以看看,http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

而在8.0中,对于反向扫描,有一个专门的词举行形貌“Backward index scan”。
  1. mysql> explain select * from slowtech.t1 order by c1;
  2. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  5. | 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
  6. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  7. row in set, 1 warning (0.00 sec)
  8. mysql> explain select * from slowtech.t1 order by c1 desc;
  9. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
  10. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
  11. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
  12. | 1 | SIMPLE | t1 | NULL | index | NULL  | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Backward index scan; Using index |
  13. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
  14. row in set, 1 warning (0.00 sec)
复制代码
终于不再对group by举行隐式排序
由于降序索引的引入,MySQL 8.0再也不会对group by操纵举行隐式排序。

下面看看MySQL 5.7和8中的测试环境   
  1. create table slowtech.t1(id int);
  2. insert into slowtech.t1 values(2);
  3. insert into slowtech.t1 values(3);
  4. insert into slowtech.t1 values(1);
复制代码
MySQL 5.7
  1. mysql> select * from slowtech.t1 group by id;
  2. +------+
  3. | id |
  4. +------+
  5. | 1 |
  6. | 2 |
  7. | 3 |
  8. +------+
  9. rows in set (0.00 sec)
  10. mysql> explain select * from slowtech.t1 group by id;
  11. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
  12. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra       |
  13. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
  14. | 1 | SIMPLE  | t1 | NULL  | ALL | NULL   | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |
  15. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
  16. row in set, 1 warning (0.00 sec)
复制代码
“Using filesort”,代表查询中有排序操纵,从结果上看,id列确实也是升序输出。
MySQL 8.0
  1. mysql> select * from slowtech.t1 group by id;
  2. +------+
  3. | id |
  4. +------+
  5. | 2 |
  6. | 3 |
  7. | 1 |
  8. +------+
  9. rows in set (0.00 sec)
  10. mysql> explain select * from slowtech.t1 group by id;
  11. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  12. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |
  13. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  14. | 1 | SIMPLE  | t1 | NULL  | ALL | NULL   | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
  15. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
  16. row in set, 1 warning (0.01 sec)
复制代码
不但结果没有升序输出,执行操持中也没有“Using filesort”。
可见,MySQL 8.0对于group by操纵确实不再举行隐式排序。
从5.7升级到8.0,依靠group by隐式排序的业务可要小心咯。

参考文档

http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/
总结
以上就是这篇文章的全部内容了,渴望本文的内容对各人的学习大概工作具有肯定的参考学习代价,假如有疑问各人可以留言交换,谢谢各人对草根技术分享的支持。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作