MySQL8.0 索引优化invisible index详情
作者:阿常呓语 发布时间:2024-01-21 11:35:40
标签:MySQL,索引,优化,invisible,index
前言
MySQL8.0 开始支持不可见索引。 优化器根本不使用不可见索引,但会以其他的方式正常维护。
默认情况下 索引是可见的。 通过不可见索引,可以方便数据库管理人员 检查 索引对查询性能的影响,而不会进行破坏性的更改 。
应用场景: 软删除,灰度发布
-- 创建测试表
mysql> create table t1(i int ,j int);
Query OK, 0 rows affected (0.03 sec)
-- 创建索引
mysql>
mysql> create index idx_i on t1(i);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> -- 创建 一个隐藏索引
mysql> create index idx_j on t1(j) invisible ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看索引
mysql> show index from t1\G
*************************** 1. row ***************************
Table: t1
Non_unique: 1
Key_name: idx_i
Seq_in_index: 1
Column_name: i
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: idx_j
Seq_in_index: 1
Column_name: j
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: NO
Expression: NULL
2 rows in set (0.00 sec)
上面 对 i
,j
分别创建了索引, 其中j
为不可见索引 ,即默认优化器不会使用到该索引的。
索引使用情况测试
mysql> explain select * from t1 where i= 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: idx_i
key: idx_i
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t1 where j= 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
如何临时让优化器可以看到这个索引呢?
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)
-- 设置开关
mysql> set session optimizer_switch='use_invisible_indexes=on';
Query OK, 0 rows affected (0.00 sec)
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=on,skip_scan=on
1 row in set (0.00 sec)
-- 此时可以看到 优化器已经可以看到这个索引啦
mysql> explain select * from t1 where j= 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: idx_j
key: idx_j
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
修改索引的可见性
mysql> ALTER TABLE t1 ALTER INDEX idx_j INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ALTER INDEX idx_j VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
注意: 主键索引 是不能设置 不可见的 。
create table t2(`id` int not null auto_increment, primary key (`id`));
mysql> show index from t2\G
*************************** 1. row ***************************
Table: t2
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.01 sec)
mysql> alter table t2 alter index PRIMARY key(`id`) invisible;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY invisible' at line 1
测试 添加 主键 为不可见索引:
mysql> create table t3(id int not null);
Query OK, 0 rows affected (0.03 sec)
mysql> alter table t3 add primary key pk_t3(id) invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible
primary key
是必须可见的, 不能设置为不可见索引。
在生产环境中 也可以利用隐藏索引进行 SQL 语句的性能测试,或者对索引进行逻辑删除,索引的灰度发布测试,以及软删除索引等。
来源:https://blog.csdn.net/u010339879/article/details/127043431
0
投稿
猜你喜欢
- Python 异常处理机制还提供了一个 finally 语句,通常用来为 try 块中的程序做扫尾清理工作。注意,和 else 语句不同,f
- 【1】 以XML 返回 (1)未定义属性的 select logisticsId,logisticsName from LogisticsC
- 方法一: 在给出的输入CD-KEY(序列号)的界面中,输入你已经安装的windows server 2003 的CD-KEY(序列号)即可以
- 引入numpy已经能够帮助我们处理数据,能够结合matplotlib解决我们数据分析的问题,那么pandas学习的目的在什么地方呢? num
- Pycharm实然提示过期了,网上找了很多Pycharm激活码,要么只支持老版本,要么是不能用。费了半天时间终于在一公众号里找到一枚可以使用
- Python的functools模块中有一种函数叫“偏函数”,自从接触它以来,发现确实是一个很有用且简单的函数,相信你看完这篇文章,你也有相
- 在python的使用中,有时也不得不调用一下外部程序,那么如何调用外部程序:首先,我们要启动python软件,使用的是python2.7的版
- 使用JdbcTemplate的步骤1、设置spring-jdbc和spring-tx的坐标(也就是导入依赖) <depen
- 前言相信在日常生活中,平常大家聚在一起总会聊聊天,特别是女生(有冒犯到doge)非常喜欢聊星座,这个男生什么星座呀,那个男生什么星座呀…今天
- 1、安装pip install djangorestframework2、创建项目及应用创建项目创建应用目录结构如图3、设置settings
- 程序设计是困难的,其核心是管理的复杂性。计算机程序是人类做出的最复杂的东西。质量是不可靠的且隐蔽的。好的体系架构是必需给程序足够的结构使其健
- 1.Python hasattr() 函数描述hasattr() 函数用于判断对象是否包含对应的属性。语法hasattr 语法:hasatt
- z-blog摘要图文混排,在月光的博客早已有介绍,但是他的图文混排是基于一篇文章只定义一个Tag的情况,对于我这样习惯多tag的博客,再更改
- DROP TABLE 数据表名称 (永久性删除一个数据表) 删除记录:delete from&nbs
- 在ORACLE中,我们可以通过file_id(file#)与block_id(block#)去定位一个数据库对象(object)。例如,我们
- sub test { my $head = "abc"; &n
- 这篇文章主要介绍了windows环境中利用celery实现简单任务队列过程解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定
- Turtle库是Python内置的图形化模块,属于标准库之一,位于Python安装目录的lib文件夹下,常用函数有以下几种:画笔控制函数pe
- 使用PyTorch进行训练和测试时一定注意要把实例化的model指定train/eval,eval()时,框架会自动把BN和DropOut固
- 数据挖掘查询任务根据 Analysis Services 内置的数据挖掘模型运行预测查询。预测查询通过使用挖掘模型来创建对新数据的预测。例如