MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀

前言

上两篇文章我们说到MySQL优化回表的三种方式:索引条件下推ICP、多范围读取MRR与覆盖索引

MySQL的优化利器⭐️索引条件下推,千万数据下性能提升273%🚀

MySQL的优化利器⭐️Multi Range Read与Covering Index是如何优化回表的?

这篇文章我们来聊聊MySQL中连接的原理以及连接的四种优化手段

为了更好的讲述文章内容,我们准备的两张表

一张是ICP文章中用到的学生表,学生表中有联合索引(age,studnet_name)

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(20) DEFAULT NULL COMMENT '名称',
  `age` smallint(6) DEFAULT NULL COMMENT '年龄',
  `info` varchar(30) DEFAULT NULL COMMENT '信息',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`student_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

另一张座位表用于关联学生表,标识某个座位是某个学生的,座位与学生的关系是多对一(比如学生菜菜有多个座位)

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

连接原理

关联多张表的查询叫做联表查询(联表又叫连接),常见的连接有:左连接、右连接、内连接

在左连接中,left join左边的表为驱动表,右边的表为被驱动表

当发生连接查询时,先在驱动表中开始寻找记录,当找到满足条件的记录,再去被驱动表中寻找满足关联条件on的记录

SELECT
  s1.*,
  s2.seat_code 
FROM
  student s1
  LEFT JOIN seat s2 ON s1.id = s2.student_id 
WHERE
  s1.age = 18 
  AND s1.student_name LIKE 'c%' 
;

比如这个例子中执行流程(1-3是循环的步骤,直到不满足条件):

  1. 先去学生表student的(age,studnet_name)联合索引中寻找满足条件的记录
  2. 拿到这条记录的id去被驱动表seat中找到满足关联条件的记录(ON s1.id = s2.student_id
  3. 将找到的记录放入结果集中,再去循环步骤1

image.png

直到图中第四条记录(18,ddseat,25)不满足查询条件s1.age = 18 AND s1.student_name LIKE 'c%'时则退出循环

连接寻找的过程是不是就像双层for循环一样?驱动表为外层循环,被驱动表为内存循环,伪代码如下:

//驱动表student
for(long studentIndex = initStudentIndex; studentIndex < student.size(); studentIndex++){
    //如果不满足条件就退出 
    if(){
       break;   
    }    
    
    //满足条件就去访问被驱动表seat
    for(long seatIndex = initSeatIndex; seatIndex < seat.size(); seatIndex++){
        //如果不满足条件就退出(继续循环驱动表)
        if(){
            break;
        }
        
        //在被驱动表中找到满足关联的条件就加入结果集
        result.add(XX);
    }

}

通过流程与代码我们可以分析:访问驱动表时,会访问多次被驱动表(驱动表每有一条满足条件的记录就要去访问被驱动表)

因此在设计上应该尽量选择驱动表为小表,用小表驱动大表

当使用内连接时,由优化器决定哪个表是驱动表,哪个表是被驱动表

当两个表时相当于双层循环,三个表时相当于三层循环,联表越多时间复杂度呈指数级别增长,联表的性能开销会非常大

优化连接

如果想要优化联表的开销有什么手段呢?

通过刚刚的分析,我们可以通过减少访问被驱动表的次数、加快查询被驱动表等方面来进行优化连接

索引

说到加快查询速度, 第一个想到的就是建立索引

为被驱动表关联字段加上索引,优化查询被驱动表的速度

以这条SQL为例,就是在seat表中加上(student_id)索引

SELECT
  s1.*,
  s2.seat_code 
FROM
  student s1
  LEFT JOIN seat s2 ON s1.id = s2.student_id 
WHERE
  s1.age = 18 
  AND s1.student_name LIKE 'c%' 
;

当在驱动表中找到记录后,去被驱动表的(student_id)索引寻找满足条件的记录

image.png

被驱动表(student_id)索引会对student_id排序,当student_id相同时对主键seat_id排序

索引student_id有序,等值比较查找会很快,从而优化查询被驱动表的速度

SELECT
  s1.*,
  s2.seat_code 
FROM
  student s1
  left JOIN seat s2 ON s1.id = s2.student_id 
WHERE
  s1.age = 18 
  AND s1.student_name LIKE 'c%'
> OK
> 时间: 2.063s

执行计划中显示,被驱动表用到student_id索引

image.png

但是还会出现回表的问题,由于(student_id)索引中不存在要查询的seat_code字段,还要回表查询聚簇索引

也可以通过在索引中增加seat_code列使用覆盖索引解决,回表相关知识前两篇文章说过,这里就不过多叙述

Block Nested Loop (BNL)

创建索引是有代价的,不仅查询时需要分析使用哪个索引的成本低,在进行写操作时还要去维护索引

因此并不是每连接一张表就要为被驱动表建立索引,在用不上索引的情况下,该如何优化连接的开销呢?

MySQL提供Block Nested Loop算法对被驱动表无法使用索引的场景,减少访问被驱动表的次数来进行优化

Block Nested Loop 算法是使用一块缓冲池(join buffer)记录满足驱动表的记录,将缓冲池装满后再去被驱动表中寻找

在被驱动表中寻找时,每遍历一条记录就用join buffer中存储的驱动表记录来进行匹配,满足关联条件就放入结果集中

image.png

SET optimizer_switch='block_nested_loop=on'用于开启BNL算法(默认开启)

开启BNL算法耗时5.215s (测试前记得把被驱动表的student_id索引删除)

SET optimizer_switch='block_nested_loop=on'
> OK
> 时间: 0.053s


SELECT
  s1.*,
  s2.seat_code 
FROM
  student s1
  left JOIN seat s2 ON s1.id = s2.student_id 
WHERE
  s1.age = 18 
  AND s1.student_name LIKE 'c%'
> OK
> 时间: 5.215s

执行计划的附加信息说明使用join buffer,算法为BNL

image.png

将BNL算法关闭测试原理中描述的双层循环,耗时12.804s

SET optimizer_switch='block_nested_loop=off'
> OK
> 时间: 0.048s


SELECT
  s1.*,
  s2.seat_code 
FROM
  student s1
  left JOIN seat s2 ON s1.id = s2.student_id 
WHERE
  s1.age = 18 
  AND s1.student_name LIKE 'c%'
> OK
> 时间: 12.804s

执行计划的附加信息中说明没用join buffer

image.png

从原来的满足一条记录就去寻找一遍被驱动表变成收集多条记录后再去访问被驱动表

如果使用的缓存池够大,还可以将驱动表中满足条件的记录装完再去访问被驱动表,相当于只访问一次

join buffer存储需要查询的列和查询条件的列,因此不要使用select *避免浪费join buffer的空间

默认情况下join buffer 占用262144 B(256KB),如果不能使用索引优化连接的情况下,可以把join buffer 设置大一些  set global join_buffer_size = 262144

Batched Key Access (BKA)

在Block Nested Loop 算法是用于优化被驱动表中不能使用索引的场景

而Batched Key Access BKA算法用于优化被驱动表上能使用索引的场景

在驱动表(age,student_name)索引中满足条件的记录,id不一定是有序的,使用乱序的id去被驱动表中查找就可能发生随机IO

BKA算法是基于MRR的,对驱动表结果的id进行排序后,再去被驱动表中查找

image.png

不懂MRR的同学可以查看上篇文章(在文章前言有链接)

由于MySQL对使用MRR的成本太高,如果想使用BKA算法,还需要关闭基于成本判断是否使用MRR

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

mrr=on 开启mrr (默认开启)

mrr_cost_based=off 关闭基于成本判断是否使用MRR (默认开启)

batched_key_access 开启BKA  (默认关闭)

测试使用BKA算法耗时1.533s

SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on'
> OK
> 时间: 0.049s


SELECT
  s1.*,
  s2.seat_code 
FROM
  student s1
  LEFT JOIN seat s2 ON s1.id = s2.student_id 
WHERE
  s1.age = 18 
  AND s1.student_name LIKE 'c%'
> OK
> 时间: 1.533s

执行计划中显示,驱动表使用MRR,被驱动表使用student_id索引和BKA算法

image.png

hash join

关联条件往往是等值比较的

散列表(哈希表)是一种非常适合寻找等值比较的数据结构

在MySQL高版本中8.0默认使用 hash 的 join buffer,通过空间换时间的方式来加速查找被驱动表

image.png

测试总结

本篇文章使用该SQL对多种优化连接的方式进行测试并将结果进行汇总分析其特点(暂时还没测试hash join)

SELECT
  s1.*,
  s2.seat_code 
FROM
  student s1
  LEFT JOIN seat s2 ON s1.id = s2.student_id 
WHERE
  s1.age = 18 
  AND s1.student_name LIKE 'c%' 
;
方式 耗时(单位:秒) 优点 缺点
无优化的嵌套循环查询 12.804 好像没有优点...逻辑清晰算吗 时间复杂度指数级别,特别慢
使用BNL算法的join buffer优化 5.215 使用join buffer减少访问被驱动表次数 增加join buffer缓冲池的开销
被驱动表增加索引 2.063 往被驱动表关联条件的列建立索引,将查询关联条件从无序查询优化为有序查询 由于ID无序查询被驱动表会出现随机IO
使用BKA算法优化 1.533s 使用BKA算法将访问被驱动表索引的随机IO转换成顺序IO 需要被驱动表建立索引和使用MRR,默认情况下使用MRR成本估算很大

默认情况下就算不用索引也不会使用无优化的嵌套查询,最少也是使用Join Buffer 5.215s

为被驱动表关联列增加索引后,相比于Join Buffer查询性能提升近150%

使用BKA算法优化后查询速度达到1.533s,相比于Join Buffer查询性能提升近240%

总结

连接的原理就是循环嵌套查询,根据驱动表满足查询条件的记录数量去多次访问被驱动表,因此连接时需要小表驱动大表;内连接Inner Join由优化器来选择驱动表

多表连接的时间复杂度呈指数级别,开销非常大,通过减少访问被驱动表数量、加速访问被驱动表等方面进行优化

在被驱动表使用不到索引的场景下,会使用缓冲池Join Buffer的BNL算法来存储驱动表满足条件记录,相当于多条记录一起访问被驱动表,以此来减少访问被驱动表次数

Join Buffer中存储查询需要的列和查询条件的列,因此不要使用select * 避免浪费Join Buffer,在不能使用索引的场景下可以增大Join Buffer的空间

为被驱动表关联条件的列建立索引可以加快访问被驱动表,将访问被驱动表聚簇索引的无序查询优化为二级索引的有序查询,但满足条件的驱动表记录中关联条件的列并不一定有序,来查被驱动表时可能是随机IO

BKA算法基于被驱动表的关联条件列建立索引和使用MRR,以此对驱动表中满足条件的列排序,将访问被驱动表时的随机IO优化为顺序IO

默认下BKA算法不开启并且MRR预估成本较大,如果确认访问被驱动表时的随机IO开销太大,可以关闭基于成本使用MRR和开启BKA算法

在MySQL 8.0高版本中Join Buffer默认使用hash join,由于关联条件常是等值比较,数据结构哈希表非常适合这种场景下的查询

最后(不要白嫖,一键三连求求拉~)

本篇文章被收入专栏 由点到线,由线到面,构建MySQL知识体系,感兴趣的同学可以持续关注喔

本篇文章笔记以及案例被收入 gitee-StudyJavagithub-StudyJava 感兴趣的同学可以stat下持续关注喔~

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

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

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
缓存 监控 关系型数据库
如何根据监控结果调整 MySQL 数据库的参数以提高性能?
【10月更文挑战第28天】根据MySQL数据库的监控结果来调整参数以提高性能,需要综合考虑多个方面的因素
22 1
|
1天前
|
监控 关系型数据库 MySQL
如何监控和诊断 MySQL 数据库的性能问题?
【10月更文挑战第28天】监控和诊断MySQL数据库的性能问题是确保数据库高效稳定运行的关键
6 1
|
1天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
7 1
|
8天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
35 9
|
8天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
26 5
|
2天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
10 0
|
3天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
16 0
|
21天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
92 6
|
19天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
54 3
Mysql(4)—数据库索引
|
4天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
29 2

推荐镜像

更多