MySQL调优之关联查询、子查询优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL调优之关联查询、子查询优化

我们准备如下两个表,并插入数据。

#分类
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

【1】左外连接

首先我们分析SQL如下,type为驱动表,book为被驱动表。

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

每次从type中获取一条数据然后后book中的数据进行对比(全表扫描),这个过程要要重复20次(type 表有20条数据)。


这里可以看到,type均为all。另外还可以看到MySQL帮我们做了一个优化,使用了join buffer进行缓存。


我们为被驱动表 book.card 添加索引优化

CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

我们为被驱动表 book.card 添加索引优化

CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

e8ba08e3eefb41ab9ae9bffea586bc81.png

里能够看到,虽然type表仍旧是要处理20次,但是拿着type的数据去book中寻找时,走的是索引。对于B+树来讲,其时间复杂度为logN,相比前面的全表扫描要快很多。


也就是对于左外连接来讲,如果只能添加一个索引,那么一定添加到被驱动表上。


当然,给type的card页创建索引也是可以的。

CREATE INDEX X ON `type`(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

如果索引只加在了驱动表(左表)呢?

DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book 
ON type.card = book.card;

可以看到,同样使用了join buffer。而对于驱动表来讲,即使用到了索引也要做一个整体的遍历(无非这时走的是索引文件)。而被驱动表没有索引,那么性能会相对较慢。

如下图所示,从其查询成本我们也可以看到显著区别。


结论: 左(外)连接时,索引加在右表的连接字段。left join用于确定如何从右表搜索行,左表一定都有。同理,右(外)连接时,索引创建在左表的连接字段。该连接字段在两个表中的数据类型保持一致。

此外,从上面Using where; Using join buffer (Block Nested Loop)我们也可以想到,如果服务器允许,那么join buffer给一个较大的容量是有助于提升性能的。

【2】内连接INNER JOIN

我们去掉索引,然后查看执行计划。

DROP INDEX X ON `type`;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

我们给被驱动表 book.card 添加索引

CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;


我们再给驱动表type添加索引

CREATE INDEX X ON `type`(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;

可以看到book作为了驱动表,type作为了被驱动表。即,对于内连接来讲,如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表出现。


如果两个表数据量不一致呢?比如这里我们type为40条,book为20条。

EXPLAIN  SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book 
ON type.card = book.card;


结论: 对于内连接来说,在两个表的连接条件都存在索引的情况下,会选择小表作为驱动表,即“小表驱动大表”


【3】join的底层原理

join方式连接多个表,本质就是各个表之间数据的循环匹配。MySQL5.5版本之前,MySQL只支持一种表间关联方式,就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大,则join关联的执行时间会非常长。在MySQL5.5以后的版本中,MySQL通过引入BNLJ算法来优化嵌套执行。

① 驱动表和被驱动表

驱动表就是主表,被驱动表就是从表、非驱动表。一个很明确的判断依据就是explain的执行计划,如下所示,type在上方,那么type为驱动表,book在下方, book为被驱动表。

对于内连接来说select * from A join B on ...,A不一定为驱动表。优化器会根据查询语句进行优化,决定先查哪张表。先查询的表就是驱动表,反之就是被驱动表。


对于外连接来说,select * from A left join B on ... 。通常大家会认为A就是驱动表,B就是被驱动表。不过这不是一定的,我们可以验证。

准备如下数据:

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;
CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;
INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

测试1

EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) 
WHERE (a.f2=b.f2);


如上图所示,A有索引,B没有索引。查询优化器决定以B表驱动A表。


测试2

EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) 
AND (a.f2=b.f2);

没有用到索引,且A表驱动B表。


测试3

EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) 
WHERE (a.f2=b.f2);


可以看到,其和测试1效果是一样的。

② Simple Nested-Loop Join(简单嵌套循环连接)

该算法相当简单,从表A中取出一条数据,遍历表B,将匹配到的数据放到result…以此类推,驱动表A中的每一条记录与被驱动表B的记录进行判断。

可以看到这种方式效率是非常低的,以上述表A数量100条,表B数据1000条计算,则A*B=10万次。开销统计如下:

image.png

当前MySQL肯定不会这么粗暴的去进行表的连接,所以就出现了后面的两种对Nesed-Loop Join优化算法。

③ Index Nested-Loop Join(索引嵌套循环连接)

ndex Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数,所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较,这样极大的减少了对内层表的匹配次数。


驱动表中的每条记录通过被驱动表的索引进行访问,因为索引查询的成本是比较固定的,故MySQL优化器都倾向于使用记录数少的表作为驱动表(外表)。

image.png

如果被驱动表加索引,效率是非常高的。但如果索引不是主键索引,还需要进行一次回表查询。所以如果被驱动表的索引是主键索引,效率会更高。

④ Block Nested-Loop Join(块嵌套循环连接)

