MySQL的3种索引合并优化⭐️or到底能不能用索引?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 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

正常情况下优化器只能选择一个它认为最成本最低的索引来生成执行计划

但在某些情况下可以使用多个索引进行索引合来优化

索引合并的优化分成三种方式:

  1. index merge intersection 交集索引合并
  2. index merge union 并集索引合并
  3. 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的记录,再回表查询聚簇索引获取完整记录

image.png

关闭交集索引合并的优化

SET optimizer_switch='index_merge_intersection=off';

查看执行计划

image.png

在这种场景下,可能存在很多满足seat_code  = caicaiseat ,但是不满足 student_id = 1的记录

如果这些记录也需要回表,再回表后还是会被过滤,浪费资源来对这些记录进行回表

回表查询不仅仅是多查询一次,在这次查询中还可能是随机IO,查询量大的情况下,回表的代价是很高的

使用交集索引合并后

  1. 先使用seat_code索引找到满足 seat_code  = caicaiseat 的条件
  2. 然后使用student_id索引找到满足 student_id = 1 的条件
  3. 接着根据主键seat_id对它们进行交集过滤,剩下的记录再进行回表,以此来减少回表的次数

(图中未回表是因为正好满足覆盖索引)

image.png

需要注意的是使用交集索引合并需要主键值需要有序,如果主键值乱序进行交集过滤,在回表时会产生随机IO,得不偿失

在二级索引中只有索引列相等时才对主键值进行排序,因此大部分使用交集索引合并的场景是等值比较=

开启交集索引合并,查看执行计划

type类型为索引合并,使用到这两个索引,附加信息显示用到交集索引合并,并且还用上覆盖索引不需要回表

由于seat座位表只存在主键seat_id、座位码seat_code、学生ID student_id,需要查询的列都在二级索引上,因此不用回表

image.png

有的同学可能注意到:为啥不把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 关闭排序的并集索引合并(是下一个要说明的索引合并,其在并集索引合并的基础上增加排序)

image.png

当使用index merge union的情况下

  1. 先使用seat_code索引找到满足条件seat_code = 'caicaiseat'的记录
  2. 再使用student_id索引找到满足条件student_id = 1的记录
  3. 然后将它们的主键值seat_id取并集后再回表查询,以此来减少开销

image.png

开启union优化,查看执行计划:已经使用index merge union

image.png

所以以后不要再傻乎乎的背八股文说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索引满足条件的记录进行排序,有序后再取并集

image.png

开启sort union后,查看执行计划:使用index merge sort union

image.png

情况与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-StudyJavagithub-StudyJava 感兴趣的同学可以stat下持续关注喔~

有什么问题可以在评论区交流,如果觉得菜菜写的不错,可以点赞、关注、收藏支持一下~

关注菜菜,分享更多干货,公众号:菜菜的后端私房菜

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
24天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
25天前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
2月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
2天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
29 7
|
29天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
18天前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
22 2
|
1月前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
73 3
|
1月前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
79 1
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
97 9
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
200 1