MySQL JOIN优化

2024-09-01 21:32
文章标签 mysql 优化 join database

本文主要是介绍MySQL JOIN优化，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

表初始化

CREATE TABLE t1(id INT PRIMARY KEY, a INT, b INT, INDEX(a));
CREATE TABLE t2 LIKE t1;DROP PROCEDURE idata;
DELIMITER ;;
CREATE PROCEDURE idata()
BEGINDECLARE i INT;SET i=1;WHILE (i <= 1000) DOINSERT INTO t1 VALUES (i,1001-i,i);SET i=i+1;END WHILE;SET i=1;WHILE (i <= 1000000) DOINSERT INTO t2 VALUES (i,i,i);SET i=i+1;END WHILE;
END;;
DELIMITER ;CALL idata();

Multi-Range Read

MRR的目的：尽量使用顺序读盘

回表

SELECT * FROM t1 WHERE a>=1 AND a<=100;

如果随着a递增的顺序进行查询的话，id的值会变成随机的，就会出现随机访问，性能相对较差
在这里插入图片描述

MRR

  1. 根据索引a，定位到满足条件的记录，将id的值放入read_rnd_buffer中
  2. 将read_rnd_buffer中的id进行递增排序
  3. 排序后的id值，依次到主键索引中查找
  4. 如果read_rnd_buffer满，先执行完第2步和第3步，然后清空read_rnd_buffer，继续遍历索引a
-- 默认值为256KB
-- 8388608 Bytes = 8 MB
mysql> SHOW VARIABLES LIKE '%read_rnd_buffer_size%';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| read_rnd_buffer_size | 8388608 |
+----------------------+---------+-- mrr_cost_based=on：现在的优化器基于消耗的考虑，更倾向于不使用MRR
mysql> SHOW VARIABLES LIKE '%optimizer_switch%'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switchValue: 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,use_invisible_indexes=off-- 稳定启动MRR优化
SET optimizer_switch='mrr_cost_based=off';

执行流程

在这里插入图片描述

explain

mysql> SET optimizer_switch='mrr_cost_based=on';
Query OK, 0 rows affected (0.00 sec)-- 优化器没有选择MRR
mysql> EXPLAIN SELECT * FROM t1 WHERE a>=1 AND a<=100;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |  100 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql> SET optimizer_switch='mrr_cost_based=off';
Query OK, 0 rows affected (0.00 sec)-- 优化器选择了MRR
mysql> EXPLAIN SELECT * FROM t1 WHERE a>=1 AND a<=100;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |  100 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+

小结

MRR提升性能的核心：能够在索引a上做范围查询，得到足够多的主键，完成排序后再回表，体现出顺序性的优势

NLJ优化

NLJ算法

从驱动表t1，一行行地取出a的值，再到被驱动表t2去join，此时没有利用到MRR的优势
在这里插入图片描述

BKA优化

Batched Key Access，是MySQL 5.6引入的对Index Nested-Loop Join（NLJ）的优化
在这里插入图片描述

  1. BKA优化的思路：复用join_buffer
  2. 在BNL算法中，利用了join_buffer来暂存驱动表的数据，但在NLJ里面并没有利用到join_buffer
  3. 在join_buffer中放入的数据为P1~P100，表示只会取查询所需要的字段
    • 如果join_buffer放不下P1~P100，就会将这100行数据分成多段执行

启动

-- BKA算法依赖于MRR
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

BNL（Block Nested-Loop Join）优化

性能问题

  1. 使用BNJ算法，可能会对被驱动表做多次扫描，如果被驱动表是一个大的冷数据表，首先IO压力会增大
  2. Buffer Pool的LRU算法
    1. 第一次从磁盘读入内存的数据页，会先放在old区
    2. 如果1s后这个数据页不再被访问，就不会被移动到LRU链表头部，对Buffer Pool的命中率影响不大
  3. 如果一个使用了BNJ算法的Join语句，多次扫描一个冷表
    1. 如果冷表不大，能够完全放入old区
      1. 再次扫描冷表的时候，会把冷表的数据页移到LRU链表头部，不属于期望的晋升
    2. 如果冷表很大，业务正常访问的数据页，可能没有机会进入young区
      1. 一个正常访问的数据页，要进入young区，需要隔1S后再次被访问
      2. 由于Join语句在循环读磁盘和淘汰内存页，进入old区的数据页，很有可能在1S内被淘汰
      3. 正常业务访问的数据页也一并被冲掉，影响正常业务的内存命中率
  4. 大表Join虽然对IO有影响，但在语句执行结束后，对IO的影响也就结束了
    1. 但对Buffer Pool的影响是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率、
    2. 为了减少这种影响，可以考虑适当地增大join_buffer_size，减少对被驱动表的扫描次数
  5. 小结
    1. 可能会多次扫描被驱动表，占用磁盘IO资源
    2. 判断Join条件需要执行M∗N次对比，如果是大表会占用非常多的CPU资源
    3. 可能会导致Buffer Pool的热数据被淘汰和正常的业务数据无法成为热数据，进而影响内存命中率
  6. 如果优化器选择了BNL算法，就需要做优化
    1. 给被驱动表Join字段加索引，把BNL算法转换成BKA算法
    2. 临时表

不适合建索引

t2中需要参与Join的只有2000行，并且为一个低频语句，为此在t2.b上建索引是比较浪费的

