详解MySQL 8.0 之不可见索引
作者:用户1278550 发布时间:2024-01-22 17:41:46
言
MySQL 8.0 从第一版release 到现在已经走过了4个年头了,8.0版本在功能和代码上做了相当大的改进和重构。和DBA圈子里的朋友交流,大部分还是5.6 ,5.7的版本,少量的走的比较靠前采用了MySQL 8.0。为了紧追数据库发展的步伐,能够尽早享受技术红利,我们准备将MySQL 8.0引入到有赞的数据库体系。
落地之前 我们会对MySQL 8.0的新特性和功能,配置参数,升级方式,兼容性等等做一系列的学习和测试。以后陆陆续续会发布文章出来。本文算是MySQL 8.0新特性学习的第一篇吧,聊聊 不可见索引。
不可见索引
不可见索引中的不可见是针对优化器而言的,优化器在做执行计划分析的时候(默认情况下)是会忽略设置了不可见属性的索引。
为什么是默认情况下,如果 optimizer_switch设置use_invisible_indexes=ON 是可以继续使用不可见索引。
话不多说,我们先测试几个例子
如何设置不可见索引
我们可以通过带上关键字VISIBLE|INVISIBLE的create table,create index,alter table 设置索引的可见性。
mysql> create table t1 (i int,
> j int,
> k int,
> index i_idx (i) invisible) engine=innodb;
Query OK, 0 rows affected (0.41 sec)
mysql> create index j_idx on t1 (j) invisible;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add index k_idx (k) invisible;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx | NO |
| j_idx | NO |
| k_idx | NO |
+------------+------------+
3 rows in set (0.01 sec)
mysql> alter table t1 alter index i_idx visible;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx | YES |
| j_idx | NO |
| k_idx | NO |
+------------+------------+
3 rows in set (0.00 sec)
不可见索引的作用
面对历史遗留的一大堆索引,经过数轮新老交替开发和DBA估计都不敢直接将索引删除,尤其是遇到比如大于100G的大表,直接删除索引会提升数据库的稳定性风险。
有了不可见索引的特性,DBA可以一边设置索引为不可见,一边观察数据库的慢查询记录和thread running 状态。如果数据库长时间没有相关慢查询 ,thread_running比较稳定,就可以下线该索引。反之,则可以迅速将索引设置为可见,恢复业务访问。
Invisible Indexes 是 server 层的特性,和引擎无关,因此所有引擎(InnoDB, TokuDB, MyISAM, etc.)都可以使用。
设置完不可见索引,执行计划无法使用索引
mysql> show create table t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`i` int NOT NULL AUTO_INCREMENT,
`j` int NOT NULL,
PRIMARY KEY (`i`),
UNIQUE KEY `j_idx` (`j`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
mysql> insert into t2(j) values(1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.04 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> explain select * from t2 where j=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 14.29
Extra: Using where
1 row in set, 1 warning (0.01 sec)
mysql> alter table t2 alter index j_idx visible;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t2 where j=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: const
possible_keys: j_idx
key: j_idx
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.01 sec)
使用不可见索引的注意事项
The feature applies to indexes other than primary keys (either explicit or implicit).
不可见索引是针对非主键索引的。主键不能设置为不可见,这里的 主键 包括显式的主键或者隐式主键(不存在主键时,被提升为主键的唯一索引) ,我们可以用下面的例子展示该规则。
mysql> create table t2 (
>i int not null,
>j int not null ,
>unique j_idx (j)
>) ENGINE = InnoDB;
Query OK, 0 rows affected (0.16 sec)
mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t2';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| j_idx | YES |
+------------+------------+
1 row in set (0.00 sec)
### 没有主键的情况下,唯一键被当做隐式主键,不能设置 不可见。
mysql> alter table t2 alter index j_idx invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible
mysql>
mysql> alter table t2 add primary key (i);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t2';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| j_idx | YES |
| PRIMARY | YES |
+------------+------------+
2 rows in set (0.01 sec)
mysql> alter table t2 alter index j_idx invisible;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t2';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| j_idx | NO |
| PRIMARY | YES |
+------------+------------+
2 rows in set (0.01 sec)
force /ignore index(index_name) 不能访问不可见索引,否则报错。
mysql> select * from t2 force index(j_idx) where j=3;
ERROR 1176 (42000): Key 'j_idx' doesn't exist in table 't2'
设置索引为不可见需要获取MDL锁,遇到长事务会引发数据库抖动
唯一索引被设置为不可见,不代表索引本身唯一性的约束失效
mysql> select * from t2;
+---+----+
| i | j |
+---+----+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 11 |
+---+----+
8 rows in set (0.00 sec)
mysql> insert into t2(j) values(11);
ERROR 1062 (23000): Duplicate entry '11' for key 't2.j_idx'
小结
其实没啥说的,祝大家用的愉快。
-The End-
来源:https://cloud.tencent.com/developer/article/1638494
猜你喜欢
- 使用非对称加密主要是借助openssl的公钥和私钥,用公钥加密私钥解密,或者私钥加密公钥解密。1.安装openssl和php的openssl
- 1 squeeze(): 去除size为1的维度,包括行和列。至于维度大于等于2时,squeeze()不起作用。行、例:>>&g
- 1.效果图:2.代码# 作用域 是 对象生效的区域(对象能被使用的区域)# 全局作用域在任意位置可生效# 局部作用域在函数内生效c = 20
- 设法让用户happy吧~只要你的设计让用户乐了,产品的个性就得到了一次彰显,而用户对网站的情感就会获得一次升华,看看下面的知名网站的人性化设
- 使用指令:pyinstaller -F -w main.py生成的.exe文件执行报错看报错信息,初步怀疑是生成的.exe不能执行.ini配
- 经典神经网络的改进点名称改进点VGG161、使用非常多的3*3卷积串联,利用小卷积代替大卷积,该操作使得其拥有更少的参数量,同时会比单独一个
- 前言:NoxfileNox 默认在一个名为noxfile.py的文件中查找配置。在运行 nox 时,你可以使用 --noxfile参数指定其
- 俗话说‘人生苦短,我有Python',但是如果初学Python的过程中碰到包和Python版本的问题估计会让你再苦一会,我在学习Py
- 对比测试 scipy.misc 和 PIL.Image 和 libtiff.TIFF 三个库输入:1. (读取矩阵) 读入uint8、uin
- 本文实例讲述了python实现线程池的方法。分享给大家供大家参考。具体如下:原理:建立一个任务队列,然多个线程都从这个任务队列中取出任务然后
- 在python中启动和关闭线程:首先导入threadingimport threading然后定义一个方法def serial_read()
- 在Https页面中,如果iframe所引入页面是非https协议的页面,或者src属性不存在都可能导致浏览器弹出安全警告。本人在网上查找相关
- 前言:又到每日分享Python小技巧的时光了,今天给大家分享的是Python接口常用封装函数。相信对于封装,大家都不陌生吧,今天就用四个小案
- 本文实例讲述了Python基于辗转相除法求解最大公约数的方法。分享给大家供大家参考,具体如下:之前总结过一次高德纳TAOCP中的最大公约数求
- 一、前言今天分享一个Python 制作透明背景的电子印章的代码,代码是通过网络获得并整理的,大家可以参考和学习。二、步骤解析代码我已调试过了
- 简介在廖雪峰的python网站上,他是这么说的python是动态语言,它允许程序在执行过程中动态绑定属性或者方法(使用MethodTpye)
- 1、Python的min函数返回列表中的最小的项。2、如何返回列表中最小的项的索引?def indexofMin(arr):
- 前言事情是这样的,昨天去表弟家,用了下他的电脑,不小心点到了他硬盘里隐藏的秘密,本来我只需要用几分钟电脑的,害得我硬是在电脑旁坐了几个小时~
- 原理希尔密码是运用基本矩阵论原理的替换密码,由Lester S. Hill在1929年发明。每个字母当作26进制数字:A=0, B=1, C
- 乱码问题破解压缩包时候会存在中文乱码问题!1:直接使用Everything搜索出要修改的库文件 zipfile.py ,并用notepad+