关于sql server 2005的索引和缓存

[xiaomifeng007]

关于sql server 2005的索引和缓存的关系疑问
测试：win2003 ，sql server2005，200w数据查询其中一条
sql语句：
SELECT *
FROM T
where
pkid='1c0fe631-dccb-4a90-b2f6-d09e8663477c' and
fkid='503411c4-958b-480f-9929-8fa98a7c4a05'
1. 表无主键，无索引， pkid不为空：700ms
2. 表无主键，fkid非聚集索引，pkid不为空：第一次查询，15000ms，以后15ms左右
3. 表无主键，fkid非聚集索引，pkid不为空：
dbcc freeproccache
dbcc dropcleanbuffers
每次都是25000ms左右。

疑问：
1. 建立完毕的索引（建索引大约用来两三分钟）和缓存还有关系吗？
2. 针对测试2的第一次查询（15000ms）为什么会比测试1（700ms）大那么多？
3. 测试3，清空缓存以后，执行语句索引为什么不起作用？

[w2jc]

如果LZ对这种细节很关心的话，应该仔细去研究一下SQL Server的I/O方面的资料

你能观察到这么大的区别主要是因为你的表记录有200万，小表上看不到这些区别。
这的确是因为MSSQL缓冲了不少东西。

1. 建立完毕的索引（建索引大约用来两三分钟）和缓存还有关系吗？
---------------------------------------------------------
索引和缓存没有直接关系。索引在磁盘上，缓存在内存中。
但是..... MSSQL在读索引的过程中会把最近读的东西放在缓存里面，
这也包括索引数据，这个时候两者又是有关系的。

2. 针对测试2的第一次查询（15000ms）为什么会比测试1（700ms）大那么多？
---------------------------------------------------------
MSSQL对于有索引的表，会试图优化查询（很慢的一个步骤），
然后在读索引的过程中会把最近读的东西放在缓存里面，
所以第一次有点慢，因为优化索引和读I/O都是最慢的。
第二次就非常快，因为都在内存里面了，不用执行I/O。

3. 测试3，清空缓存以后，执行语句索引为什么不起作用？
---------------------------------------------------------
清空缓存之后，MSSQL又要执行I/O去读索引文件，所以又很慢。
你的例子中，dbcc dropcleanbuffers 和你的测试没有什么关系。


1. 表无主键，无索引， pkid不为空：700ms
2. 表无主键，fkid非聚集索引，pkid不为空：第一次查询，15000ms，以后15ms左右
---------------------------------------------------
你这两个测试结果里面，第一个因为没有索引，所以MSSQL应该直接用“全表扫描”去找记录，
但是我也不太清楚为什么会只需要700ms（如果有200万记录的话）
第二种情况呢，MSSQL会先试图优化SQL（这也很花时间），然后去读索引文件，所以第一次很慢。
看你fkid,pkid数据，就像UUID一样的值，这样的数据建出来的索引文件比较大，这可能是比较慢的原因。

[mengxj85]

Mark

[dawugui]

你认识一个人十年了,现在每次见到他,你就知道他的相关信息,不用去了解,不用问别人.
但是你某天失去记忆了,再看到他,得又花十年才能知道他的相关信息.

不知道这么说,你能否明白.

[xiaomifeng007]

不明白，还请不吝赐教，索引不是建在物理磁盘上的吗，怎么还和缓存有关呢

[zhuxianzhu]

缓存跟磁盘也有关

[dawugui]

[Quote=引用 2 楼 xiaomifeng007 的回复:]
不明白，还请不吝赐教，索引不是建在物理磁盘上的吗，怎么还和缓存有关呢
[/Quote]
你对人的记忆不是已经在脑海中了吗?(可你的脑海中不止对他一个人的记忆)

所以你看到他时得把有关他的信息从脑海中提取出来.其他的人的信息得暂时屏蔽.

[xiaomifeng007]

谢谢“龟”大侠，我还是再去仔细研究一下索引和缓存的机制吧

[Ny-6000]

可能会定期重建，或被清空的。