欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL 8 新特性之Invisible Indexes

程序员文章站 2023-08-20 17:20:45
背景 索引是把双刃剑,在提升查询速度的同时会减慢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_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。

总结

以上所述是小编给大家介绍的mysql 8 新特性之invisible indexes ,希望对大家有所帮助