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

标签：c3 index idx 特性索引 MySQL8.0 test c2 NULL

概念描述

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

使用场景

非主键上的索引使用
测试删除索引对性能的影响，而无需进行破坏性更改（通常对于大表来说，删除或者重建索引的影响很大，可以通过设置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)

#### 查看建表语句会显示索引的INVISIBLE提示信息：/*!80000 INVISIBLE */
mysql >show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int NOT NULL,
  `c1` int DEFAULT '0',
  `c2` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_c2` (`c2`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)


#### 通过create index命令创建
mysql >create index idx_test_c1 on test(c1) invisible;
Query OK, 0 rows affected (3.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

#### 通过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

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

mysql >show create table test\G
*************************** 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`),
  KEY `idx_test_c1` (`c1`),
  KEY `idx_test_c3` (`c3`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

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

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

mysql >show create table test\G
*************************** 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)

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 test\G
*************************** 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

标签：c3,index,idx,特性,索引,MySQL8.0,test,c2,NULL
From： https://blog.51cto.com/u_13482808/8088486