• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL 8.0新特性:隐藏索引

MySQL SEian.G 3周前 (03-28) 50次浏览 已收录 0个评论

MySQL 8.0 支持隐藏索引(invisible index),也称为不可见索引。隐藏索引不会被优化器使用。 它允许快速启用/禁用MySQL Optimizer使用的索引;主键不能设置为隐藏(包括显式设置或隐式设置)。

索引默认是可见的(visible)。使用CREATE TABLE、CREATE INDEX 或ALTER TABLE语句的VISIBLE或者INVISIBLE选项设置一个新建索引的可见性:

mysql >CREATE TABLE t1 (
    ->   i INT,
    ->   j INT,
    ->   k INT,
    ->   INDEX i_idx (i) INVISIBLE
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql >CREATE INDEX j_idx ON t1 (j) INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql >ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用ALTER TABLE … ALTER INDEX语句的VISIBLE或者INVISIBLE选项修改已有索引的可见性:

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;

ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

通过数据字典表 INFORMATION_SCHEMA.STATISTICS 或者SHOW INDEX命令可以查看索引的可见性。例如:

mysql >SELECT INDEX_NAME, IS_VISIBLE        FROM INFORMATION_SCHEMA.STATISTICS        WHERE TABLE_SCHEMA = 'wjqtest' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx      | YES        |
| j_idx      | NO         |
| k_idx      | NO         |
+------------+------------+
3 rows in set (0.00 sec)

不可见索引特性可以用于测试删除某个索引对于查询性能的影响,同时又不需要真正删除索引,也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时,而将其设置为不可见或可见将会非常简单快捷。

如果某个设置为隐藏的索引实际上仍然需要或者被优化器所使用,可以通过以下多种方法发现缺少该索引带来的影响:

索引提示中使用了该索引的查询将会产生错误。

性能模式(Performance Schema)中的数据显示受影响查询的负载升高。

EXPLAIN 语句显示了不同的查询执行计划。

慢查询日志中出现了新的查询语句。

系统变量 optimizer_switch 的 use_invisible_indexes 值控制了优化器构建执行计划时是否使用隐藏索引。如果设置为 off (默认值),优化器将会忽略隐藏索引(与引入该属性之前的行为相同)。如果设置为 on,隐藏索引仍然不可见,但是优化器在构建执行计划时将会考虑这些索引。

使用SET_VAR优化器提示来optimizer_switch临时更新临时值 ,可以仅在单个查询期间启用不可见索引,如下所示:

mysql >show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i` int DEFAULT NULL,
  `j` int DEFAULT NULL,
  `k` int DEFAULT NULL,
  KEY `i_idx` (`i`),
  KEY `j_idx` (`j`) /*!80000 INVISIBLE */,
  KEY `k_idx` (`k`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql >EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ i, j FROM t1 WHERE j >= 2\G   
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

mysql >EXPLAIN SELECT i, j FROM t1 WHERE k >= 2\G                                   
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

但是我在MySQL 8.0.23版本的环境测试,好像和官方文档描述的存在差异,使用使用SET_VAR优化器提示来optimizer_switch临时更新临时值的时候出现如下警告,从警告信息看,optimizer_switch不能设置use_invisible_indexes=on,但是尚未定位到具体的原因,如果有人遇到过,可以留言一起交流;

mysql dba_admin@127.0.0.1:wjqtest21:02:43>show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                       |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1231 | Variable 'optimizer_switch' can't be set to the value of 'use_invisible_indexes=on'                                           |
| Note    | 1003 | /* select#1 */ select `wjqtest`.`t1`.`i` AS `i`,`wjqtest`.`t1`.`j` AS `j` from `wjqtest`.`t1` where (`wjqtest`.`t1`.`j` >= 2) |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

索引的可见性不会影响索引的维护。例如,无论索引是否可见,每次修改表中的数据时都需要对相应索引进行更新,而且唯一索引都会阻止插入重复的列值。

一个没有明确定义主键的表仍然可能存在有效的隐式主键( 表中某些 NOT NULL 字段上创建了 UNIQUE 索引)。在这种情况下,表中第一个这样的索引和显式主键存在相同的约束效果,该索引也不能被设置为不可见。假如存在以下表定义:

CREATE TABLE t2 (
i INT NOT NULL,
j INT NOT NULL,
UNIQUE j_idx (j)) ENGINE = InnoDB;

定义中没有明确指定主键,但是 NOT NULL 字段 j 上存在一个唯一索引,它实现了和主键相同的数据约束,不能设置为不可见:

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible.

如果为该表指定一个显式的主键:

ALTER TABLE t2 ADD PRIMARY KEY (i);

该主键不能设置为不可见。除此之外,字段 j 上的唯一索引不再是一个隐式的主键,因此可以设置为隐藏索引:

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
Query OK, 0 rows affected (0.03 sec)

如果我们现在要删除索引,我们可以将其更改为隐藏。 但是使用“FORCE / USE INDEX”的查询怎么样? 他们是否会抛出一个错误? 如果强制不存在的索引,你会收到错误。 你不会看到隐藏索引的错误。 优化器不会使用它,但知道它存在。

mysql> show create table t1 G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i` int(11) DEFAULT NULL,
`j` int(11) DEFAULT NULL,
`k` int(11) DEFAULT NULL,
KEY `i_idx` (`i`),
KEY `idx_1` (`i`,`j`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_1         | idx_1 | 10      | const,const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql> alter table t1 alter index idx_1 invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |     6.25 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from t1 where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | i_idx         | i_idx | 5       | const |    2 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

正如你所看到的,如果我们使用带有隐藏索引的“FORCE INDEX”,MySQL会执行全表扫描。 MySQL不会抛出任何错误,因为索引存在,但它不可见。 即使有另一个可用的索引,它也将执行全表扫描。 在大型表上,这可能会导致严重的性能问题。 即使MySQL在查询执行期间不抛出任何错误,它也应该会在错误日志中记录一个警告。

总结一些对这个新功能的首次使用的想法和总结:

如果你想删除一个索引,但又想事先知道效果。你就可以使它对优化程序不可见。这是一个快速的元数据更改,使索引不可见。一旦确定没有性能下降,就可以真正去删除索引。

关键的一点是,隐藏索引不能供优化器使用,但它仍然存在,并通过写入操作保持最新。即便我们尝试“FORCE INDEX”,优化器也不会使用它,虽然我认为我们应该能够在某种程度上强制它。可能会有这样的情况:

我们可以创建一个新的隐形索引,但如果想要测试它,必须使它可见。这意味着所有对应用程序有即时影响的查询都将能够使用它。如果目的只是想测试它,我不认为这是最好的方法,不是所有人的服务器上都有相同的数据大小和真实数据。强制隐藏索引这时候可能会很有用。

你有许多索引,但不确定哪一个未使用。你可以将一个索引更改为不可见,以查看是否存在任何性能下降。如果是,你可以立即更改。

你可能有一个特殊情况,只有一个查询可以使用该索引。在这种情况下,隐藏索引可能是一个很好的解决方案。

隐藏索引应用场景:软删除、灰度发布。

参考链接

https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html


MySQL 8.0新特性:隐藏索引


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL 8.0新特性:隐藏索引
喜欢 (2)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址