MySQL 表连接优化

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 概述表连接是业务场景中经常会出现的一种SQL类型,一般的,复杂的业务会存在大量的表连接查询。就性能而言,多表的连接的性能会很低。做好表连接优化也是提升业务性能的重要方面表连接语句认识表连接之前,先认识下表连接的类型。

概述

表连接是业务场景中经常会出现的一种SQL类型,一般的,复杂的业务会存在大量的表连接查询。就性能而言,多表的连接的性能会很低。做好表连接优化也是提升业务性能的重要方面

表连接语句

认识表连接之前,先认识下表连接的类型。一般的,表三种常见的表连接方式:

  • INNER JOIN:如果表中有至少一个匹配,则返回行
  • LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
  • RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行

inner join
只显示两表相互匹配的行

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a inner join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读,汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏,电影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
返回行数:[3],耗时:6 ms.

left join
可以看到,左表中有的行而右表中的没有,右表的字段会用null值显示出来

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a left join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读,汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏,电影 |
| 5 | 库洛洛 | 2 | 四川成都 | | |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
返回行数:[4],耗时:3 ms.

right join
右表中有的左表中没有行,会显示右表中的行,左表中会null值显示

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a right join user_info b on a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读,汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏,电影 |
| | | | | 宇航员 | 悠悠球,篮球 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
返回行数:[4],耗时:4 ms.

MySQL表连接算法

Nested-Loop Join算法

mysql的基本表连接算法是Nested-Loop Join,也就是我们常说的嵌套循环算法。一个简单的嵌套循环连接(NLJ)算法一次一个循环地从第一个表中读取行,将每一行传递给一个嵌套循环,该循环处理连接中的下一个表 。

对于一个t1,t2,t3,官方文档上给出一个伪代码如下:

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

这里是假设有三张表t1,t2,t3.分别使用类型为range,ref,all来连接,从伪代码中可以看出
t3表会被全表扫,然后依次去读取t1和t2上 的数据,t3一般就被称为驱动表,MySQL 的优化器自称是可以智能选择结果集最小的表作为驱动表,根据算法来看,结果集较小的驱动表确实可以使循环次数减少,达到优化的目的。

Block Nested-Loop join算法

除了嵌套循环之外,官方文档上还介绍了 一种块循环算法,将外层循环的数据存在join buffer中,内层循环中的表会和buffer中的数据进行对比,从而减少循环次数
官方文档给出一个表达式,来表述循环次数的关系

(S * C)/join_buffer_size + 1

以上面的t1,t2,t3三表连接为例,S表示t1,t2组合在缓存中的大小,C是这些组合在buffer中的数量,整个式子就是t3被扫描的次数

可以看出,join_buffer_size越大,扫描的次数越小,但是这个优化有上限,当join_buffer_size大到能够缓存所有之前的行组合,那么就是性能最好的时候,再增大这个值,也就没有优化效果了。

join 优化总结

从基础算法可以看出,一般情况下,这种嵌套循环的算法消耗非常高,尤其是时间上的消耗,我们在创建涉及到表连接的SQL时,一定要选择最优性能的SQL

一般的,表连接的SQL性能优化可从这几个方面入手

  • 索引优化
    在有索引的情况下,MySQL会尝试使用Index Nested-Loop Join算法,相对于简单的嵌套循环,性能会好一些。这就需要在必要的字段上建上索引

那对于左连接和右连接,建索引的列有不同,对于left join,左边的表会全部返回,右边则只返回匹配到的行,所以一般的,左连接的连接条件字段中,右表的字段最好需要一个索引,用来过滤不需要的行。相对的,对于right join 而言,表连接字段的左表字段的索引就显得十分重要了

那有时除了匹配字段之外,表连接会伴随着一些条件语句,如:

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a left join user_info b on a.user_id = b.ifnoid where a.sex = 1;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读,汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏,电影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+

对于这些有条件字段的语句,毫无疑问,需要索引的支持,过滤掉无需要的行数,来减少循环的次数。从而使SQL执行的更加迅速

  • 驱动表优化
    当进行多表连接查询时,驱动表的定义为:

1)指定了联接条件时,满足查询条件的记录行数少的表为驱动表
2)未指定联接条件时,行数少的表为驱动表

注意:如果搞不清楚该让谁做驱动表,谁join谁,可以让MySQL运行时自行判断

对于left和right join mysql的驱动表选择一般都是需要返回全部行的那个表,对于inner join和full join,一般情况下,mysql自己的优化器,智能选择较少的表作为驱动表,这样循环的效率会高很多。

此外,根据驱动表的字段进行排序,也会使表连接性能提升

select * from t1 a left join t2 b on a.id = b.id left join t3 c a.id = c.id ORDER BY a.id DESC ;

如果你实在难以确定驱动表的选择,可以去掉join,如下,mysql会选择最少的表作为驱动表,这会对你的选择给出建议

mysql>select a.user_id,a.name,a.sex,a.address,b.job,b.like from user a,user_info b where a.user_id = b.ifnoid;
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| user_id | name | sex | address | job | like |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
| 1 | 小明 | 1 | 浙江杭州 | 老师 | 唱跳rap |
| 2 | 蓝天 | 1 | 浙江杭州 | 司机 | 阅读,汽车 |
| 3 | 小白 | 1 | 上海 | 程序员 | 游戏,电影 |
+-------------------+----------------+---------------+-------------------+---------------+----------------+
  • join_buffer_size

    在join的操作需求,MySQL在完成某些join需求的时候(all row join/all index /scan join)为了减少参与join的“被驱动表”的读取次数以提高性能,需要使用到join buffer来协助完成join操作,当join buffer 太小,MySQL不会将该buffer存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作,然后清空join buffer中的数据,继续将剩余的结果集写入次buffer中,如此往复,这势必会造成被驱动表需要被多次读取,成倍增加IO访问,降低效率(执行计划中如果现实using join buffer)

    也可以从上面的循环次数的配置可以看出,当join_buffer_size的值增大时,是可以减少循环的次数,相当于进行了一些优化

(S * C)/join_buffer_size + 1

MySQL5.7版本这个参数的默认值256K ,若是感觉过小,可以适当增加。最大值不能超过3GB

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
10天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
2月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
334 9
|
2天前
|
缓存 算法 关系型数据库
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
|
3天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
MySQL底层概述—7.优化原则及慢查询
|
3月前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
3天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
5天前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
55 23
|
5天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
22天前
|
监控 关系型数据库 MySQL
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
49 22