问题场景
我们知道,索引太多会导致UPDATE/DELETE/INSERT的时候,引擎需要更新索引信息,产生额外的开销; 从而影响数据库性能; 所以需要清理无效索引;
但是表是数年前建的,索引基本都不是自己添加的,无法准确判定是否可以删除,万一删错了,导致大的慢查询,引起事故就得不偿失了,表比较小的话,还可以重新添加,表的数据量如果非常大,新增索引的耗时就会非常大,到时候可能就只剩下跑路的份儿了; 在老的库中,经常会发现许多表索引空间比表空间占用还大的情况;
功能介绍
于是乎:MYSQL 8.0 引入了隐藏索引功能;
可以设置索引INVISIBLE, 借助该功能来进行灰度删除; 当索引设置为INVISIBLE的时候,该索引对优化器不可见(默认情况下),并且及时表非常大的情况下,执行速度也非常快捷,且就地操作;
如果打算删除某个索引,却又不十分确定
1, 可以先设置为INVISIBLE, 查看涉及索引的相关查询执行计划
2, 分析对比慢日志;是否出现了新的慢查询;
3, 观察几天确认没有影响的情况下进行删除操作;
注意:1,隐藏索引只针对辅助索引,无法对主键生效;
2,虽然performance_schema.table_io_waits_summary_by_index_usage表可以查看索引是否使用,但并不十分准确;还是需要慎重判断;
3, 索引的INVISIBLE,并不影响索引树的维护,例如UPDATE的时候,依然会动态维护更新索引; 唯一索引INVISIBLE的时候,该列依然必须要保持唯一;
设置方法
1, CREATE TABLE
创建索引的时候,可以设置VISIBLE(默认值)/INVISIBLE字段来指定索引是否可见;
create table dragonball(
id int auto_increment comment 'main key',
name varchar(20) default '' comment 'user name',
sex varchar(1) default '' comment '男/女',
skill varchar(10) default '' comment 'QWERDF',
primary key(id),
key idx_name(name) invisible,
key id_sex(sex)
)engine = innodb charset='utf8' comment '用户表';
2,alter table dragonball add key idx_skill(skill) invisible,alter index id_sex invisible;
ALTER 添加索引的时候可以指定,或者修改现有索引为INVISIBLE;
3, create index idx_skill on dragonball (skill) invisible;
创建隐藏索引;
查看是否有隐藏索引
1, SHOW CREATE TABLE:
隐藏索引后面有注释: /*!80000 INVISIBLE */
mysql> show create table dragonball\G
*************************** 1. row ***************************
Table: dragonball
Create Table: CREATE TABLE `dragonball` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'main key',
`name` varchar(20) DEFAULT '' COMMENT 'user name',
`sex` varchar(1) DEFAULT '' COMMENT '男/女',
`skill` varchar(10) DEFAULT '' COMMENT 'QWERDF',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) /*!80000 INVISIBLE */,
KEY `id_sex` (`sex`) /*!80000 INVISIBLE */,
KEY `idx_skill` (`skill`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
1 row in set (0.00 sec)
2, show index from dragonball;
VISIBLE字段为YES,即可见,NO即不可见;
mysql> show index from dragonball;
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dragonball | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| dragonball | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL |
| dragonball | 1 | id_sex | 1 | sex | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL |
| dragonball | 1 | idx_skill | 1 | skill | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
3, information_schema.STATISTICS.IS_VISIBLE 字段可以查看确认是否可见;
mysql> select TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,IS_VISIBLE from STATISTICS where table_schema='slower' and table_name='dragonball';
+--------------+------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | IS_VISIBLE |
+--------------+------------+------------+------------+
| slower | dragonball | id_sex | NO |
| slower | dragonball | idx_name | NO |
| slower | dragonball | idx_skill | NO |
| slower | dragonball | PRIMARY | YES |
+--------------+------------+------------+------------+
设置隐藏索引是否对执行计划生效:
optimizer_switch 变量下的use_invisible_indexes 开关,可以控制隐藏索引是否对查询执行计划生效;
如果是OFF(默认值),如果索引被设置为INVISIBLE,则EXPLAIN/DESC 查看执行计划的时候,不命中该索引;
如果是ON的时候, 索引的是否隐藏都不影响执行计划命中索引;
举例:
#查看表结构:position_index和 sub_position_index被设置为INVISIBLE
mysql> show create table release_article\G
*************************** 1. row ***************************
Table: release_article
Create Table: CREATE TABLE `release_article` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '文章ID',
`title` varchar(300) DEFAULT NULL COMMENT '文章标题',
`type` int(11) DEFAULT NULL COMMENT '文章分类',
`status` int(11) DEFAULT NULL COMMENT '文章状态',
`index_image` varchar(300) DEFAULT NULL COMMENT '首图链接',
`createtime` datetime DEFAULT NULL COMMENT '创建时间',
`publishtime` datetime DEFAULT NULL COMMENT '第一次发布时间',
`top` tinyint(4) DEFAULT '0' COMMENT '是否置顶,1为置顶,0为非置顶',
`top_order` tinyint(4) DEFAULT '4' COMMENT '置顶顺序,最多3篇置顶',
`top_start_time` datetime DEFAULT NULL COMMENT '置顶开始时间',
`top_end_time` datetime DEFAULT NULL COMMENT '置顶结束时间',
`recommend` tinyint(4) DEFAULT '0' COMMENT '是否推荐,1为推荐,0为非推荐',
`recommend_top` tinyint(4) DEFAULT '0' COMMENT '是否推荐置顶,1为推荐置顶,0为非推荐置顶',
`recommend_top_order` tinyint(4) DEFAULT '4' COMMENT '推荐池文章置顶顺序,最多3篇',
`recommend_top_start_time` datetime DEFAULT NULL COMMENT '推荐置顶开始时间',
`recommend_top_end_time` datetime DEFAULT NULL COMMENT '推荐置顶结束时间',
`is_choice` tinyint(4) DEFAULT '0' COMMENT '是否精选0 否 1是',
`author_id` varchar(20) DEFAULT NULL COMMENT '发布者pin',
`author_name` varchar(100) DEFAULT NULL COMMENT '发布者名',
`source` int(1) DEFAULT '0' COMMENT '文章来源',
`off_reason` varchar(300) DEFAULT NULL COMMENT '下线原因',
`editor_letters` varchar(300) DEFAULT NULL COMMENT '编按',
`operator` varchar(50) DEFAULT NULL COMMENT '操作者',
`summary` varchar(2000) DEFAULT NULL,
`audittime` datetime DEFAULT NULL COMMENT '审核时间',
`audit_fail_reason` varchar(300) DEFAULT NULL COMMENT '审核失败原因',
`tags` varchar(500) DEFAULT NULL COMMENT '标签id列表',
`banner` varchar(4000) DEFAULT NULL COMMENT '文章末banner图',
`to_audit` datetime DEFAULT NULL COMMENT '投稿时间提交审核时间',
`video_flag` int(2) DEFAULT '0' COMMENT '视频标识(0:无,1:有)',
`last_modify_time` datetime DEFAULT NULL COMMENT '最后一次上线时间',
`position` tinyint(4) DEFAULT '0' COMMENT '子渠道标识,参照cms_discovery_release_channel表',
`sub_position` int(11) DEFAULT NULL COMMENT '子渠道标识,参照cms_discovery_release_sub_channel表',
`sub_title` varchar(512) DEFAULT NULL COMMENT '副标题',
`introduction` varchar(100) DEFAULT NULL COMMENT '导语',
`style` tinyint(2) DEFAULT '0' COMMENT '文本样式 0 :普通文本,1:攻略模板,2:视频购',
`skus` varchar(1000) DEFAULT NULL COMMENT '文章内sku集合',
`sku_num` tinyint(4) DEFAULT NULL COMMENT '文章内sku数量',
`bi_list_show` tinyint(4) DEFAULT '1' COMMENT 'bi,1,0',
`one_category` varchar(20) DEFAULT NULL COMMENT '一级品类',
`two_category` varchar(20) DEFAULT NULL COMMENT '二级品类',
`three_category` varchar(20) DEFAULT NULL COMMENT '三级品类',
`pic_num` tinyint(4) DEFAULT '0' COMMENT '图集图片数目',
`probation` tinyint(1) DEFAULT '0',
`private_status` int(11) DEFAULT NULL COMMENT '私域状态,0下线,1上线',
`extend_value` varchar(1000) DEFAULT '' COMMENT '文章扩展属性信息',
`content_type` int(5) DEFAULT NULL COMMENT '文章内容分类',
`index_video` bigint(20) DEFAULT NULL COMMENT '封面视频或主视频',
`preview_video` bigint(20) DEFAULT NULL COMMENT '预览视频',
PRIMARY KEY (`id`),
KEY `article_type_index` (`type`),
KEY `article_status_index` (`status`),
KEY `article_recommend_index` (`recommend`),
KEY `article_source_index` (`source`),
KEY `article_title_index` (`title`(255)),
KEY `position_index` (`position`) USING BTREE /*!80000 INVISIBLE */,
KEY `sub_position_index` (`sub_position`) USING BTREE /*!80000 INVISIBLE */,
KEY `idx_three_category` (`three_category`),
KEY `idx_tags` (`tags`(255)),
KEY `idx_publishtime` (`publishtime`),
KEY `idx_last_modify_time` (`last_modify_time`),
KEY `idx_author_id_status_modify_time` (`author_id`,`status`,`last_modify_time`),
KEY `idx_top` (`top`),
KEY `idx_recommend_top` (`recommend_top`),
KEY `idx_style_createtime` (`style`,`createtime`),
KEY `idx_sub_position` (`sub_position`,`bi_list_show`,`status`),
KEY `idx_style_to_audit` (`style`,`to_audit`),
KEY `IDX_SUB_POSITION_IS_CHOICE` (`sub_position`,`is_choice`)
) ENGINE=InnoDB AUTO_INCREMENT=234366805 DEFAULT CHARSET=utf8
#执行前查看use_invisible_indexes=off,默认值
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on
1 row in set (0.00 sec)
#查看执行计划,命中的索引是:idx_author_id_status_modify_time
mysql> desc SELECT count(1) FROM release_article ar WHERE ar.status != -1 and ar.author_id = '18316' AND position = 12 AND sub_position = 61\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ar
partitions: NULL
type: range
possible_keys: article_status_index,idx_author_id_status_modify_time,idx_sub_position,IDX_SUB_POSITION_IS_CHOICE
key: idx_author_id_status_modify_time
key_len: 68
ref: NULL
rows: 2719
filtered: 0.00
Extra: Using index condition; Using where
1 row in set, 1 warning (0.01 sec)
#修改变量:use_invisible_indexes=on;
mysql> set optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on'
-> ;
Query OK, 0 rows affected (0.00 sec)
#再次查看执行计划,此时看到命中的索引是:position_index,sub_position_index
mysql> desc SELECT count(1) FROM release_article ar WHERE ar.status != -1 and ar.author_id = '18316' AND position = 12 AND sub_position = 61\G *************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ar
partitions: NULL
type: index_merge
possible_keys: article_status_index,position_index,sub_position_index,idx_author_id_status_modify_time,idx_sub_position,IDX_SUB_POSITION_IS_CHOICE
key: position_index,sub_position_index
key_len: 2,5
ref: NULL
rows: 492
filtered: 5.00
Extra: Using intersect(position_index,sub_position_index); Using where
1 row in set, 1 warning (0.00 sec)
mysql>