- MySQL 8.0 后支持隐藏索引（invisible index），主要用于测试索引删除后产生的查询性能影响。索引隐藏适用于除主键索引（显示或者隐式设置）之外的索引，而主键索引不能通过任何方式隐藏。
- MySQL 数据库默认创建的索引都是可见的，要显式控制一个索引的可见性，可以在 CREATE TABLE，CREATE INDEX 或 ALTER TABLE 的索引定义命令中使用 VISIBLE 或 INVISIBLE 关键字。
示例
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(25),
age INT,
weight INT,
INDEX name_idx (name) INVISIBLE
) ENGINE = InnoDB;
ALTER TABLE user ADD INDEX age_idx (age) INVISIBLE;
CREATE INDEX weight_idx ON user (weight) INVISIBLE;
- 系统变量 optimizer_switch 的 use_invisible_indexes 值控制了优化器执行计划构建时是否使用隐藏后的索引。当索引隐藏不被优化器所使用时，用于索引的软删除。
语法
SET optimizer_switch='use_invisible_indexes=on';
SET optimizer_switch='use_invisible_indexes=off';
|
参数 |
描述 |
|
off |
关闭状态（默认值），优化器默认会忽略隐藏索引，即和加入该参数之前的效果一样。 |
|
on |
打开状态，隐藏索引仍然保持不可见，但优化器会把隐藏索引加入到执行计划的构建中。 |
- 利用索引隐藏快速测试删除索引后对 SQL 查询性能的影响，而无需进行索引删除、重建操作。如果需要该索引，再设置可见就好了，这在大表测试中无疑非常有用，因为对于大表索引的删除和重新添加很耗性能，甚至影响表的正常工作。
- 索引隐藏后，如果想要在某条单个 SQL 查询语句上对优化器启用，可以使用 SET_VAR 优化器提示来临时更新 optimizer_switch 的值，如下所示：
示例
SELECT
SET_VAR(optimizer_switch = 'use_invisible_indexes=on')
age,
name
FROM user
…
注
- 虽然索引隐藏不会被优化器使用，但仍然需要进行维护
- 索引的可见性不会影响索引的自身维护，例如，不管索引是可见还是不可见，每次表数据行的更改索引都会更新，并且唯一索引也可防止插入重复数据。
- 没有显式主键的表如果在 NOT NULL 列上有任何一个唯一索引，则仍可能成为有效的隐式主键。在这种情况下，第一个这样的索引会对表数据行施加与显式主键相同的约束，并且该索引不能设置为不可见。
查看隐藏的索引
- 使用 SHOW CREATE TABLE 语句查看索引定义，索引隐藏后会附加注释： /*!80000 INVISIBLE */
示例
SHOW CREATE TABLE user；
运行结果如下：
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(20) ,
KEY `idx_name` (`name`) /*!80000 INVISIBLE */,
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
- 使用 SHOW INDEX 查看索引的可见性，VISIBLE字段为YES，即可见，NO即不可见
示例
SHOW INDEX FROM user
运行结果如下：
- information_schema.STATISTICS.IS_VISIBLE 字段可以查看确认是否可见
示例
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
IS_VISIBLE
FROM STATISTICS
WHERE table_schema='slower'
AND table_name='user';
运行结果如下：
+---------------------+------------------+------------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | IS_VISIBLE |
+---------------------+------------------+------------------+----------------+
| slower | user | idx_name | NO |
| slower | user | PRIMARY | YES |
+---------------------+------------------+------------------+----------------+