原文:
一个sql的优化
目的:为了查询某天某个服务器上的登录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
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' );
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
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 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';
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';
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';
(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';
执行时间为2.36s,又提升了一点点..
