MYSQL8隐藏索引

问题场景

    我们知道，索引太多会导致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>