为什么 SQL 语句不要过多的 join？

Joe.Ye • 2023-04-01 • MySQL

free命令缓存buff/cache

查看内存的命令：free 或者 top，其中使用free命令都可以看到信息

$ free -mh
              total        used        free      shared  buff/cache   available
Mem:           2.0G        516M        677M         23M        806M        1.2G
Swap:            0B          0B          0B

total 总内存
used 已用内存
free 空闲内存
buff/cache 已使用的缓存
avaiable 可用内存

清理已使用的缓存(buff/cache)：

sync; echo 3 > /proc/sys/vm/drop_caches

但是注意在线上切勿执行这条命令，原因之一可以通过 MySQL 的 join指令说明

SQL Join

SQL中的join可以根据某些条件把指定的表给结合起来并将数据返回

join的方式有：5 种

inner join 内连接

left join 左连接

right join 右连接

full join 全连接

在项目开发中如果需要使用join语句，如何优化提升性能?

数据规模较小：可全部塞进内存
数据规模较大：可以通过增加索引来优化join语句的执行速度，可以通过冗余信息来减少join的次数，尽量减少表连接的次数，一个SQL语句表连接的次数不要超过5次

join语句是相对比较耗费性能，因为在执行join语句的时候必然要有一个比较的过程，逐条比较两个表的语句是比较慢的，因此我们可以把两个表中数据依次读进一个内存块中，以MySQL的InnoDB引擎为例，使用语句show variables like '%buffer%'可以查到相关的内存区域

mysql> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 8388608        |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| key_buffer_size                     | 8388608        |
| myisam_sort_buffer_size             | 8388608        |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 131072         |
| read_rnd_buffer_size                | 262144         |
| sort_buffer_size                    | 262144         |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
22 rows in set (0.00 sec)

其中join_buffer_size的大小将会影响我们join语句的执行性能

大部分数据库中的数据最终要保存到硬盘上，并且以文件的形式进行存储。以MySQL的InnoDB引擎为例：

InnoDB以页(page)为基本的IO单位，每个页的大小为16KB
InnoDB会为每个表创建用于存储数据的.ibd文件

这意味着我们有多少表要连接就需要读多少个文件，虽然可以利用索引，但还是免不了频繁的移动硬盘的磁头

也就是说频繁的移动磁头会影响性能，现在的开源框架都喜欢说自己通过顺序读写大大的提升了性能，比如hbase、kafka

那Linux有对此做出优化吗？再执行一次free命令看一下

$ free -m
              total        used        free      shared  buff/cache   available
Mem:           2.0G        610M        198M         23M        1.2G        1.1G
Swap:            0B          0B          0B

奇怪缓存怎么竟然占用1.2G多

buff/cache里面存的是什么？
为什么buff/cache占了那么多内存，可用内存即availlable还有1.1G？
为什么可以通过两条命令来清理buff/cache占用的内存，而想要释放used只能通过结束进程来实现？

这么随便就释放了buff/cache所占用的内存，说明它就不重要，清除它不会对系统的运行造成影响

想起来《CSAPP》（深入理解计算机系统）里面说过一句话

存储器层次结构的本质是，每一层存储设备都是较低一层设备的缓存

翻译成人话，就是说Linux会把内存当作是硬盘的高速缓存

相关资料：http://tldp.org/LDP/sag/html/buffer-cache.html

Join算法

面试官：如果让你来实现Join算法你会怎么做？

我：无索引的话，嵌套循环就完事了嗷。有索引的话，则可以利用索引来提升性能.

面试官：说回join_buffer 你认为join_buffer里面存储的是什么？

我：在扫描过程中，数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer

面试官：有索引的情况下是怎么处理的？

我：这个就比较简单了，直接读取两个表的索引树进行比较就完事了嗷，我这边介绍一下无索引的处理方式

（1）Nested Loop Join

sql = 'select * from outerTable left join innerTable on'
      + 'outerTable.id = innerTable.id'
