以前写过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为索引，则执行流程如下：
-
对驱动表 t1 做了全表扫描，获取到指定数据集{R}，共N个数据；
-
而对于t1中每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。假设t1和t2数据一一对应，那么每次的搜索过程都只会扫描到一行，也是总共扫描N行；
-
所以，整个执行流程，总扫描行数是 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不是索引，则流程如下：
-
把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入内存；
-
扫描表 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-3，直到外层的元组全部处理完毕。
非相关子查询（嵌套子查询）
执行过程
-
执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。
-
执行外部查询，并显示整个结果。
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承担的业务压力增大，是否会对生产环境产生影响。
资料
-
数据库子查询和join的比较
-
MySQL的语句执行顺序和子查询执行顺序
-
mysql将查询结果作为临时表查询_mysql使用查询结果作为临时表
-
相关子查询与不相关子查询的区别是什么？
-
解析MYsql explain执行计划extra列输出
最后
大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）
我的个人博客为：https://shidawuhen.github.io/
往期文章回顾：
2830
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
