表的连接

两个误区

不论逻辑多复杂，只要能使用一条 SQL 语句完成，都可以搞定
- SQL 语句中掺杂太多业务逻辑，难以维护
永远不使用连接，单表查询搞定一切
- 会导致业务代码过于复杂

连接过程

CREATE TABLE student (
    number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(5) COMMENT '姓名',
    major VARCHAR(30) COMMENT '专业',
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';

CREATE TABLE score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩',
    PRIMARY KEY (number, subject)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';

以两表连接为例，看如下语句

SELECT * FROM student, score WHERE student.number > 10 AND student.number = score.number AND score.score > 80;

其执行过程简略如下：

首先确定‘驱动表’，即 MySQL server 第一要查询的表；此处假设驱动表为 student 表，那么先处理条件 student.number > 10 ，找出表中符合条件的所有记录；
针对步骤 1 中从驱动表产生的结果中的每一条数据，都需要去被驱动表中进行一次查询。假设步骤1 中查出符合条件的记录有两条，分别为 student.number = 12 和 student.number = 15；此时再处理被驱动表 score，有两个需要处理的条件，student.number = score.number 和 score.score > 80;

对于 student.number = 12，查询 score 表时，条件即为 score.number = 12 AND score.score > 80
对于 student.number = 15，查询 score 表时，条件即为 score.number = 15 AND score.score > 80

可以看出，在本次连接查询过程中，驱动表只查询了一次，被驱动表的查询次数取决于驱动表查询结果集中的记录数量

INNER JOIN 和 OUTER JOIN

区分内外连接的目的：驱动表中的记录在被驱动表中没找到符合条件的记录，是否需要添加在结果集中

不需要，则使用内连接
需要，则使用外连接

连接查询的过滤条件分为两种

WHERE 子句，不论哪一种连接方式，只要不符合 WHERE 子句条件都不会加入到结果集中
ON 子句，对于外连接而言，即便在被驱动表中没有找到符合 ON 子句的记录，那对应驱动表中的记录都会被加入到结果集中；但对于内连接而言，WHERE 子句和 ON 子句的效果时一样的

小表驱动大表

即记录少的表作为驱动表，记录多的表作为被驱动表；主要原因在于减少查表次数（每一次查表都需要建立对表的连接）

如何区分驱动表与非驱动表
- LEFT JOIN 左侧为驱动表
- RIGHT JOIN 右侧为驱动表
- INNER JOIN MySQL 优化器会选择数据量较小的表作为驱动表
- EXPLAIN 语句执行结果的第一条记录中使用的表，即为驱动表

连接查询的底层实现方式

嵌套循环连接 Nested-Loop Join

for each row in t1 {
    for each row in t2{
        for each row in t3{
            if row satisfies join conditions, send to client
        }
    }
}

给被驱动表（t2 / t3）的查询条件添加必要索引，可有效提升查询速度

基于块得我嵌套循环查询 Block Nested-Loop Join

连接查询中，对于被驱动表来说，每一条驱动表查询结果都会导致被驱动表被扫描一次（无论何种方式的扫描），I/O 密集度高，代价大；

为了减少被驱动表访问次数，MySQL 设计了一个 JOIN BUFFER，驱动表所有符合条件的数据都会被拉到 Join Buffer 中，完美情况下，一次性拉取到 Join buffer 中，再处理被驱动表，因为是内存操作，能够有效降低 I/O 次数

Join Buffer 实例

JOIN BUFFER 默认大小为 256KB，最小值为 128B，可以通过启动参数 join_buffer_size 设置

为什么不要用 Select *

增加使用覆盖索引的概率，不论是单表查询或多表联合查询，都可以有效提升速度
减少 join buffer 容量中，驱动表数据的占用范围，可以有效提升 join buffer 的使用率，进而提升连接查询的速度

【笔记】MySQL 中的连接