for (outerRow in outerTable) {
  for (innerRow in innerTable) {
    if (compare(outerRow, innerRow)) {
      resultRow combine(outerRow, innerRow)
      sendToReplyBuffer(resultRow)
    }
  }
}

嵌套循环，每次只读取表中的一行数据，也就是说如果outerTable有10万行数据，innerTable有100行数据，需要读取10000000次（假设这两个表的文件没有被操作系统给缓存到内存，我们称之为冷数据表）

当然现在没啥数据库引擎使用这种算法（太慢了）

（2）Block Nested Loop

byte[] joinBuffer = new byte[joinBufferSize]

for (; fi11(joinBuffer, outerTable) != EOF;) {
  for (innerRow in innerTable) {
    for (outerRow in joinBuffer) {
      if (compare(outerRow, innerRow) {
        resultRow = combine(outerRow, innerRow)
        sendToReplyBuffer(resultRow)
      }
    }
  }
}

Block 块，也就是说每次都会取一块数据到内存以减少I/O的开销

当没有索引可以使用的时候，MySQL InnoDB 就会使用这种算法

考虑以下两个表 t_a 和t_b

CREATE TABLE `t_a` (
  `id` int(11) NOT NULL,
  `content` varchar(32) DEFAULT NULL,
  `str_idx` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `t_a` ADD PRIMARY KEY (`id`), ADD KEY `str_idx` (`str_idx`);
ALTER TABLE `t_a` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `t_b` (
  `id` int(11) NOT NULL,
  `content` varchar(32) DEFAULT NULL,
  `str_idx` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `t_b` ADD PRIMARY KEY (`id`), ADD KEY `str_idx` (`str_idx`);
ALTER TABLE `t_b` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

mysql> desc t_a;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| content | varchar(32) | YES  |     | NULL    |                |
| str_idx | varchar(32) | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc t_b;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| content | varchar(32) | YES  |     | NULL    |                |
| str_idx | varchar(32) | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

当无法使用索引执行join操作的时候，InnoDB会自动使用 Block Nested Loop 算法

mysql> explain select * from t_b left join t_a on t_b.content = t_b.content;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t_b   | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL                                               |
|  1 | SIMPLE      | t_a   | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

总结

上学时，数据库老师最喜欢考数据库范式，直到上班才学会一切以性能为准，能冗余就冗余，实在冗余不了的就join。如果join真的影响到性能，试着调大join_buffer_size，或者换固态硬盘。

参考资料：

《深入理解计算机系统》- 第6章存储器层次结构
https://www.linuxatemyram.com/play.html 作者通过几个例子来说明硬盘缓存对程序执行性能的影响
https://www.linuxatemyram.com/ Free参数的解释
https://www.thegeekdiary.com/how-to-clear-the-buffer-pagecache-disk-cache-under-linux/ 文章开头送分题命令的解释
https://juejin.im/book/5bffcbc9f265da614b11b731/section/5c061a4de51d451df113c10d MySQL 是怎样运行的：从根儿上理解 MySQL
https://mariadb.com/kb/en/block-based-join-algorithms/ 来自MariaDB官方文档解释了Block-Nested-Loop算法的实现

转载至：https://blog.csdn.net/u013939918/article/details/106736557

版权声明：
作者：Joe.Ye
链接：https://www.appblog.cn/index.php/2023/04/01/why-dont-sql-statements-have-too-many-joins/
来源：APP全栈技术分享
文章版权归作者所有，未经允许请勿转载。

THE END

二维码

打赏

海报

为什么 SQL 语句不要过多的 join？

free命令缓存buff/cache 查看内存的命令：free 或者 top，其中使用free命令都可以看到信息 $ free -mh total used free sha……

关于 MySQL InnoDB 锁的超全总结

<<上一篇

NumPy快速入门指南

下一篇>>

文章目录

关闭

搜索内容

为什么 SQL 语句不要过多的 join？

free命令缓存buff/cache

SQL Join

Join算法

总结

取消回复

共有 0 条评论

热门文章

最新评论