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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 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下持续关注喔~

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

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5天前
|
SQL Java 关系型数据库
MYSQL--JDBC优化
MYSQL--JDBC优化
|
1天前
|
SQL 缓存 关系型数据库
MySQL慢查询优化实践问答
MySQL慢查询优化实践问答
|
1天前
|
SQL 关系型数据库 MySQL
|
2天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
5 2
|
4天前
|
缓存 关系型数据库 MySQL
如何优化MySQL 8.0的性能?
【6月更文挑战第14天】如何优化MySQL 8.0的性能?
22 5
|
5天前
|
存储 算法 关系型数据库
*MYSQL--索引--内部原理
*MYSQL--索引--内部原理
|
6天前
|
缓存 监控 关系型数据库
mysql优化
【6月更文挑战第12天】mysql优化
16 3
|
7天前
|
NoSQL 关系型数据库 Serverless
Serverless 应用引擎产品使用合集之连接RDS、Redis等数据库时,是否需要通过安全组来控制访问权限
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
|
7天前
|
DataWorks 关系型数据库 MySQL
DataWorks操作报错合集之在尝试连接到MySQL数据库时遇到了“Communications link failure”错误,如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
4天前
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库,一步就能脱胎换骨
阿里云瑶池数据库来开课啦!自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定!