- DBA禁用join
- 若有两个大小不同的表做join,用哪个表做驱动表?
今天这篇文章,我就先跟你说说join语句到底是怎么执行的,然后再来回答这两个问题。
示例表:
- 往表t2里插入了1000行数据
- 在表t1里插入的是100行数据
可见,两表都有一个主键索引id和一个索引a
Index Nested-Loop Join
select * from t1 straight_join t2 on (t1.a = t2.a);
若直接使用join语句,MySQL优化器可能会选择表t1或t2作为驱动表,这会影响我们分析SQL语句的执行过程。为便于分析执行过程中的性能,改用straight_join让MySQL使用固定的连接方式执行查询,这样优化器只会按照我们指定的方式去join。所以,该语句里:
- t1 是驱动表
- t2是被驱动表
- 使用索引字段join的 explain结果
t2的字段a上有索引,join过程用了该索引,因此该语句执行流程:
- 从t1读入一行数据 R
- 从数据行R中,取出a字段到t2里查找
- 取出t2中满足条件的行,跟R组成一行,作为结果集一部分
- 重复执行步骤1到3,直到t1的末尾循环结束
这个过程是先遍历t1,然后根据从t1中取出的每行数据中的a值,去t2中查找满足条件的记录。形式上和我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,称之为“Index Nested-Loop Join”,NLJ。
- Index Nested-Loop Join算法的执行流程
TODO
该流程:
- 对驱动表t1做了全表扫描,需扫描100行
- 对于每一行R,根据a字段去t2查找,这是树搜索。由于构造数据一一对应,因此每次搜索过程都只扫描一行,共扫描100行
- 所以,整个执行流程,总扫描行数是200
所以能不能使用join?
假设不使用join,那就只能用单表查询:
select * from t1
查出t1所有数据,这里有100行。
循环遍历这100行数据:
- 从每一行R取出字段a的值$R.a
- 执行select * from t2 where a=$R.a
- 把返回的结果和R构成结果集的一行
该查询过程,也扫描了200行,但共执行了101条语句,比join多了100次交互。而且客户端还要自己拼接SQL语句和结果。
这性能还不如直接join。
怎么选择驱动表?
该示例中,驱动表t1走全表扫描,被驱动表t2走树搜索。
假设被驱动表行数M。每次在被驱动表查一行数据,要先搜索索引a,再搜索主键索引。每次搜索一棵树的时间复杂度log2M,所以在被驱动表上查一行的时间复杂度是 2*log2M。
假设驱动表行数N,执行过程就要扫描驱动表N行,然后对每一行,到被驱动表上匹配一次。
因此整个执行过程,时间复杂度是 N + N*2*log2M。N扩大1000倍,扫描行数就会扩大1000倍;而M扩大1000倍,扫描行数扩大不到10倍。
可见,N严重影响扫描行数,应该让小表做驱动表。
小结
- 使用join语句,性能比强行拆成多个单表执行SQL语句的性能要好
- 如果使用join语句的话,需要让小表做驱动表。
这些结论的前提是“可以使用被驱动表的索引”。
若被驱动表用不上索引呢?