索引三剑客之降序索引和不可见索引

吹水老王

发布于 2022-05-17 16:50:00

1.4K0

发布于 2022-05-17 16:50:00

文章被收录于专栏：MySQL 8.0MySQL 8.0

索引三剑客之降序索引和不可见索引

前言

MySQL 8.0中引入了三个索引方面的新特性，暂且将其称为“索引三剑客”。前面我们已经学习了三剑客之一的函数索引，现在我们来见识一下另外两剑客 - 降序索引和不可见索引。

1. 降序索引

1.1 降序索引介绍

辟水剑，剑走偏锋，独辟蹊径，如刀细雨又快又密。降序索引，顾名思义，就是按照降序排列的索引。MySQL 8.0之前，无论索引定义是升序还是降序，在实际创建索引时一律是按照升序来组织索引项的；MySQL 8.0中引入降序索引特性，索引可以按照指定的升序或者降序的方式来组织索引项。

无图无真相。我们分别在MySQL 5.7 和 8.0 中创建一个降序索引，看看两者是否有区别。

# 以t_wang表为例，MySQL 5.7中创建降序索引
[root@3306][test]> alter table t_wang add index idx_ftime_desc(id asc, ftime desc);

[root@3306][test]> show create table t_wang\G
*************************** 1. row ***************************
       Table: t_wang
