详解MySQL 8.0 之不可见索引

2023-07-13 05:23:03 122

言

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

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

不可见索引

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

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

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

如何设置不可见索引

我们可以通过带上关键字VISIBLE|INVISIBLE的createtable,createindex,altertable设置索引的可见性。

mysql>createtablet1(iint,
>jint,
>kint,
>indexi_idx(i)invisible)engine=innodb;
QueryOK,0rowsaffected(0.41sec)

mysql>createindexj_idxont1(j)invisible;
QueryOK,0rowsaffected(0.19sec)
Records:0Duplicates:0Warnings:0

mysql>altertablet1addindexk_idx(k)invisible;
QueryOK,0rowsaffected(0.10sec)
Records:0Duplicates:0Warnings:0

mysql>selectindex_name,is_visiblefrominformation_schema.statisticswheretable_schema='test'andtable_name='t1';
+------------+------------+
|INDEX_NAME|IS_VISIBLE|
+------------+------------+
|i_idx|NO|
|j_idx|NO|
|k_idx|NO|
+------------+------------+
3rowsinset(0.01sec)

mysql>altertablet1alterindexi_idxvisible;
QueryOK,0rowsaffected(0.06sec)
Records:0Duplicates:0Warnings:0

mysql>selectindex_name,is_visiblefrominformation_schema.statisticswheretable_schema='test'andtable_name='t1';
+------------+------------+
|INDEX_NAME|IS_VISIBLE|
+------------+------------+
|i_idx|YES|
|j_idx|NO|
|k_idx|NO|
+------------+------------+
3rowsinset(0.00sec)

不可见索引的作用

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

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

InvisibleIndexes是server层的特性，和引擎无关，因此所有引擎（InnoDB,TokuDB,MyISAM,etc.）都可以使用。

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

mysql>showcreatetablet2\G
***************************1.row***************************
Table:t2
CreateTable:CREATETABLE`t2`(
`i`intNOTNULLAUTO_INCREMENT,
`j`intNOTNULL,
PRIMARYKEY(`i`),
UNIQUEKEY`j_idx`(`j`)/*!80000INVISIBLE*/
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci
1rowinset(0.01sec)
mysql>insertintot2(j)values(1),(2),(3),(4),(5),(6),(7);
QueryOK,7rowsaffected(0.04sec)
Records:7Duplicates:0Warnings:0


mysql>explainselect*fromt2wherej=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:Usingwhere
1rowinset,1warning(0.01sec)

mysql>altertablet2alterindexj_idxvisible;
QueryOK,0rowsaffected(0.08sec)
Records:0Duplicates:0Warnings:0

mysql>explainselect*fromt2wherej=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:Usingindex
1rowinset,1warning(0.01sec)

使用不可见索引的注意事项

Thefeatureappliestoindexesotherthanprimarykeys(eitherexplicitorimplicit).

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

mysql>createtablet2(
>iintnotnull,
>jintnotnull,
>uniquej_idx(j)
>)ENGINE=InnoDB;
QueryOK,0rowsaffected(0.16sec)

mysql>selectindex_name,is_visiblefrominformation_schema.statisticswheretable_schema='test'andtable_name='t2';
+------------+------------+
|INDEX_NAME|IS_VISIBLE|
+------------+------------+
|j_idx|YES|
+------------+------------+
1rowinset(0.00sec)

###没有主键的情况下，唯一键被当做隐式主键，不能设置不可见。
mysql>altertablet2alterindexj_idxinvisible;
ERROR3522(HY000):Aprimarykeyindexcannotbeinvisible
mysql>
mysql>altertablet2addprimarykey(i);
QueryOK,0rowsaffected(0.44sec)
Records:0Duplicates:0Warnings:0

mysql>selectindex_name,is_visiblefrominformation_schema.statisticswheretable_schema='test'andtable_name='t2';
+------------+------------+
|INDEX_NAME|IS_VISIBLE|
+------------+------------+
|j_idx|YES|
|PRIMARY|YES|
+------------+------------+
2rowsinset(0.01sec)

mysql>altertablet2alterindexj_idxinvisible;
QueryOK,0rowsaffected(0.04sec)
Records:0Duplicates:0Warnings:0

mysql>selectindex_name,is_visiblefrominformation_schema.statisticswheretable_schema='test'andtable_name='t2';
+------------+------------+
|INDEX_NAME|IS_VISIBLE|
+------------+------------+
|j_idx|NO|
|PRIMARY|YES|
+------------+------------+
2rowsinset(0.01sec)

force/ignoreindex(index_name)不能访问不可见索引，否则报错。

mysql>select*fromt2forceindex(j_idx)wherej=3;
ERROR1176(42000):Key'j_idx'doesn'texistintable't2'

设置索引为不可见需要获取MDL锁，遇到长事务会引发数据库抖动

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

mysql>select*fromt2;
+---+----+
|i|j|
+---+----+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
|6|6|
|7|7|
|8|11|
+---+----+
8rowsinset(0.00sec)
mysql>insertintot2(j)values(11);
ERROR1062(23000):Duplicateentry'11'forkey't2.j_idx'

小结

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

-TheEnd-

以上就是详解MySQL8.0之不可见索引的详细内容，更多关于MySQL8.0不可见索引的资料请关注毛票票其它相关文章！

详解MySQL 8.0 之不可见索引

热门推荐

随机推荐