Mysql join大表优化案例

一、准备知识（Mysql join原理及结论）

1、MySQL join分为

• inner join
• left (outer) join
• right (outer) join
• full join（mysql不支持full join，但是可以利用left join + union + right join实现full join）
• cross join（笛卡尔积），实际上inner join不指定on即和cross join表现一样
• straight_join（效果等同于inner join，只是固定了驱动表顺序）

2、驱动表与被驱动表

• inner join：由执行器自行决定谁是驱动表，谁是被驱动表
• left join：左表是驱动表，右表是被驱动表
• right join：右表是驱动表，左表是被驱动表
• straight_join：固定左边为驱动表，右边为被驱动表

3、join执行流程

每取驱动表一行数据，去和被驱动表匹配。可以理解为双层for循环。
所以数据量的时候，循环次数变多，这也是join性能问题的根源。

4、join执行的实现原理

• Nest Loop Join (NLJ)，就是单纯双层循环
• Block Nest Loop Join (BNLJ)，在NLJ基础上，利用join_buffer，一次取出一批驱动表数据，可以减少循环匹配次数
• Index Nest Loop Join (INLJ)，在NLJ基础上，利用被驱动表连接字段的索引直接找到匹配数据，可以减少循环次数

5、join on

• on后跟连接条件，一般必须指定，且只对被驱动表有效(即即使对驱动表加了过滤条件该条件也无效)
• 由此可知join on之后，驱动表包含全部数据，被驱动表只包含on条件过滤后的数据

6、on和where

• on在join时就会过滤数据，而where是join完成后再对数据进行过滤，所以on比where先作用
• 所以理论上过滤条件放在on后，比放在where后性能好
• 但是过滤条件放在on后和where后，结果可能不一样(原因就在于on后条件只对被驱动表有效)，所以谨慎在on后加驱动表的过滤条件
• 针对inner join，on和where无啥差别

根据前面的说明，以下属于结论性说明

7、加过滤条件要想清楚

是先对被驱动表进行过滤还是join完再对驱动表和被驱动表进行过滤

8、尽量小表驱动大表

这里针对的是left join和right join，因为inner join会自动选择

9、被驱动表连接字段要加索引

否则Mysql就会使用Block Nest Loop Join，影响性能

10、explain命令分析出的第一行的表即是驱动表

11、优化join的思路

• 顺着减少join时驱动表匹配被驱动表时的循环查找次数进行思考(想一下哪些结论是针对这个思路的？)
• 如果join后的数据量很大，且还要进行相关聚合操作，可以考虑先聚合出临时表，再join（本案例就是这个思路）

二、案例

1、原sql

EXPLAIN
select camf.asset_management_id
from crm_asset_management_friend camf  INNER JOIN crm_asset_management cam ON camf.asset_management_id=cam.id
where camf.`status`=1
	 and cam.wx_status != 0
group by camf.asset_management_id having count(camf.id) > 10;

• 用时：1.5s
• explain结果：

2、分析

• cam数据量4000多，且基本不会再大量增加；camf数据量接近400万，且持续稳定增加ing
• camf在asset_management_id上有索引
• join完之后有320万数据（表明每一条驱动表cam数据匹配被驱动表camf循环次数很多(1条匹配几千条)），如下sql，再进行groupby，性能可想而知

select camf.asset_management_id
from crm_asset_management_friend camf  INNER JOIN crm_asset_management cam ON camf.asset_management_id=cam.id
where camf.`status`=1
	 and cam.wx_status != 0

3、新sql

EXPLAIN			
select cam.id from (		
select camf.asset_management_id
    from 
    crm_asset_management_friend camf  
    where camf.`status`=1
    	group by camf.asset_management_id having count(camf.id) > 10
    ) tmp INNER JOIN crm_asset_management cam ON tmp.asset_management_id=cam.id
where  cam.wx_status!=0;

• 用时：1.0s
• explain结果：
  

4、结语

• 之所以还是在1s以上，是因为本身如下groupby就耗时0.9s多

select camf.asset_management_id
from 
crm_asset_management_friend camf  
where camf.`status`=1
group by camf.asset_management_id having count(camf.id) > 10

• 限于业务，表结构，也只能优化到这儿了。
• 要想彻底优化，是需要提取出每个asset_management的count(camf.id)作为一个新字段维护到cam表（维护比较麻烦，这也是没有维护的原因）。这样查询friend数大于10的资产就不需要绕这样一大圈了。sql就会变为类似：

select cam.id from crm_asset_management cam
where  cam.wx_status!=0 and friendCount>10

这速度就会杠杆的了！！！