关于正向索引和反向索引

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

当表中有一个索引a_desc_b_asc (a DESC, b
ASC)时,以下是上述6个查询的性能指标。

www.yabovip4.com 1

Query 1: SELECT * FROM t1 ORDER BY a DESC;

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

Query 2: SELECT * FROM t1 ORDER BY a ASC;

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

 

1, 对于查询1,也即ORDER BY a DESC;:
我们看到查询1中性能的提升,因为请求的语句排序是“a”列的DESC
译者注:因为MySQL8.0中可以建立倒叙索引,查询1按照a字段desc排序,直接走正向(forwarded)索引扫描即可完成查询,
避免了在MySQL5.7中查询出来数据之后再进行排序操作的步骤

5,如果用户想要避免查询5和查询6的filesorts,可以修改表以添加一个键(a
ASC, b ASC)。
亚搏app官方网站,此外,如果用户也想避免反向索引扫描,可以同时添加(a ASC, b DESC)和(a
DESC, b DESC)。

随着倒序索引(反向索引)的引入,我们已经删除了对隐式排序的支持,结果是作为GROUP
BY的一部分提到的列的升序。
除了上述改进外,我们还看到在一些情况下性能得到了改善,这些情况下的顺序是隐含的,但可能不是必需的。

下面是添加了第5点下的额外索引后的MySQL-5.7.14和MySQL-8.0-labs的最后对比:

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)
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)

 

 

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

性能指标的解释:

数据大小为1000万行。在MySQL-5.7中,它是a_asc_关于正向索引和反向索引。b_asc(a
ASC, b ASC),因为不支持倒叙索引。

3,对于查询3 也即ORDER BY a DESC, b ASC;:
查询3的排序方式与查询1类似,然而在MySQL-5.7中,对于任何请求混合顺序的查询,会对查询结果重新排序,因此性能差别是巨大的。

MySQL 8.0中的改进

关于正向索引和反向索引。注意,在MySQL-5.7中,我们不能添加额外的索引来提高上述查询的性能。
而且,有了这个特性,在某些情况下可以避免物化,比如在连接中的第一个表上请求混合顺序。
在一些用例中,反向索引提高了性能。区间扫描访问方法也使用反向索引。
虽然并不是所有的范围扫描访问方法都使用反向索引,但我们将在未来尝试消除这些限制。

 

总结

关于正向索引和反向索引。Query 3: SELECT * FROM t1 ORDER BY a DESC, b ASC;

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

以下为译文:

www.yabovip4.com 2

原文链接:http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/ 

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)
mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a DESC, b 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; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

 


简介

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 4: SELECT * FROM t1 ORDER BY a ASC, b DESC;

改进

4,对于查询4 也即 ORDER BY a ASC, b DESC;
可以看到,在MySQL
8.0中,查询4执行的是反向索引扫描,因此比查询3花费了更多的时间,
尽管如此,在查询5和查询6中,排序的方式是(a DESC, b DESC)/(a ASC, b
ASC),不管是正向扫描还是反向扫描,都无法满足排序需求,因此会用到filesort
但是,在这种情况下,由于在MySQL-5.7中ASC/DESC索引标志被忽略(译者注:MySQL
5.7中没有正向和反向索引的概念),因此MySQL-5.7可以使用(正向/反向)索引扫描来给出请求的顺序。

Query 5: SELECT * FROM t1 ORDER BY a DESC, b DESC;

 

2,对于查询2:
由于查询2的排序为正序(译者注:与索引的顺序相反,因此需要反向扫描),由于反向索引扫描,
在MySQL-8.0中(相对于查询1)执行向反向索引扫描需要更多的时间
(注意,从图中可以看出,MySQL-8.0总体上表现更好。MySQL
5.7中正向索引扫描,与MySQL 8.0中反向索引扫描花费的时间(几乎)相同)

 

 

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

mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a ASC, b 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 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
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社区长期存在的功能请求之一。请了解倒叙索引的特性,让我们知道你的想法!

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

 

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

Query 5: SELECT * FROM t1 ORDER BY a ASC, b ASC;

mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a ASC, 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; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

发表评论

电子邮件地址不会被公开。 必填项已用*标注