问题场景
我们知道，索引太多会导致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>
547
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
