以前写过MySQL开发规范，SQL设计中有一条建议：避免使用JOIN和子查询，禁止使用3表以上的JOIN，必要时推荐用JOIN代替子查询。

1.解释

避免使用JOIN和子查询，有两层解释

第一层是数据库设计合理，不需要使用JOIN或者子查询便可满足业务。

第二层是如果需要用JOIN或子查询实现，可以想想有没有替代JOIN、子查询的方案。

2.原因

不建议使用JOIN和子查询，主要是性能问题。

2.1JOIN

使用JOIN时，被驱动表是否使用索引，性能差别极大。如果驱动表是t1，行数为N，被驱动表是t2，行数为M

2.1.1使用索引

被驱动表使用索引情况下，SQL语句为select * from t1 straight_join t2 on (t1.a=t2.a)，其中a为索引，则执行流程如下：

1. 对驱动表 t1 做了全表扫描，获取到指定数据集{R}，共N个数据；
2. 而对于t1中每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。假设t1和t2数据一一对应，那么每次的搜索过程都只会扫描到一行，也是总共扫描N行；
3. 所以，整个执行流程，总扫描行数是 2*N。

整个流程近似复杂度是N + N*2*log2M，通过这个公式能看出N越大，消耗越大，所以需要用小表做驱动表。不同SQL得出的复杂度不同，需具体问题具体分析。

如果不强制指定驱动表，MySQL会自动计算出合适的驱动表，但无法保证100%选择准确。

所以在用到被驱动表索引的情况下，使用 JOIN 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好。因为强拆方案，总扫描行数不变，但是客户端和MySQL之间交互增多，还需要客户端自己处理数据。

2.1.2不使用索引

如果被驱动表上没有可用索引，SQL语句为select * from t1 straight_join t2 on (t1.a=t2.b)，其中b不是索引，则流程如下：

1. 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入内存；
2. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

这里是把驱动表的数据读入内存join_buffer。之所以放入join_buffer，是因为内存判断比从表里取出数据判断要快一些，但是比较次数仍为M*N量级，所以被驱动表如果没有可用索引，就别使用JOIN了。

放得下

如果join_buffer放得下，则近似复杂度为（N+M）次扫描+（M*N）次内存判断

放不下

如果join_buffer放不下，则会将驱动表里的数据分多段、多次放入join_buffer，每次放置后，被驱动表和join_buffer里的数据做比较，最终近似复杂度为（N+K*M）次扫描+（N*M）次内存判断

其中K是段数，在join_buffer大小一定的情况下，驱动表越小，分段越少，性能越高。

在MySQL Explain详解里创建过两张表，拿来做测试，可以发现JOIN使用join_buffer：

mysql> explain select * from trace_sp_info straight_join trace_sp_info2 on (trace_sp_info.id=trace_sp_info2.type);

2.2子查询

2.2.1分类

子查询又称内部查询，而包含子查询的语句称之外部查询（又称主查询）。所有的子查询可以分为两类，即相关子查询和非相关子查询。

相关子查询

相关子查询表示两个查询之间有一定的条件关联，可以理解为2层循环，要想执行内层的查询，需要先从外层查询到1个值出来。执行的顺序是，父查询1个值，子查询对这个得到的值进行1轮查询，总查询次数是m*n。

因为子查询需要父查询的结果才能执行，所以叫相关子查询，样例SQL如下：

select t.id,t.name,t.pass from student t where 80<=(select f.score from f
 where f.id=t.id and f.name='xxx')

非相关子查询（嵌套子查询）

独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询，样例SQL如下：

select t.id,t.name,t.pass from t where t.id in (select f.id from  f where f.score=70)

由于2个查询是分开的，无关联的，子查询不需要父查询把结果传进来，所以叫不相关子查询。执行顺序是子查询先执行，得到结果后传给父查询，父查询就不用每次查询完1个值后再执行1轮子查询。

2.2.2执行过程

相关子查询

执行过程

1. 从外层查询中取出一个元组（即一行），将元组相关列的值传给内层查询。
2. 执行内层查询，得到子查询操作的值。
3. 外查询根据子查询返回的结果或结果集得到满足条件的行。
4. 然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。

非相关子查询（嵌套子查询）

执行过程

1. 执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。
2. 执行外部查询，并显示整个结果。

2.3JOIN与子查询比较

JOIN和子查询扫描、比较次数都比较多，都不是特别好的方案。

执行某些子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程，所以要比JOIN性能差一些。

通过explain可以看出，子查询在某些情况下用到了临时表：

mysql> explain select * from trace_sp_info where type in (select type from trace_sp_info2);

3.总结

这次聊了JOIN和子查询性能差的原因，所以生产中应尽量避免使用JOIN和子查询。需二选一的话，大概率使用JOIN替代子查询。

使用JOIN时，要用小表做驱动表，并且一定要保证用了被驱动表的索引。虽然这种方式性能上，比强行拆成多个单表执行 SQL 语句要好，但也需要考虑DB承担的业务压力增大，是否会对生产环境产生影响。

资料

1. 数据库子查询和join的比较
2. MySQL的语句执行顺序和子查询执行顺序
3. mysql将查询结果作为临时表查询_mysql使用查询结果作为临时表
4. 相关子查询与不相关子查询的区别是什么？
5. 解析MYsql explain执行计划extra列输出

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

我的个人博客为：https://shidawuhen.github.io/

往期文章回顾：

1. 设计模式
2. 招聘
3. 思考
4. 存储
5. 算法系列
6. 读书笔记
7. 小工具
8. 架构
9. 网络
10. Go语言