select count(*) from tb是否需要扫描全部的索引中间层？解决办法

2012-01-09

select count(*) from tb是否需要扫描全部的索引中间层？表tb有聚集索引,有13个索引中间页.那么在select co

select count(*) from tb是否需要扫描全部的索引中间层？
表tb有聚集索引,有13个索引中间页.
那么在select count(*) from tb时，发现全走全部的索引中间层叶.

从理论上讲，由于每个数据页都记录着它的上一个page和下一个page,那么最小的io读取应该是:
读根页--->读最小的中间页--->读数据叶子---->依次往后读全部数据页.

但实际上却是读取全部的索引中间页，谁能解释一下？

附部分代码：

SQL code

use tempdbgoCREATE TABLE tmp (id int  ,c1 char(500),c2 char(500))CREATE CLUSTERED INDEX CI_id ON tmp(id)DECLARE @i INTSET @i=0WHILE @i<20129BEGIN    INSERT INTO tmp(id,c1,c2)    SELECT @i,'a','z'    SET @i=@i+1ENDset statistics io onselect COUNT(*) from tmpDBCC IND(tempdb,tmp,-1)dbcc traceon(3604)dbcc page(tempdb,1,1666,3)dbcc page(tempdb,1,196,3)dbcc page(tempdb,1,193,3)

[解决办法]
这些问题值得推荐探讨
[解决办法]
SF是我的
[解决办法]
1111111111111111111111111
[解决办法]

[解决办法]
看了下不懂！~~~~~~~~~~
[解决办法]
學習~~
[解决办法]

[解决办法]
强烈学习.....................
[解决办法]
1.最小的io读取应该是:读根页--->读最小的中间页--->读数据叶子---->依次往后读全部数据页
2.实际上却是读取全部的索引中间页

→ 不是加了聚集索引，就一定走最小的io读取吧？是不是该这么理解?
[解决办法]

[解决办法]
如果什么条件都没有不是应该把所有的数据页都读出来再流聚合吗,为什么不走全部的数据页呢?
[解决办法]
从根节点到所有的页节点可以不用走全部的中间节点吗?
[解决办法]
学习中，期待进一步的解答。关注中。。。
[解决办法]
路过，关注，学习.....
[解决办法]

[解决办法]
路过学习

回复内容太短了！
[解决办法]
queryer可能选择了次优算法
[解决办法]
好有深度的问题,学习!
[解决办法]
学习中

回复内容太短了回复内容太短了
[解决办法]
学习了, 数据库永远是一个学不完的东东
[解决办法]
关注~
回复内容太短！
[解决办法]
是不是和查询一样，有二分法呀！呵呵！不懂，只是猜测！期待高手解答

[解决办法]
天啊我看不懂题目天啊我看不懂题目天啊我看不懂题目天啊我看不懂题目
[解决办法]
看来我看不懂啊，等待高手详细分析
[解决办法]

[解决办法]
中间层是什么东东?中间层是什么东东?
[解决办法]
前面的那些T-SQL语句还看得懂，但后面的“DBCC IND(tempdb,tmp,-1)”不知道是什么啊？

[解决办法]
路过看看............
[解决办法]
看来自己学的一点都不好！还得努力加油了
[解决办法]
学习
[解决办法]
这个问题太深了。。。等高手
[解决办法]
先学习下 SQL 语言吧，详细的自学教程就在 CSDN.NET 网站上下载：

http://download.csdn.net/source/2070397
[解决办法]
不懂
[解决办法]
这个问题有点深,平时没有关注
[解决办法]
不懂顶..............
[解决办法]
不懂呃!!!!!!!!!!!!!
[解决办法]
好像不是很懂。。。。学习了
[解决办法]
没看明白！不过还是要学习学习！
[解决办法]
MARK 学习学习

回复内容太短了！
[解决办法]

C# code

看看
[解决办法]
select count(*) 仅仅统计一下表的行数而已，不一定涉及到表扫描。
你说的：最小的io读取应该是:读根页--->读最小的中间页--->读数据叶子---->依次往后读全部数据页。

未必吧!!!难道你将select count(*) 混淆成 select * 了。

