数据库的多表运动（Join）

走进阿里索引规约

阿里索引规约

阿里Java开发手册，索引规约中，强制规范我们禁止对三个及以上的表进行 join ，并且就算两个表的 join 也要注意索引，SQL性能。

阿里的这一条规约说明虽然 join 很好用，但是使用不当会对我们的性能造成极大的问题，接下来我们来探究下 join 的神秘面纱

什么是 join

join 分类

从图中，可以简单归纳 join 就是可以根据某些条件把指定的表给结合起来并将数据返回给客户端。

在归纳一句，只要是关联查询，首先都是进行笛卡尔积（记住它），然后根据连接条件和where条件进行条件过滤。

接下来，我们拿 LEFT JOIN 来举个栗子 ✍

产品 ： 帅哥，我想知道各个手机库存还有多少

你： 好，马上给你查出来

item 表

item_stock 表

SQL语句：

SELECT
 t1.title,
 t2.stock 
FROM
 item AS t1
 LEFT JOIN item_stock AS t2 ON t1.id = t2.item_id 

结果：

产品 ： 为什么没有苹果手机的库存？

你： 因为我使用的是 LEFT JOIN 。简单来说，即使右表中没有匹配，也从左表返回所有的行

看着 产品 若有所思的走去的背影，我偷偷关掉了关于 join 的菜鸟教程

接下来附上建表语句，配合上面的菜鸟基础教程，小伙伴们可以试一下不同的 Join 会有什么不一样的惊喜！

建表语句

CREATE TABLE `item` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `price` DECIMAL(5,1) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `item_stock` ( `id` int(11) NOT NULL, `stock` varchar(255) NOT NULL, `item_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `item_id` (`item_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

深入理解 join

执行 join 语句的时候其实是会有一个比较的过程

在MySQL中，逐条比较两个表的语句是较慢的一种操作，因此我们可以把两个表中数据依次读进一个内存块中,

以MySQL的InnoDB引擎为例，使用以下语句我们必然可以查到相关的内存区域

show variables like ‘%buffer%’

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

并且，在 MySQL 中，每个表都都会有一个.ibd文件在硬盘上，借此来存储表的数据

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

所以？我们不应该 join 过多的表，因为这是一个非常影响性能的操作

案例引入（一个因为SQL join引发的血案）

join 虽好，但是可不能乱用哦

最近，小王公司的系统频繁的发生内存泄露，把server上的dump文件下载下来打开却是损坏的。

用 Jconsle 监控 server 发现，线程的数量和内存的使用率都在不断上升。服务器重启后还好，运行一天之后就会发现线程数量又是很多，内存使用率也很高，监控 GC 的日志发现 full GC一直被调用，然后 metaspace 的空间不能得到释放，

然后这个版本和上个版本之间唯一的区别就是几个 native sql。在 Jconsle 的线程页面可以看到一些错误是关于其中一个修改过的方法的，这个方法以前是调用 Hibernate 的 get 方法，现在改成了native sql。

经过 review，发现这个 SQL语句 使用了很大left join，一共left join了8张表，其中3张对于主表是多对多或者多对一的关系

SELECT * 
FROM 主表
LEFT JOIN a
LEFT JOIN b
LEFT JOIN c
LEFT JOIN d LEFT JOIN e LEFT JOIN f LEFT JOIN dd LEFT JOIN ee WHERE id=:id 

其中a,b,c都返回多条数据，a返回101条，b返回218条，c返回204条，其它表返回一条，相乘之后正好返回4491672条数据。

虽然我们已经知道了join是笛卡尔集，但是因为开发中往往用到这种many-to-one的join比较少所以容易忽略。虽然每一张表只返回一两百条数据，但是笛卡尔集就是几百万。

血一样的教训，告诉我们，记得遵守阿里开发手册，不去 join 过多的表

**留个问题 **

我们知道 join_buffer_size 是用来表示连接缓存池大小，那么在 linux 上，我们可以使用 free 命令去查询内存以及缓存的使用情况，那么这个缓存可以随便的清除掉吗？为什么？

微信搜一搜，看看是否和你想的一致！

文章永久链接：https://tech.souyunku.com/29277