MySQL的3种索引合并优化⭐️or到底能不能用索引?
MySQL的3种索引合并优化⭐️or到底能不能用索引?
前言
前文我们讨论过MySQL优化回表的多种方式:索引条件下推ICP、多范围读取MRR、覆盖索引等
这篇文章我们来聊聊MySQL提供的另一种优化回表的手段:index merge 索引合并
在阅读本文前,你需要了解MySQL的server层与存储引擎层如何交互、二级索引和聚簇索引的区别、回表等知识
如果同学不太了解这些知识可以回看前文:
MySQL的优化利器⭐️索引条件下推,千万数据下性能提升273%🚀
MySQL的优化利器⭐️Multi Range Read与Covering Index是如何优化回表的?
MySQL导致索引失效的八股文中有这样一条:使用or
会导致索引失效
那么是不是所有场景都会失效呢?带着这个问题,我们往下看
案例使用上篇文章的座位表,并分别建立seat_code、student_id 两个二级索引
CREATE TABLE `seat` ( `seat_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '座位ID', `seat_code` char(10) DEFAULT NULL COMMENT '座位码', `student_id` bigint(20) DEFAULT NULL COMMENT '座位关联的学生ID', PRIMARY KEY (`seat_id`), KEY `idx_student_id` (`student_id`), KEY `idx_seat_code` (`seat_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
index merge
正常情况下优化器只能选择一个它认为最成本最低的索引来生成执行计划
但在某些情况下可以使用多个索引进行索引合来优化
索引合并的优化分成三种方式:
- index merge intersection 交集索引合并
- index merge union 并集索引合并
- index merge sort union 排序并集索引合并
三种方式各自有什么不同呢?请按顺序往下看:
index merge intersection
index merge intersection 是用于交集的索引合并,交集往往和查询条件中的and
相关
什么是交集?
比如有两个集合分别是(1,2,3)、(2,3,4),那么交集就是它们都存在的值(2,3)
举例这样一条SQL:
select * from seat where seat_code = 'caicaiseat' and student_id = 1
当不使用索引合并优化时,优化器可能选择seat_code索引或者student_id索引
当使用seat_code索引时,先在索引中找到满足seat_code = caicaiseat
的记录,再回表查询聚簇索引获取完整记录
关闭交集索引合并的优化
SET optimizer_switch='index_merge_intersection=off';
查看执行计划
在这种场景下,可能存在很多满足seat_code = caicaiseat
,但是不满足 student_id = 1
的记录
如果这些记录也需要回表,再回表后还是会被过滤,浪费资源来对这些记录进行回表
回表查询不仅仅是多查询一次,在这次查询中还可能是随机IO,查询量大的情况下,回表的代价是很高的
使用交集索引合并后
- 先使用seat_code索引找到满足
seat_code = caicaiseat
的条件 - 然后使用student_id索引找到满足
student_id = 1
的条件 - 接着根据主键seat_id对它们进行交集过滤,剩下的记录再进行回表,以此来减少回表的次数
(图中未回表是因为正好满足覆盖索引)
需要注意的是使用交集索引合并需要主键值需要有序,如果主键值乱序进行交集过滤,在回表时会产生随机IO,得不偿失
在二级索引中只有索引列相等时才对主键值进行排序,因此大部分使用交集索引合并的场景是等值比较=
开启交集索引合并,查看执行计划
type类型为索引合并,使用到这两个索引,附加信息显示用到交集索引合并,并且还用上覆盖索引不需要回表
由于seat座位表只存在主键seat_id、座位码seat_code、学生ID student_id,需要查询的列都在二级索引上,因此不用回表
有的同学可能注意到:为啥不把seat_code与student_id组成(seat_code,student_id)联合索引呢?
实际上(seat_code,student_id)联合索引也是可以用上索引的,但如果要单独查询student_id就会导致索引失效了
index merge union
index merge union是用于并集的索引合并,并集往往与查询条件or
相关
什么是并集?
比如有两个集合分别是(1,2,3)、(2,3,4),那么并集就是它们都存在值的总和(1,2,3,4)
举例这样一条SQL
select * from seat where seat_code = 'caicaiseat' or student_id = 1;
当不使用index merge union的情况下,会直接全表扫描(聚簇索引),依次判断记录是否满足条件
index_merge_union=off 关闭并集索引合并
index_merge_sort_union 关闭排序的并集索引合并(是下一个要说明的索引合并,其在并集索引合并的基础上增加排序)
当使用index merge union的情况下
- 先使用seat_code索引找到满足条件
seat_code = 'caicaiseat'
的记录 - 再使用student_id索引找到满足条件
student_id = 1
的记录 - 然后将它们的主键值seat_id取并集后再回表查询,以此来减少开销
开启union优化,查看执行计划:已经使用index merge union
所以以后不要再傻乎乎的背八股文说or
用不上索引啦~
使用index merge union的前提与index merge intersection类似也需要主键值有序
index merge sort union
由于or在某些场景下会让优化器认为回表成本大,不如全表扫描,从而导致索引失效
而index merge union的使用前提(主键有序)太苛刻,很多场景下还是无法使用索引
index merge sort union 排序的并集索引合并:对主键值无序的场景排序后再进行并集
比如这条SQL中
select * from seat where seat_code like 'a%' and student_id = 1
查询条件seat_code不再是等值比较,这样满足seat_code like 'a%'
记录的主键值也不一定是有序的
而seat_code索引中满足 student_id = 1
的记录主键值是有序的
为了将seat_code索引满足条件的记录与seat_code索引满足条件的记录作并集
先对seat_code索引满足条件的记录进行排序,有序后再取并集
开启sort union后,查看执行计划:使用index merge sort union
情况与index merge union类似,使用前提可以是主键乱序,在主键乱序后对其进行排序再取交集
总结
index merge索引合并优化默认开启,分为intersection交集、union并集、sort union排序并集三种方式
index merge intersection使用的前提:and
和可以使用多个索引且结果中主键有序,分别在对应的索引中找到满足条件的记录,对记录进行交集过滤后再进行回表,减少不必要的回表开销
index merge union 使用的前提:or
和可以使用多个索引且结果中主键有序,分别在对应索引中找到满足条件的记录,对记录进行并集过滤后再进行回表,避免全表扫描
index merge intersection/union使用的前提都是需要主键有序,因为主键乱序需要先排序再进行交集/并集,否则会有随机IO
由于index merge union中or
容易导致优化器认为回表成本大进而全表扫描,而满足主键有序的场景太苛刻,因此使用index merge sort union 在主键乱序的情况下排序再取并集
最后(不要白嫖,一键三连求求拉~)
本篇文章被收入专栏 由点到线,由线到面,构建MySQL知识体系,感兴趣的同学可以持续关注喔
本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~
有什么问题可以在评论区交流,如果觉得菜菜写的不错,可以点赞、关注、收藏支持一下~
关注菜菜,分享更多干货,公众号:菜菜的后端私房菜
本文由博客一文多发平台 OpenWrite 发布!
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
接上回,如何用 LlamaIndex 搭建聊天机器人?
LlamaIndex 是领先的开源数据检索框架,能够在各种应用中发挥优势,其中一个典型的应用就是在企业内部搭建聊天机器人。 对于企业而言,随着文档数量不断增多,文档管理会变得愈发困难。因此,许多企业会基于内部知识库搭建聊天机器人。在搭建过程中,需要关注三个要点:如何切割数据、保存哪些元数据以及如何路由查询。 01.为什么要用 LlamaIndex 搭建聊天机器人? 在上一篇文章中,我们使用 Zilliz Cloud(全托管的 Milvus 云服务)搭建了一个最基本的检索增强生成(RAG)(https://zilliz.com/use-cases/llm-retrieval-augmented-generation)聊天机器人。在本教程中我们可以继续使用 Zilliz Cloud 免费版,大家也可以使用自己的 Milvus(https://milvus.io/) 实例,在 notebook 中快速启动并使用 Milvus Lite(https://milvus.io/docs/milvus_lite.md)。 上一篇文章中我们将文章进行切割,获取许多小的文本块。当输入问题“什么是大型语言...
- 下一篇
MySQL 数据脱敏方式盘点
对于企业而言,数据脱敏可以在数据共享或测试时用于保护敏感数据(如信用卡,社保卡,地址等)。通过对敏感数据进行脱敏处理,组织可以最大限度地降低数据泄露和未经授权访问的风险,同时仍能够使用真实的开发,测试和分析目的所需的数据。 有很多方法进行数据脱敏,比如遮挡,替换,洗牌和加密,等等,它们适用于不同场景。本文主要聚焦「遮挡」,用特定符号 (比如 X 或 ) 遮挡敏感数据,这种方法可以在脱敏的同时保持原有数据感观。 MySQL 企业级数据脱敏插件 MySQL 官方这边,数据脱敏只作为插件在 MySQL 企业版中提供 。 MySQL 数据脱敏插件的工作原理是插件中包含了用于进行数据脱敏的语法,例如 mask_inner, mask_outer, mask_ssn 等。 组织里有权限的人员(通常来说是数据库管理员)会首先定义一个显示脱敏数据的视图 (VIEW)。即使用户对敏感数据的访问受限,他们也可以将该视图视为一张表。因此,要访问数据,用户不是直接使用脱敏语法进行直接查询,而是从视图中查询即可。 这种方法很直接,但也有一定限制: 依赖于细粒度的 MySQL 用户账户 / 角色。实际上,大多数 ...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Docker安装Oracle12C,快速搭建Oracle学习环境
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
- SpringBoot2整合Redis,开启缓存,提高访问速度
- Windows10,CentOS7,CentOS8安装Nodejs环境
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- SpringBoot2整合Thymeleaf,官方推荐html解决方案
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Linux系统CentOS6、CentOS7手动修改IP地址
- SpringBoot2更换Tomcat为Jetty,小型站点的福音