连接查询

CREATE DATABASE IF NOT EXISTS demo;
USE demo;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

CREATE TABLE t1(m1 int, n1 char(1));
CREATE TABLE t2(m2 int, n2 char(1));
INSERT INTO t1 VALUES(1, 'a'),(2, 'b'),(3, 'c');
INSERT INTO t2 VALUES(2, 'b'),(3, 'c'),(4, 'd');

• 连接查询
  连接就是把各个表中的记录都取出来进行依次匹配，这个查询过程就是连接查询。

  mysql> SELECT * FROM t1;
  +------+------+
  | m1   | n1   |
  +------+------+
  |    1 | a    |
  |    2 | b    |
  |    3 | c    |
  +------+------+
  3 rows in set (0.00 sec)
  
  mysql> SELECT * FROM t2;
  +------+------+
  | m2   | n2   |
  +------+------+
  |    2 | b    |
  |    3 | c    |
  |    4 | d    |
  +------+------+
  3 rows in set (0.00 sec)
  
  mysql> SELECT * FROM t1, t2;
  +------+------+------+------+
  | m1   | n1   | m2   | n2   |
  +------+------+------+------+
  |    1 | a    |    2 | b    |
  |    2 | b    |    2 | b    |
  |    3 | c    |    2 | b    |
  |    1 | a    |    3 | c    |
  |    2 | b    |    3 | c    |
  |    3 | c    |    3 | c    |
  |    1 | a    |    4 | d    |
  |    2 | b    |    4 | d    |
  |    3 | c    |    4 | d    |
  +------+------+------+------+
  9 rows in set (0.00 sec)
  

• 笛卡尔积
  如果连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，这样的结果就是笛卡尔积。

连接过程简介

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

上面的查询中，我们指定3个过滤条件：
1、t1.m1 > 1;
2、t1.m1 = t2.m2
3、t2.n2 < 'd'

• 驱动表
  首先确定第一个需要查询的表，这个表称为驱动表，这里的驱动表就是t1。

怎样在单表中执行查询语句已经在前面介绍过了：只需要选取代价最小的那种访问方法：const，const，const，const，const，const

然后从驱动表中获取的每一条记录，都需要到t2表中去查找匹配的记录。

从上面的查询中，我们可以得出结果：在2表的连接查询中，驱动表只需要访问一次，被驱动表可能需要访问多次。

内连接和外连接

对于内连接的2个表，若驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集中。
对于外连接的2个表，即使驱动表中的记录在被驱动表中没有匹配的记录，也仍需要加入到结果集中。

• 内连接

  SELECT * FROM t1 [INNER|CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件]
  

• 外连接
  外连接根据选取的驱动表不同，外连接可以分为2种：
  • 左外连接
    选取左侧的表为驱动表

    SELECT * FROM t1 LEFT [OUTER] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件]
    

  • 右外连接
    选取右侧的表为驱动表

    SELECT * FROM t1 RIGHT [OUTER] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件]
    

• ON 和 WHERE 过滤条件

使用索引加快连接速度

使用 Join Buffer（连接缓冲区）

Join Buffer 就是在执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性地与 Join Buffer 中的多条驱动表记录进行匹配，由于匹配过程是在内存中完成的，这样可以显著减少被驱动表的I/O代价。

这个Join Buffer 的大小可以通过启动选项或者系统变量join_buffer_size进行配置，默认大小为 256KB，最小为 128 字节。

Join Buffer 中并不会存放驱动表记录的所有列，只有查询列表中的列和过滤条件中的列才会被放入中，所以，这也提醒我们最好不要用*作为查询条件。