如果存在索引,那么会使用index的方式进行join。如果join的列没有索引,被驱动表要扫描的次数太多了。每次访问被驱动表,其表中的记录都会被加载到内存中。然后再从驱动表中取一条与其匹配,匹配结束后清除内存。然后再从驱动表中加载一条记录,然后再把被驱动表的记录加载到内存中进行匹配…这样周而复始,大大增加了IO的次数。为了减少被驱动表的IO次数,就出现了Block Nested-Loop Join的方式。


不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区。将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表。被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率。


注意,这里换成的不只是关联表的列,select后面的列也会缓存起来。在一个有N个join关联的SQL中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段,可以让join buffer中可以存放更多的列。


40da6789d8894fa6bbab025096000930.png

image.png

参数设置

block_nested_loop

SHOW VARIABLES LIKE '%optimizer_switch%';

如下所示,这里我们可以看到block_nested_loop=on,也就是默认是开启的。

index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on

join_buffer_size

驱动表能不能一次加载完,要看join buffer能不能存储所有的数据,默认情况下是256K。

SHOW VARIABLES LIKE '%join_buffer_size%';

⑥ Join小结

1.整体效率比较:INLJ > BNLJ > SNLJ

2.永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(小的度量单位指的是:表行数*每行大小)。

# 推荐
select t1.b,t2.* from t1 straight_join t2 on(t1.b=t2.b) 
where t2.id<=100;
#不推荐
select t1.b,t2.* from t2 straight_join t1 on(t1.b=t2.b)
where t2.id<=100;

3.为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)

4.增大join_buffer_size的大小(一次缓存的数据越多,那么内存表的扫描次数就越少)。

5.减少驱动表不必要的字段查询(字段越少,join buffer所缓存的数据就越多)。

【4】Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join,默认都会使用hash join 。

Nested Loop

对于被连接的数据子集较小的情况,Nested Loop是个较好的选择。

Hash Join 是做大数据集连接时的常用方式,优化器使用两个表中较小(相对较小)的表利用 join key在内存中建立散列表,然后扫描较大的表并探测散列表,找出与hash表匹配的行。

  • 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和。
  • 在表很大的情况下并不能完全放入内存,这时优化器会将它分隔成若干不同的分区。不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高IO的性能。
  • 它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。大多数人都说它是join的重型升降机。Hash join只能应用于等值连接,这是由Hash的特点决定的。


image.png

【5】子查询优化

MySQL从4.1版本开始支持子查询,使用子查询可以进行select语句的嵌套查询。即一个select查询的结果作为另一个select语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是MySQL的一项重要的功能,可以帮助我们通过一个SQL语句实现比较复杂的查询。但是子查询的执行效率不高。原因如下:

  • 执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。这样会消耗过多的CPU和IO资源,产生大量的慢查询。
  • 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。
  • 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

在MySQL中,可以使用连接(join)查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引的话,性能就会更好。

① 查询学生表中是班长的学生信息

使用子查询

#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);
#查询班长的信息
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);

使用多表查询:推荐

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;

对比二者的执行计划,可以看到使用子查询明显建立了临时表。故整体成本关联查询是优于子查询的。

② 查询不为班长的学生信息

使用子查询

EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
      SELECT monitor FROM class b 
      WHERE monitor IS NOT NULL) 

使用多表查询

EXPLAIN SELECT SQL_NO_CACHE a.*
FROM  student a LEFT OUTER JOIN class b 
ON a.stuno =b.monitor
WHERE b.monitor IS NULL;

对比二者的执行计划,子查询采用了SUBQUERY,而关联查询均是SIMPLE。整体成本关联查询是优于子查询的。

结论:尽量不要使用not int或者not exists,用left join XXX on XX where XX is null替代。

注意:这里说的整体成本是考虑了临时表的创建、回收中消耗的CPU、IO资源。如果单纯从查询时间上来讲,不同数量级二者效果是不一样的。并没有说多表关联查询在查询时间上一定快于子查询。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
23天前
|
缓存 算法 关系型数据库
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
|
24天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
102 15
MySQL底层概述—7.优化原则及慢查询
|
24天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
15天前
|
监控 关系型数据库 MySQL
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
47 9
|
9天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
21 2
|
26天前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
62 23
|
11天前
|
算法 关系型数据库 MySQL
join查询可以⽆限叠加吗?MySQL对join查询有什么限制吗?
大家好,我是 V 哥。本文详细探讨了 MySQL 中 `JOIN` 查询的限制及其优化方法。首先,`JOIN` 查询不能无限叠加,存在资源(CPU、内存、磁盘 I/O)、性能和语法等方面的限制。过多的 `JOIN` 操作会导致数据库性能急剧下降。其次,介绍了三种常见的 `JOIN` 查询算法:嵌套循环连接(NLJ)、索引嵌套连接(INL)和基于块的嵌套循环连接(BNL),并分析了它们的触发条件和性能特点。最后,分享了优化 `JOIN` 查询的方法,包括 SQL 语句优化、索引优化、数据库配置调整等。关注 V 哥,了解更多技术干货,点赞👍支持,一起进步!
|
14天前
|
关系型数据库 MySQL
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
28 3
|
26天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。