当前位置:数据库 > > 正文

对mysql索引的理解(详解MySQL 8.0 之不可见索引)

时间:2022-04-02 14:18:50类别:数据库

对mysql索引的理解

详解MySQL 8.0 之不可见索引

MySQL 8.0 从第一版release 到现在已经走过了4个年头了,8.0版本在功能和代码上做了相当大的改进和重构。和DBA圈子里的朋友交流,大部分还是5.6 ,5.7的版本,少量的走的比较靠前采用了MySQL 8.0。为了紧追数据库发展的步伐,能够尽早享受技术红利,我们准备将MySQL 8.0引入到有赞的数据库体系。

落地之前 我们会对MySQL 8.0的新特性和功能,配置参数,升级方式,兼容性等等做一系列的学习和测试。以后陆陆续续会发布文章出来。本文算是MySQL 8.0新特性学习的第一篇吧,聊聊 不可见索引。

不可见索引

不可见索引中的不可见是针对优化器而言的,优化器在做执行计划分析的时候(默认情况下)是会忽略设置了不可见属性的索引。

为什么是默认情况下,如果 optimizer_switch设置use_invisible_indexes=ON 是可以继续使用不可见索引。

话不多说,我们先测试几个例子

如何设置不可见索引

我们可以通过带上关键字VISIBLE|INVISIBLE的create table,create index,alter table 设置索引的可见性。

  • ?
  • 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
  • mysql> create table t1 (i int,
  •    > j int,
  •    > k int,
  •    > index i_idx (i) invisible) engine=innodb;
  • Query OK, 0 rows affected (0.41 sec)
  •  
  • mysql> create index j_idx on t1 (j) invisible;
  • Query OK, 0 rows affected (0.19 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  •  
  • mysql> alter table t1 add index k_idx (k) invisible;
  • Query OK, 0 rows affected (0.10 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  •  
  • mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t1';
  • +------------+------------+
  • | INDEX_NAME | IS_VISIBLE |
  • +------------+------------+
  • | i_idx   | NO     |
  • | j_idx   | NO     |
  • | k_idx   | NO     |
  • +------------+------------+
  • 3 rows in set (0.01 sec)
  •  
  • mysql> alter table t1 alter index i_idx visible;
  • Query OK, 0 rows affected (0.06 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  •  
  • mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t1';
  • +------------+------------+
  • | INDEX_NAME | IS_VISIBLE |
  • +------------+------------+
  • | i_idx   | YES    |
  • | j_idx   | NO     |
  • | k_idx   | NO     |
  • +------------+------------+
  • 3 rows in set (0.00 sec)
  • 不可见索引的作用

    面对历史遗留的一大堆索引,经过数轮新老交替开发和DBA估计都不敢直接将索引删除,尤其是遇到比如大于100G的大表,直接删除索引会提升数据库的稳定性风险。

    有了不可见索引的特性,DBA可以一边设置索引为不可见,一边观察数据库的慢查询记录和thread running 状态。如果数据库长时间没有相关慢查询 ,thread_running比较稳定,就可以下线该索引。反之,则可以迅速将索引设置为可见,恢复业务访问。

    Invisible Indexes 是 server 层的特性,和引擎无关,因此所有引擎(InnoDB, TokuDB, MyISAM, etc.)都可以使用。

    设置完不可见索引,执行计划无法使用索引

  • ?
  • 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
  • mysql> show create table t2 \G
  • *************************** 1. row ***************************
  •     Table: t2
  • Create Table: CREATE TABLE `t2` (
  •  `i` int NOT NULL AUTO_INCREMENT,
  •  `j` int NOT NULL,
  •  PRIMARY KEY (`i`),
  •  UNIQUE KEY `j_idx` (`j`) /*!80000 INVISIBLE */
  • ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  • 1 row in set (0.01 sec)
  • mysql> insert into t2(j) values(1),(2),(3),(4),(5),(6),(7);
  • Query OK, 7 rows affected (0.04 sec)
  • Records: 7 Duplicates: 0 Warnings: 0
  •  
  •  
  • mysql> explain select * from t2 where j=3\G
  • *************************** 1. row ***************************
  •       id: 1
  •  select_type: SIMPLE
  •     table: t2
  •   partitions: NULL
  •      type: ALL
  • possible_keys: NULL
  •      key: NULL
  •    key_len: NULL
  •      ref: NULL
  •      rows: 7
  •    filtered: 14.29
  •     Extra: Using where
  • 1 row in set, 1 warning (0.01 sec)
  •  
  • mysql> alter table t2 alter index j_idx visible;
  • Query OK, 0 rows affected (0.08 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  •  
  • mysql> explain select * from t2 where j=3\G
  • *************************** 1. row ***************************
  •       id: 1
  •  select_type: SIMPLE
  •     table: t2
  •   partitions: NULL
  •      type: const
  • possible_keys: j_idx
  •      key: j_idx
  •    key_len: 4
  •      ref: const
  •      rows: 1
  •    filtered: 100.00
  •     Extra: Using index
  • 1 row in set, 1 warning (0.01 sec)
  • 使用不可见索引的注意事项

    The feature applies to indexes other than primary keys (either explicit or implicit).

    不可见索引是针对非主键索引的。主键不能设置为不可见,这里的 主键 包括显式的主键或者隐式主键(不存在主键时,被提升为主键的唯一索引) ,我们可以用下面的例子展示该规则。

  • ?
  • 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
  • mysql> create table t2 (
  •    >i int not null,
  •    >j int not null ,
  •    >unique j_idx (j)
  •    >) ENGINE = InnoDB;
  • Query OK, 0 rows affected (0.16 sec)
  •  
  • mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t2';
  • +------------+------------+
  • | INDEX_NAME | IS_VISIBLE |
  • +------------+------------+
  • | j_idx   | YES    |
  • +------------+------------+
  • 1 row in set (0.00 sec)
  •  
  • ### 没有主键的情况下,唯一键被当做隐式主键,不能设置 不可见。
  • mysql> alter table t2 alter index j_idx invisible;
  • ERROR 3522 (HY000): A primary key index cannot be invisible
  • mysql>
  • mysql> alter table t2 add primary key (i);
  • Query OK, 0 rows affected (0.44 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  •  
  • mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t2';
  • +------------+------------+
  • | INDEX_NAME | IS_VISIBLE |
  • +------------+------------+
  • | j_idx   | YES    |
  • | PRIMARY  | YES    |
  • +------------+------------+
  • 2 rows in set (0.01 sec)
  •  
  • mysql> alter table t2 alter index j_idx invisible;
  • Query OK, 0 rows affected (0.04 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  •  
  • mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t2';
  • +------------+------------+
  • | INDEX_NAME | IS_VISIBLE |
  • +------------+------------+
  • | j_idx   | NO     |
  • | PRIMARY  | YES    |
  • +------------+------------+
  • 2 rows in set (0.01 sec)
  • force /ignore index(index_name) 不能访问不可见索引,否则报错。

  • ?
  • 1
  • 2
  • mysql> select * from t2 force index(j_idx) where j=3;
  • ERROR 1176 (42000): Key 'j_idx' doesn't exist in table 't2'
  • 设置索引为不可见需要获取MDL锁,遇到长事务会引发数据库抖动

    唯一索引被设置为不可见,不代表索引本身唯一性的约束失效

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • mysql> select * from t2;
  • +---+----+
  • | i | j |
  • +---+----+
  • | 1 | 1 |
  • | 2 | 2 |
  • | 3 | 3 |
  • | 4 | 4 |
  • | 5 | 5 |
  • | 6 | 6 |
  • | 7 | 7 |
  • | 8 | 11 |
  • +---+----+
  • 8 rows in set (0.00 sec)
  • mysql> insert into t2(j) values(11);
  • ERROR 1062 (23000): Duplicate entry '11' for key 't2.j_idx'
  • 小结

    其实没啥说的,祝大家用的愉快。

    -The End-

    以上就是详解MySQL 8.0 之不可见索引的详细内容,更多关于MySQL 8.0 不可见索引的资料请关注开心学习网其它相关文章!

    原文链接:https://cloud.tencent.com/developer/article/1638494

    上一篇下一篇

    猜您喜欢

    热门推荐