日期:2014-05-17  浏览次数:20520 次

优化下SQL
SQL code

--数据量在130W左右

select projectid,username,completestate,count(1) as amount from projectlink201209 where starttime between 
'2012-10-29 00:00:00' and '2012-10-29 23:59:59'  and len(username)>0 and len(username)<14 and 
isnull(completestate,'') in ('C','S','Q') group by projectid,completestate,username



------解决方案--------------------
SQL code
select projectid,username,completestate,count(1) as amount from projectlink201209 where starttime between 
'2012-10-29 00:00:00' and '2012-10-29 23:59:59'  and len(username)>0 and len(username)<14 and completestate in ('C','S','Q') group by projectid,completestate,username

------解决方案--------------------
starttime 字段加索引试试 username completestate字段加了索引应该也不会走索引


------解决方案--------------------
username 这个字段感觉应该是 not null 
那么语句能修改的地方

AND username <>''
AND LEN(username) < 14
AND completestate IN ('C', 'S', 'Q')
--修改的原则就是尽量不要再where 条件上面使用函数
使用了函数,这些条件就不可SARG了

然后就是补上缺失的索引
------解决方案--------------------
SQL code
SELECT   projectid,
         username,
         completestate,
         Count(1) AS amount
FROM     projectlink201209
WHERE    starttime BETWEEN '2012-10-29 00:00:00'
                           AND '2012-10-29 23:59:59'
         AND Len(username) > 0
         AND Len(username) < 14
         AND completestate IN ('C',
                               'S',
                               'Q')
GROUP BY projectid,
         completestate,
         username

------解决方案--------------------
好东西,学习下
------解决方案--------------------
探讨
SQL code

--数据量在130W左右

select projectid,username,completestate,count(1) as amount from projectlink201209 where starttime between
'2012-10-29 00:00:00' and '2012-10-29 23:59:59' and len(username)>0……

------解决方案--------------------
1、starttime加聚集索引。
2、projectid、username、completestate加一个联合非聚集索引
3、更新表的统计信息