数据查询之诡异,请高手赐教...
我数据库有这样几个表:jldinfo(1799条数据),zdjbxx(80条数据),zddainfo(15条数据),energy(134015条数据)
我用这条语句查询一秒钟就查出来:
select count(*)?
from jldinfo as c?
inner join zdjbxx as a on a.zdid=c.zdid?
inner join zddainfo as b on a.zdid=b.zdid?
inner join energy as d on d.teNumber=a.zddzm and d.measurePoint=c.zdcldh?
where d.type=2 and d.subtype=1 and datediff(day,'2012-3-18',d.copytime)=0?
and a.zdlx not in (5,6,7)
and c.dwid in ('1','2','3','4')?
但是我将上面这个语句最后那个4删掉,就要查30秒:
select count(*)?
from jldinfo as c?
inner join zdjbxx as a on a.zdid=c.zdid?
inner join zddainfo as b on a.zdid=b.zdid?
inner join energy as d on d.teNumber=a.zddzm and d.measurePoint=c.zdcldh?
where d.type=2 and d.subtype=1 and datediff(day,'2012-3-18',d.copytime)=0?
and a.zdlx not in (5,6,7)
and c.dwid in ('1','2','3')?
这个是为甚么呀?请高手赐教呀...
[解决办法]
看执行计划，有没有走索引
[解决办法]
1.建议对表:jldinfo,zdjbxx,zddainfo的zdid字段建立索引.
2.and a.zdlx not in (5,6,7)
? and c.dwid in ('1','2','3','4') ?
这两句改为:
and a.zdlx not between 5 and 7
and c.dwid between '1' and '4' ?
[解决办法]
