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。

相关文章

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言：第一步：第二步：第三步：总结：前言：当你想通过命令窗口想打开mysql时候发现提http://www.cpp
阅读更多...
MySQL大表数据的分区与分库分表的实现

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区？1.2 分区的类型1.3 分区的优点1.4 分
阅读更多...
MySQL错误代码2058和2059的解决办法

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》：本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法（敲重点！！！）1. php前置理解2058和2059的错误
阅读更多...
Mysql删除几亿条数据表中的部分数据的方法实现

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T
阅读更多...
MySQL INSERT语句实现当记录不存在时插入的几种方法

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面：本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE
阅读更多...
MySQL Workbench 安装教程(保姆级)

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言：详细步骤：一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本，要是
阅读更多...
mysql数据库重置表主键id的实现

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中，难免在做测试的时候会生成一些杂乱无章的SQL主键数据，当我们
阅读更多...
浅谈mysql的sql_mode可能会限制你的查询

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景：问题描述原因分析：解决方案：第一种：修改后，只有当前生效，若是mysql服务重启，就会失效；
阅读更多...
MySQL多列IN查询的实现

MySQL多列IN查询的实现

《MySQL多列IN查询的实现》多列IN查询是一种强大的筛选工具,它允许通过多字段组合快速过滤数据,本文主要介绍了MySQL多列IN查询的实现,具有一定的参考价值,感兴趣的可以了解一下... 目录一、基础语法：多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析与优化1.
阅读更多...
MySQL新增字段后Java实体未更新的潜在问题与解决方案

MySQL新增字段后Java实体未更新的潜在问题与解决方案

《MySQL新增字段后Java实体未更新的潜在问题与解决方案》在Java+MySQL的开发中,我们通常使用ORM框架来映射数据库表与Java对象,但有时候,数据库表结构变更（如新增字段）后,开发人员可... 目录引言1. 问题背景：数据库与 Java 实体不同步1.1 常见场景1.2 示例代码2. 不同操作
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2