MySQL 8 新特性之Invisible Indexes
背景 索引是把双刃剑，在提升查询速度的同时会减慢DML的操作。毕竟，索引的维护需要一定的成本。所以，对于索引，要加上该加的，删除无用的。前者是加法，后者是减法。但在实际
背景
索引是把双刃剑，在提升查询速度的同时会减慢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_NAMEcharacter_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。
总结
以上所述是小编给大家介绍的MySQL 8 新特性之Invisible Indexes ，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！
