一个sql的优化

「爱情、让人受尽委屈。」 2021-12-19 17:33 160阅读 0赞

目的:为了查询某天某个服务器上的登录id的个数

刚开始编写的sql:

select count(a.mac) logusers from Log\_MacLogin\_All a

where isMoNi != 1  
and loginTime <= '2015-02-01 23:59:59' and loginTime >= '2015-02-01 00:00:00'  
and a.mac in (select mac from Inst\_User\_Mac b

where doTime <= '2015-01-30 23:59:59'  
and doTime >= '2015-01-30 00:00:00 and serverKey='p1s1' );

执行时间为:33s

对于这个sql，首先的优化点在于"in",因为测试发现in条件里面的数据差不多将近万以上的数据..

第一次优化：把in改为exists后，效率有一点的提升

select count(mac) logusers from Log\_MacLogin\_All a

where isMoNi != 1  
and loginTime <= '2015-02-01 23:59:59' and loginTime >= '2015-02-01 00:00:00'  
and exists (select mac from Inst\_User\_Mac b

where doTime <= '2015-01-30 23:59:59'  
and doTime >= '2015-01-30 00:00:00 and serverKey='p1s1'

and a.mac = b.mac );

执行时间为：26s

第二次优化：在网上查了下，有的说用join会快一些。把exists改为join试试

select count(a.mac) logusers from Log\_MacLogin\_All a  inner join

(select mac from Inst\_User\_Mac where doTime <= '2015-01-30 23:59:59'

and doTime >= '2015-01-30 00:00:00 and serverKey='p1s1' ) b

on a.mac = b.mac

where a.isMoNi != 1  
and a.loginTime <= '2015-02-01 23:59:59' and a.loginTime >= '2015-02-01 00:00:00';

执行时间为2.6s，性能有了明显的提高啊

第三次优化:把>= <= 改为between and 这样会减少把数据查出来后的计算操作

select count(a.mac) logusers from Log\_MacLogin\_All a  inner join

(select mac from Inst\_User\_Mac where doTime between '2015-01-30 00:00:00

and '2015-01-30 23:59:59' and serverKey='p1s1' ) b

on a.mac = b.mac

where a.isMoNi != 1  
and a.loginTime between '2015-02-01 00:00:00' and '2015-02-01 23:59:59';

执行时间为2.4s，提升了0.2s

第四次优化:假如要查某一天的数据不如直接date\_format()此时一定要注意，假如用了内置函数就无法享受到表的索引字段带来的效率了，刚开始理解有错，现已改正;

select count(a.mac) logusers from Log\_MacLogin\_All a inner join  
(select mac from Inst\_User\_Mac where DATE\_FORMAT(doTime,'%Y%m%d')='20150130' and serverKey='p1s1' ) b  
on a.mac = b.mac  
where a.isMoNi != 1 and DATE\_FORMAT(a.loginTime,'%Y%m%d') = '20150201';

这个虽然采用了内置函数，简化了运算，但不能运用索引。各有利弊吧。

转载于:https://www.cnblogs.com/lovebaoqiang/p/4274770.html