JOIN 操作有多种办法，取决于终极数据的合并效果。常用连接办法的有以下几种:
2、驱动表的定义
什么是驱动表?
多表关联查询时，第一个被处理的表，利用此表的记录去关联其他表, 驱动表的确定很关键，会直接影响多表连接的关联顺序，也决定了后续关联时的查询性能。
驱动表的选择遵照一个原则：
在对终极结果集没影响的条件下，优先选择结果集最小的那张表作为驱动表
3、三种JOIN算法
1、Simple Nested-Loop Join（大略的嵌套循环连接)
大略来说嵌套循环连接算法便是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果。
这种算法是最大略的方案，性能也一样平常。对内循环没优化。
例如有这样一条SQL:
-- 连接用户表与订单表 连接条件是 u.id = o.user_idselect from user t1 left join order t2 on t1.id = t2.user_id;-- user表为驱动表,order表为被驱动表
转换成代码实行时的思路是这样的:
for(user表行 uRow : user表){ for(Order表的行 oRow : order表){ if(uRow.id = oRow.user_id){ return uRow; } }}
匹配过程如下图
SNL 的特点
大略粗暴随意马虎理解，便是通过双层循环比较数据来得到结果查询效率会非常慢,假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下：A 表扫描 1 次。B 表扫描 M 次。一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N M 次2、Index Nested-Loop Join（索引嵌套循环连接）
Index Nested-Loop Join 其优化的思路: 紧张是为了减少内层表数据的匹配次数 , 最大的差异在于，用来进行 join 的字段已经在被驱动表中建立了索引。从原来的 匹配次数 = 外层表行数 内层表行数 , 变成了 匹配次数 = 外层表的行数 内层表索引的高度 ，极大的提升了 join的性能。当 order 表的 user_id 为索引的时候实行过程会如下图：把稳：利用Index Nested-Loop Join 算法的条件是匹配的字段必须建立了索引。
3、Block Nested-Loop Join，块嵌套循环
如果 join 的字段有索引，MySQL 会利用 INL 算法。如果没有的话，MySQL 会如何处理？
由于不存在索引了，以是被驱动表须要进行扫描。这里 MySQL 并不会大略粗暴的运用 SNL 算法，而是加入了 buffffer 缓冲区，降落了内循环的个数，也便是被驱动表的扫描次数。
在外层循环扫描 user表中的所有记录。扫描的时候，会把须要进行 join 用到的列都缓存到buffffer 中。buffffer 中的数据有一个特点，里面的记录不须要一条一条地取出来和 order 表进行比较，而是全体 buffffer 和 order表进行批量比较。如果我们把 buffffer 的空间开得很大，可以容纳下 user 表的所有记录，那么 order 表也只须要访问一次。MySQL 默认 buffffer 大小 256K，如果有 n 个 join 操作，会天生 n-1个join buffffer。
mysql> show variables like '%join_buffer%';+------------------+--------+| Variable_name | Value |+------------------+--------+| join_buffer_size | 262144 |+------------------+--------+ mysql> set session join_buffer_size=262144;Query OK, 0 rows affected (0.00 sec)
4、总结
永久用小结果集驱动大结果集(实在质便是减少外层循环的数据数量)；为匹配的条件增加索引(减少内层表的循环匹配次数)；增大join buffffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）；减少不必要的字段查询（字段越少，join buffffer 所缓存的数据就越多；二、in和exists函数上面我们说了 小表驱动大表,便是小的数据集驱动大的数据集, 紧张是为了减少数据库的连接次数,根据详细情形的不同,又涌现了两个函数 exists 和 in 函数。
创建部门表与员工表,并插入数据
-- 部门表CREATE TABLE department (id INT(11) PRIMARY KEY,deptName VARCHAR(30) ,address VARCHAR(40)) ;-- 部门表测试数据INSERT INTO `department` VALUES (1, '研发部', '1层');INSERT INTO `department` VALUES (2, '人事部', '3层');INSERT INTO `department` VALUES (3, '市场部', '4层');INSERT INTO `department` VALUES (5, '财务部', '2层');-- 员工表CREATE TABLE employee (id INT(11) PRIMARY KEY,NAME VARCHAR(20) ,dep_id INT(11) ,age INT(11) ,salary DECIMAL(10, 2));-- 员工表测试数据INSERT INTO `employee` VALUES (1, '鲁班', 1, 15, 1000.00);INSERT INTO `employee` VALUES (2, '后裔', 1, 22, 2000.00)INSERT INTO `employee` VALUES (4, '阿凯', 2, 20, 3000.00);INSERT INTO `employee` VALUES (5, '露娜', 2, 30, 3500.00);INSERT INTO `employee` VALUES (6, '李白', 3, 25, 5000.00);INSERT INTO `employee` VALUES (7, '韩信', 3, 50, 5000.00);INSERT INTO `employee` VALUES (8, '蔡文姬', 3, 35, 4000.00);INSERT INTO `employee` VALUES (3, '孙尚喷鼻香', 4, 20, 2500.00);
假设: department表的数据小于 employee表数据, 将所有部门下的员工都查出来,该当利用 in 函数
-- 编写SQL,使in 函数SELECT FROM employee e WHERE e.dep_id IN (SELECT id FROM department);
in函数的实行事理
in 语句, 只实行一次, 将 department 表中的所有id字段查询出来并且缓存。
然后, 检讨 department 表中的id与 employee 表中的 dep_id 是否相等, 如果相等 添加到结果集, 直到遍历完 department 所有的记录。
-- 先循环: select id from department; 相称于得到了小表的数据-- 后循环: select from employee where e.dep_id = d.id;for(i = 0; i < $dept.length; i++){ -- 小表 for(j = 0 ; j < $emp.legth; j++){ -- 大表 if($dept[i].id == $emp[j].dep_id){ $result[i] = $emp[j] break; } }}
结论: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时该当用 in
2、exists 函数
假设: department表的数据大于 employee表数据, 将所有部门下的的员工都查出来,该当利用exists 函数。
explain SELECT FROM employee e WHERE EXISTS(SELECT id FROM department d WHERE d.id = e.dep_id);
exists 特点
exists 子句返回的是一个 布尔值，如果有返回数据，则返回值是 true ，反之是 false 。
如果结果为 true , 外层的查询语句会进行匹配,否则 外层查询语句将不进行查询或者查不出任何记录。
exists 函数的实行事理
-- 先循环: SELECT FROM employee e;-- 再判断: SELECT id FROM department d WHERE d.id = e.dep_idfor(j = 0; j < $emp.length; j++){ -- 小表 -- 遍历循环外表，检讨外表中的记录有没有和内表的的数据同等的, 匹配得上就放入结果集。 if(exists(emp[i].dep_id)){ -- 大表 $result[i] = $emp[i]; }}
