MySQL8.0 索引优化invisible index详情

前言

MySQL8.0 开始支持不可见索引。 优化器根本不使用不可见索引，但会以其他的方式正常维护。

默认情况下 索引是可见的。 通过不可见索引，可以方便数据库管理人员 检查 索引对查询性能的影响，而不会进行破坏性的更改 。

应用场景： 软删除，灰度发布

-- 创建测试表
mysql> create table t1(i int ,j int);
Query OK, 0 rows affected (0.03 sec)



-- 创建索引 
mysql>
mysql> create index idx_i on t1(i);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> -- 创建 一个隐藏索引
mysql> create index idx_j on t1(j) invisible ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0



-- 查看索引
mysql> show index from t1\G
*************************** 1. row ***************************
      Table: t1
 Non_unique: 1
   Key_name: idx_i
Seq_in_index: 1
Column_name: i
  Collation: A
Cardinality: 0
   Sub_part: NULL
     Packed: NULL
       Null: YES
 Index_type: BTREE
    Comment:
Index_comment:
    Visible: YES
 Expression: NULL
*************************** 2. row ***************************
      Table: t1
 Non_unique: 1
   Key_name: idx_j
Seq_in_index: 1
Column_name: j
  Collation: A
Cardinality: 0
   Sub_part: NULL
     Packed: NULL
       Null: YES
 Index_type: BTREE
    Comment:
Index_comment:
    Visible: NO
 Expression: NULL
2 rows in set (0.00 sec)

上面 对 i,j 分别创建了索引， 其中j 为不可见索引 ，即默认优化器不会使用到该索引的。

索引使用情况测试

mysql> explain  select * from t1 where i= 10\G
*************************** 1. row ***************************
         id: 1
select_type: SIMPLE
      table: t1
 partitions: NULL
       type: ref
possible_keys: idx_i
        key: idx_i
    key_len: 5
        ref: const
       rows: 1
   filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain  select * from t1 where j= 10\G
*************************** 1. row ***************************
         id: 1
select_type: SIMPLE
      table: t1
 partitions: NULL
       type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
       rows: 1
   filtered: 100.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

如何临时让优化器可以看到这个索引呢？

mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: 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
1 row in set (0.00 sec)

-- 设置开关
mysql> set session optimizer_switch='use_invisible_indexes=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: 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=on,skip_scan=on
1 row in set (0.00 sec)

-- 此时可以看到 优化器已经可以看到这个索引啦
mysql> explain  select * from t1 where j= 10\G
*************************** 1. row ***************************
         id: 1
select_type: SIMPLE
      table: t1
 partitions: NULL
       type: ref
possible_keys: idx_j
        key: idx_j
    key_len: 5
        ref: const
       rows: 1
   filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

修改索引的可见性

mysql> ALTER TABLE t1 ALTER INDEX idx_j INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ALTER INDEX idx_j  VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意: 主键索引 是不能设置 不可见的 。

create  table t2(`id` int not null auto_increment, primary key (`id`));


mysql> show index from t2\G
*************************** 1. row ***************************
      Table: t2
 Non_unique: 0
   Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
  Collation: A
Cardinality: 0
   Sub_part: NULL
     Packed: NULL
       Null:
 Index_type: BTREE
    Comment:
Index_comment:
    Visible: YES
 Expression: NULL
1 row in set (0.01 sec)

mysql> alter table t2 alter index PRIMARY key(`id`) invisible;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY invisible' at line 1

测试 添加 主键 为不可见索引:

mysql> create  table t3(id int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t3 add primary key pk_t3(id)  invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible

primary key 是必须可见的， 不能设置为不可见索引。

在生产环境中 也可以利用隐藏索引进行 SQL 语句的性能测试，或者对索引进行逻辑删除，索引的灰度发布测试，以及软删除索引等。

关于MySQL8.0 索引优化invisible index详情的文章就介绍至此，更多相关MySQL索引优化内容请搜索编程宝库以前的文章，希望以后支持编程宝库！

 一、先描述一下问题吧如下创建表时候报错了CREATE TABLE `xxx` (  `id` bigint(20) NOT NULL AUTO_INCREMENT, ...