规范上说避免使用JOIN

2023-05-04 11:14| 来源: 网络整理| 查看: 265

小知识，大挑战！本文正在参与“程序员必备小知识”创作活动。

以前写过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分类

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