SELECT * FROM t1 JOIN t2 ON (t1.b=t2.b) WHERE t2.b>=1 AND t2.b<=2000;

采用BNL

  1. 取出t1的所有字段，存入join_buffer（无序数组），完全放得下
  2. 扫描t2，取出每一行数据跟join_buffer中的数据进行对比
    1. 如果不满足t1.b=t2.b，则跳过
    2. 如果满足t1.b=t2.b，再判断是否满足其它条件，如果满足就作为结果集的一部分返回，否则跳过
  3. 等值判断的次数为1000100W=10亿，计算量很大
-- 使用BNL算法
mysql> EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.b=t2.b) WHERE t2.b>=1 AND t2.b<=2000;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   1000 |   100.00 | Using where                                        |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 998414 |     1.11 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+-- 执行耗时为75S，非常久！
mysql> SELECT * FROM t1 JOIN t2 ON (t1.b=t2.b) WHERE t2.b>=1 AND t2.b<=2000;
...
|  999 |    2 |  999 |  999 |  999 |  999 |
| 1000 |    1 | 1000 | 1000 | 1000 | 1000 |
+------+------+------+------+------+------+
1000 rows in set (1 min 15.29 sec)# Time: 2019-03-11T12:04:49.066846Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 75.288703  Lock_time: 0.000174 Rows_sent: 1000  Rows_examined: 1001000
SET timestamp=1552305889;
SELECT * FROM t1 JOIN t2 ON (t1.b=t2.b) WHERE t2.b>=1 AND t2.b<=2000;

临时表

思路
  1. 把t2中满足条件的数据先放到临时表tmp_t中
  2. 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引
  3. 让表t1和tmp_t做join操作
执行过程
CREATE TEMPORARY TABLE temp_t (id INT PRIMARY KEY, a INT, b INT, INDEX(b)) ENGINE=InnoDB;
INSERT INTO temp_t SELECT * FROM t2 WHERE b>=1 AND b<=2000;# Time: 2019-03-11T12:20:01.810030Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.624821  Lock_time: 0.002347 Rows_sent: 0  Rows_examined: 1000000
SET timestamp=1552306801;
INSERT INTO temp_t SELECT * FROM t2 WHERE b>=1 AND b<=2000;-- 采用NLJ算法，如果batched_key_access=on，将采用BKA优化
mysql> EXPLAIN SELECT * FROM t1 JOIN temp_t ON (t1.b=temp_t.b);
+----+-------------+--------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref       | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | t1     | NULL       | ALL  | NULL          | NULL | NULL    | NULL      | 1000 |   100.00 | Using where |
|  1 | SIMPLE      | temp_t | NULL       | ref  | b             | b    | 5       | test.t1.b |    1 |   100.00 | NULL        |
+----+-------------+--------+------------+------+---------------+------+---------+-----------+------+----------+-------------+-- 执行耗时为20ms，提升很大
mysql> SELECT * FROM t1 JOIN temp_t ON (t1.b=temp_t.b);
...
|  999 |    2 |  999 |  999 |  999 |  999 |
| 1000 |    1 | 1000 | 1000 | 1000 | 1000 |
+------+------+------+------+------+------+
1000 rows in set (0.02 sec)# Time: 2019-03-11T12:20:11.041259Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.012139  Lock_time: 0.000187 Rows_sent: 1000  Rows_examined: 2000
SET timestamp=1552306811;
SELECT * FROM t1 JOIN temp_t ON (t1.b=temp_t.b);
  1. 执行INSERT语句构造tmp_t表并插入数据的过程中，对t2做了全表扫描，扫描行数为100W
  2. JOIN语句先扫描t1，扫描行数为1000，在JOIN的比较过程中，做了1000次带索引的查询

Hash Join

  1. 如果join_buffer维护的不是一个无序数组，而是一个哈希表，那只需要100W次哈希查找即可\
  2. MySQL目前不支持Hash Join，业务端可以自己实现Hash Join
    1. SELECT * FROM t1。 取t1的全部1000行数据，在业务端存入一个hash结构
    2. SELECT * FROM t2 WHERE b>=1 AND b<=2000，获取t2中满足条件的2000行数据
    3. 把这2000行数据，一行行地到hash结构去匹配，将满足匹配条件的行数据，作为结果集的一行

小结

  1. BKA是MySQL内置支持的，推荐使用
  2. BNL算法效率低，建议都尽量换成BKA算法，优化的方向是给被驱动表的关联字段加上索引
  3. 基于临时表的改进方案，对于能够提前过滤出小数据的JOIN语句来说，效果还是很明显的
  4. MySQL目前还不支持Hash Join

参考资料

《MySQL实战45讲》

这篇关于MySQL JOIN优化的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1128093。

相关文章

Vue3 的 shallowRef 和 shallowReactive：优化性能

Vue3 的 shallowRef 和 shallowReactive：优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉，那么对 shallowRef 和 shallowReactive 是否了解呢？ 在编程和数据结构中，“shallow”（浅层）通常指对数据结构的最外层进行操作，而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素，而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层（Shallow） 定义
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
使用opencv优化图片（画面变清晰）

使用opencv优化图片（画面变清晰）

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化，使其看起来更清晰，同时保持尺寸不变，通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多，主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器：相机传
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2