个人感觉：索引页比起数据页来每页能够存储很多索引行。基于io成本，读取索引页应该能够执行更少的io来节约成本。
[解决办法]
看不懂。。帮顶。。。。。。
[解决办法]
mark
[解决办法]
to #43:

以lz附上的代码为例，count(*) from tb就是需要扫描整个clustered index 结构的。

clustered index的叶结点包含数据，且数据页之间是用双向链接关联起来的，所以理论上说只要从根-->最小值中间层--->叶子--->按链接顺序依次往前读取叶子，这样便可遍历读取完所有的数据.

其它的中间层页，似乎真的没有必要去触碰到,只需要读取最小的就行.

我跟perfect年前就讨论过这问题，他也咨询过微软支持，得到的答复是预读机制的原由，但我们觉得这个说法不能完全说的通。

[解决办法]
深奥了。。。
[解决办法]

探讨
深奥了。。。

[解决办法]
说不定是优化器就没打算走最优化的路线呢
[解决办法]

探讨
to #43:

以lz附上的代码为例，count(*) from tb就是需要扫描整个clustered index 结构的。

clustered index的叶结点包含数据，且数据页之间是用双向链接关联起来的，所以理论上说只要从根-->最小值中间层--->叶子--->按链接顺序依次往前读取叶子，这样便可遍历读取完所有的数据.

其它的中间层页，似乎真的没有必要去触碰到,只需要读取最小的就行.

我跟perfect年前就讨论过这问题，他也咨询过微软支持，得到的答复是预读机制的原由，但我们觉得这个说法不能完全说的通。

[解决办法]

------解决方案--------------------

[解决办法]
關注後續討論= =
無責任猜測可能是索引或優化器記錄了常用搜索模式
走了最常用的掃面策略
[解决办法]
关注，现在学习一下。
[解决办法]
我只看不说话
[解决办法]
值得研究，，，，，。。。。
[解决办法]
看不懂，数据库这东西，比较麻烦~~~
[解决办法]
路过，关注，学习.....
[解决办法]

[解决办法]
比较高深阿，学习了。
[解决办法]
学习~~~~~~~~~~
[解决办法]
我是来学习的。。。。。。。。。。。。
[解决办法]
高深
[解决办法]
应该SQL估算成本相近，所以没有选择最优化的执行路径。我也是猜测，等一下吧测试一下再说。
[解决办法]
深层次问题，标记待学习
[解决办法]
估计是SQL SERVER的内部算法导致的。
[解决办法]
drop table tmp
CREATE TABLE tmp (id CHAR(800) ,c1 char(2),c2 char(2))
CREATE CLUSTERED INDEX CI_id ON tmp(id)
DECLARE @i INT
SET @i=0
WHILE @i < 20000
BEGIN
INSERT INTO tmp(id, c1, c2)
--SELECT Replicate(@i, 10),'a','z'
SELECT @i * 10000 ,'a','z'
SET @i=@i+1
END
这样在测试一下，可以肯定的是。不会遍历所有中间层页面，
但是比楼主估计的计划逻辑度还是高了一些，原因现在还没弄清楚。
[解决办法]
重建索引

探讨
前面的那些T-SQL语句还看得懂，但后面的“DBCC IND(tempdb,tmp,-1)”不知道是什么啊？

[解决办法]
好复杂，看得我头疼

（回复内容太短了！）
[解决办法]
猜测是读取了叶节点和倒数低二层及IndexLevel = 1的索引。这个只是猜测，还没有证实哈。
[解决办法]
1、通过索引查找数据页面，SQLSERVER是从根开始从最左的指针进行扫描，以确定数据页面的首页
2、存储引擎使用高于叶级的中间索引页上的信息为包含键的页安排序列预读，存储引擎将扫描中间索引页并生成必须要读取的叶级页的列表。然后再根据列表再进行页面的读取，所以中间页面必须读
3、这是猜测，SQLSERVER　使用残缺页保护和校验机制保证完整性，不读所有的页面怎么检验呢
[解决办法]

