优化Using join buffer (Block Nested Loop)

最新推荐文章于 2023-06-27 21:16:48 发布
最新推荐文章于 2023-06-27 21:16:48 发布
阅读量514 收藏
点赞数
分类专栏： mysql 文章标签： 数据库 sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_46360888/article/details/129684860
版权
文章通过四个场景探讨了SQL查询优化中的关键因素，包括关联字段是否添加索引、右表数据量大小、字段类型一致性以及是否使用联合索引。在每个场景中，都展示了如何通过调整这些因素来提高查询性能，例如为关联字段添加索引、增加右表数据、保持字段编码一致性和创建联合索引。
摘要由CSDN通过智能技术生成

场景1：检查表之间的关联字段有没有添加索引

若右边的表的关联的字段没有索引，整体sql查询也不会走索引

表结构

学生表：有10万数据

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生评级表：有3条的数据

CREATE TABLE `student_grade` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `student_class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行sql

EXPLAIN SELECT
	s.id
FROM
	student s
	LEFT JOIN student_grade g ON s.id = g.student_id 
	AND s.class = g.student_class

结果分析

仔细分析发现student_grade的student_id、student_class字段没有添加索引，添加索引即可

场景2：检查表之间的右边的表的数据量

若右边的表的数据量很少，也不会走索引

表结构

学生表：有10万数据

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生评级表：有3条的数据

CREATE TABLE `student_grade` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `student_class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学生班级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `student_class` (`student_class`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

执行sql

EXPLAIN SELECT
	s.id
FROM
	student s
	LEFT JOIN student_grade g ON s.id = g.student_id 
	AND s.class = g.student_class

执行结果

在这里插入图片描述

结果分析

仔细检查发现是s.class = g.student_class中关联字段编码一样，然而student_grade的数据量太少，于是在student_grade表增加一条记录，结果走索引了
在这里插入图片描述

场景3：检查表之间的关联字段类型是否一致

表结构

学生表：有10万数据

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生评级表：有3条以上的数据

CREATE TABLE `student_grade` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `student_class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学生班级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `student_class` (`student_class`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

执行sql

EXPLAIN SELECT
	s.id
FROM
	student s
	LEFT JOIN student_grade g ON s.id = g.student_id 
	AND s.class = g.student_class

执行结果

在这里插入图片描述

结果分析

仔细检查发现是s.class = g.student_class中关联字段编码不一样，s.class的编码是utf8，g.student_class的编码是utf8mb4
将编码改成一致之后执行sql就走索引了
在这里插入图片描述

场景4：添加联合索引

查询的字段和表之间的关联字段之间不是联合索引

表结构

学生表：有10万数据

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `class` (`class`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生评级表：有3条以上的数据

CREATE TABLE `student_grade` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `student_class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  KEY `student_class` (`student_class`),
  KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行sql

EXPLAIN
SELECT
	s.id,s.class,s.age,g.create_time
FROM
	student s
	LEFT JOIN student_grade g ON ( s.id = g.student_id AND s.class = g.student_class )

结果分析

查询student的id,class,age之间的字段没有建立联合索引
在这里插入图片描述

解决方案

加上联合索引，结果发现走索引了

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`class`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注

以上结果仅供参考，具体根据实际的sql语句进行优化

shmily&&ylimhs
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL中Nested-Loop Join算法小结
09-10
Block Nested-Loop Join的效率提升在于使用join buffer来存储外部表的行。这样，内部表只需与buffer中的行组合进行比较，而不是与每一行单独比较。对于buffer中的行S和内部表的组合C，扫描内部表t3的次数可以用公式`...
Improving Nested Loop Pipelining on Coarse-Grained Reconfigurable Architectures
07-02
complete flow of mapping loop nests onto CGRA. Experiment results on most kernels of the Polybench show that our proposed approach can improve the performance of the kernels by 42% on average, as ...
Using join buffer (Block Nested Loop)
weixin_34013044的博客
08-23 6833
2019独角兽企业重金招聘Python工程师标准>>> ...
MySQL查询优化与调优
清风
08-01 2324
MySQL查询优化与调优，大方向上可以分为物理查询优化和逻辑查询优化两块。 物理查询优化是通过索引和表连接方式等技巧来进行优化。 逻辑查询优化是通过SQL等价变换提升查询效率。
mysql Using join buffer (Block Nested Loop) join连接查询优化
weixin_45087693的博客
05-25 2502
Block Nested Loop算法是Join Buffer的一种实现方式，它的特点是使用了类似于嵌套循环的方式进行查询，即将一个表的每一个数据块都分别和另一个表进行匹配，最终得到连接结果。使用Block Nested Loop算法需要注意两个表的大小，一般情况下，对于大小不一的两张表，应该把小表放在外面循环，而把大表放在内部循环，这样可以有效提高执行效率。在执行连接操作时，MySQL需要将两个表的数据进行组合，通常是先取出一张表的数据，然后根据指定条件在另外一张表中进行查找。
SQL优化之Using join buffer (Block Nested Loop)
u010088278的博客
08-30 9314
数据库优化作为每个程序猿必掌握之技能，在开发过程中时常遇到，特别对于互联网公司而言，一个sql的执行速度，直接影响到用户的体验。而DB攻城狮对一般企业来说又是可望不可即的存在，只能寄托给咱们苦逼的程序猿……......
数据库监控与调优【十二】—— JOIN语句优化
最新发布
qq_46601365的博客
06-27 1003
如图，存在三张表，t1、t2、t3，使用NLJ算法，执行过程大概是这样的，先查询t1中符合条件的数据，然后通过for循环遍历这些数据，在循环中查询t2表符合条件的数据，并使用reference key匹配，也就是join中的on条件。假设t3表是存在索引的，从join buffer中读取数据，用t3表的索引和join buffer匹配，筛选出匹配的索引之后，再通过MRR对t3表的主键进行排序，最后再读取t3表的数据。还是之前的三张表，因此，t1是t2的驱动表，t2是t1的被驱动表。
P5 MySQL数据类型和类型选择
Running Snail
08-29 1053
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。 目录数值类型日期和时间类型字符串类型   数值类型 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。 BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。 金钱最好用int/bigint(整数,单位用分,拿出来进行*100换成元),千万不要直接用浮点,会有精度损失.   日期和时间类型     字符串类型   CHAR 和 VARCHAR
SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化
09-09
Block Nested-Loop JOIN（BNL JOIN）是其优化版本，通过缓存部分内部表的数据，减少重复读取，提高性能。MySQL使用join_buffer_size系统变量控制每个JOIN缓冲区的大小，并且只在特定JOIN类型（如INDEX或ALL）下启用...
mysql Join使用以及优化
04-14
Index Nested-Loop Join（NLJ）是一种利用索引减少I/O次数的Join策略，当被驱动表的字段上有索引时，Join操作的效率较高，因此这也是推荐的做法。然而，如果被驱动表的字段上没有索引，则Join操作将退化为Simple ...
Using join buffer(Block Nested Loop) 优化
zjltju1203的博客
09-06 8474
最近在优化项目中的慢查询SQL，发现了一个Using Join Buffer 引起的慢查询，经过细致排查，终于解决了这个问题，将优化方式记录一下。 1，原始SQL 这是最开始的SQL，分页查询最开始的50条记录竟然话费了近2分钟的时间，这怎么能忍。 2，原始SQL分析 对原始SQL进行EXPLAIN分析，结果如图。 发现eqtType和ps这两张表在Extra中出现Using join buffer(Block Nested Loop) 。 Block Nested...
Sql优化记录之Using join buffer (Block Nested Loop)
xiaoWangZi89的博客
07-04 906
sql优化，慢查询。
Using join buffer (Batched Key Access)
weixin_33898233的博客
08-23 1096
2019独角兽企业重金招聘Python工程师标准>>> ...
MySQL中的Block Nested Loop优化分析
自律使我自由
09-22 4395
前言 一般在MySQL规范中，都会规定如果两张表进行join查询，那么join的字段一定要有索引，在之前的文章中我们分析了MySQL join大小表前后顺序影响分析，这是在有索引的情况下，今天我们再来看看如果没有索引MySQL会如何处理。 数据准备 新建了两张表，分别为t1，t2。 t1和t2表都是一个id字段作为主键，一个c字段，没有索引。 分别插入10条数据 t1，t2表数据都如下 Join查询 EXPLAIN select * from t1 LEFT JOIN t2 on t1.c = t2.c
mysql 优化 Using join buffer (Block Nested Loop)
逆天killer的博客
07-27 1711
说明:生产环境发现接口响应慢,经查是SQL执行慢,发现是表关联时候右表数据量大,循环嵌套查询速度很慢。 如图刚开始SQL执行计划 这是测试环境,生产环境fsp_cor_inf数据量很大,经过排查发现是Using join buffer (Block Nested Loop)导致的查询慢, 开始解决, 首先,在表关联的地方建立索引,然后执行发现没有效果。 然后,发现两表字符集不一致,一个是uft8一个是uft8mb4,可能这个原因导致的索引失效。 随后修改表字符集 然后发现还是没效果,原来上面修改的是表的字
MySQL 高级篇 -- EXPLAIN 分析
L小Ray想有腮
03-26 2524
定位了查询慢的 SQL 之后，我们就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。
Mysql 学习
qq_34064680的博客
04-01 1373
Mysql 学习安装使用新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 安装 检查是否安装 rpm -qa|grep -i mysql 使用 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值