mysql索引倒序_（译）MySQL8.0实验室---MySQL中的倒序索
引（Descen。。。
译者注：
MySQL 8.0之前，不管是否指定索引建的排序⽅式，都会忽略创建索引时候指定的排序⽅式(语法上不会报错)，最终都会创建为ASC⽅式的索引，
在执⾏查询的时候，只存在forwarded(正向)⽅式对索引进⾏扫描。
关于正向索引和反向索引，逻辑上很容易理解，这⾥有两个相关的概念：
正向索引或者反向(倒序)索引，两者都是在构建B树索引时候的相关字段排序⽅式，是B索引树的逻辑存储⽅式
正向扫描(forward)和反向扫描( Backward index scan;)是执⾏查询的过程中对B树索引的扫描⽅式，是数据执⾏计划时候的⼀种索引扫描⽅式
关于正向扫描或者反向扫描不是随意的，受sql语句中(正/反向)排序⽅式以及(正/反向)索引的影响
之前在sqlserver中简单写过⼀点类似的东西，wwwblogs/wy123/p/5552719.html
整体上看，抛开正向索引和倒序索引，在扫描扫描的过程中，正向索引扫描的在性能上，稍微优于反向索引扫描。
不过，即便是反向索引扫描，也是优化器根据具体查询进⾏优化的结果，并⾮⼀个不好的选择。
以下为译⽂：
从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.47sec)
mysql5.7> SHOW CREATE TABLEt1\G*************************** 1. row ***************************
Table: t1Create 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=latin11 row in set (0.00 sec)
应该注意的是，MySQL 5.7 optimizer能够反向扫描⼀个升序索引(按照降序排列)，其成本较⾼
(译者注：以上是原⽂中写道的，MySQL 5.7中不知道怎么去判断在对索引扫描的时候，究竟是正向扫描还是反向扫描)。
随机函数rand 1到100如下可以进⼀步测试，我们可以看到正向索引扫描⽐反向索引扫描好~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.47sec)
mysql8.0> show create tablet1;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
mysql语句顺序| 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;卵巢肿瘤能活多长时间
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)
Query 5: SELECT * FROM t1 ORDER BY a DESC, b DESC;
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)
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)
当表中有⼀个索引a_desc_b_asc (a DESC, b ASC)时，以下是上述6个查询的性能指标。
数据⼤⼩为1000万⾏。在MySQL-5.7中，它是a_asc_b_asc(a ASC, b ASC)，因为不⽀持倒叙索引。
性能指标的解释:
1， 对于查询1，也即ORDER BY a DESC;：
我们看到查询1中性能的提升，因为请求的语句排序是“a”列的DESC
译者注：因为MySQL8.0中可以建⽴倒叙索引，查询1按照a字段desc排序，直接⾛正向(forwarded)索引扫描即可完成查询，
避免了在MySQL5.7中查询出来数据之后再进⾏排序操作的步骤
2，对于查询2：
由于查询2的排序为正序(译者注：与索引的顺序相反，因此需要反向扫描)，由于反向索引扫描，
在MySQL-8.0中(相对于查询1)执⾏向反向索引扫描需要更多的时间
(注意，从图中可以看出，MySQL-8.0总体上表现更好。MySQL 5.7中正向索引扫描，与MySQL 8.0中反向索引扫描花费的时间(⼏乎)相同)
3，对于查询3 也即ORDER BY a DESC, b ASC;：
查询3的排序⽅式与查询1类似，然⽽在MySQL-5.7中，对于任何请求混合顺序的查询，会对查询结果重新排序，因此性能差别是巨⼤的。
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可以使⽤(正向/反向)索引扫描来给出请求的顺序。
5，如果⽤户想要避免查询5和查询6的filesorts，可以修改表以添加⼀个键(a ASC, b ASC)。
此外，如果⽤户也想避免反向索引扫描，可以同时添加(a ASC, b DESC)和(a DESC, b DESC)。
下⾯是添加了第5点下的额外索引后的MySQL-5.7.14和MySQL-8.0-labs的最后对⽐:
注意，在MySQL-5.7中，我们不能添加额外的索引来提⾼上述查询的性能。
⽽且，有了这个特性，在某些情况下可以避免物化，⽐如在连接中的第⼀个表上请求混合顺序。
在⼀些⽤例中，反向索引提⾼了性能。区间扫描访问⽅法也使⽤反向索引。
虽然并不是所有的范围扫描访问⽅法都使⽤反向索引，但我们将在未来尝试消除这些限制。
改进
随着倒序索引(反向索引)的引⼊，我们已经删除了对隐式排序的⽀持，结果是作为GROUP BY的⼀部分提到的列的升序。除了上述改进外，我们还看到在⼀些情况下性能得到了改善，这些情况下的顺序是隐含的，但可能不是必需的。
总结
我们很⾼兴能够解决MySQL社区长期存在的功能请求之⼀。请了解倒叙索引的特性，让我们知道你的想法!
发表评论