面试现场：join联表的注意点、有哪些联表查询方式、真题...

嗯！那我现在有这样的两张表。

create table `t1`(
  `id` int(11) NOT NULL,
  `a`  int(11) default null,
  `b`  int(11) default null,
  primary key (`id`),
  key `a` (`a`)
)engine=innoDB;


create table `t2`(
  `id` int(11) NOT NULL,
  `a`  int(11) default null,
  `b`  int(11) default null,
  primary key (`id`),
  key `a` (`a`)
)engine=innoDB;

 其中表t1中有100行数据，表t2中有1000行数据。

你能说一下表t1和t2在进行join操作时，是t1 join t2复杂度更低呢？还是t2 join t1复杂度更低呢？

嗯嗯，具体是小表join大表，或者是谁join谁都无所谓是要根据具体的SQL以及join buffer具体的分析的！

比如我们的SQL是这样的

select * 
from t1 staight_join t2
where t1.a = t2.b

这条SQL的where条件中使用的b列并不存在任何索引。那看起来，貌似这条SQL的执行逻辑应该像下面这样：

Step1：依次扫描t1表中的每一行rx。

Step2：取出rx中的b列的值去t2表中进行全表扫描。

Step3：找到t2表中符合条件的row，和现有的数据组合作为结果

Step4：重复1～3，直到t1中的数据被遍历一遍。

这样看起来无论是t1 join t2，还是t2 join t1，都避免不了在遍历t1的同时也遍历t2，或者在遍历t2的时候遍历t1。最终都要扫描100*1000次磁盘。

But，MySQL其实没有这么做！

嗯，还有如果我们的SQL join条件如下

select * 
from t1 staight_join t2
where t1.a = t2.a

那这条SQL的执行条件如下：

Step1：遍历表t1，依次从t1中取出一行数据rx

Step2：从rx中取出列的值，使用这个值去t2表的a列这棵二级索引树中进行检索。B+Tree的查询时间复杂度是：log2(M)

Step3：拿着Step2中获取到的id列在t2中进行回表，id也有对应的B+Tree，这次查询的时间复杂度也是log2(M)，将查询到的结果组合作为返回值。

Step4：重复1～3

因为这条SQL检索t2时走了索引，所以时间复杂度肯定比上一条SQL更好。

t1肯定是走了全表扫描：100行

t2每次需要两次在B+Tree中检索：2*log2(M)

所以整体的时间复杂度是：N+2*log2(M)

所以在这种情况下，N对整体复杂度的影响最大！所以还是小表驱动大表要更高效一些。

另外刚才有说到的join buffer这个缓存。它里面会存储的驱动表中的数据。默认大小是256k。

比如我们上面的SQL语句是select * from，那么t1中的数据会被全部加载进join buffer中。

嗯，针对这种情况：解决的策略就是将t1表中的数据分段、分别加载进join buffer中。

比如t1中的100行数据被分成了80行、20行两段分别放入join buffer中进行上面说过的Step操作，但是整个过程中使用where条件比较的次数 (80+20)*1000 是不变的...

嗯嗯，是的！另外还有这样一个参数：join_buffer_size 可以控制join buffer的大小。

mysql> show variables like '%join_buffer_size%';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| join_buffer_size | 8388608 |
+------------------+---------+
1 row in set (0.08 sec)

好（内心：这泥马）

mysql> select * from t1,t2 where t1.id=t2.id;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  1  | a   |  1 | e    |
|  1 | b    |  1 | e    |
|  2  | c   |  2 | f    |
|  2 | d   |  2 | f    |
+----+------+----+------+
4 rows in set (0.00 sec)

（难是不难，你有没有被唬住？）