一个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

                                                            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';

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