select count(*) from tb是否需要扫描全部的索引中间层?

nzperfect 2010-02-23 03:55:04
加精
表tb有聚集索引,有13个索引中间页.
那么在select count(*) from tb时,发现全走全部的索引中间层叶.

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

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

附部分代码:

use tempdb
go
CREATE TABLE tmp (id int ,c1 char(500),c2 char(500))
CREATE CLUSTERED INDEX CI_id ON tmp(id)
DECLARE @i INT
SET @i=0
WHILE @i<20129
BEGIN
INSERT INTO tmp(id,c1,c2)
SELECT @i,'a','z'
SET @i=@i+1
END

set statistics io on
select COUNT(*) from tmp

DBCC 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)


...全文
6446 169 打赏 收藏 转发到动态 举报
写回复
用AI写文章
169 条回复
切换为时间正序
请发表友善的回复…
发表回复
lawbc 2010-03-06
  • 打赏
  • 举报
回复
太深奥了,俺不懂啊 ,呵呵
zhangqiang0538 2010-03-06
  • 打赏
  • 举报
回复
想想想想想想想想想想不出来
keyyi 2010-03-05
  • 打赏
  • 举报
回复
高贴,mARK一下


回复内容 回复内容太短了!



javazhou1988 2010-03-05
  • 打赏
  • 举报
回复
来看看..............
liuhaifeng1976 2010-03-05
  • 打赏
  • 举报
回复
dingdingdingding
时光审美 2010-03-04
  • 打赏
  • 举报
回复
越来越深入了 顶起学习。。。数据啊数据。
live360 2010-03-04
  • 打赏
  • 举报
回复
lcw321321 2010-03-04
  • 打赏
  • 举报
回复
mark,回复内容太短了
蛙易 2010-03-04
  • 打赏
  • 举报
回复
学习了(它说我回复的内容太短了)
baisiye 2010-03-03
  • 打赏
  • 举报
回复
很强大~~~~~~~~~~~~
wenboliang 2010-03-03
  • 打赏
  • 举报
回复
看不懂是什么东西 东方饭店的的的的
nzperfect 2010-03-02
  • 打赏
  • 举报
回复
谢谢大家的讨论,暂时结了。
adnin2010 2010-03-02
  • 打赏
  • 举报
回复
学习.
jwwyqs 2010-03-02
  • 打赏
  • 举报
回复
学习
!!!!!!
  • 打赏
  • 举报
回复
引用 153 楼 perfectaction 的回复:
我今天在想一个问题可以推翻之前的想法,并且可以解释count(*)的问题:

sql server 在读取索引数据层数据时,有可能是通过索引中间层读出符合条件的全部数据层的数据页pageid列表,然后通过这个列表去读数据页。而不是通过索引中间层找到其中一个数据页,通过该数据页中标记的nextpageid来读取下一个数据页。

预读机制并不是count(*)扫描全部索引层的最好解释,因为预读时,即使你只取一条数据,预读也可能是读全部的索引中间层及中部分数据页,因为sql server 总是尽可能的缓存数据表的数据以送减小io.这个可以通过
SQL codeDBCC DROPCLEANBUFFERS--无法清除tempdb的缓存数据.(所以不要使用tempdb库做测试)SELECT bd.*FROM sys.dm_os_buffer_descriptorsAS bdINNERJOIN
(SELECTobject_name(object_id)AS name
,index_id ,allocation_unit_idFROM sys.allocation_unitsAS auINNERJOIN sys.partitionsAS pON au.container_id= p.hobt_idAND (au.type=1OR au.type=3)UNIONALLSELECTobject_name(object_id)AS name
,index_id, allocation_unit_idFROM sys.allocation_unitsAS auINNERJOIN sys.partitionsAS pON au.container_id= p.partition_idAND au.type=2
)AS objON bd.allocation_unit_id= obj.allocation_unit_idWHERE database_id=db_id()and name='tbname'ORDERBY allocation_unit_id,page_type

通过执行此查询语句,可以发现执行select count(*) from tbname时,缓存全部的索引中间层和数据层,并未缓冲IAM页,所以不知count(*)时是否读取IAM页?另外,如查执行select * from tbname where id=1,可以发现预读机制可能会缓存全部索引中间层及差不多一半的数据层,但逻辑读只有3个page.



假设索引有3层。中间层和叶子层上的索引时分别单独自己串联起来的。中间层指向其所索引的第一个叶子层页面。
为了得到数据页面的页号信息,中间层的页面应该是最便捷的(长远来说,虽然目前你这个例子不是最好)。

另外,预读了不一定本次用。其他会话可能遇到高速缓存的预读数据。

对于sybase,我已经分析出来了索引的物理存储结构。请查看:http://blog.csdn.net/andkylee/archive/2010/03/01/5337013.aspx
Leshami 2010-03-02
  • 打赏
  • 举报
回复
学习

回复内容太短了!
nzperfect 2010-03-02
  • 打赏
  • 举报
回复

我今天在想一个问题可以推翻之前的想法,并且可以解释count(*)的问题:

sql server 在读取索引数据层数据时,有可能是通过索引中间层读出符合条件的全部数据层的数据页pageid列表,然后通过这个列表去读数据页。而不是通过索引中间层找到其中一个数据页,通过该数据页中标记的nextpageid来读取下一个数据页。

预读机制并不是count(*)扫描全部索引层的最好解释,因为预读时,即使你只取一条数据,预读也可能是读全部的索引中间层及中部分数据页,因为sql server 总是尽可能的缓存数据表的数据以送减小io.这个可以通过

