对mysql索引的理解（详解MySQL 8.0 之不可见索引）

对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