MySQL 8 新特性之Invisible Indexes

背景

索引是把雙刃劍，在提升查詢速度的同時會減慢DML的操作。畢竟，索引的維護需要一定的成本。所以，對於索引，要加上該加的，刪除無用的。前者是加法，後者是減法。但在實際工作中，大家似乎更熱衷於前者，而很少進行後者。究其原因，在於後者，難。難的不是操作本身，而是如何確認一個索引是無用的。

 

如何確認無用索引

在不可見索引出現之前，大家可以透過sys.schema_unused_indexes來確定無用索引。在MySQL 5.6中，即使沒有sys庫，也可透過該檢視的基表來進行查詢。

 

mysql> show create table sys.schema_unused_indexesG*************************** 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_ci1 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的值，如，

 

mysql> show create table slowtech.t1G*************************** 1. row ***************************
      Table: t1Create 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_ci1 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)

 

不可見索引的常見操作

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;

 

如何檢視哪些索引不可見

mysql> select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no';+--------------+------------+------------+-------------+------------+| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | IS_VISIBLE |+--------------+------------+------------+-------------+------------+| slowtech    | t1        | idx_name  | name        | NO        |+--------------+------------+------------+-------------+------------+1 row in set (0.00 sec)

 

注意

1. 主鍵索引不可被設定為invisible。