MySQL8.0之不可见索引

//

mysql8.0之不可见索引

//

MySQL8.0引入了不可见索引(invisible index)和不可见列(invisible column)，今天我们来说说这个特性。

00

不可见索引

不可见索引之所以称之为"不可见"，不是说我们人为看不见，而是说优化器不会选择它来对SQL语句进行优化。

1、如何创建不可见索引？

通常情况下，我们可以通过下面三种方法来创建不可见索引：

方案1：直接创建
m5603:  [test] 23:11:37> CREATE TABLE t1 (
    ->   i INT,
    ->   j INT,
    ->   k INT,
    ->   INDEX i_idx (i) INVISIBLE
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.03 sec)

方案2：create语法
m5603:  [test] 23:14:27> CREATE INDEX j_idx ON t1 (j) INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

方案3: alter语法
m5603:  [test] 23:14:27> ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引完毕后，我们可以通过下面的语句来查看索引情况（截取了部分字段）：

m5603:  [test] 23:14:55> show index from t1;
+-------+------------+----------+------------+---------+------------+
| Table | Non_unique | Key_name | Index_type | Visible | Expression |
+-------+------------+----------+------------+---------+------------+
| t1      |          1         | i_idx       | BTREE          | NO         | NULL       |
| t1      |          1         | j_idx       | BTREE          | NO         | NULL       |
| t1      |          1         | k_idx       | BTREE          | NO         | NULL       |
+-------+------------+----------+------------+---------+------------+
3 rows in set (0.01 sec)

可以看到，其中有一列内容是visible，代表是否可见，它的值是No，代表这个索引为不可见索引。

我们新增一个字段t，然后创建普通索引，t_idx，再查看索引，发现普通索引和不可见索引都可以查询到，只是普通索引的visible字段是Yes，说明它是可见的。

 m5603:  [test] 23:15:10> alter table t1 add t int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


m5603:  [test] 23:15:56> alter table t1 add index t_idx(t);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

m5603:  [test] 23:16:08> show index from t1;
+-------+------------+----------+------------+---------+------------+
| Table | Non_unique | Key_name | Index_type | Visible | Expression |
+-------+------------+----------+------------+---------+------------+
| t1    |          1 | i_idx    | BTREE      | NO      | NULL       |
| t1    |          1 | j_idx    | BTREE      | NO      | NULL       |
| t1    |          1 | k_idx    | BTREE      | NO      | NULL       |
| t1    |          1 | t_idx    | BTREE      | YES     | NULL       |
+-------+------------+----------+------------+---------+------------+
4 rows in set (0.00 sec)

2、如何修改索引的类型？

我们可以通过alter的语法来修改索引的可见还是不可见类型，语法如下

alter table tbl_name alter index idx_type；

举例如下：

m5603:  [test] 23:35:25> alter table t1 alter index t_idx invisible;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

m5603:  [test] 23:35:44> show index from t1;
+-------+------------+----------+------------+---------+------------+
| Table | Non_unique | Key_name | Index_type | Visible | Expression |
+-------+------------+----------+------------+---------+------------+
| t1    |          1 | i_idx    | BTREE      | NO      | NULL       |
| t1    |          1 | j_idx    | BTREE      | NO      | NULL       |
| t1    |          1 | k_idx    | BTREE      | NO      | NULL       |
| t1    |          1 | t_idx    | BTREE      | NO      | NULL       |
+-------+------------+----------+------------+---------+------------+
4 rows in set (0.00 sec)

上面的操作，将索引t_idx修改为不可见索引。

3、主要作用？

不可见索引可以用来测试删除索引对查询性能的影响，而无需进行破坏性的修改。对于一个大表来说，频繁的增加或者删除索引，代价是比较大的，我们可以通过变更索引的类型来间接实现索引的删除和新增。

如果优化器在执行SQL的时候需要用到某个索引，而我们设置它的属性为不可见之后，它对查询性能的影响体现在以下几个方面：

1、看看指定了具体索引的查询是否报错。例如查询中显示执行了using 某个索引；

2、performance schema会记录查询负载增加的相关数据；

3、explain看到的查询计划会有所改变；

4、查询可能会出现在慢查询日志中；

4、如何控制是否使用invisible索引？

可以通过系统参数来控制是否在查询中使用不可见索引，相关参数变量如下：

m5603:  [(none)] 23:49:10> show 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
1 row in set (0.00 sec)

如代码所示，可以改变use_invisible_indexes选项的值来改变索引的选用策略。

如下代码所示，同样的SQL，使用和不使用不可见索引的执行计划，可以看到，有明显的不同：

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */  i, j FROM t1 WHERE j >= 50G
*************************** 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 >= 50G
*************************** 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

5、限制

1、不可见属性不能用在主键上（注意，不是主键列）。

2、没有显示主键的表，如果在not null列上有unique约束，那么这个列不能被设置为不可见索引，如下：

CREATE TABLE t2 (
  i INT NOT NULL,
  j INT NOT NULL,
  UNIQUE j_idx (j)
) ENGINE = InnoDB;

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

3、有显示主键的表，即使主键列上的普通索引，也可以设置为不可见(注意和第一点进行区别)。如下：

m5603:  [test] 23:59:52> show create table t1G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i` int DEFAULT NULL,
  `j` int DEFAULT NULL,
  `k` int DEFAULT NULL,
  `t` int NOT NULL,
  PRIMARY KEY (`t`),
  KEY `idx_t` (`t`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)