背景
索引是把双刃剑, 在提升查询速度的同时会减慢 DML 的操作. 毕竟, 索引的维护需要一定的成本. 所以, 对于索引, 要加上该加的, 删除无用的. 前者是加法, 后者是减法. 但在实际工作中, 大家似乎更热衷于前者, 而很少进行后者. 究其原因, 在于后者, 难. 难的不是操作本身, 而是如何确认一个索引是无用的.
如何确认无用索引
在不可见索引出现之前, 大家可以通过 sys.schema_unused_indexes 来确定无用索引. 在 MySQL 5.6 中, 即使没有 sys 库, 也可通过该视图的基表来进行查询.
- 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 的值, 如,
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)
不可见索引的常见操作
- 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.
来源: http://www.linuxidc.com/Linux/2018-05/152501.htm