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字段来指定索引是否可见; 

 
  1. create table dragonball(

  2. id int auto_increment comment 'main key',

  3. name varchar(20) default '' comment 'user name',

  4. sex varchar(1) default '' comment '男/女',

  5. skill varchar(10) default '' comment 'QWERDF',

  6. primary key(id),

  7. key idx_name(name) invisible,

  8. key id_sex(sex)

  9. )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 */

 
  1. mysql> show create table dragonball\G

  2. *************************** 1. row ***************************

  3. Table: dragonball

  4. Create Table: CREATE TABLE `dragonball` (

  5. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'main key',

  6. `name` varchar(20) DEFAULT '' COMMENT 'user name',

  7. `sex` varchar(1) DEFAULT '' COMMENT '男/女',

  8. `skill` varchar(10) DEFAULT '' COMMENT 'QWERDF',

  9. PRIMARY KEY (`id`),

  10. KEY `idx_name` (`name`) /*!80000 INVISIBLE */,

  11. KEY `id_sex` (`sex`) /*!80000 INVISIBLE */,

  12. KEY `idx_skill` (`skill`) /*!80000 INVISIBLE */

  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'

  14. 1 row in set (0.00 sec)

2,  show index from dragonball; 

     VISIBLE字段为YES,即可见,NO即不可见;

 
  1. mysql> show index from dragonball;

  2. +------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

  3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

  4. +------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

  5. | dragonball | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |

  6. | dragonball | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL |

  7. | dragonball | 1 | id_sex | 1 | sex | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL |

  8. | dragonball | 1 | idx_skill | 1 | skill | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL |

  9. +------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

  10. 4 rows in set (0.01 sec)

3,  information_schema.STATISTICS.IS_VISIBLE 字段可以查看确认是否可见;

 
  1. mysql> select TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,IS_VISIBLE from STATISTICS where table_schema='slower' and table_name='dragonball';

  2. +--------------+------------+------------+------------+

  3. | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | IS_VISIBLE |

  4. +--------------+------------+------------+------------+

  5. | slower | dragonball | id_sex | NO |

  6. | slower | dragonball | idx_name | NO |

  7. | slower | dragonball | idx_skill | NO |

  8. | slower | dragonball | PRIMARY | YES |

  9. +--------------+------------+------------+------------+

设置隐藏索引是否对执行计划生效:

optimizer_switch 变量下的use_invisible_indexes 开关,可以控制隐藏索引是否对查询执行计划生效; 

如果是OFF(默认值),如果索引被设置为INVISIBLE,则EXPLAIN/DESC 查看执行计划的时候,不命中该索引; 

如果是ON的时候, 索引的是否隐藏都不影响执行计划命中索引; 

举例:

 
  1. #查看表结构:position_index和 sub_position_index被设置为INVISIBLE

  2. mysql> show create table release_article\G

  3. *************************** 1. row ***************************

  4. Table: release_article

  5. Create Table: CREATE TABLE `release_article` (

  6. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '文章ID',

  7. `title` varchar(300) DEFAULT NULL COMMENT '文章标题',

  8. `type` int(11) DEFAULT NULL COMMENT '文章分类',

  9. `status` int(11) DEFAULT NULL COMMENT '文章状态',

  10. `index_image` varchar(300) DEFAULT NULL COMMENT '首图链接',

  11. `createtime` datetime DEFAULT NULL COMMENT '创建时间',

  12. `publishtime` datetime DEFAULT NULL COMMENT '第一次发布时间',

  13. `top` tinyint(4) DEFAULT '0' COMMENT '是否置顶,1为置顶,0为非置顶',

  14. `top_order` tinyint(4) DEFAULT '4' COMMENT '置顶顺序,最多3篇置顶',

  15. `top_start_time` datetime DEFAULT NULL COMMENT '置顶开始时间',

  16. `top_end_time` datetime DEFAULT NULL COMMENT '置顶结束时间',

  17. `recommend` tinyint(4) DEFAULT '0' COMMENT '是否推荐,1为推荐,0为非推荐',

  18. `recommend_top` tinyint(4) DEFAULT '0' COMMENT '是否推荐置顶,1为推荐置顶,0为非推荐置顶',

  19. `recommend_top_order` tinyint(4) DEFAULT '4' COMMENT '推荐池文章置顶顺序,最多3篇',

  20. `recommend_top_start_time` datetime DEFAULT NULL COMMENT '推荐置顶开始时间',

  21. `recommend_top_end_time` datetime DEFAULT NULL COMMENT '推荐置顶结束时间',

  22. `is_choice` tinyint(4) DEFAULT '0' COMMENT '是否精选0 否 1是',

  23. `author_id` varchar(20) DEFAULT NULL COMMENT '发布者pin',

  24. `author_name` varchar(100) DEFAULT NULL COMMENT '发布者名',

  25. `source` int(1) DEFAULT '0' COMMENT '文章来源',

  26. `off_reason` varchar(300) DEFAULT NULL COMMENT '下线原因',

  27. `editor_letters` varchar(300) DEFAULT NULL COMMENT '编按',

  28. `operator` varchar(50) DEFAULT NULL COMMENT '操作者',

  29. `summary` varchar(2000) DEFAULT NULL,

  30. `audittime` datetime DEFAULT NULL COMMENT '审核时间',

  31. `audit_fail_reason` varchar(300) DEFAULT NULL COMMENT '审核失败原因',

  32. `tags` varchar(500) DEFAULT NULL COMMENT '标签id列表',

  33. `banner` varchar(4000) DEFAULT NULL COMMENT '文章末banner图',

  34. `to_audit` datetime DEFAULT NULL COMMENT '投稿时间提交审核时间',

  35. `video_flag` int(2) DEFAULT '0' COMMENT '视频标识(0:无,1:有)',

  36. `last_modify_time` datetime DEFAULT NULL COMMENT '最后一次上线时间',

  37. `position` tinyint(4) DEFAULT '0' COMMENT '子渠道标识,参照cms_discovery_release_channel表',

  38. `sub_position` int(11) DEFAULT NULL COMMENT '子渠道标识,参照cms_discovery_release_sub_channel表',

  39. `sub_title` varchar(512) DEFAULT NULL COMMENT '副标题',

  40. `introduction` varchar(100) DEFAULT NULL COMMENT '导语',

  41. `style` tinyint(2) DEFAULT '0' COMMENT '文本样式 0 :普通文本,1:攻略模板,2:视频购',

  42. `skus` varchar(1000) DEFAULT NULL COMMENT '文章内sku集合',

  43. `sku_num` tinyint(4) DEFAULT NULL COMMENT '文章内sku数量',

  44. `bi_list_show` tinyint(4) DEFAULT '1' COMMENT 'bi,1,0',

  45. `one_category` varchar(20) DEFAULT NULL COMMENT '一级品类',

  46. `two_category` varchar(20) DEFAULT NULL COMMENT '二级品类',

  47. `three_category` varchar(20) DEFAULT NULL COMMENT '三级品类',

  48. `pic_num` tinyint(4) DEFAULT '0' COMMENT '图集图片数目',

  49. `probation` tinyint(1) DEFAULT '0',

  50. `private_status` int(11) DEFAULT NULL COMMENT '私域状态,0下线,1上线',

  51. `extend_value` varchar(1000) DEFAULT '' COMMENT '文章扩展属性信息',

  52. `content_type` int(5) DEFAULT NULL COMMENT '文章内容分类',

  53. `index_video` bigint(20) DEFAULT NULL COMMENT '封面视频或主视频',

  54. `preview_video` bigint(20) DEFAULT NULL COMMENT '预览视频',

  55. PRIMARY KEY (`id`),

  56. KEY `article_type_index` (`type`),

  57. KEY `article_status_index` (`status`),

  58. KEY `article_recommend_index` (`recommend`),

  59. KEY `article_source_index` (`source`),

  60. KEY `article_title_index` (`title`(255)),

  61. KEY `position_index` (`position`) USING BTREE /*!80000 INVISIBLE */,

  62. KEY `sub_position_index` (`sub_position`) USING BTREE /*!80000 INVISIBLE */,

  63. KEY `idx_three_category` (`three_category`),

  64. KEY `idx_tags` (`tags`(255)),

  65. KEY `idx_publishtime` (`publishtime`),

  66. KEY `idx_last_modify_time` (`last_modify_time`),

  67. KEY `idx_author_id_status_modify_time` (`author_id`,`status`,`last_modify_time`),

  68. KEY `idx_top` (`top`),

  69. KEY `idx_recommend_top` (`recommend_top`),

  70. KEY `idx_style_createtime` (`style`,`createtime`),

  71. KEY `idx_sub_position` (`sub_position`,`bi_list_show`,`status`),

  72. KEY `idx_style_to_audit` (`style`,`to_audit`),

  73. KEY `IDX_SUB_POSITION_IS_CHOICE` (`sub_position`,`is_choice`)

  74. ) ENGINE=InnoDB AUTO_INCREMENT=234366805 DEFAULT CHARSET=utf8

  75. #执行前查看use_invisible_indexes=off,默认值

  76. mysql> select @@optimizer_switch\G

  77. *************************** 1. row ***************************

  78. @@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

  79. 1 row in set (0.00 sec)

  80.  
  81.  
  82. #查看执行计划,命中的索引是:idx_author_id_status_modify_time

  83. 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

  84. *************************** 1. row ***************************

  85. id: 1

  86. select_type: SIMPLE

  87. table: ar

  88. partitions: NULL

  89. type: range

  90. possible_keys: article_status_index,idx_author_id_status_modify_time,idx_sub_position,IDX_SUB_POSITION_IS_CHOICE

  91. key: idx_author_id_status_modify_time

  92. key_len: 68

  93. ref: NULL

  94. rows: 2719

  95. filtered: 0.00

  96. Extra: Using index condition; Using where

  97. 1 row in set, 1 warning (0.01 sec)

  98.  
  99. #修改变量:use_invisible_indexes=on;

  100. 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'

  101. -> ;

  102. Query OK, 0 rows affected (0.00 sec)

  103.  
  104. #再次查看执行计划,此时看到命中的索引是:position_index,sub_position_index

  105. 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 ***************************

  106. id: 1

  107. select_type: SIMPLE

  108. table: ar

  109. partitions: NULL

  110. type: index_merge

  111. possible_keys: article_status_index,position_index,sub_position_index,idx_author_id_status_modify_time,idx_sub_position,IDX_SUB_POSITION_IS_CHOICE

  112. key: position_index,sub_position_index

  113. key_len: 2,5

  114. ref: NULL

  115. rows: 492

  116. filtered: 5.00

  117. Extra: Using intersect(position_index,sub_position_index); Using where

  118. 1 row in set, 1 warning (0.00 sec)

  119.  
  120. mysql>

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值