我有这样的疑问:
我已经在排序字段上建立了非聚凑索引了,
----按两个字段排序
select top 200 jsjdm,szdmd,djzclxdm,qxfjdm,gjbzhydm,zcgmdm,dqsr 当期收入,
dqljsr 累计收入, tqsr 同期收入, tqljsr 同期累计,zje 增减额,zf [增幅(%)],
ljzje 累计增减额,ljzf 累计增幅 from
swfx_bak where zyrq_month='2008-07-01'
order by gjbzhydm ,同期累计 DESC ----执行计划
|--Top(TOP EXPRESSION:((200)))
|--Parallelism(Gather Streams, ORDER BY:([sw_bak].[dbo].[swfx_bak].[GJBZHYDM] ASC, [sw_bak].[dbo].[swfx_bak].[tqljsr] DESC))
|--Sort(TOP 200, ORDER BY:([sw_bak].[dbo].[swfx_bak].[GJBZHYDM] ASC, [sw_bak].[dbo].[swfx_bak].[tqljsr] DESC))
|--Table Scan(OBJECT:([sw_bak].[dbo].[swfx_bak]), WHERE:([sw_bak].[dbo].[swfx_bak].[ZYRQ_MONTH]='2008-07-01 00:00:00.000'))按一个字段排序, |--Top(TOP EXPRESSION:((200)))
|--Filter(WHERE:([sw_bak].[dbo].[swfx_bak].[ZYRQ_MONTH]='2008-07-01 00:00:00.000'))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1004]) WITH ORDERED PREFETCH)
|--Index Scan(OBJECT:([sw_bak].[dbo].[swfx_bak].[index_06]), ORDERED FORWARD)
|--RID Lookup(OBJECT:([sw_bak].[dbo].[swfx_bak]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)如果按照一个字段排序就用到了索引,是scan方式,索引页面的扫描,如果是按照两个字段排序,那就是没有用到索引,直接对表的页面进行扫描
我已经在排序字段上建立了非聚凑索引了,
----按两个字段排序
select top 200 jsjdm,szdmd,djzclxdm,qxfjdm,gjbzhydm,zcgmdm,dqsr 当期收入,
dqljsr 累计收入, tqsr 同期收入, tqljsr 同期累计,zje 增减额,zf [增幅(%)],
ljzje 累计增减额,ljzf 累计增幅 from
swfx_bak where zyrq_month='2008-07-01'
order by gjbzhydm ,同期累计 DESC ----执行计划
|--Top(TOP EXPRESSION:((200)))
|--Parallelism(Gather Streams, ORDER BY:([sw_bak].[dbo].[swfx_bak].[GJBZHYDM] ASC, [sw_bak].[dbo].[swfx_bak].[tqljsr] DESC))
|--Sort(TOP 200, ORDER BY:([sw_bak].[dbo].[swfx_bak].[GJBZHYDM] ASC, [sw_bak].[dbo].[swfx_bak].[tqljsr] DESC))
|--Table Scan(OBJECT:([sw_bak].[dbo].[swfx_bak]), WHERE:([sw_bak].[dbo].[swfx_bak].[ZYRQ_MONTH]='2008-07-01 00:00:00.000'))按一个字段排序, |--Top(TOP EXPRESSION:((200)))
|--Filter(WHERE:([sw_bak].[dbo].[swfx_bak].[ZYRQ_MONTH]='2008-07-01 00:00:00.000'))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1004]) WITH ORDERED PREFETCH)
|--Index Scan(OBJECT:([sw_bak].[dbo].[swfx_bak].[index_06]), ORDERED FORWARD)
|--RID Lookup(OBJECT:([sw_bak].[dbo].[swfx_bak]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)如果按照一个字段排序就用到了索引,是scan方式,索引页面的扫描,如果是按照两个字段排序,那就是没有用到索引,直接对表的页面进行扫描
Table Scan!你的ZYRQ_MONTH字段没有索引?
在ZYRQ_MONTH建立索引,毕竟where的比order by的重要
我有这样的疑问:
我已经在排序字段上建立了非聚凑索引了,
----按两个字段排序
select top 200 jsjdm,szdmd,djzclxdm,qxfjdm,gjbzhydm,zcgmdm,dqsr 当期收入,
dqljsr 累计收入, tqsr 同期收入, tqljsr 同期累计,zje 增减额,zf [增幅(%)],
ljzje 累计增减额,ljzf 累计增幅 from
swfx_bak where zyrq_month='2008-07-01'
order by gjbzhydm ,同期累计 DESC ----执行计划
|--Top(TOP EXPRESSION:((200)))
|--Parallelism(Gather Streams, ORDER BY:([sw_bak].[dbo].[swfx_bak].[GJBZHYDM] ASC, [sw_bak].[dbo].[swfx_bak].[tqljsr] DESC))
|--Sort(TOP 200, ORDER BY:([sw_bak].[dbo].[swfx_bak].[GJBZHYDM] ASC, [sw_bak].[dbo].[swfx_bak].[tqljsr] DESC))
|--Table Scan(OBJECT:([sw_bak].[dbo].[swfx_bak]), WHERE:([sw_bak].[dbo].[swfx_bak].[ZYRQ_MONTH]='2008-07-01 00:00:00.000')) 按一个字段排序就用到了索引 |--Top(TOP EXPRESSION:((200)))
|--Filter(WHERE:([sw_bak].[dbo].[swfx_bak].[ZYRQ_MONTH]='2008-07-01 00:00:00.000'))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1004]) WITH ORDERED PREFETCH)
|--Index Scan(OBJECT:([sw_bak].[dbo].[swfx_bak].[index_06]), ORDERED FORWARD)
|--RID Lookup(OBJECT:([sw_bak].[dbo].[swfx_bak]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) 如果按照一个字段排序就用到了索引,是scan方式,索引页面的扫描,如果是按照两个字段排序,那就是没有用到索引,直接对表的页面进行扫描
index_02 nonclustered located on PRIMARY szdmd(-)
index_03 nonclustered located on PRIMARY qxfjdm(-)
index_04 nonclustered located on PRIMARY djzclxdm(-)
index_06 nonclustered located on PRIMARY GJBZHYDM(-)
index_07 nonclustered located on PRIMARY zcgmdm
index_100 nonclustered located on PRIMARY dqsr(-)
index_101 nonclustered located on PRIMARY dqljsr(-)
index_102 nonclustered located on PRIMARY tqsr(-)
index_103 nonclustered located on PRIMARY tqsr(-)
index_104 nonclustered located on PRIMARY sqsr(-)
index_105 nonclustered located on PRIMARY zje(-)
index_107 nonclustered located on PRIMARY zf(-)
index_108 nonclustered located on PRIMARY ljzje(-)
index_109 nonclustered located on PRIMARY ljzf(-)
这个表已经有15个索引,索引已经占了3个g的空间了
|--Top(TOP EXPRESSION:((200)))
|--Filter(WHERE:([sw_bak].[dbo].[swfx_bak].[ZYRQ_MONTH]='2008-07-01 00:00:00.000'))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1004]) WITH ORDERED PREFETCH)
|--Index Scan(OBJECT:([sw_bak].[dbo].[swfx_bak].[index_06]), ORDERED FORWARD)
|--RID Lookup(OBJECT:([sw_bak].[dbo].[swfx_bak]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) Index Scan,而非Index Seek你增加ZYRQ_MONTH索引试试效果再说吧
这个貌似很消耗资源
既然where后面跟这个 为什么不在这个字段加索引?
|--Parallelism(Gather Streams, ORDER BY:([sw_bak].[dbo].[swfx_bak].[GJBZHYDM] ASC, [sw_bak].[dbo].[swfx_bak].[zje] DESC))
|--Sort(TOP 1000, ORDER BY:([sw_bak].[dbo].[swfx_bak].[GJBZHYDM] ASC, [sw_bak].[dbo].[swfx_bak].[zje] DESC))
|--Table Scan(OBJECT:([sw_bak].[dbo].[swfx_bak]), WHERE:([sw_bak].[dbo].[swfx_bak].[ZYRQ_MONTH]='2008-07-01 00:00:00.000'))
这是加上索引以后的执行计划,在where确实用到了索引,但是整个语句的执行还很慢,在排序的时候仍热是没用到索引
和ctrl+L 观察一下走哪个索引可以达到你的要求
SQL自己通过统计信息做出的最优查询
未必是最好的,有些时候需要自己加with的
还有定期更新统计信息也是必要的.