有一记录非常多的表(百万级)sj_tab,为了提高下面语句查询速度.
SELECT Workshop,sum(sj1) sj1,sum(sj2) sj2
FROM sj_tab
WHERE sjstatus='001' and sjtype='001' and sjdate = '2009-03-31'
group by Workshop
我对Workshop,sjstatus,sjtype,sjdate建了聚集索引,但查询时间仍然比较长.不知何故.另外字段在聚集索引中前后有关系吗?
SELECT Workshop,sum(sj1) sj1,sum(sj2) sj2
FROM sj_tab
WHERE sjstatus='001' and sjtype='001' and sjdate = '2009-03-31'
group by Workshop
我对Workshop,sjstatus,sjtype,sjdate建了聚集索引,但查询时间仍然比较长.不知何故.另外字段在聚集索引中前后有关系吗?
SELECT Workshop,sum(sj1) sj1,sum(sj2) sj2
FROM sj_tab
WHERE sjstatus='001' and sjtype='001' and sjdate = '2009-03-31'
group by Workshop
你需要建你要查询的相关字段的联合索引。
sjstatus,sjtype,sjdate
不过还是建议你把索引价值比较高的字段单独建索引比较好。
查询条件必须以Workshop开始才能用到你建的索引。
建议你在数据字段上建个索引,然后把时间字段放在where后面。
然后这样查询,肯定会快很多的。我看你的sjstatus和sjtype一定会多次重复出现,所以没必要建个联合索引。SELECT Workshop,sum(sj1) sj1,sum(sj2) sj2
FROM sj_tab
WHERE sjdate = '2009-03-31' and sjstatus='001' and sjtype='001'
group by Workshop