(目录)
本文问题
- 隐藏索引有什么特点？
- 如何隐藏索引？
- 如何查询索引是否隐藏？
- 如何控制优化器是否使用隐藏索引
MySQL支持隐藏索引，即在不改变实际索引结构的情况下，优化器不使用该索引。这个特性可以使用在任何非主键上。
隐藏索引特点
隐藏索引可以在不改变实际索引结构的情况下，测试删除索引对于查询性能的影响，这对于大表的优化十分有用，因为大表删除并重建索引的开销很大。
索引是否隐藏不影响索引的维护，例如：无论索引是否隐藏。当表数据更改时，索引仍然会进行更改，唯一索引仍然会阻止重复值的插入。
隐藏索引无法在主键上使用，在没有主键的表上，如果存在一个UNIQUE NOT NULL索引，这个索引被当做主键，也无法隐藏。
控制隐藏索引是否可用
可以通过optimizer_switch的use_invisible_indexes标识来控制优化器是否使用隐藏索引。如果该标识为off，优化器将忽略隐藏索引。如果标识为on，即便索引是隐藏的，优化器也会使用该索引。
控制索引是否隐藏
在CREATE TABLE,ALTER TABLE,CREATE INDEX语句中使用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;
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
查看索引是否隐藏
索引是否隐藏可以通过SHOW INDEX或者查询INFORMATION_SCHEMA.STATISTICS表来进行查看
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 |
+------------+------------+
问题答案
- 隐藏索引有什么特点？
- 隐藏索引可以在非主键上设置
- 隐藏索引不被优化器使用
- 隐藏索引依然具有索引本身的属性
- 如何隐藏索引？
使用
INVISIBLE子句来设置索引隐藏 - 如何查询索引是否隐藏？
SHOW INDEX或者查询INFORMATION_SCHEMA.STATISTICS表 - 如何控制优化器是否使用隐藏索引
设置
optimizer_switch中的use_invisible_indexes标志。off不使用隐藏的索引，on使用隐藏的索引。
