最经项目中要用到一个树形加载结构,由于底层数据有点多,再加上sql语句没有怎么优化,所以页面数据加载的时候特别慢。只查询底层数据的话就需要七八秒中,后来经过别人的指点从新优化sql后,底层数据的加载查询控制在了200毫秒以为。现在就贴上我的sql语句,供大家看一下:
没有优化的sql语句
select c.key,k.keywords,k.id,
(select count(id) from post.CONTENT_ALARM_RECORD_2013 cr where k.keywords=cr.key and cr.read_status=0)as unReady,
(select count(id) from post.CONTENT_ALARM_RECORD_2013 cr where k.keywords=cr.key and cr.read_status=1)as ready
from POST.CONTENT_ALARM_MONITOR c,POST.KEYWORDS_ALARM k WHERE c.id=k.type_id and c.key='假证' and c.source_type= 1
优化过的sql语句
SELECT C.KEY, K.KEYWORDS, K.ID,
(SELECT COUNT(1) FROM POST.CONTENT_ALARM_RECORD_2013 CR WHERE CR.READ_STATUS=0 AND K.KEYWORDS=CR.KEY) AS UNREADY,
(SELECT COUNT(1) FROM POST.CONTENT_ALARM_RECORD_2013 CR WHERE CR.READ_STATUS=1 AND K.KEYWORDS=CR.KEY ) AS READY
FROM POST.CONTENT_ALARM_MONITOR C, POST.KEYWORDS_ALARM K WHERE C.ID=K.TYPE_ID AND C.SOURCE_TYPE= 1 AND C.KEY='假证'
查询时间由原来的7000毫秒,变为了200毫秒
在这里自己总结一下优化的几点:
1.sql全部改为大写
2.多表联查的时候,数据量少的表写在前边
3.数据量差不多的表,安装a-z的表明顺序
4.如果有C.SOURCE_TYPE= 1 AND C.KEY='假证'这样条件的 数字的条件放在前边,String 字符串的查询条件放在后边