HisTAAmountLog201306:该表ID为自增,作为主键
SELECT t1.isValid, t1.MID,t1.ExecuteDate, t1.ExecuteType,
t1.Amount, t1.RelationMID,t1.RelationTicket, t1.Balance,
t1.Remark, 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,该表我要怎么建索引才是更有效?我对这样的分析图不是很明白,能不能帮我分析下,万分感谢!
索引 执行计划 优化顾问
------解决方案--------------------
你先试试建这个索引,不管重复不重复,建了后,看看查询能不能更快:
create index idx_HisTAAmountLog201306_c1
on HisTAAmountLog201306(TradeDate,MID,OperatorID,UID)
------解决方案--------------------
哦 ,优化顾问的建议是有道理的,我上面的索引少了一个字段id,那么现在从原来的聚集索引扫描,变成了索引查找,从表面来看,应该是效率要提高一点了。
你这么建索引了后,速度提高了吗