使用聚集索引优化SQL查询
首先让我们做一个测试,现创建一个表 向表中插入不等数据
--DROP TABLE T_UserInfo--------------------------------------
CREATE
TABLE
T_UserInfo
(
Userid
varchar(20),
UserName varchar(20)
)
--
@I INT
DECLARE
@ENDID INT
@I =
1
SELECT
@ENDID = 100
-- 在此处更改要插入的数据,重新插入之前要删掉所有数据
BEGIN
INSERT INTO T_UserInfo
SELECT 'ABCDE' + CAST ( @I AS VARCHAR ( 20 ))+ 'EF' , 'Fan' + CAST ( @I AS VARCHAR ( 20 ))
SELECT @I = @I+1
END
--相关SQL语句解释
-------------------------------------------------------------建聚集索引
CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
--建非聚集索引
CREATE NONCLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
--删除索引
DROP INDEX T_UserInfo.INDEX_Userid
我们创建 表T_UserInfo ,向T_UserInfo 添加100条数据。这时不创建索引。
然后写了一条查询语句:
SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF' 选中以上语句,
按Ctrl+L
表扫描:扫描表中的行
执行:SET STATISTICS IO ON
此时再执行该SQL:SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'
切换到消息栏显示如下:
表'T_UserInfo'。扫描计数1,逻辑读1 次,物理读0 次,预读0 次。
解释下其意思:
四个值分别为:
执行的扫描次数;
从数据缓存读取的页数;
从磁盘读取的页数;
为进行查询而放入缓存的页数
我们在创建一个索引
执行CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
然后再执行SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'
切换到消息栏如下显示:
CTRL+ L
表'T_UserInfo'。扫描计数1,逻辑读2 次,物理读0 次,预读0 次。
此时逻辑读由原来的1变成2, 说明我们又加了一个索引页,现在我们查询时,逻辑读就是要读两页(1索引页+1数据页),此时的效率还不如不建索引。
那么okay, 让我们把数据填加止1000条
重复上面的步骤
现在我再把测试数据改变成1000条
再执行SET STATISTICS IO ON,
再执行 SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'
在不加聚集索引的情况下:
表'T_UserInfo'。扫描计数1,逻辑读7 次,物理读0 次,预读0 次。
在加聚集索引的情况下:CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)
表'T_UserInfo'。扫描计数1,逻辑读2 次,物理读0 次,预读0 次。
(其实也就是说此时是读了一个索引页,一个数据页) 如此,在数据量稍大时,索引的查询优势就显示出来了。