10.3.12 Invisible 索引
MySQL 支持不可见索引,即不被优化器使用的索引。该特性适用于除主键(隐式或显式)以外的所有索引。
索引默认是可见的。要控制新索引的可见性,使用在CREATE TABLE
、CREATE INDEX
或ALTER TABLE
中VISIBLE
或INVISIBLE
关键字:
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;
要更改现有索引的可见性,使用VISIBLE
或INVISIBLE
关键字与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 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)