欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

(译)MySQL 8.0实验室---MySQL中的倒序索引(Descending Indexes)

程序员文章站 2022-03-15 10:24:22
译者注:MySQL 8.0之前,不管是否指定索引建的排序方式,都会忽略创建索引时候指定的排序方式(语法上不会报错),最终都会创建为ASC方式的索引,在执行查询的时候,只存在forwarded(正向)方式对索引进行扫描。关于正向索引和反向索引,逻辑上很容易理解,这里有两个相关的概念:正向索引或者反向( ......

 

译者注:
mysql 8.0之前,不管是否指定索引建的排序方式,都会忽略创建索引时候指定的排序方式(语法上不会报错),最终都会创建为asc方式的索引,
在执行查询的时候,只存在forwarded(正向)方式对索引进行扫描。
关于正向索引和反向索引,逻辑上很容易理解,这里有两个相关的概念:
正向索引或者反向(倒序)索引,两者都是在构建b树索引时候的相关字段排序方式,是b索引树的逻辑存储方式
正向扫描(forward)和反向扫描( backward index scan;)是执行查询的过程中对b树索引的扫描方式,是数据执行计划时候的一种索引扫描方式
关于正向扫描或者反向扫描不是随意的,受sql语句中(正/反向)排序方式以及(正/反向)索引的影响
之前在sqlserver中简单写过一点类似的东西,

整体上看,抛开正向索引和倒序索引,在扫描扫描的过程中,正向索引扫描的在性能上,稍微优于反向索引扫描。
不过,即便是反向索引扫描,也是优化器根据具体查询进行优化的结果,并非一个不好的选择。

 

 


原文链接:

 

以下为译文:

从8.0优化器实验室发布开始,mysql开始支持倒序索引。
正如我将在本文中详细介绍的,这个新特性可以用来消除对结果排序的需求,并在许多查询中带来性能改进。

 

简介

在此版本之前,所有索引都是按升序创建的。当语法本身被解析时,元数据不会被保留。例如在mysql 5.7中:

mysql 5.7> create table t1 (a int, b int, index a_desc_b_asc (a desc, b asc));
query ok, 0 rows affected (0.47 sec)

mysql 5.7> show create table t1\g
*************************** 1. row ***************************
       table: t1
create table: create table `t1` (
  `a` int(11) default null,
  `b` int(11) default null,
  key `a_desc_b_asc` (`a`,`b`) <-- 创建索引时候的元数据没有被保留
) engine=innodb default charset=latin1
1 row in set (0.00 sec)

应该注意的是,mysql 5.7 optimizer能够反向扫描一个升序索引(按照降序排列),其成本较高

(译者注:以上是原文中写道的,mysql 5.7中不知道怎么去判断在对索引扫描的时候,究竟是正向扫描还是反向扫描)。
如下可以进一步测试,我们可以看到正向索引扫描比反向索引扫描好~15%。
不能支持倒叙索引的主要限制是,优化器必须对混合顺序(如desc、b asc的顺序)使用文件排序。

mysql 8.0中的改进

引入反向索引后,innodb现在可以按照降序顺序存储数据行,优化器将在查询中请求降序时利用它。
重复上面的例子,我们可以看到在创建表时索引顺序信息被正确地保留了:

mysql 8.0> create table t1 (a int, b int, index a_desc_b_asc (a desc, b asc));
query ok, 0 rows affected (0.47 sec)
 
mysql 8.0> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| table | create table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | create table `t1` (
`a` int(11) default null,
`b` int(11) default null,
key `a_desc_b_asc` (`a` desc,`b`)
) engine=innodb default charset=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

为了区分向后和向前索引扫描,还改进了explain的输出。
对于mysql-5.7,除了查询2和查询6之外,我们对所有查询都使用反向索引扫描或文件排序,因为这两个查询只需要升序。

 

query 1: select * from t1 order by a desc;

mysql 8.0> explain select * from t1 order by a desc;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1  | simple    | t1    | null    | index  | null | a_desc_b_asc | 10 | null | 10 | 100.00 | using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

query 2: select * from t1 order by a asc;

mysql 8.0> explain select * from t1 order by a asc;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
| 1 | simple | t1 | null | index | null | a_desc_b_asc | 10 | null | 10 | 100.00 | backward index scan; using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

query 3: select * from t1 order by a desc, b asc;

mysql 8.0> explain select * from t1 order by a desc, b asc;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | simple | t1 | null | index | null | a_desc_b_asc | 10 | null | 10 | 100.00 | using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

query 4: select * from t1 order by a asc, b desc;