1 两个问题
我们DBA不让使用join，使用join有什么问题呢？
如果有两个大小不同的表做join，应该用哪个表做驱动表呢？
2.案例表
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create table t1 like t2;
insert into t1 (select * from t2 where id<=100)
3.straight_join 固定连接方式和驱动表
如select * from t1 straight_join t2 on (t1.a=t2.a); , t1是驱动表
执行过程 伪代码 , 假设t2没有索引a , 扫描 100*1000行
for t1 as row1
for t2 as row2
if row1.a==row2.a
res[ ]=row1.row2
return res
有索引a , 扫描了200行,执行一条语句
for t1 as row1
primaryID= search in b+tree where key=row1.a
if primaryID exist
row2 = retrieve from primarykey index
res[]=row1.row2
return res
4.不使用join 的话 , 则是对row1每条语句执行一次select 等值查询 , 扫描了200行 , 但执行了101条语句
5.驱动表是走全表扫的 , 被驱动是走b+ 树查找的 , 因此应该用小表做驱动表
6 复杂度分析 扫描驱动表的N行的复杂度 + 到被驱动表查找的复杂度 N*2logM (需要回表 , 所以是2logM , 查两棵树 ) , 这个复杂度分析不是很懂 , 为什么不是n2logm , 而是n+n2logm ?
7 结论
使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；如果使用join语句的话，需要让小表做驱动表。8 上面的是Indexed NLJ , 使用了索引的NLJ
9 Simple NLJ 就是没有索引的NLJ
10 Block NLJ 是 mysql对Simple NLJ做的优化
11 BNLJ的执行过程:
把t1的数据读入线程内存的join_buffer中
扫描t2表 , ……………
select * from t1 straight_join t2 on (t1.a=t2.b);
伪代码:
join_buffer = read t1 data from disk index
for t2 as row1
for join_buffer as row2
if row1.b=row2.a
res[ ] = row1.row2
return res
和Simple NLJ的不同就是内层的10万次遍历判断是在内存中的 , 速度和性能快很多 , S NLJ则是从磁盘一个一个取
12 如果你的join语句很慢，就把join_buffer_size改大
13 画图分析BNLJ和INLJ的过程
14 为什么小表做驱动表速度快? N+N2logm , N小则快 , BNLJ的情况下小表更容易塞进join_buffer_size , 不需要分成更多段 , 导致被驱动表多次被全表扫描
15 结论
如果可以使用被驱动表的索引，join语句还是有其优势的；
不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句就尽量不要使用；
在使用join的时候，应该让小表做驱动表。
16 思考题
我们在上文说到，使用Block Nested-Loop Join算法，可能会因为join_buffer不够大，需要对被驱动表做多次全表扫描。
我的问题是，如果被驱动表是一个大表，并且是一个冷数据表，除了查询过程中可能会导致IO压力大以外，你觉得对这个MySQL服务还有什么更严重的影响吗？（这个问题需要结合上一篇文章的知识点）
17 驱动表不是外层和内层循环的简单判断
18 小表的定义是什么
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
网友评论