MySQL8新特性:降序索引详解

前言

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/

而在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/

总结

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

(0)

相关推荐

  • MySQL 8 新特性之Invisible Indexes

    背景 索引是把双刃剑,在提升查询速度的同时会减慢DML的操作.毕竟,索引的维护需要一定的成本.所以,对于索引,要加上该加的,删除无用的.前者是加法,后者是减法.但在实际工作中,大家似乎更热衷于前者,而很少进行后者.究其原因,在于后者,难.难的不是操作本身,而是如何确认一个索引是无用的. 如何确认无用索引 在不可见索引出现之前,大家可以通过sys.schema_unused_indexes来确定无用索引.在MySQL 5.6中,即使没有sys库,也可通过该视图的基表来进行查询. mysql> sh

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

    前言 自增主键没有持久化是个比较早的bug,这点从其在官方bug网站的id号也可看出(https://bugs.mysql.com/bug.php?id=199).由Peter Zaitsev(现Percona CEO)于2003年提出.历史悠久且臭名昭著. 首先,直观的重现下. mysql> create table t1(id int auto_increment primary key); Query OK, 0 rows affected (0.01 sec) mysql> inser

  • MySQL 8.0的关系数据库新特性详解

    前言 MySQL 8.0 当前的最新版本是 8.0.4 rc,估计正式版本出来也快了.本文介绍几个 8.0 在关系数据库方面的主要新特性. 你可能已经知道 MySQL 从版本 5.7 开始提供了 NoSQL 存储的功能,在 8.0 中这部分功能也得到了一些改进,但鉴于这个在实际当中用得极少,本人也是没用过,所以本文不会介绍这方面的东西,而是关注其关系数据库方面. 1.隐藏索引 隐藏索引的特性对于性能调试非常有用.在 8.0 中,索引可以被"隐藏"和"显示".当一个索

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

    前言 在8之前的版本中,对于全局变量的修改,其只会影响其内存值,而不会持久化到配置文件中.数据库重启,又会恢复成修改前的值.从8开始,可通过SET PERSIST命令将全局变量的修改持久化到配置文件中. 试举一例 mysql> show variables like '%max_connections%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+

  • Mysql 8 新特性 window functions 的作用

    MySQL 8.0的新特性包括: 对Unicode 9.0的开箱即用的完整支持 支持窗口函数和递归SQL语法,这在以往是不可能或者很难才能编写这样的查询语句 对原生JSON数据和文档存储功能的增强支持 MySQL 8.0的发布,跳过了多个版本号(从5.5开始),由于6.0修改和7.0是用来保留做MySQL的集群版本,因此采用了8.0的版本号. 1. 问题 Mysql 8.0.2 中新增加了一个主要功能 - 窗口函数 window function 这个功能具体是解决什么问题?下面先看一个SQL查

  • MySQL8新特性:降序索引详解

    前言 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.

  • Java8新特性Stream流实例详解

    什么是Stream流? Stream流是数据渠道,用于操作数据源(集合.数组等)所生成的元素序列. Stream的优点:声明性,可复合,可并行.这三个特性使得stream操作更简洁,更灵活,更高效. Stream的操作有两个特点:可以多个操作链接起来运行,内部迭代. Stream可分为并行流与串行流,Stream API 可以声明性地通过 parallel() 与sequential() 在并行流与顺序流之间进行切换.串行流就不必再细说了,并行流主要是为了为了适应目前多核机器的时代,提高系统CP

  • Java8 新特性Lambda表达式实例详解

    Java8 新特性Lambda表达式实例详解 在介绍Lambda表达式之前,我们先来看只有单个方法的Interface(通常我们称之为回调接口): public interface OnClickListener { void onClick(View v); } 我们是这样使用它的: button.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { v.setText("

  • C#语法新特性之元组实例详解

    1.元组(Tuple) 元组(Tuple)在4.0 的时候就有了,但元组也有些缺点,如: 1)Tuple 会影响代码的可读性,因为它的属性名都是:Item1,Item2.. . 2)Tuple 还不够轻量级,因为它是引用类型(Class),对于一个变量要使用一个类型,有点太没必要了. 源代码如下: // 摘要: // 提供用于创造元组对象的静态方法.若要浏览此类型的.NET Framework 源代码,请参阅 Reference Source. public static class Tuple

  • c++11 新特性——智能指针使用详解

    c++11添加了新的智能指针,unique_ptr.shared_ptr和weak_ptr,同时也将auto_ptr置为废弃(deprecated). 但是在实际的使用过程中,很多人都会有这样的问题: 不知道三种智能指针的具体使用场景 无脑只使用shared_ptr 认为应该禁用raw pointer(裸指针,即Widget*这种形式),全部使用智能指针 初始化方法 class A { public: A(int size){ this->size = size; } A(){} void Sh

  • ES9的新特性之正则表达式RegExp详解

    简介 正则表达式是我们做数据匹配的时候常用的一种工具,虽然正则表达式的语法并不复杂,但是如果多种语法组合起来会给人一种无从下手的感觉. 于是正则表达式成了程序员的噩梦.今天我们来看一下如何在ES9中玩转正则表达式. Numbered capture groups 我们知道正则表达式可以分组,分组是用括号来表示的,如果想要获取到分组的值,那么就叫做capture groups. 通常来说,我们是通过序号来访问capture groups的,这叫做Numbered capture groups. 举

  • Go1.18新特性对泛型支持详解

    目录 1.泛型是什么 2.泛型类型的定义 2.1.声明一个自定义类型 2.2.内置的泛型类型any和comparable 2.3.泛型中的~符号是什么 1.泛型是什么 Go1.18增加了对泛型的支持,泛型是一种独立于使用的特定类型编写代码的方式.现在可以编写函数和类型适用于一组类型集合的任何一种.泛型生命周期只在编译期,旨在开发中减少重复代码的编写. 由于go属于静态强类型语言,例如在比较两个数的大小时,没有泛型的时候,仅仅只是传入类型不一样,我们就要再复制一份一样的函数,如果有了泛型就可以减少

  • Java8新特性之Stream API详解

    一.前言 StreamAPI在Java8版本中使用,关注的是对数据的筛选.查找.存储等 它可以做的事情有:过滤.排序.映射.归约 二.使用流程 Stream实例化中间操作(过滤.排序.映射.规约)终止操作(匹配查找.归约.收集) 三.案例演示 public class EmployeeData { public static List<Employee> getEmployees(){ List<Employee> list = new ArrayList<>(); l

  • Java 8 新特性终极版指南详解

    前言: Java 8已经公布有一段时间了,种种迹象表明Java 8是一个有重大改变的发行版.在Java Code Geeks上已经有很多介绍Java 8新特性的文章,例如Playing with Java 8 – Lambdas and Concurrency.Java 8 Date Time API Tutorial : LocalDateTime和Abstract Class Versus Interface in the JDK 8 Era.本文还参考了一些其他资料,例如:15 Must

  • Java 8新特性 内建函数式接口详解

    Java 8新特性内建函数式接口 在之前的一片博文 Lambda 表达式,提到过Java 8提供的函数式接口.在此文中,将介绍一下Java 8四个最基本的函数式接口 对于方法的引用,严格来讲都需要定义一个接口.不管我们如何操作实际上有可能操作的接口只有四种. Java 8 提供了函数式接口包java.util.function.*,在该包下有许多Java 8内建的函数式接口.不过基本上分为四种基本的: 功能型接口 (Function) 将 T 作为输入,返回 R 作为输出,他还包含了和其他函数组

随机推荐