Documentation Home
MySQL 8.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 8.4 Reference Manual  /  ...  /  Invisible Indexes

10.3.12 Invisible 索引

MySQL 支持不可见索引,即不被优化器使用的索引。该特性适用于除主键(隐式或显式)以外的所有索引。

索引默认是可见的。要控制新索引的可见性,使用在CREATE TABLECREATE INDEXALTER TABLEVISIBLEINVISIBLE关键字:

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

要更改现有索引的可见性,使用VISIBLEINVISIBLE关键字与ALTER TABLE ... ALTER INDEX操作:

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

关于索引是否可见或不可见的信息可以从信息_schemaSTATISTICS表或SHOW INDEX输出中获取。例如:

mysql> SELECT INDEX_NAME, IS_VISIBLE
       FROM INFORMATION_SCHEMA.STATISTICS
       WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx      | YES        |
| j_idx      | NO         |
| k_idx      | NO         |
+------------+------------+

不可见索引使得可以测试删除索引对查询性能的影响,而无需进行破坏性更改,后续如果索引证明是必要的,可以快速地将其设置为可见状态。删除和重新添加索引对于大表可能非常耗时,而将索引设置为不可见或可见则是快速、不需要移动数据的操作。

如果实际上需要或使用了不可见索引,存在多种方法来注意其缺失对查询的影响:

  • 出现错误的查询语句,其中包含指向不可见索引的索引提示。

  • 性能_schema 数据显示受影响查询的工作负载增加。

  • 查询语句具有不同的EXPLAIN执行计划。

  • 之前未出现的查询语句现在出现在慢查询日志中。

控制器使用不可见索引来构建查询执行计划的use_invisible_indexes标志是optimizer_switch系统变量的一部分。如果标志设置为off(默认值),控制器将忽略不可见索引(与之前的行为相同)。如果标志设置为on,不可见索引仍然不可见,但控制器会在构建执行计划时考虑它们。

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

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
     >     i, j FROM t1 WHERE j >= 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: j_idx
          key: j_idx
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

mysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\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: 5
     filtered: 33.33
        Extra: Using where

索引可见性不影响索引维护。例如,索引仍将根据表行的变化进行更新,而唯一索引也将阻止对某一列的重复插入,无论索引是否可见。

如果没有明确的主键,表可能仍然具有隐式的主键,如果该表有任何UNIQUE索引在NOT NULL列上。在这种情况下,第一个这样的索引将对表行施加同样的约束,如明确的主键,并且该索引不能被隐藏。考虑以下表定义:

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

该定义中没有明确的主键,但在NOT NULLj上的索引将对行施加同样的约束,如主键,并且不能被隐藏:

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)