文章目录[隐藏]
- 查询1：select * from idx_tab1 order by c1;
- 查询2：select * from idx_tab1 order by c1 desc;
- 查询3：select * from idx_tab1 order by c1,c2 desc;
- 查询4：select * from idx_tab1 order by c1 desc,c2;
- 查询5：select * from idx_tab1 order by c1 desc,c2 desc;
- 查询6：select * from idx_tab1 order by c1,c2;
- 不再对group by进行隐式排序
- 使用条件及限制
- 参考链接
MySQL从8.0开始终于支持真正的降序索引了，实际上，在以前的版本当中，语法上可以通过desc来指定索引为降序，但实际上创建的仍然是常规的升序索引。以前，索引可以以相反的顺序进行扫描，但会降低性能。降序索引可以按向前顺序进行扫描，这样效率更高。当最有效的扫描顺序将某些列的升序与其他列的降序混合时，降序索引还使优化程序可以使用多列索引。
建表语句：
CREATE TABLE `idx_tab1` (id int primary key auto_increment, `c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,KEY `idx_c1_c2` (`c1`,`c2` desc));
5.7
mysql>show create table idx_tab1\G
*************************** 1. row ***************************
Table: idx_tab1
Create Table: CREATE TABLE `idx_tab1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
虽然c2列指定了desc，但在实际的建表语句中还是将其忽略了。无法支持降序索引的主要限制是优化器必须针对混合顺序求助于文件排序，例如ORDER BY c1 DESC，c2 ASC。
再来看看MySQL 8.0的结果。
8.0
mysql>show create table idx_tab1\G
*************************** 1. row ***************************
Table: idx_tab1
Create Table: CREATE TABLE `idx_tab1` (
`id` int NOT NULL AUTO_INCREMENT,
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
通过引入降序索引，InnoDB现在可以按降序存储条目，并且当查询中请求降序时，优化器将利用它。
EXPLAIN的输出也得到了改进，以区分反向索引扫描和正向索引扫描。在MySQL-5.7的情况下，我们对所有查询使用向后索引扫描或文件排序，但下面显示的查询2和查询6除外，因为这两个查询都只需要升序。
当表具有一个索引`idx_c1_c2` (`c1`,`c2` DESC)时，以下是上述6个查询的所有性能数字。在MySQL-5.7中，它是`idx_c1_c2` (`c1`,`c2` )，因为不支持降序索引。
查询1：select * from idx_tab1 order by c1;
5.7 mysql>explain select * from idx_tab1 order by c1; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 2468626 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 8.0 mysql>explain select * from idx_tab1 order by c1; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1888550 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
查询2：select * from idx_tab1 order by c1 desc;
5.7 mysql>explain select * from idx_tab1 order by c1 desc; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 2482754 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 8.0: mysql>explain select * from idx_tab1 order by c1 desc; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+----------------------------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1910453 | 100.00 | Backward index scan; Using index | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec)
在一条对idx_tab1表的查询语句中只对c1字段进行降序排序，无论是MySQL5.7还是MySQL8.0都用到了索引，在8.0中，对于索引的反向扫描，有一个专门的词进行描述“Backward index scan”。虽然c1是升序索引，但在第二个查询中，对其进行降序排列时，并没有进行额外的排序，使用的还是索引。在这里，大家容易产生误区，以为升序索引就不能用于降序排列，实际上，对于索引，MySQL不仅支持正向扫描，还可以反向扫描。反向扫描的性能同样不差。
如果一个查询，需要对多个列进行排序，且顺序要求不一致。在这种场景下，要想避免数据库额外的排序-“filesort”，只能使用降序索引。还是上面这张表，来看看有降序索引和没有的区别。
查询3：select * from idx_tab1 order by c1,c2 desc;
5.7 mysql>explain select * from idx_tab1 order by c1,c2 desc; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 2621338 | 100.00 | Using index; Using filesort | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec) 8.0： mysql>explain select * from idx_tab1 order by c1,c2 desc; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1965191 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
查询4：select * from idx_tab1 order by c1 desc,c2;
5.7： mysql>explain select * from idx_tab1 order by c1 desc,c2; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 2738900 | 100.00 | Using index; Using filesort | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec) 8.0： mysql>explain select * from idx_tab1 order by c1 desc,c2; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+----------------------------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1987028 | 100.00 | Backward index scan; Using index | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec)
两者的对比可以看出，MySQL 8.0因为降序索引的存在，避免了“filesort”。
这其实是降序索引的主要应用场景。如果只对单个列进行排序，降序索引的意义不是太大，无论是升序还是降序，升序索引完全可以应付。还是同样的表，看看下面的查询。
虽然在对多字段进行排序时，能够最大发挥降序索引的作用，但也尤其要注重使用的场景，否则仍然可能适得其反。例如对于如下两条查询语句，降序索引的表现就并没有那么乐观。从执行计划中可以看出，使用了filesort。但是在MySQL5.7中正常的升序索引对这两条语句的执行却有着上佳的表现
查询5：select * from idx_tab1 order by c1 desc,c2 desc;
5.7： mysql>explain select * from idx_tab1 order by c1 desc,c2 desc; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 2784945 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 8.0： mysql>explain select * from idx_tab1 order by c1 desc,c2 desc; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 2007069 | 100.00 | Using index; Using filesort | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
查询6：select * from idx_tab1 order by c1,c2;
5.7： mysql>explain select * from idx_tab1 order by c1,c2; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 2814911 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) 8.0： mysql>explain select * from idx_tab1 order by c1,c2; +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ | 1 | SIMPLE | idx_tab1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 2045605 | 100.00 | Using index; Using filesort | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
为什么降序索引此时失去了它的魔力呢？
因为我们执行的这两条查询语句对排序的两个字段要么全部升序，要么全部倒序。而在MySQL5.7中，由于组成联合索引的c1字段和c2字段都是升序排列的，那么在使用order by c1,c2排序时，MySQL可以对索引进行正向扫描，在使用order by c1 desc,c2 desc，MySQL对索引进行完全的反向扫描即可。意识到降序索引在这两种排序场景下的缺陷。根据同样的道理，我们也能够发现降序索引在另一种场景下的优势。在文章中的第一个例子中，我们使用的排序顺序为order by c1 , c2 desc，这种场景下，MySQL对索引进行的是正向扫描，那么，让数据库对降序索引进行反向扫描，同样可以发挥降序索引的优势。如查询3和查询4的SQL语句，MySQL用到了索引，使用了Backward index scan。
降序索引最大的应用场景便是如上例这样对多字段排序的场景，这种场景下，能够最大的发挥降序索引的作用。在对单字段排序时，无论是升序还是降序都是可以用到索引的。因为数据库不仅可以对索引进行从前向后的扫描，也可以对索引进行从后向前的扫描。
降序索引的引入，使得在查询中，需要对多字段进行排序，且顺序要求不一致的场景，能够极大的提升查询的效率。但是需要注意的是，降序索引的引入，只是多提供给我们一种索引的使用方法，它并不能做到赢家通吃，无法适用所有的排序场景。因此，在工作中，我们还是要贴合业务的需求，合理的运用索引，这样写出的SQL执行起来才能事半功倍。
考虑下面的表定义，其中包含两列和四个两列索引定义，用于定义列上的升序和降序索引的各种组合：
CREATE TABLE t ( c1 INT, c2 INT, INDEX idx1 (c1 ASC, c2 ASC), INDEX idx2 (c1 ASC, c2 DESC), INDEX idx3 (c1 DESC, c2 ASC), INDEX idx4 (c1 DESC, c2 DESC));
该表定义产生四个不同的索引。优化器可以对每个ORDER BY子句执行前向索引扫描， 并且不需要使用 filesort操作：
ORDER BY c1 ASC, c2 ASC -- optimizer can use idx1 ORDER BY c1 DESC, c2 DESC -- optimizer can use idx4 ORDER BY c1 ASC, c2 DESC -- optimizer can use idx2 ORDER BY c1 DESC, c2 ASC -- optimizer can use idx3
不再对group by进行隐式排序
由于降序索引的引入，MySQL 8.0再也不会对group by操作进行隐式排序。
create table tab2(id int); insert into tab2 values(2); insert into tab2 values(3); insert into tab2 values(1);
5.7版本：
mysql>select * from tab2 group by id; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql>explain select * from tab2 group by id; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | tab2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
8.0版本：
mysql>select * from tab2 group by id; +------+ | id | +------+ | 2 | | 3 | | 1 | +------+ 3 rows in set (0.00 sec) mysql>explain select * from tab2 group by id; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | tab2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)
不仅结果没有升序输出，执行计划中也没有“Using filesort”。可见，MySQL 8.0对于group by操作确实不再进行隐式排序。从5.7升级到8.0，依赖group by隐式排序的业务可要小心咯。
使用条件及限制
只有InnoDB存储引擎支持降序索引，且具有以下限制：
- 如果索引包含一个降序索引列或主键包含降序索引列，则辅助索引无法用到change buffer。
- InnoDB SQL解析器不使用降序索引。对于InnoDB全文搜索，这意味着索引表的FTS_DOC_ID列上所需的索引不能定义为降序索引。
- 适用于升序索引的所有数据类型也都支持降序索引。
- 普通(非生成)和生成列(VIRTUAL和STORED)都支持降序索引。
- DISTINCT可以使用任何一个构成索引的字段，包括索引中降序的字段。
- 对于调用了聚合函数但没有GROUP BY子句的这一类查询，索引的降序列不能用于优化MIN()/MAX()这样的聚合函数。
- BTREE支持降序索引，但HASH索引、FULLTEXT或SPATIAL索引不支持降序索引。
参考链接
https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/
如果您觉得本站对你有帮助，那么可以收藏和推荐本站，帮助本站更好地发展，在此谢过各位网友的支持。
转载请注明原文链接：MySQL 8.0新特性：降序索引
转载请注明原文链接：MySQL 8.0新特性：降序索引
