MySql中join操作

join的实现是采用Nested Loop Join算法，就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果有多个join，则将前面的结果集作为循环数据，再一次作为循环条件到后一个表中查询数据。

具体例子可以看这里

下面的例子演示了在使用join的时候，mysql利用join buffer。

mysql> show create table c;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| c     | CREATE TABLE `c` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table d;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| d     | CREATE TABLE `d` (
  `id` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  `stuid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select c.id,d.score from c,d where c.id=d.stuid;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |   42 |                                |
|  1 | SIMPLE      | d     | ALL  | NULL          | NULL | NULL    | NULL |   61 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.00 sec)

注：mysql在优化sql的时候，会根据条件选用不同的执行策略。比如说在上面的d和c表中，如果按照当前的c和d的结构，执行explain之后，是c驱动d表。

那么如果在c的id上加一个index之后，mysql就会采用d驱动c表了。

【因为此时，在Nested Loop Join算法中，内部循环可以使用c表上的索引，加速执行c表的查询。内部查询每加快一点，对整个join来说都是效率上比较大的提升】

如下：

mysql> alter table c add index(id);
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select c.id,d.score from c,d where c.id=d.stuid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+
|  1 | SIMPLE      | d     | ALL  | NULL          | NULL | NULL    | NULL         |   61 |             |
|  1 | SIMPLE      | c     | ref  | id            | id   | 4       | test.d.stuid |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+
2 rows in set (0.00 sec)

---------------------------------------------------------------

实际上，Join Buffer只有当我们的 Join 类型为 ALL(如示例中)，index，range或者是 index_merge 的时候才能够使用，所以，在我们去掉 group_message_content 表的 group_msg_id 字段的索引之前，由于 Join 是 ref 类型的，所以我们的执行计划中并没有看到有使用 Join Buffer。

实例如下：修改d表，增加一个index(stuid)，那么此时由于join是ref类型的，所以不会用到join buffer。

mysql> alter table d add index(stuid);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select c.id,d.score from c,d where c.id=d.stuid;
+----+-------------+-------+------+---------------+-------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+-------+---------+-----------+------+-------------+
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL  | NULL    | NULL      |   42 |             |
|  1 | SIMPLE      | d     | ref  | stuid         | stuid | 5       | test.c.id |    1 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

----------------------------------------------------------------------

下面演示一下range方式弃用join buffer的例子。【待续】

-----------------------------------------------------------------

Join操作使用内存(join_buffer_size)：应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作（具体 Join 实现算法请参考：MySQL中的 Join基本实现原理）。当 Join Buffer太小，MySQL不会将该 Buffer存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join 的表进行 Join操作，然后清空 Join Buffer中的数据，继续将剩余的结果集写入此 Buffer中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO访问，降低效率。

参数：join_buffer_size，默认8MB

-------------------------------------------------------------------------

join语句的优化
1. 用小结果集驱动大结果集，尽量减少join语句中的Nested Loop的循环总次数；
2. 优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；
3. 对被驱动表的join字段上建立索引；
4. 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。