table表内总共14000多条数据,用cateid划分,数量大概如下
cateid=1:4500条
cateid=2:500条
cateid=3:5条
cateid=4:4000条
cateid=5:1500条
cateid=7:500条
cateid=8:3000条主键是id,另外建立了复合索引list,顺序为:cateid(smallint) + istop(desc, tinyint) + lastupdate(desc, datetime)
由于lastupdate字段随时都在更新,所以在字段id上建立了聚集索引下面是两个条件相同的查询,只是数量不同
查询1:
select top 60 id,title
from table
where cateid = 2 and istop = 0
order by lastupdate desc查询2:
select top 70 id,title
from table
where cateid = 2 and istop = 0
order by lastupdate desc查询1的成本是:4.28%
SELECT:0%,TOP:0%,BookMark Lookup:47%,Index Seek:52%
查询2的成本是:95.72%
SELECT:0%,TOP:0%,BookMark Lookup:98%,Index Seek:2%数据相差10条成本竟然相差几十倍。这是什么原因?再看下面这两个查询,提取数量相同,条件也相同,只是值不同
select top 100 id,title
from table
where cateid = 2 and istop = 0
order by lastupdate descselect top 100 id,title
from table
where cateid = 1 and istop = 0
order by lastupdate desc查询1的成本是49.51%
SELECT:0%,TOP:0%,BookMark Lookup:98%,Index Seek:2%
查询2的成本是50.49
SELECT:0%,Sort/TopN Sort:9%,Clustered Index Scan:91%很奇怪为什么查询2放弃了索引?
cateid=1:4500条
cateid=2:500条
cateid=3:5条
cateid=4:4000条
cateid=5:1500条
cateid=7:500条
cateid=8:3000条主键是id,另外建立了复合索引list,顺序为:cateid(smallint) + istop(desc, tinyint) + lastupdate(desc, datetime)
由于lastupdate字段随时都在更新,所以在字段id上建立了聚集索引下面是两个条件相同的查询,只是数量不同
查询1:
select top 60 id,title
from table
where cateid = 2 and istop = 0
order by lastupdate desc查询2:
select top 70 id,title
from table
where cateid = 2 and istop = 0
order by lastupdate desc查询1的成本是:4.28%
SELECT:0%,TOP:0%,BookMark Lookup:47%,Index Seek:52%
查询2的成本是:95.72%
SELECT:0%,TOP:0%,BookMark Lookup:98%,Index Seek:2%数据相差10条成本竟然相差几十倍。这是什么原因?再看下面这两个查询,提取数量相同,条件也相同,只是值不同
select top 100 id,title
from table
where cateid = 2 and istop = 0
order by lastupdate descselect top 100 id,title
from table
where cateid = 1 and istop = 0
order by lastupdate desc查询1的成本是49.51%
SELECT:0%,TOP:0%,BookMark Lookup:98%,Index Seek:2%
查询2的成本是50.49
SELECT:0%,Sort/TopN Sort:9%,Clustered Index Scan:91%很奇怪为什么查询2放弃了索引?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货