探讨
1、通过索引查找数据页面，SQLSERVER是从根开始从最左的指针进行扫描，以确定数据页面的首页
2、存储引擎使用高于叶级的中间索引页上的信息为包含键的页安排序列预读，存储引擎将扫描中间索引页并生成必须要读取的叶级页的列表。然后再根据列表再进行页面的读取，所以中间页面必须读
3、这是猜测，SQLSERVER　使用残缺页保护和校验机制保证完整性，不读所有的页面怎么检验呢

[解决办法]
Data page都已经读到buffer pool中了，还需要什么预读? 看看第一次、第二次执行的logical reads的读数.

------解决方案--------------------

SQL code

存储引擎使用高于叶级的中间索引页上的信息为包含键的页安排序列预读。如果请求针对的是 ABC 到 DEF 之间的所有键，则存储引擎将首先读取高于叶级页的索引页，但它并不是仅仅按顺序读取页 504 到页 556（即指定范围内的包含键的最后一页）之间的每个数据页。相反，存储引擎将扫描中间索引页并生成必须要读取的叶级页的列表。然后，存储引擎会按键的顺序安排所有读取。存储引擎还会识别出页 504/505 以及页 527/528 是相邻页，并执行一次散播读取，从而在单个操作中检索这些相邻页。如果在一个序列操作中要检索许多页，则存储引擎将一次安排一个读取块。完成这些读取子集后，存储引擎将安排同等数量的新读取，直到安排完所需的全部读取。
[解决办法]
分析下来，SELECT COUNT(*) FROM tmp
执行的时候遍历叶节点和 IndexLevel = 1 的页面
再加上从IndexLevel 最大的页面遍历到 IndexLevel = 1 的读取次数
核对下来数据是这样。但是SQL为什么要这么执行就要等高手来讲解了。

[解决办法]

探讨
SQL code存储引擎使用高于叶级的中间索引页上的信息为包含键的页安排序列预读。如果请求针对的是 ABC 到 DEF 之间的所有键，则存储引擎将首先读取高于叶级页的索引页，但它并不是仅仅按顺序读取页504 到页556（即指定范围内的包含键的最后一页）之间的每个数据页。相反，存储引擎将扫描中间索引页并生成必须要读取的叶级页的列表。然后，存储引擎会按键的顺序安排所有读取。存储引擎还会识别出页504/505 以及页527/528 是相邻页，并执行一次散播读取，从而在单个操作中检索这些相邻页。如果在一个序列操作中要检索许多页，则存储引擎将一次安排一个读取块。完成这些读取子集后，存储引擎将安排同等数量的新读取，直到安排完所需的全部读取。

这是08联机丛书上面的索引扫描那段,不过没有确切理解

[解决办法]
猜测是读取了叶节点和倒数低二层及IndexLevel = 1的索引
[解决办法]

探讨
微软一技术支持给我的回复：

对于select count(*) from tb这个语句，其执行过程中确实走了全部的聚集索引中间层叶.
这个问题实际上不只是对于count(*)这种情况，而是SQL Server在执行Clustered Index Scan和Index Scan操作时对于索引页访问的普遍情况。
虽然从逻辑读IO的角度考虑，直接找到索引树的叶级页中的第一个页，然后顺着相邻页之间的链接指针依次访问所有的叶级页的方式是逻辑读IO最低的，但是从物理IO的性能角度考虑，这意味在一些索引页尚未缓存在内存中的情况下，将会导致较多的物理读IO（因为只能按照叶级页之间的逻辑关系顺次读取每个页）。
由于磁盘读IO的开销远远大于内存中的逻辑读，因此SQL Server在执行Index Scan这类操作时需要优先考虑如何尽量减少物理读IO的开销，具体来说就是尽量采用预读（Read-Ahead）的机制：SQL Server首先依次遍历索引的每个中间层的所有页，由此确定下一层所有索引页的物理位置，然后将这些页按照其物理位置的顺序读到缓存中（其中物理上相邻的页可以用更少的磁盘IO读入）。
关于SQL Server中的Read-Ahead的机制可以参考如下链接：
http://msdn.microsoft.com/en-us/library/ms191475.aspx

