MySQL查询之join优化

SQL语句开发技巧
正确使用SQL带来的优势
1 增加数据库处理效率，减少应用响应时间
2 减少数据库服务器负载，增加服务器稳定性
3 减少服务器间通讯的网络流量

 正确使用join语句
 SQL标准中join的类型
 内连接 INNER
 全外连接 FULL OUTER
 左外连接 LEFT OUTER
 右外连接 RIGHT OUTER
 交叉连接 CROSS


 内连接：查询两个表中公共的部分
 左连接：查询包含左表全部结果以及和左表对应的右边匹配的结果
 右连接：左连接的反向
 全外连接：FULL JOIN 左右连接的合集
 mysql不直接支持全外连接，需要借助union all
 >
 select a.`user_name`,a.`over`,b.`over` from user1 a left join user2 b on a.`user_name`=b.`user_name` 
 union all 
 select b.`user_name`,b.`over`,a.`over` from user1 a right join user2 b on b.`user_name`=a.`user_name`;

 CROSS JOIN 交叉连接，又称笛卡尔连接或叉乘，如果A和B是两个集合，它们的交叉连接就记为A X B
 >
 select a.`user_name`,a.`over`,b.`user_name`,b.`over` from user1 a cross join user2 b;

 如何更细使用过滤条件中包含自身的表？
  原始SQL
 update user1 set over='齐天大圣' where user1.`user_name` in 
 (
 select b.`user_name` from user1 a join user2 on a.`user_name` = b.`user_name`
 )
 会报错 更细的表不能出现在from从句中
 改进
 update user1 a join (select b.`user_name` from user1 a join user2 b on a.`user_name` = b.`user_name` ) b on a.`user_name` = b.`user_name` set a.`over`='七天大圣';

 使用join优化子查询
 原始SQL
 select a.user_name,a.over,(select over from user2 b where a.user_name=b.user_name) as over2 from user1 a;
 优化后
 select a.user_name,a.over,b.over as over2 from user1 a left join user2 b on a.user_name = b.user_name;

 使用join优化聚合查询
 select a.user_name b.timestr,b.kills from user1 a 
 join user_kills b on a.id=b.user_id 
 where b.kills=(select max(c.kills) from user_kills c where c.user_id=b.user_id);

 优化后
 select a.user_name,b.timestr,b.kills from user1 a 
 join user_kills b on a.id=b.user_id
 join user_kills c on c.user_id =b.user_id
 group by a.user_name,b.timestr,b.kills having b.kills=max(c.kills);