HisTAAmountLog201306:该表ID为自增,作为主键
SELECT t1.isValid, t1.MID,t1.ExecuteDate, t1.ExecuteType,
t1.Amount, t1.RelationMID,t1.RelationTicket, t1.Balance,
t1.Re, t1.LoginID,t1.UID, OperatorInfo.LoginID AS OperatorName,
t1.OperatorID, t1.TradeDate,t1.TradeStartDate, t1.TradeEndDate,
t1.Id, dbo.Customer.CustomerName
FROM HisTAAmountLog201306 as t1 INNER JOIN
dbo.Customer AS OperatorInfo ON
t1.OperatorID = OperatorInfo.UID INNER JOIN
dbo.Customer ON t1.UID = dbo.Customer.UID
INNER JOIN taprop t2 ON t1.MID=t2.MID
WHERE ( OperatorInfo.customertype = '1' or Customer.CustomerType='1')
and TradeDate >= '2013-06-04' and TradeDate <= '2013-06-04'
order by Mid, ID然后通过数据库引擎优化顾问,提示以下信息
请问下,HisTAAmountLog201306,该表我要怎么建索引才是更有效?我对这样的分析图不是很明白,能不能帮我分析下,万分感谢!索引执行计划优化顾问
SELECT t1.isValid, t1.MID,t1.ExecuteDate, t1.ExecuteType,
t1.Amount, t1.RelationMID,t1.RelationTicket, t1.Balance,
t1.Re, t1.LoginID,t1.UID, OperatorInfo.LoginID AS OperatorName,
t1.OperatorID, t1.TradeDate,t1.TradeStartDate, t1.TradeEndDate,
t1.Id, dbo.Customer.CustomerName
FROM HisTAAmountLog201306 as t1 INNER JOIN
dbo.Customer AS OperatorInfo ON
t1.OperatorID = OperatorInfo.UID INNER JOIN
dbo.Customer ON t1.UID = dbo.Customer.UID
INNER JOIN taprop t2 ON t1.MID=t2.MID
WHERE ( OperatorInfo.customertype = '1' or Customer.CustomerType='1')
and TradeDate >= '2013-06-04' and TradeDate <= '2013-06-04'
order by Mid, ID然后通过数据库引擎优化顾问,提示以下信息
请问下,HisTAAmountLog201306,该表我要怎么建索引才是更有效?我对这样的分析图不是很明白,能不能帮我分析下,万分感谢!索引执行计划优化顾问
自動增長ID感覺沒什麽意義,業務一般不會用ID進行查詢和排序
create index idx_HisTAAmountLog201306_c1
on HisTAAmountLog201306(TradeDate,MID,OperatorID,UID)
楼上为什么不加上ID一起建立索引呢。而且那个51%开销,我一个索引都没有建立,为什么就提示聚集索引扫描。
应该是显示全表扫描才对啊!实在是不懂如何分析这样的图,然后如何建索引!
哦,你的表在创建的时候,设置了主键,那么sql server默认会给这个主键,创建一个聚集索引,所以没有显示为表扫描,而是聚集索引扫描。
create index idx_HisTAAmountLog201306_c1
on HisTAAmountLog201306(TradeDate,MID,OperatorID,UID)
如果按照优化顾问的建议,建立以下索引:
CREATE NONCLUSTERED INDEX [idx_HisTAAmountLog201306_c2] ON [dbo].[HisTAAmountLog201306]
(
[TradeDate] ASC,
[UID] ASC,
[OperatorID] ASC,
[MID] ASC,
[Id] ASC
)
INCLUDE ( [isValid],[ExecuteDate],[ExecuteType],[Amount],[RelationMID],[RelationTicket],[Balance],[Re],[LoginID],[TradeStartDate],[TradeEndDate])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
执行计划如下:
这要怎么分析啊!
如果按照优化顾问的建议,建立以下索引:
CREATE NONCLUSTERED INDEX [idx_HisTAAmountLog201306_c2] ON [dbo].[HisTAAmountLog201306]
(
[TradeDate] ASC,
[UID] ASC,
[OperatorID] ASC,
[MID] ASC,
[Id] ASC
)
INCLUDE ( [isValid],[ExecuteDate],[ExecuteType],[Amount],[RelationMID],[RelationTicket],[Balance],[Re],[LoginID],[TradeStartDate],[TradeEndDate])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
执行计划如下:
这要怎么分析啊! 哦 ,优化顾问的建议是有道理的,我上面的索引少了一个字段id,那么现在从原来的聚集索引扫描,变成了索引查找,从表面来看,应该是效率要提高一点了。你这么建索引了后,速度提高了吗
on HisTAAmountLog201306(TradeDate,MID,OperatorID,UID)如果我单独建索引,就是建立四个索引。
跟建立组合索引,有什么区别。千万级别的数据,搜索范围1万左右,有索引用时4秒。
无索引:18秒