MySQL的连接查询包括内连接（inner join）、外连接（left/right join，下文一律以left join代替）、交叉连接（在MySQL中等价于内连接，但是在标准SQL中是不等价的）、全连接（MySQL不支持full join，但是可以通过union构造），本文主要讲解一般我们在写SQL中最常用的：内连接以及外连接两种连接查询，通过一些案例来说明我们在使用关联查询中需要注意什么问题，要怎么做才能做到最优查询。
重点说在前面
- MySQL对表连接至今只支持nested loop join，而不支持hash join，这个是MySQL不建议执行复杂关联查询的根源（MariaDB已经实现hash join）
通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
- 优化的目标是尽可能减少关联查询中nested loop的循环次数，也就是说尽量缩小驱动表的结果集
- inner join驱动顺序由优化器自己指定，如果优化器选择有误可以使用straight_join自己指定驱动顺序以达到优化的目的
- left join驱动顺序是固定的，left join左边的表为驱动表，右边为匹配表，RIGHT JOIN则刚好相反
其实这也不是绝对的，当left join跟inner join等价的时候，MySQL优化器就会自己选择驱动表，需求请见下文“容易混淆的地方”部分
- 存在group by或者order by子句的关联查询中，如果引用的字段是驱动表的字段那么分组或者排序是可以使用索引的，如果引用的是其他匹配表的字段，那边分组或者排序动作则无法使用索引
- 在MySQL5.6以前的版本关联子查询可以用关联查询来替代优化（MySQL5.6或者更新的版本或者MariaDB等就无需替代，优化器会自动帮你优化处理）
- 在MySQL5.6以及以后的版本中，缩小匹配表的结果集也能达到优化的效果
什么叫连接查询
首先我们要搞清楚什么叫连接查询，内连接跟外连接又有什么区别？
内连接
select * from a inner join b on a.id = b.id;
等价于
select * from a,b where a.id = b.id;
外连接
select * from a left join b on a.id = b.id;
select * from a right join b on a.id = b.id;
简单来说内连接就是检索出与连接条件完全匹配的数据行；而外连接则是保留了所有驱动表的数据，匹配表中无法匹配的数据则以NULL输出，下面是一个简单的例子。
test 1-1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
| mysql> create table a (id int not null , name varchar(10) not null default ''); Query OK, 0 rows affected (0.06 sec) mysql> create table b (id int not null , name varchar(10) not null default ''); Query OK, 0 rows affected (0.00 sec) mysql> insert into a values (1,'aaa'),(2,'bbb'),(3,'ccc'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into b values (1,'aaa'),(3,'ccc'),(4,'ddd'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 +----+------+----+------+ +----+------+----+------+ | 1 | aaa | 1 | aaa | +----+------+----+------+ 2 rows in set (0.00 sec) +----+------+------+------+ +----+------+------+------+ | 1 | aaa | 1 | aaa | | 3 | ccc | 3 | ccc | +----+------+------+------+ 3 rows in set (0.00 sec)
|
使用straight_join优化inner join
内连接跟外连接的区别在于返回值范围不同，驱动表无法自行选择，其余就没什么太大的区别。同样需要注意驱动表选择、需要注意分组或者排序的选择等等。这里我们通过下面这个案例来演示下怎么使用straight_join自己指定驱动表来优化inner join语句。
测试环境（下面所有的SQL测试都是基于同样的环境）
Linux as 5.8
Percona 5.6.13 & MySQL 5.5.28
t_user InnoDB 10万数据
t_user_log InnoDB 约3000万数据
执行一个简单的关联查询
test 2-1
1 2 3
| mysql> select a.* from t_user_log a inner join t_user b on a.uid = b.uid order by a.logintime desc limit 100; ... 100 rows in set (17 min 41.83 sec)
|
我们来看下它的执行计划：MySQL的优化器选择了t_user来做驱动表，根据我们之前说的存在order by子句的情况，MySQL优化器存在无法使用索引（logintime）来完成排序动作，只能通过创建临时表来保持临时结果集，然后在临时结果集中完成排序同坐，于是就产生了Using temporary。我们完全可以预见这个SQL执行是有多么慢多么的耗费服务器资源特别是IO资源，实际上在我测试的过程中服务器的iowait%一直保持在6%左右。
test 2-2
1 2 3 4 5 6 7 8 9 10 11 12
| +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+----------------------------------------------+ +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+----------------------------------------------+ | 1 | SIMPLE | b | index | idx_uid | idx_uid | 4 | NULL | 100105 | Using index; Using temporary; Using filesort | +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+----------------------------------------------+ 2 rows in set (0.00 sec) mysql> show index from t_user_log; ... | t_user_log | 1 | logintime | 1 | logintime | A | 3668150 | NULL | NULL | | BTREE | | ...
|
MySQL优化器选择驱动表是粗暴的，简单的以当前条件返回结果集来判断，结构集小的就是驱动表。在这个案例中除了排序并无其他条件，也就是说结果集是3000万比10万，于是MySQL优化器选择了t_user当驱动表，然后就发生了上面的一幕。
如果说我们要优化这个SQL，那么首先应该就是避免Using temporary产生，也就是说我们需要在索引中完成排序动作。那么根据我们上面描述的带有order by自己的关联查询的情况，如果order by子句想使用索引那么就必须把引用字段所在的表作为驱动表，也就是案例中的t_user_log，这里我使用straight_join将t_user_log设置成驱动表看看会是什么情况。
test 2-3
1 2 3 4 5 6 7 8 9 10 11 12
| mysql> select a.* from t_user_log a straight_join t_user b on a.uid = b.uid order by a.logintime desc limit 100; ... 100 rows in set (0.14 sec) +----+-------------+-------+-------+-----------------------+-----------+---------+--------------------+------+-------------+ +----+-------------+-------+-------+-----------------------+-----------+---------+--------------------+------+-------------+ | 1 | SIMPLE | a | index | uid_logintime,idx_uid | logintime | 4 | NULL | 100 | NULL | +----+-------------+-------+-------+-----------------------+-----------+---------+--------------------+------+-------------+ 2 rows in set (0.00 sec)
|
很明显MySQL优化器乖乖的按照我们的引导将t_user_log设置成驱动表后使用logintime(logintime)索引在索引中完成排序动作，效率提升是非常明显的。17′41.87″ vs 0.14″，效率提升了7500倍+。
这个案例说明了两件事：
- 一个是straight_join确实可以用来指定inner join的驱动表
- 一个是关联查询驱动表选择非常重要
有一点需要注意的是：我们前面说要尽量缩小驱动表的结果集，但是这里却选择了一个超大表来做为驱动表，但其实两者是在一定程度上是不相悖的。因为缩小驱动表结果集实际上是为了减少nested loop的次数，而我们修改了驱动表以后，排序动作直接在索引完成，优化器直接按照logintime desc的顺序进行扫描，只要返回记录数等于100时就可以完成查询操作，实际的nested loop次数是很少的。而未修改前的SQL之所以需要执行那么久是因为它的排序无法使用索引，所以必须先等待临时结果集生成，而且是全量生成，也就是说需要在约3000万行的表里进行10万次查询，然后把结果聚集到一张临时表再进行排序选出100行记录。
通过缩小驱动表结果集来优化关联查询
还是上面的例子，这里我们用count(*)来做一次全表扫描的测试再看看驱动表应该怎么选。
test 3-1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| +----------+ +----------+ +----------+ 1 row in set (16 min 57.25 sec) +----------+ +----------+ +----------+ 1 row in set (0.74 sec) +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+----------+-------------+ +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+----------+-------------+ | 1 | SIMPLE | a | index | uid_logintime,idx_uid | idx_uid | 4 | NULL | 29345201 | Using index | | 1 | SIMPLE | b | ref | idx_uid | idx_uid | 4 | join_test_db.a.uid | 1 | Using index | +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+----------+-------------+ 2 rows in set (0.00 sec) +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+-------------+ +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+-------------+ | 1 | SIMPLE | b | index | idx_uid | idx_uid | 4 | NULL | 100105 | Using index | +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+-------------+ 2 rows in set (0.00 sec)
|
通过这个实验我们可以证明：驱动表结果集越小越好。
子查询说明
以下面四个SQL为例：
- 第一个是我们正常的关联子查询的写法
- 第二个是在MySQL5.6以前版本中SQL1的等价写法
- 第三个是采用关联查询的优化写法
- 第四个外连接改写关联子查询，等价于SQL3的内连接写法
test 4-1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
| +----------+ +----------+ +----------+ 1 row in set (0.81 sec) +----------+ +----------+ +----------+ 1 row in set (1 min 34.70 sec) +----------+ +----------+ +----------+ 1 row in set (0.82 sec) +----------+ +----------+ +----------+ 1 row in set (0.79 sec) +----+-------------+--------+-------+-----------------------+---------+---------+-------------------------+--------+------------------------+ +----+-------------+--------+-------+-----------------------+---------+---------+-------------------------+--------+------------------------+ | 1 | SIMPLE | t_user | index | idx_uid | idx_uid | 4 | NULL | 100105 | Using index; LooseScan | | 1 | SIMPLE | a | ref | uid_logintime,idx_uid | idx_uid | 4 | join_test_db.t_user.uid | 5 | Using index | +----+-------------+--------+-------+-----------------------+---------+---------+-------------------------+--------+------------------------+ 2 rows in set (0.00 sec) +----+--------------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------+ +----+--------------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------+ | 1 | PRIMARY | a | index | NULL | idx_uid | 4 | NULL | 29345201 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | b | ref | idx_uid | idx_uid | 4 | join_test_db.a.uid | 1 | Using index | +----+--------------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------+ 2 rows in set (0.00 sec) +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+-------------+ +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+-------------+ | 1 | SIMPLE | b | index | idx_uid | idx_uid | 4 | NULL | 100105 | Using index | +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+-------------+ 2 rows in set (0.00 sec) +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+--------------------------+ +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+--------------------------+ | 1 | SIMPLE | b | index | idx_uid | idx_uid | 4 | NULL | 100105 | Using where; Using index | +----+-------------+-------+-------+-----------------------+---------+---------+--------------------+--------+--------------------------+ 2 rows in set (0.00 sec)
|
这里我们再来看看SQL1与SQL2在MySQL5.5版本中的效率已经执行计划
test 4-2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| +----------+ +----------+ +----------+ 1 row in set (1 min 5.13 sec) +----------+ +----------+ +----------+ 1 row in set (1 min 24.81 sec) +----+--------------------+--------+----------------+---------------+---------+---------+------+----------+--------------------------+ +----+--------------------+--------+----------------+---------------+---------+---------+------+----------+--------------------------+ | 1 | PRIMARY | a | index | NULL | idx_uid | 4 | NULL | 30736327 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t_user | index_subquery | idx_uid | idx_uid | 4 | func | 1 | Using index | +----+--------------------+--------+----------------+---------------+---------+---------+------+----------+--------------------------+ 2 rows in set (0.01 sec) +----+--------------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------+ +----+--------------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------+ | 1 | PRIMARY | a | index | NULL | idx_uid | 4 | NULL | 30736327 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | b | ref | idx_uid | idx_uid | 4 | join_test_db.a.uid | 1 | Using index | +----+--------------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------+ 2 rows in set (0.00 sec)
|
容易混淆的地方
下面这两个SQL是等价的吗？
1 2
| select count(*) from t_user_log a left join t_user b on a.uid = b.uid and b.city = 78; select count(*) from t_user_log a left join t_user b on a.uid = b.uid where b.city = 78;
|
答案是否定的，因为前者在匹配表中加了过滤条件，而后者在关联结果中加了过滤条件，前者不影响驱动表检索出来的数据（与匹配表无法匹配的数据依然会检索出来，只是匹配表字段部分值等于NULL），后者影响驱动表检索出来的数据（因为在结果集中直接被过滤掉了）。
test 5-2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| +----------+ +----------+ +----------+ 1 row in set (1 min 6.83 sec) +----------+ +----------+ +----------+ 1 row in set (0.01 sec)
|
再看看这样的两个SQL等价吗？
1 2
| select count(*) from t_user_log a left join t_user b on a.uid = b.uid where b.city = 78; select count(*) from t_user_log a inner join t_user b on a.uid = b.uid where b.city = 78;
|
答案是肯定的，因为前者在关联结果中加了匹配表的过滤条件，后者是内关联等价于在关联结果中过滤数据，因此两者是等价的。
也就是在这种情况下MySQL优化器会认为：外关联等价于内关联，会由优化器自行选择驱动表，而不是直接以left join左边的t_user_log表作为驱动表。
test 5-2
1 2 3 4 5 6 7 8
| mysql> explain select count(*) from t_user_log a left join t_user b on a.uid = b.uid where b.city = 78; +----+-------------+-------+------+---------------------------+---------+---------+--------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------+---------+---------+--------------------+------+--------------------------+ | 1 | SIMPLE | b | ref | idx_uid,idx_1,idx_2,idx_4 | idx_2 | 1 | const | 942 | Using where; Using index | | 1 | SIMPLE | a | ref | uid_logintime,idx_uid | idx_uid | 4 | join_test_db.b.uid | 5 | Using index | +----+-------------+-------+------+---------------------------+---------+---------+--------------------+------+--------------------------+ 2 rows in set (0.00 sec)
|
接下来再看看这两个SQL是否等价。
1 2
| select count(*) from t_user_log a left join t_user b on a.uid = b.uid where a.logintype = 3; select count(*) from t_user_log a inner join t_user b on a.uid = b.uid where a.logintype = 3;
|
很多同学看了test 5-2的两个SQL是等价，自然也觉得test 5-3这两个SQL也是等价的。但是很遗憾，结果是不等价。
test 5-3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| +----------+ +----------+ +----------+ 1 row in set (31.15 sec) +----------+ +----------+ +----------+ 1 row in set (47.63 sec)
|
因为这两个SQL都是在结果集中进行数据过滤，两者的结果集不同，因此过滤结果也可能不同，所以两个SQL是不等价的。
简单来说：关联查询where后面部分如果带有匹配表上的查询条件，那么这个外关联SQL可以等价于内关联SQL（匹配表.clo is null除外）。
缩小匹配表结果集能达到优化的效果吗
通过上面的一些说明与实验我们知道缩小驱动表的结果集能够优化连接查询，那么如果在驱动表的结果集无法缩小，且无法替换驱动表的情况下，通过所缩小匹配表的结果集能达到优化效果吗？
test 6-1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
| +----------+ +----------+ +----------+ 1 row in set (23.16 sec) +----------+ +----------+ +----------+ 1 row in set (1 min 10.74 sec) +----+-------------+------------+-------+---------------+-------------+---------+--------------------+----------+-------------------------+ +----+-------------+------------+-------+---------------+-------------+---------+--------------------+----------+-------------------------+ | 1 | PRIMARY | a | index | NULL | idx_uid | 4 | NULL | 29345201 | Using index | | 1 | PRIMARY | <derived2> | ref | <auto_key1> | <auto_key1> | 4 | join_test_db.a.uid | 15 | Using where; Not exists | | 2 | DERIVED | t_user | ref | idx_2,idx_4 | idx_2 | 1 | const | 942 | NULL | +----+-------------+------------+-------+---------------+-------------+---------+--------------------+----------+-------------------------+ 3 rows in set (0.00 sec) +----+-------------+-------+-------+---------------------------+---------+---------+--------------------+----------+-------------------------+ +----+-------------+-------+-------+---------------------------+---------+---------+--------------------+----------+-------------------------+ | 1 | SIMPLE | a | index | NULL | idx_uid | 4 | NULL | 29345201 | Using index | | 1 | SIMPLE | b | ref | idx_uid,idx_1,idx_2,idx_4 | idx_uid | 4 | join_test_db.a.uid | 1 | Using where; Not exists | +----+-------------+-------+-------+---------------------------+---------+---------+--------------------+----------+-------------------------+ 2 rows in set (0.00 sec)
|
从执行计划上看第二个SQL应该比第一个SQL更优，但实际结果却是第一个SQL执行效率更高，原因也许就出在我暂时还不明白的上，这个是MySQL5.6以后才出现的，放在My5.5及以前的版本中是没有的，自然执行的效率也是可以预见的非常非常低，下面是同样的SQL在MySQL5.5版本执行的结果。
test 6-2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| mysql> select count(*) from t_user_log a left join ( select * from t_user where city = 78 )b on a.uid = b.uid where b.id is null; ... // 执行超过10分钟还没出结果，直接被我X掉了 +----------+ +----------+ +----------+ 1 row in set (1 min 7.68 sec) +----+-------------+------------+-------+----------------+---------+---------+------+----------+-------------------------+ +----+-------------+------------+-------+----------------+---------+---------+------+----------+-------------------------+ | 1 | PRIMARY | a | index | NULL | idx_uid | 4 | NULL | 30736327 | Using index | | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 943 | Using where; Not exists | | 2 | DERIVED | t_user | ref | idx_city,idx_2 | idx_2 | 1 | | 942 | | +----+-------------+------------+-------+----------------+---------+---------+------+----------+-------------------------+ 3 rows in set (0.00 sec) +----+-------------+-------+-------+------------------------+---------+---------+--------------------+----------+-------------------------+ +----+-------------+-------+-------+------------------------+---------+---------+--------------------+----------+-------------------------+ | 1 | SIMPLE | a | index | NULL | idx_uid | 4 | NULL | 30736327 | Using index | | 1 | SIMPLE | b | ref | idx_uid,idx_city,idx_2 | idx_uid | 4 | join_test_db.a.uid | 1 | Using where; Not exists | +----+-------------+-------+-------+------------------------+---------+---------+--------------------+----------+-------------------------+ 2 rows in set (0.00 sec)
|
果然如我们预料在MySQL5.5版本上SQL1执行的效率低的可怕，至于MySQL5.6上为什么缩小匹配表结果集能达到优化就等下回分解吧。
说在后面
关于MySQL的SQL优化，照本宣科达是不到什么效果的，只有了解原则结合实际场景（数据密度，数据分布，应用场景等等）才能得出最优的优化方案，照本宣科生搬硬套往往只会有反效果。
遗留问题
MySQL5.6确实有做了相关优化吗：缩小匹配表结果集能达到优化关联查询？