sql语句优化小结
sql的优化技巧。
1.用join进行子查询的优化。
低效的子查询 select a.user_name,a.over,(select over from user2 b where a.user_name=b.user_name) as over2 from user1 a; 使用join优化后的子查询: select a.user_name,a.over,b.over as over2 from user1 a left join user2 b on a.user_name=b.user_name;
2.用join进行聚合函数的优化。
使用join + having优化聚合子查询: 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,btimestr,b.kills having b.kills = max(c.kills);
3.分类聚合函数的优化
分类聚合方式查询每一个用户某一个字段数据最大的两条数据: select d.user_name ,c.ctimestr,kills from (select user_id ,timestr ,kills ,( select count(*) from user_kills b where b.user_id = a.user_id and a.kills <= b.kills) as cnt from user_kills a group by user_id,timestr,kills) c join user1 d on c.user_id = d.id where cnt <= 2
4.自连接行转列
select * from ( select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id = b.use_id and a.user_name='孙悟空' ) a cross join ( select sum(kills) as '猪八戒' from user1 a join user_kills b on a.id = b.use_id and a.user_name='猪八戒' ) b cross join( select sum(kills) as '沙僧' from user1 a join user_kills b on a.id = b.use_id and a.user_name='沙僧' ) c
5.case语句列转行
select sum(case when user_name=' 孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪八戒' then kills end) as '猪八戒', sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join use_kills b on a.id = b.user_id;
6.使用序列化方法转换:
select user_name, replace(substring(substring_index(mobile,','a.id),char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile --截取字符串 from tb_sequence as a cross join( select user_name, concat(mobile,',') as mobile, --在mobile结尾增加逗号 length(mobile)-length(replace(mobile,',',''))+1 as size --通过mobile总长度-排除逗号后的长度+1计算得出总段数 from user1 as b ) as b on a.id<=b.size
7.使用union all 将结果集 合并
SELECT user_name,'arms' as equipment, arms FROM user1 a JOIN user1_equipment b ON a.id=b.user_id UNION ALL SELECT user_name,'clothing' AS equipment,clothing FROM user1 a JOIN user1_equipment b ON a.id=b.user_id UNION ALL SELECT user_name,'shoe' AS equipment,shoe FROM user1 a JOIN user1_equipment b ON a.id=b.user_id