DBCC DROPCLEANBUFFERS --无法清除tempdb的缓存数据.(所以不要使用tempdb库做测试)
SELECT bd.*
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id() and name='tbname'
ORDER BY allocation_unit_id,page_type


通过执行此查询语句,可以发现执行select count(*) from tbname时,缓存全部的索引中间层和数据层,并未缓冲IAM页,所以不知count(*)时是否读取IAM页?另外,如查执行select * from tbname where id=1,可以发现预读机制可能会缓存全部索引中间层及差不多一半的数据层,但逻辑读只有3个page.
wjlsmail 2010-03-02
  • 打赏
  • 举报
回复
是不是聚集函数都会引发类似的扫描?
黄_瓜 2010-03-01
  • 打赏
  • 举报
回复
引用 119 楼 xman_78tom 的回复:
预读应该是普遍机制,数据库引擎在执行查询前是不会预先知道所要查询的数据是否都被缓存,因此在执行 index scan 时都必须读取中间级索引页,以辅助预读。

猫兄的回答总是那么精辟,越来越崇拜你了
风云1978 2010-03-01
  • 打赏
  • 举报
回复
学习一下,大家都发表一下自己的意见
加载更多回复(148)
​ 博主介绍:✌在职Java研发工程师、专注于程序设计、源码分享、技术交流、专注于Java技术领域和毕业设计✌项目名称基于Web的酒店客房管理系统的设计与实现系统说明3.2.1  用户模块功能分析前台模块应主要包括用户登录模块、用户注册模块、查看客房信息模块、客房预定 模块、用户留言模块、充值模块和个人信息维护模块。用户登录模块:用户通过输入注册的的账号和密码,然后进行身份验证,匹配成功 后实现登录功能。用户注册模块:用户输入合法的账号和密码可以实现注册功能。 查看客房信息模块:用户可以通过首页查看客房的价格,图片,详情等信息,从而   可以选择想要预定的房间。 客房预定模块:用户选择自己想要预定的客房后,可以通过输入相关信息进行订房操作。用户留言模块:用户可以向管理员发送留言。 充值模块:用户可以通过添加银行卡再为自己进行充值操作。 个人信息维护模块:用户可以修改自己的姓名、密码、身份证号等信息,还可以查看自己的订单。3.2.2  管理员模块功能分析对于管理员而言,一个好的管理系统总是能让酒店的管理工作事半功倍[7]。管理员 能够通过这个系统对自己的酒店情况一目了然,应该包括客户留言模块、客房管理模 块、订房信息管理模块、入住信息管理模块、统计分析模块、酒店新闻管理模块、会 员信息管理模块、员工信息管理模块、系统用户管理模块、个人信息维护模块。客户留言模块:管理员可以查看并回复用户的留言。客房管理模块:管理员可以管理客房信息,可以添加新的客房,删除已经停用的客房信息,还可以修改现有的客房信息。订房信息管理模块:管理员可以处理用户的订房请求,为用户办理入住手续。入住信息管理模块:对于没有注册的线下客户,也可以办理入住手续。统计分析模块:可以对酒店所有的入住信息进行记录和总结分析。酒店新闻管理模块:管理员可以更新网站上的新闻公告,展示图片等信息。会员信息管理模块:管理员可以查看到所有的注册会员信息,可以对会员信息进行删除,修改,添加操作。员工信息管理模块:管理员可以查看到自己公司所有的员工信息,而且还可以对员工的相关信息进行管理。系统用户管理模块:管理员可以查看到所有的系统管理员信息并对管理员信息进行管理。个人信息维护模块:管理员可以更改自己的登录密码或者是姓名、性别、手机号等 相关个人信息。​编辑 环境需要1.运行环境:最好是java jdk 1.8,我们在这个平台上运行的。其他版本理论上也可以。2.IDE环境:IDEA,Eclipse,Myeclipse都可以。推荐IDEA;3.tomcat环境:Tomcat 7.x,8.x,9.x版本均可4.硬件环境:windows 7/8/10 1G内存以上;或者 Mac OS; 5.数据库:MySql 5.7版本;6.是否Maven项目:否;技术栈1. 后端:Spring+SpringMVC+Mybatis2. 前端:JSP+CSS+JavaScript+jQuery使用说明1. 使用Navicat或者其它工具,在mysql中创建对应名称的数据库,并导入项目的sql文件;2. 使用IDEA/Eclipse/MyEclipse导入项目,Eclipse/MyEclipse导入时,若为maven项目请选择maven;若为maven项目,导入成功后请执行maven clean;maven install命令,然后运行;3. 将项目中springmvc-servlet.xml配置文件中的数据库配置改为自己的配置;4. 运行项目,在浏览器中输入http://localhost:8080/ 登录运行截图​编辑​编辑​编辑​编辑​编辑​编辑​编辑​编辑​编辑​编辑​编辑​编辑​编辑​编辑​编辑 用户管理控制层:package com.houserss.controller;import javax.servlet.http.HttpSession;import org.apache.commons.lang3.StringUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.ResponseBody;import com.houserss.common.Const;import com.houserss.common.Const.Role;import com.houserss.common.ServerResponse;import com.houserss.pojo.User;import com.houserss.service.IUserService;import com.houserss.service.impl.UserServiceImpl;import com.houserss.util.MD5Util;import com.houserss.util.TimeUtils;import com.houserss.vo.DeleteHouseVo;import com.houserss.vo.PageInfoVo;/** * Created by admin */@Controller@RequestMapping(/user/)public class UserController if (ip != null && ip.length() > 0) String[] ips = ip.split(,);for (int i = 0; i  

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