前言
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: 0mysql> -- 创建 一个隐藏索引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: NULL2 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: refpossible_keys: idx_i key: idx_i key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL1 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where1 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=on1 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=on1 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: refpossible_keys: idx_j key: idx_j key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL1 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: 0mysql> 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: NULL1 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
