AnySQL.net

    网友说这个SQL语句跑了两上小时, 没有什么思路去解决.

select tbilltrace_43.bno,tbilltrace_43.SAVETIMESTAMP
from
   ( select bno,SAVETIMESTAMP
     from tbilltrace
     where  SAVETIMESTAMP>=monitor_start_date
       and SAVETIMESTAMP<=monitor_end_date and opcode=43
   ) tbilltrace_43
where not exists
(select bno
   from ( select bno from tbilltrace
          where SAVETIMESTAMP>=monitor_start_date  
            and SAVETIMESTAMP<=monitor_end_date and opcode=50
          union all
          select bno from tbilltrace
          where SAVETIMESTAMP>=monitor_start_date
            and SAVETIMESTAMP<=monitor_end_date and opcode=51
        ) tbilltrace_51_50
where tbilltrace_51_50.bno=tbilltrace_43.bno);  

    SQL语句的性能和数据量有关, 首先需要知道第一个子查询会返回多少条记录, 其次中间的那个UNION ALL应当折开成两个Not Exists, 我认为在opcode或SAVETIMESTAMP列上的索引应当不是很有效的, 而bno列上的索引应当是很有效的. 因此改写后的SQL应当如下:

select bno,SAVETIMESTAMP
     from tbilltrace tbilltrace_43
     where  SAVETIMESTAMP>=monitor_start_date
       and SAVETIMESTAMP<=monitor_end_date and opcode=43
where not exists
  (select 1 from tbilltrace tbilltrace_50
          where SAVETIMESTAMP>=monitor_start_date  
            and SAVETIMESTAMP<=monitor_end_date and opcode=50
            and bno = tbilltrace_43.bno)
  and not exists
  (select 1 from tbilltrace tbilltrace_51
          where SAVETIMESTAMP>=monitor_start_date  
            and SAVETIMESTAMP<=monitor_end_date and opcode=51
            and bno = tbilltrace_43.bno)

    如果不考虑空间的情况, 则建以下的两个索引:

Index 1 (opcode, savetimestamp, bno)
Index 2 (bno, opcode, savetimestamp)

    这样的Tuning其实很简单的.