MySQL 8 新特性之Invisible Indexes
作者:iVictor 发布时间:2024-01-19 04:56:06
标签:mysql,8,新特性,Invisible,Indexes
背景
索引是把 * 剑,在提升查询速度的同时会减慢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 ,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的!
来源:https://www.cnblogs.com/ivictor/p/8998797.html
0
投稿
猜你喜欢
- ASP中判断字符串中是否包含字母和数字的两个函数function isnaw(str) for
- DateTimeField日期+时间。与python里的 datetime.datetime 实例同。比如,数据库字段内容为2018-08-
- 解读pandas.DataFrame.corrwithpandas.DataFrame.corrwith用于计算DataFrame中行与行或
- Python的字典一般都直接查找key ,比如dict={'a':1,'b':2,'c':3
- 目录准备数据集导入所需的软件包将数据从文件加载到Python变量拆分数据进行训练和测试标记化并准备词汇预处理输出标签/类建立Keras模型并
- 得益于 Python 的自动垃圾回收机制,在 Python 中创建对象时无须手动释放。这对开发者非常
- 本文实例讲述了Python实现的特征提取操作。分享给大家供大家参考,具体如下:# -*- coding: utf-8 -*-"&q
- 前言schedule是一个第三方轻量级的任务调度模块,可以按照秒,分,小时,日期或者自定义事件执行时间。如果想执行多个任务,也可以添加多个t
- 这是我以前发表在经典论坛的帖子,现在转贴回来。仿淘宝网站的导航效果。此方法有几个优点:根据字数自适应项目长度不同的项目使用不同的颜色来区分无
- 脚本如下: drop table table1; CREATE TABLE `andrew`.`table1` ( `name` VARCH
- Matplotlib的概念这里就不多介绍了,关于绘图库Matplotlib的安装方法:点击这里小编之前也和大家分享过python使用matp
- python聊天室很多人都觉得微信,QQ,ICQ(我不知道现在还能不能用了)都过于垃圾,想要自己做一个聊天室(或是聊天软件),所以我们可以自
- 背景:python函数库scipy的quad、dblquad实现一维二维积分的范例。需要注意dblquad的积分顺序问题。代码:import
- 学习了css一段时间,现在对css的一些技巧进行一次小结.希望能对那些刚学习css的新手们带来帮助.一、关于注释在创建xhtml+CSS网站
- objectobject 是 Python 为所有对象提供的父类,默认提供一些内置的属性、方法;可以使用 dir 方法查看新式类以 obje
- 赋值:其实就是对象的引用(相当于取别名)。浅拷贝(copy):拷贝父对象,不会拷贝对象内部的子对象,会引用子对象。深拷贝(deepcopy)
- smtplib模块负责发送邮件:是一个发送邮件的动作,连接邮箱服务器,登录邮箱,发送邮件(有发件人,收信人,邮件内容)。email模块负责构
- 1、tox 能做什么?细分的用途包括:创建开发环境运行静态代码分析与测试工具自动化构建包针对 tox 构建的软件包运行测试检查软件包是否能在
- 通过@classmethod 实现多态1.概述python中通常使用对象创建多态模式,python还支持类创建多态模式。下面通过一个例子展示
- 转:coolcode.cn通常情况下,我们的网页要指定一个编码字符集,如 GB2312、UTF-8、ISO-8859-1