MySQL8.0 索引优化invisible index详情

前言

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

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

-- 创建测试表 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 为不可见索引 ，即默认优化器不会使用到该索引的。

索引使用情况测试

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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)

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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)

修改索引的可见性

?

1 2 3 4 5 6 7

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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

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

?

1 2 3 4 5

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索引优化内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/u010339879/article/details/127043431