mysql8索引,MySQL8新特性：降序索引详解

最新推荐文章于 2024-07-09 08:49:23 发布
最新推荐文章于 2024-07-09 08:49:23 发布
阅读量205 收藏
点赞数
文章标签： mysql8索引
MySQL8.0开始真正支持降序索引，这优化了涉及多列排序的查询性能，避免了额外的“filesort”。文章通过对比MySQL5.7和8.0，展示了降序索引如何影响查询计划，以及在groupby操作中不再进行隐式排序。此外，降序索引对于单列排序也提供了反向扫描的高效性能。
摘要由CSDN通过智能技术生成

前言

MySQL 8.0终于支持降序索引了。其实，从语法上，MySQL 4就支持了，但正如官方文档所言，"they are parsed but ignored"，实际创建的还是升序索引。

无图无真相，同一个建表语句，看看MySQL 5.7和8.0的区别。

create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

MySQL 5.7

mysql> show create table slowtech.t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(11) DEFAULT NULL,

`c2` int(11) DEFAULT NULL,

KEY `idx_c1_c2` (`c1`,`c2`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

row in set (0.00 sec)

虽然c2列指定了desc，但在实际的建表语句中还是将其忽略了。再来看看MySQL 8.0的结果。

mysql> show create table slowtech.t1\G

*************************** 1. row ***************************

Table: t1

Create Table: 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

row in set (0.00 sec)

c2列还是保留了desc子句。

降序索引的意义

如果一个查询，需要对多个列进行排序，且顺序要求不一致。在这种场景下，要想避免数据库额外的排序-“filesort”，只能使用降序索引。还是上面这张表，来看看有降序索引和没有的区别。

MySQL 5.7

mysql> explain select * from slowtech.t1 order by c1,c2 desc;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+

| 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 | 1 | 100.00 | Using index; Using filesort |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+

row in set, 1 warning (0.00 sec)

MySQL 8.0

mysql> explain select * from slowtech.t1 order by c1,c2 desc;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

| 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 | 1 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

row in set, 1 warning (0.00 sec)

两者的对比可以看出，MySQL 8.0因为降序索引的存在，避免了“filesort”。

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

MySQL 5.7

mysql> explain select * from slowtech.t1 order by c1;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

| 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 | 1 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

row in set, 1 warning (0.00 sec)

mysql> explain select * from slowtech.t1 order by c1 desc;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

| 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 | 1 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

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/

6ebc85de9db51e120c5bddf1ab65a97a.png

而在8.0中，对于反向扫描，有一个专门的词进行描述“Backward index scan”。

mysql> explain select * from slowtech.t1 order by c1;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

| 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 | 1 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

row in set, 1 warning (0.00 sec)

mysql> explain select * from slowtech.t1 order by c1 desc;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+

| 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 | 1 | 100.00 | Backward index scan; Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+

row in set, 1 warning (0.00 sec)

终于不再对group by进行隐式排序

由于降序索引的引入，MySQL 8.0再也不会对group by操作进行隐式排序。

下面看看MySQL 5.7和8中的测试情况

create table slowtech.t1(id int);

insert into slowtech.t1 values(2);

insert into slowtech.t1 values(3);

insert into slowtech.t1 values(1);

MySQL 5.7

mysql> select * from slowtech.t1 group by id;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

rows in set (0.00 sec)

mysql> explain select * from slowtech.t1 group by id;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

row in set, 1 warning (0.00 sec)

“Using filesort”，代表查询中有排序操作，从结果上看，id列确实也是升序输出。

MySQL 8.0

mysql> select * from slowtech.t1 group by id;

+------+

| id |

+------+

| 2 |

| 3 |

| 1 |

+------+

rows in set (0.00 sec)

mysql> explain select * from slowtech.t1 group by id;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+

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/

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。

您可能感兴趣的文章:MySQL 8.0的关系数据库新特性详解

Mysql 8 新特性 window functions 的作用

MySQL 8 新特性之Invisible Indexes

MySQL8新特性：持久化全局变量的修改方法

MySQL8新特性：自增主键的持久化详解

鹿哥说
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
mysql 降序索引_MySQL8.0中的降序索引
weixin_36431035的博客
01-19 1645
前言相信大家都知道，索引是有序的；不过，在MySQL之前版本中，只支持升序索引，不支持降序索引，这会带来一些问题；在最新的MySQL 8.0版本中，终于引入了降序索引，接下来我们就来看一看。降序索引单列索引(1)查看测试表结构mysql> show create table sbtest1\G*************************** 1. row ***************...
MySQL 8 新特性之降序索引
weixin_34102807的博客
05-22 714
MySQL 8.0终于支持降序索引了。其实，从语法上，MySQL 4就支持了，但正如官方文档所言，"they are parsed but ignored"，实际创建的还是升序索引。   无图无真相，同一个建表语句，看看MySQL 5.7和8.0的区别。 create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc))...
MySQL8.0索引新特性
最新发布
时间如瑾 代码如诗
07-09 1360
在MySQL 8.0版本中查看数据表ts1的结构，从结果可以看出，索引已经是降序了。当表中没有显示主键时，表中的第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），在MySQL 5.7版本中查看数据表ts1的结构，从结果可以看出，索引仍然是默认的升序。注意 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。
Mysql8中降序索引的底层实现
yb223731的博客
10-16 392
https://juejin.im/post/6844903874424209415 https://www.cnblogs.com/ivictor/p/9072361.html 什么是降序索引 大家可能对索引比较熟悉，而对降序索引比较陌生，事实上降序索引是索引的子集。 我们通常使用下面的语句来创建一个索引： create index idx_t1_bcd on t1(b,c,d); 复制代码 上面sql的意思是在t1表中，针对b,c,d三个字段创建一个联合索引。 但是大家不知道的是，上面这
MySQL8新增降序索引
qq_39652397的博客
05-08 2009
一、MySQL5.7 降序索引 MySQL在语法上很早就已经支持降序索引，但实际上创建的却仍然是升序索引，如下MySQL 5.7 所示，row2字段降序，但是从show create table看 row2 仍然是升序的。 CREATE TABLE t_desc_index ( row1 INT, row2 INT, INDEX idx_row1_row2 ( row1, row2 DESC ) ); SHOW CREATE TABLE t_desc_index 二、MySQL8 降序索
MySQL8.0中索引的新特性（降序索引、隐藏索引）
G_amazing的博客
11-25 957
主要讲解MySQL8.0中索引的新特性，降序索引和隐藏索引
MySQL8新特性：降序索引详解
09-09
从MySQL 8.0版本开始，该数据库系统引入了一个重要的新特性——降序索引。在之前的版本中，尽管语法上支持了降序索引的创建，但是实际上这些索引会被忽略，数据库依然建立的是升序索引。现在MySQL 8.0实现了真正意义...
MySQL8新特性之降序索引底层实现详解
09-08
MySQL8的降序索引是其新特性之一，这一特性主要解决了在数据库底层对降序排序支持的问题。在MySQL8之前，虽然语法上允许创建降序索引（DESC），但其实现仅停留在语法层面，实际索引存储仍是升序排列。然而在MySQL8中...
MySQL8.0索引新特性—支持降序索引与支持隐藏索引
Java_LingFeng的博客
11-09 869
MySQL中的索引可以为提高我们的查询效率，相比较于低版本, MySQL 8.0中针对索引做了不少的优化，本文主要分享下MySQL8.0中关于索引的两个新特性，这两个新特性都非常好用，希望大家可以在日常的开发中根据实际场景用起来。
mysql8.0降序索引
ITxiaobaimrLIU的博客
10-16 135
在mysql8。0中引入了降序索引 create index idx_emp_age_salary on emp(age,salary); EXPLAIN select age,salary from emp order by age desc,salary asc; -- extra中的值 Backward index scan; Using index 8.0以前extra中的值则为using filesort,效率较低 并且mysql8.0中对group by取消了隐式排序 ...
MySQL 8.0新特性—降序索引
fangmeng1997的博客
08-18 222
降序索引（Descending Indexes） 先看下MySQL官方文档介绍： DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be sc
新特性｜MySQL 8.0 - 降序索引
分享既学习
05-19 704
一、前言 MySQL 8.0 之前的索引排序规则之前只允许 ASC 存储，创建时指定 DESC 也会被忽略。 8.0 版本为我们带来了 Descending Indexes 降序索引 ???????????? 只能使用 ASC 索引，带来的问题就是使用 DESC 会出现更多的 using filesort 导致更多的性能损耗。 二、动手实验 5.7 版本用了一张推荐系统的表，简单测试一下： 我们是在 5.7 版本里创建了一个 idx_similarity_desc(similarity desc)
MySql中降序索引及作用，简要概述
Miss__King的博客
08-07 3232
代码：explain select b from a order by bdesc,c,d extra。代码：explain select b from a order by b,c,d。create index idx1 on a(b desc,c desc,d desc) 降序。create index idx1(自定义索引名) on a(b（字段名）,c,d) 升序。降序（desc，大到小，mysql 8.0之后支持，之前语法支持，效果不支持）会先按照顺序先a,在b, c 的方式进行排序。...
mySQL定义降序索引_MySQL8新特性：降序索引详解
weixin_39531834的博客
02-02 527
前言mysql 8.0终于支持降序索引了。其实，从语法上，mysql 4就支持了，但正如官方文档所言，"they are parsed but ignored"，实际创建的还是升序索引。无图无真相，同一个建表语句，看看mysql 5.7和8.0的区别。create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));mysql ...
MYSQL8.0 创建排序索引
qq_42006088的博客
10-09 389
MySQL 8.0 给我们带来了倒序索引（Descending Indexes），也就是说反向存储的索引。这里不要跟搜索引擎中的倒排索引混淆了，MySQL这里只是反向排序存储而已。不过这个倒序存储已经解决了很大的问题。
MySQL InnoDB索引详解：BTree、优化与分析
MySQL中的InnoDB索引是其核心存储引擎中用于快速访问和排序数据的重要组成部分。本文将重点介绍BTree索引，它是InnoDB中最常用的一种索引类型。BTree索引是一种平衡查找树，它的设计旨在提供高效的数据检索和插入/...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值