连接的本质
连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果
集并返回给用户(笛卡尔积)
内连接和外连接
内连接:驱动表中的记录在被驱动表中找不到匹配的记录，该
记录不会加入到最后的结果集,mysql会自主优化选择小表作为驱动表
语法:
eg. SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score
AS s2 WHERE s1.number = s2.number;
或 inner join
外连接:选择固定的驱动表,匹配不到的数据以null填充
eg. SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT
JOIN score AS s2 ON s1.number
e1 left join e2 此时e1作为驱动表
e1right join e2 此时e2作为驱动表
嵌套循环连接(nest-loop join)
对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。
用伪代码表示结果如下:
for each row in e1 {
#此处表示遍历满足对 e1 单表查询结果集中的每一条
记录，N 条for each row in e2 {
#此处表示对于某条 e1 表的记录来说，遍历满足
对 e2 单表查询结果集中的每一条记录，M 条
for each row in t3 {
#此处表示对于某条 e1 和 e2 表的记录组
合来说，对 t3 表进行单表查询，L 条
if row satisfies join conditions, send to client
}
}
}
复杂度为O(NML)
使用索引加快连接速度
对于被驱动表实际上可以转化为单表查询 即:
SELECT * FROM e2 WHERE e2.m2 = (e1查出来的值) AND e2.n2 < ‘d’;
因此,此时连接条件e2.m2加索引,实际上用到了 ref的访问方法
如果m2是e2表的主键或者唯一索引,则是const级别.在连接查询中称为eq_ref
基于块的嵌套循环连接
mysql中有一个joinbuffer的概念,先将若干驱动表结果集装在 joinbuffer中 ,然后开始扫描被驱动表，每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的 I/O 代价
- 最好的情况是 join buffer 足够大，能容纳驱动表结果集中的所有记录。
- 只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中，所以
我们最好不要把*作为查询列表,这样可以装更多的列
对in查询的优化
1.物化表(Materialize)
MySQL 的改进是不直接将不相关子查询的结果集当作外层查询的参数，而是
将该结果集写入一个临时表里。写入临时表的过程是这样的：
1、该临时表的列就是子查询结果集中的列。
2、写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有
列建立主键或者唯一索引。一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用
Memory 存储引擎的临时表，而且会为该表建立哈希索引。
如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者
max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的
记录，索引类型也对应转变为 B+树索引。
2.物化表转连接
3.将子查询(相关子查询)转为semi-join
SELECT FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE
order_no = ‘a’);
转化为
SELECT s1. FROM s1 SEMI JOIN s2 ON s1.order_note = s2.order_note
WHERE order_no= ‘a’;
对于 s1 表的某条记录来说，我们 只关心在 s2 表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹
配，最终的结果集中只保留 s1 表的记录,于是有了semi-join的概念
策略:
Table pullout （子查询中的表上拉）:
当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的
表上拉到外层查询的 FROM 子句中
SELECT FROM s1
WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = ‘a’);——>
SELECT s1. FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = ‘a’;
DuplicateWeedout execution strategy （重复值消除）
不是唯一索引所以存在s1表在s2表多条匹配的情况,需要开一个临时表去重
LooseScan execution strategy （松散扫描）
SELECT * FROM s1 WHERE order_note IN (SELECT order_no FROM s2 WHERE
order_no> ‘a’ AND order_no< ‘b’);
在子查询中，对于 s2 表的访问可以使用到 order_no 列的索引，而恰好子查
询的查询列表处就是 order_no 列(子查询查询列刚好是索引列),此时即便不是唯一索引,
也只需要在索引上(存在多条匹配的情况)取第一条即可.所以称为松散扫描
Semi-join Materializationa 半连接物化、FirstMatch execution strategy等
使用条件:
只有 形如这样的查询才可以被转换为 semi-join：
SELECT … FROM outer_tables
WHERE (oe1, oe2, …) IN (SELECT ie1, ie2, … FROM inner_tables …) AND …
不相关子查询优化
大于200条则尝试物化表
in转EXISTS 子查询
若有收获，就点个赞吧
0 人点赞