Create Table: CREATE TABLE `t_wang` (
  `id` int(11) NOT NULL,
  `ftime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ftime_desc` (`id`,`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 仍然以t_wang表为例，MySQL 8.0中创建降序索引
MySQL [test]> alter table t_wang add index idx_ftime_desc(id asc, ftime desc);

MySQL [test]> show create table t_wang\G
*************************** 1. row ***************************
       Table: t_wang
Create Table: CREATE TABLE `t_wang` (
  `id` int NOT NULL,
  `ftime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ftime_desc` (`id`,`ftime` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

对比一下上述例子，我们分别在MySQL 5.7 和 8.0中创建了一个 ftime desc 的倒序索引，但是在实际的表元数据中只有MySQL 8.0中保留了desc子句，MySQL 5.7中直接忽略了desc子句。

1.2 降序索引的优化

MySQL 8.0 支持降序索引。索引定义中的DESC不再被忽略，而是导致按降序存储索引键值。之前，查询结果要求按照倒序排序时，MySQL对升序索引使用反向扫描也能利用索引，但是性能上有一些损耗；现在MySQL可以直接利用降序索引的正向扫描来提升索引扫描性能，同时可以消除排序。降序索引也使得MySQL优化器可以针对组合索引中部分列升序和部分列降序的组合排序时进行优化。

例如，发起以下查询（order by id asc, ftime desc），同样都会使用到上面例子创建的索引，查看执行计划有何异同。

# MySQL 5.7中Extra部分出现了using filesort，需要对查询结果进行排序
[root@3306][test]> explain select * from t_wang where id > 100 and ftime < '2022-01-01 00:00:00' order by id asc, ftime desc;
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys          | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | t_wang | NULL       | index | PRIMARY,idx_ftime_desc | idx_ftime_desc | 10      | NULL |    1 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+------------------------------------------+

# MySQL 8.0的执行计划中则没有了排序
MySQL [test]> explain select * from t_wang where id > 100 and ftime < '2022-01-01 00:00:00' order by id asc, ftime desc;
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys          | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_wang | NULL       | index | PRIMARY,idx_ftime_desc | idx_ftime_desc | 10      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+--------------------------+

上述查询，在MySQL 5.7 和 8.0 都可以到上述索引，不过MySQL 5.7的执行计划中显示需要对结果进行filesort排序，而8.0中则无需排序。我们知道filesort排序对于MySQL来说是很耗时的操作，省去了排序对于查询效率的优化显而易见。

1.3 MySQL 5.7 vs 8.0不同排序场景下的性能对比

以下是官方对于降序索引在不同排序场景下的压测结果。

测试表：

测试表也只有两列（a，b），表中存储1000w行记录，创建了一个联合索引 (a desc, b asc)。

# 测试表
CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# 分表在MySQL 5.7 和 8.0中创建一个组合索引
alter table t1 add index `a_desc_b_asc`(a desc, b asc);

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

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

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

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

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

查询6：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)

查询性能对比：

我们看到，MySQL 8.0 在查询1/2/3/4中的性能都要好于5.7；而在查询5/6中的性能要差于5.7。

查询5（a DESC, b DESC）和查询6（a ASC, b ASC）与我们创建的索引 (a desc, b asc) 排序方式不同，导致了filesort 排序。为了避免8.0中的排序操作，我们可以再添加一个索引（a asc, b asc)，这时所有6个查询在MySQL 8.0中的性能都比5.7中要好很多。

具体测试参考：MySQL 8.0 实验室 - MySQL 中的降序索引

1.4 降序索引的影响

MySQL 8.0中的降序索引不仅对提升了查询性能；同时由于降序索引的引入，MySQL官方在group by分组操作中去掉了默认按照升序隐式排序的处理，使得很多场景下的group by性能有了明显提升。不过，开发人员需要注意，group by操作不再对分组后结果进行隐式排序可能带来的变化。

2. 不可见索引

2.1 不可见索引

万剑归宗乃是剑术最高境界，化剑气于无形，聚无形剑气如万剑自生。最后来学习一下索引特性中最后一位剑客 - 不可见索引。

索引维护是数据库日常维护的一项重要工作。我们经常会向一张拥有大量记录的表添加新的索引以提高查询性能；或是当系统运行一段时间之后，经过需求变更、结构设计变化之后数据库中一些大表索引不再满足查询要求，这些索引的存在不仅降低查询效率而且占用大量空间，我们需要找到哪些低效甚至无用的索引并删除它们。

对于实时数据处理的数据库来讲，无论是大表添加索引还是删除索引，都是很成本高风险大的操作。我们要尽量避免不必要的索引维护。

MySQL 8.0中引入的不可见索引这一特性正好解决这个问题。不可见索引，顾名思义，即看不见的索引，可以通过关键字visible/invisible 控制索引的可见性。通过设置索引的可见性，可以用来测试索引对查询性能的影响。

# 设置索引不可见
alter table t_wang alter index idx_ftime_desc invisible;
# 设置索引可见
alter table t_wang alter index idx_ftime_desc visible;
# 查询索引是否可见
MySQL [test]> select index_name, is_visible from information_schema.statistics where table_name = 't_wang';
+----------------+------------+
| INDEX_NAME     | IS_VISIBLE |
+----------------+------------+
| idx_desc       | YES        |
| idx_ftime_desc | NO         |
| PRIMARY        | YES        |
+----------------+------------+
4 rows in set (0.00 sec)

通过设置不可见索引，有几种方式可以观测到它是否对表查询有影响：

包含不可见索引的hint会发生报错；
查询SQL的实际执行效率下降；
查询SQL的explain执行计划变化；
查询SQL出现在了慢日志中。

如果设置索引不可见之后，查询SQL报错或者性能出现波动，那么我们就可以明确这个索引是不可或缺的。对于大表的索引维护是成本很高的操作，我们可以简单地通过设置索引可见来恢复索引，索引设置可见与否只修改元数据，这个操作是瞬间完成的。

不过这里仅仅是针对优化器不可见而已，索引的维护并不受影响。例如，索引会随着表中记录的更改而持续更新，并且像唯一索引这种唯一约束也一直存在。

2.2 实例验证

我们还是以上面那张测试表t_wang来验证一下不可见索引的作用。

# 测试表
MySQL [test]> show create table t_wang\G
*************************** 1. row ***************************
       Table: t_wang
Create Table: CREATE TABLE `t_wang` (
  `id` int NOT NULL,
  `ftime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ftime_desc` (`id`,`ftime` DESC) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 查询SQL使用到索引idx_ftime_desc
MySQL [test]> explain select * from t_wang where id >100 and ftime < '2022-01-01 00:00:00' order by id asc, ftime desc;
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys          | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_wang | NULL       | index | PRIMARY,idx_ftime_desc | idx_ftime_desc | 10      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+------+----------+--------------------------+

# 设置索引idx_ftime_desc为不可见索引，验证该索引对执行计划的影响
MySQL [test]> alter table t_wang alter index idx_ftime_desc invisible;

# 再次查看执行计划，可以确认该索引可以优化SQL查询效率
MySQL [test]> explain select * from t_wang where id >100 and ftime < '2022-01-01 00:00:00' order by id asc, ftime desc;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t_wang | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where; Using filesort |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

# 恢复索引可见
MySQL [test]> alter table t_wang alter index idx_ftime_desc visible;

总结

MySQL 8.0新引入了索引三剑客，函数索引、降序索引和不可见索引，提高了查询性能和可维护性。有了函数索引，业务不需要手动在表上添加虚拟列，就能够享受虚拟列带来的性能提升；降序索引，使得MySQL可以直接利用降序索引的正向扫描来提升索引扫描性能，同时可以消除排序；不可见索引，大大降低了索引维护的成本。MySQL 8.0在索引上的优化，极大地提升了数据库的性能核稳定性。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-05-04，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server