MySQL8.0新特性之：不可见索引

概念描述

  MySQL从8.0版本开始支持不可见索引（invisible index）也可叫隐式索引，隐形索引或者隐藏索引。不可见索引是不被MySQL优化器使用的，但是优化器会正常维护它。

使用场景

1. 非主键上的索引使用
2. 测试删除索引对性能的影响，而无需进行破坏性更改（通常对于大表来说，删除或者重建索引的影响很大，可以通过设置invisible/visible 属性来验证索引带来的效果）

测试验证

1. 创建不可见索引

#### 建表时指定索引的 invisible属性：
mysql >create table test(
-> id int not null primary key,
-> c1 int default 0,
-> c2 int not null default 0,
-> index idx_c2(c2) invisible);
Query OK, 0 rows affected (0.00 sec)

#### 通过alter table XXX add index 命令创建
mysql >alter table test add index idx_test_c3(c3) invisible;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0


2. 更改不可见索引

   要调整现有索引的可见性，请使用 VISIBLE 或者 INVISIBLE 关键字并通过语法： alter table… alter index 进行操作：

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

mysql >alter table test alter index idx_test_c1 visible;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

3. 查询不可见索引

• 通过show index 进行查询：

#### 查看Visible列值Yes Or No
mysql >show index from test;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| test | 1 | idx_c2 | 1 | c2 | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |
| test | 1 | idx_test_c1 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | | NO | NULL |
| test | 1 | idx_test_c3 | 1 | c3 | A | 3 | NULL | NULL | | BTREE | | | NO | NULL |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)


• 通过show creat table 进行查询：

#### 查看索引信息KEY行后面的注释： /*!80000 INVISIBLE */
mysql >show create table testG
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int NOT NULL,
`c1` int DEFAULT '0',
`c2` int NOT NULL DEFAULT '0',
`c3` int NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c2` (`c2`) /*!80000 INVISIBLE */,
KEY `idx_test_c1` (`c1`) /*!80000 INVISIBLE */,
KEY `idx_test_c3` (`c3`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)


• 通过INFORMATION_SCHEMA.STATISTICS表进行查询：

#### 查询information_schema.statistics表的IS_VISIBLE列值：
select TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAME, IS_VISIBLE from information_schema.statistics where table_schema='rockdb' and table_name='test';
+--------------+------------+--------------+-------------+--------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | IS_VISIBLE |
+--------------+------------+--------------+-------------+--------------+-------------+------------+
| rockdb | test | rockdb | idx_c2 | 1 | c2 | NO |
| rockdb | test | rockdb | idx_test_c1 | 1 | c1 | NO |
| rockdb | test | rockdb | idx_test_c3 | 1 | c3 | NO |
| rockdb | test | rockdb | PRIMARY | 1 | id | YES |
+--------------+------------+--------------+-------------+--------------+-------------+------------+
4 rows in set (0.00 sec)


限制和约束

• MySQL版本不低于8.0
• 不可见索引不能用于主键

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


• 可以通过系统变量use_invisible_indexes来调整优化器是否使用不可见索引来构建查询执行计划

#### use_invisible_indexes默认为off (优化器将忽略不可见索引)

mysql >show global variables like 'optimizer_switch'G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

#### 默认执行计划会忽略不可见索引
mysql >desc select * from test where c3=5;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

#### 通过hint提示来控制系统变量use_invisible_indexes 让SQL执行计划使用不可见索引
mysql >desc select /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ * from test where c3=5;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_test_c3 | idx_test_c3 | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)


 

参考文档

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