SQL SERVER 2008 索引

USE [XIADONGERP]
GO

/****** Object: Index [PK_WM_IOForm] Script Date: 02/02/2018 10:38:08 ******/
ALTER TABLE [dbo].[WM_IOForm] ADD CONSTRAINT [PK_WM_IOForm] PRIMARY KEY CLUSTERED
(
[DSYSID] ASC  升序   DESC 降序
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

 

select * from WM_IOForm A inner join WM_IOFormDts B on A.DSYSID=B.DSYSID where A.DSYSID=190

执行计划

select * from (select * from WM_IOForm where DSYSID=190) A inner join WM_IOFormDts B on A.DSYSID=B.DSYSID 

执行计划

可以看出他们的执行计划是一样的,也就是inner join 在连接两张表之前是先查找后拼接, 查询速度的快慢主要决定于磁盘I/O的快慢,所以如果得到结果越多,时间就会越长

select * from WM_IOForm A,WM_IOFormDts B where A.DSYSID=B.DSYSID

 时间:744317 行受影响   84306ms

select * from WM_IOForm A INNER JOIN WM_IOFormDts B ON A.DSYSID=B.DSYSID

时间:744317 行受影响    81536ms

时间几乎相差无几

select * from WM_IOForm A LEFT OUTER JOIN WM_IOFormDts B ON A.DSYSID=B.DSYSID

时间:744358 行受影响  84090ms

没有索引的

 select * from WM_IOForm A INNER JOIN WM_IOFormDts B ON A.DSYSID=B.DSYSID

时间:398592 行受影响  17970ms

 

有索引的  select * from WM_IOForm A INNER JOIN WM_IOFormDts B ON A.DSYSID=B.DSYSID

时间:398592 行受影响   17360ms

差别不是很大,因为那是主要有I/O决定的