MySQL 8 新特性之Invisible Indexes

吾爱主题阅读：163 2024-04-05 14:00:53 评论：0

背景

索引是把双刃剑，在提升查询速度的同时会减慢DML的操作。毕竟，索引的维护需要一定的成本。所以，对于索引，要加上该加的，删除无用的。前者是加法，后者是减法。但在实际工作中，大家似乎更热衷于前者，而很少进行后者。究其原因，在于后者，难。难的不是操作本身，而是如何确认一个索引是无用的。

如何确认无用索引

在不可见索引出现之前，大家可以通过sys.schema_unused_indexes来确定无用索引。在MySQL 5.6中，即使没有sys库，也可通过该视图的基表来进行查询。

1 2 3 4 5 6 7 mysql> show create table sys.schema_unused_indexes\G *************************** 1. row *************************** View : schema_unused_indexes Create View : CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_unused_indexes` ( `object_schema`,`object_name`,`index_name`) AS select `t`.`OBJECT_SCHEMA` AS `object_schema`,`t`.`OBJECT_NAME` AS `object_name`,`t`.`INDEX_NAME` AS `index_name` from (`performance_schema`.`table_io_waits_summary_by_index_usage` `t` join `information_schema`.` STATISTICS ` `s` on (((`t`.`OBJECT_SCHEMA` = convert (`s`.`TABLE_SCHEMA` using utf8mb4)) and (`t`.`OBJECT_NAME` = convert (`s`.`TABLE_NAME` using utf8mb4)) and ( convert (`t`.`INDEX_NAME` using utf8) = `s`.`INDEX_NAME`)))) where ((`t`.`INDEX_NAME` is not null ) and (`t`.`COUNT_STAR` = 0) and (`t`.`OBJECT_SCHEMA` <> 'mysql' ) and (`t`.`INDEX_NAME` <> 'PRIMARY' ) and (`s`.`NON_UNIQUE` = 1) and (`s`.`SEQ_IN_INDEX` = 1)) order by `t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME`character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci 1 row in set , 1 warning (0.00 sec)

但这种方式也有不足，

1. 如果实例发生重启，performance_schema中的数据就会清零。

2. 如果基于上面的查询删除了索引，查询性能突然变差，怎么办？

不可见索引的出现，可有效弥补上述不足。将index设置为invisible，会导致优化器在选择执行计划时，自动忽略该索引，即便使用了FORCE INDEX。

当然，这个是由optimizer_switch变量中use_invisible_indexes选项决定的，默认为off。如果想看一个查询在索引调整前后执行计划的差别，可在会话级别调整use_invisible_indexes的值，如，

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 mysql> show create table slowtech.t1\G *************************** 1 . row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int ( 11 ) NOT NULL, `name` varchar( 10 ) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) /*!80000 INVISIBLE */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set ( 0.00 sec) mysql> explain select * from slowtech.t1 where name= 'a' ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning ( 0.00 sec) mysql> set session optimizer_switch= "use_invisible_indexes=on" ; Query OK, 0 rows affected ( 0.00 sec) mysql> explain select * from slowtech.t1 where name= 'a' ; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_name | idx_name | 43 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning ( 0.00 sec)

不可见索引的常见操作

1 2 3 create table t1(id int primary key , name varchar (10), index idx_name ( name ) invisible); alter table t1 alter index idx_name visible; alter table t1 alter index idx_name invisible;