对于以上的回复，我起初也是认为讲的通，但是后来注意到一点，预读并不是逻辑读,预读需要这么做，是因为他要把数据缓存起来，而逻辑读是从缓存中读数据，为啥还有再读全部索引中间层的道理？这个似乎解释不通了.

[解决办法]
没条件的count(*)实际使用是很少的，我觉得没必要深究
[解决办法]
师傅真强，帮顶
[解决办法]
看了你的问题我才发现原来我很浅很浅
[解决办法]
首先楼主的说法不够准确，根据我的测试结果来看，更准确的说法应该是sql server在做索引全扫(index full scan)时，会先扫描全部的level 1的节点，然后在扫描处于level 0的叶节点。也就是说一次索引全扫的顺序是（这个顺序对于clustered index和普通index都是一样的）：
读根页--->读最小的中间页（level 1以上的层）--->依次读level 1的所有节点的索引页---->读全部数据页
上述顺序可以在将数据表索引层数增加到3层以上时得到验证。

实际上我觉得sql server这样做是很有道理的，楼主认为的是所读的页越少，io就越小，这是不对的。对于sql server来说，一次io并不一定只读取一个页，很多时候特别是做表全扫或索引全扫时，sql server都会尽量的让一次io读取尽可能多的数据页，这样才能较少io的次数。为达到这个目的，sql server首先就要知道它到底需要读取那些页，知道要读取页的分布之后，就可以安排io调度器尽可能的将临近的数据页用一次io读取上来，而要知道需要读取数据页的分布就需要先读取到level 1上的所有页，以找出要读取io页的分布情况，然后再做最优的io读取安排。
同样的道理，如果sql server是先定位到叶节点的起始数据页，然后从依次的读取所有的数据页的话，那sql server就只能一次io读取一个数据页，然后找出对应的下一页的指针，再去读取下一个数据页，这样实际上花费的io会远比批量读取数据页多很多的。这就好比我们在做表的lookup时，如果需要lookup的行非常之多的话sql server会转而选择表扫描来达到目的的。

我上面的说法可以通过下面的文章得到部分的验证（这是在楼主另一个帖子里面别人回复的）。

引用
"SQL Server 2000 uses the information in the intermediate index page above the leaf level to schedule serial read-ahead I/Os for the pages containing the keys. If a request is made for all the keys from 'ABC' to 'DEF', the instance of SQL Server 2000 first reads the index page above the leaf page. It does not, however, simply read each individual data page in sequence from page 504 to page 556, the last one with keys in the desired range. Instead, the storage engine scans the intermediate index page and builds a list of the leaf pages that must be read. The storage engine then schedules all the I/Os in key order. The storage engine also recognizes that pages 504/505 and 527/528 are contiguous, and performs a single scatter-gather read to retrieve the adjacent pages in one operation. When there are many pages to be retrieved in a serial operation, SQL Server schedules a block of reads at a time. When a subset of these reads is completed, SQL Server schedules an equal number of new reads until all the needed reads have been scheduled."

原文参见：http://msdn.microsoft.com/zh-cn/library/aa175258(en-us,SQL.80).aspx

[解决办法]
楼主另一个帖子为 http://social.technet.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/0f0101b1-abd2-4cfa-a796-c70ef5fde5e4
[解决办法]
633，，，，；；；
[解决办法]
学习
[解决办法]
记号，虽然一点都不懂，还是先看着
[解决办法]

探讨
sql server 通过预读来讲数据缓存到高速缓存中，逻辑读是从这个缓存中读取的。

[解决办法]

探讨
因此在决定一个查询语句的执行方法时sql server是不知道数据是否在缓冲的.

这个说法KG也提出过，似乎只要这样解释才能说明set statistics io on 中的逻辑读数数量.
但按这个说法，当查id=1时，sql server也不知道是否被缓存，是否也要这样搞一个全部的索引中间层扫描呢？如果是这样，为什么显示的逻辑读只有3,而预读的数据是1500之多？

[解决办法]

我是菜鸟，看不懂这玩意儿的~~
[解决办法]
还没有关注这么深呢。
[解决办法]
帮忙 uP 大家多讨论

热点排行