详解SQL Server表和索引存储结构

本文详细分析了SQL Server中表和索引结构存储的原理以及对于如何加快搜索速度和提高效率等方面做了详细的分析，以下是主要内容。

下图显示了表的存储组织，每张表有一个对应的对象ID，并且包含一个或多个分区，每个分区会有一个堆或者多个B树，堆或者B树的结构是预留的。每个堆或者是B树都有三个分配单元用来存放数据，分别是数据、LOB、行溢出，使用最多的分配单元是数据。如果有LOB数据或者是长度超过8000字节的记录，则可能有另外的LOB分配单元和行溢出分配单元。

小总结： 一个表可以有多个分区，但是每个分区（堆/B树）最多有三个分配单元，每个分配单元可以有很多页，对于每个分配单元内的数据页，根据表是否有索引，以及索引是聚集还是非聚集，组织方式有以下三种：

1. 堆

所谓堆（heap）,就是不含聚集索引的表。堆的 sys.partitions 中具有一行，对于堆使用的每个分区，都有 index_id = 0。只有一个分区，在系统表里，对于这个分区下面的每个分配单元都有一个连接指向Index Allocation Map页（IAM）,在IAM页里，描述了区的信息。

sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页。SQL Server  使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序，也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。

2. 具有非聚集索引的表

如果有一个表只有非聚集索引而没有聚集索引，对应的索引号是2--250。那么针对每个非聚集索引，都有一个对应的分区，在系统表进而，对于这个分区下面的每个分配单元，都有一个连接指向根页。数据页之间通过前后指针互相联系，是一个完整的树形结构。在树的底层，会有一个连接指向真正的数据，连接的形式是文件号+页号+行号，而真正的数据是以堆的形式存放的。如下图所示：

3. 具有聚集索引的表

表中的聚集索引，对应的索引号是1。它有一个对应的分区，该分区下的每个分配单元都有一个连接指向根页。对于聚集索引来说，叶子结点里存放的是真正的数据，而不是非聚集索引那样的连接。如下图所示：

非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：

基础表的数据行不按非聚集键的顺序排序和存储。

非聚集索引的叶层是由索引页而不是由数据页组成

案例分析： 我们来查看一个表的存储结构，我们在此使用的表是一个生产表，共有1亿多条记录，查看表的object_ID，如下图所示：

此表，我已经做了分区，查看其分区信息，可以使用下图所示的命令：

从上图可以看到，此表共有16个分区，对应不同的索引，基本上每个分区都有1千多万条记录。从此图中还可以看到堆或者B树的ID跟分区ID是一样的，如果希望进一步查看某一个索引的具体信息，可以使用下面的命令，如查看72057594067419136的信息。

从这个图当中，我们可以看到这个分区只有一个分配单元，IN_ROW_DATA表明此分配单元只用来存放具体数据，共5353页，已使用5346页，数据占用5320页。

如果希望查看根页的位置，可以使用下面的命令：

但需要注意，这里显示的根页的位置是0xEC0100001100，由于存储的关系，用倒序的方式对它进行解析，也就是0x0011000001EC,最前面的两个字节表明是所在的文件组编号，后面的4个字节是页的编号，即(1,0x01CE) ,换成十进制（1，492），然后可以利用我们上一节所说的DBCC PAGE命令查看页的信息，如下图所示：

从中可以看到具体的数据，此界面的返回结果会因表上的聚集索引、非聚集索引而不同。如果查看一个表使用的总页数和区数，也可以使用命令：DBCC SHOWCONFIG，如下图所示：

在同样表结构的情况下，建立聚集索引不会增加表格的大小，但是建立非聚集索引反而会增加不少空间，在性能方面，SQL Server产品组做过测试，在select、update、delete操作下，聚集索引性能较高，在插入记录时，聚集索引和非聚集索引性能相同，没有出现聚集索引影响插入速度的现象，但在生产环境中，还是要谨慎行事。

赞 (0)

相关推荐

  • 详解SQL Server表和索引存储结构

    详解SQL Server表和索引存储结构

    本文详细分析了SQL Server中表和索引结构存储的原理以及对于如何加快搜索速度和提高效率等方面做了详细的分析,以下是主要内容. 下图显示了表的存储组织,每张表有一个对应的对象ID,并且包含一个或多个分区,每个分区会有一个堆或者多个B树,堆或者B树的结构是预留的.每个堆或者是B树都有三个分配单元用来存放数据,分别是数据.LOB.行溢出,使用最多的分配单元是数据.如果有LOB数据或者是长度超过8000字节的记录,则可能有另外的LOB分配单元和行溢出分配单元. 小总结: 一个表可以有多个分区,但是

  • 详解SQL Server数据库状态和文件状态

    数据库状态 (database states) 查询数据库的当前状态 : 1.查询所有数据库的状态 ,通过sys.databases目录视图的state_desc列 user master go select state_desc ,[name] from sys.databases go 2.查询指定数据库的状态,通过DATABASEPROPERTYEX函数的Status属性 select DATABASEPROPERTYEX('demoData','status') go 状态: ONLIN

  • 详解SQL Server的聚焦过滤索引

    详解SQL Server的聚焦过滤索引

    前言 这一节我们还是继续讲讲索引知识,前面我们聚集索引.非聚集索引以及覆盖索引等,在这其中还有一个过滤索引,通过索引过滤我们也能提高查询性能,简短的内容,深入的理解. 过滤索引,在查询条件上创建非聚集索引(1) 过滤索引是SQL 2008的新特性,被应用在表中的部分行,所以利用过滤索引能够提高查询,相对于全表扫描它能减少索引维护和索引存储的代价.当我们在索引上应用WHERE条件时就是过滤索引.也就是满足如下格式: CREATE NONCLUSTERED INDEX <index name> O

  • 图文详解SQL Server 2008R2使用教程

    图文详解SQL Server 2008R2使用教程

    本文为大家分享了SQL Server 2008R2简单使用教程,供大家参考,具体内容如下 1 首先找到开始菜单中相关内容:如下图:安装的组件不同可能有所不同:我的电脑中包括如下项: 商业智能:管理控制台:导入和导出数据:分析服务:集成服务:配置工具:文档和教程:性能工具: 因为偶装的组件多: 2 进入管理控制台 首先是登录:服务器类型选择 数据库引擎:此处先用Windows身份验证登录: 3 进入管理控制台 界面如下:看到 数据库 下有四个子项,系统数据库.数据库快照.ReportServer.

  • 详解SQL Server中的数据类型

    详解SQL Server中的数据类型

    前言 前面几篇文章我们讲解了索引有关知识,这一节我们再继续我们下面内容讲解,简短的内容,深入的理解. 数据类型 SQL Server支持两种字符数据类型,一种是常规,另外一种则是Unicode.常规数据类型包括CHAR和VARCHAR,Unicode数据类型包括NCAHR和NVARCHAR.常规字符的每个字符使用1个字节存储,而Unicode数据的每个字符要求2个字节.常规字符列限制为仅仅只针对于英语,而Unicode则是针对于多种语言.两种字符数据类型的文本表示方式也不相同,在表示常规字符文本

  • 详解SQL Server 2016快照代理过程

    本文我们通过SQL Server 2016一个实例数据表,给大家详细分析了快照代理过程遇到的问题和解决办法,并对快照生成过程做了详细说明,以下是全部内容: 概述 快照代理准备已发布表的架构和初始数据文件以及其他对象.存储快照文件并记录分发数据库中的同步信息. 快照代理在分发服务器上运行:SQLServer2016版本对快照代理做了一些比较好的优化,接下来详细了解一下快照的执行过程. 一.快照代理文件 在执行快照作业是会在指定的快照目录生成4种类型的文件. BCP文件:发布对象的数据文件. IDX

  • 详解SQL Server 2008工具SQL Server Profiler

    详解SQL Server 2008工具SQL Server Profiler

    一.SQL Profiler工具简介 SQL Profiler是一个图形界面和一组系统存储过程,其作用如下: 1.图形化监视SQL Server查询: 2.在后台收集查询信息: 3.分析性能: 4.诊断像死锁之类的问题: 5.调试T-SQL语句: 6.模拟重放SQL Server活动: 也可以使用SQL Profiler捕捉在SQL Server实例上执行的活动.这样的活动被称为Profiler跟踪. 1.Profiler跟踪 从开始=>所有程序=>Microsoft SQL Server 2

  • 详解SQL Server的简单查询语句

    详解SQL Server的简单查询语句

    前言 对于一些原理性文章园中已有大量的文章尤其是关于索引这一块,我也是花费大量时间去学习,对于了解索引原理对于后续理解查询计划和性能调优有很大的帮助,而我们只是一些内容进行概括和总结,这一节我们开始正式步入学习SQL中简单的查询语句,简短的内容,深入的理解. 简单查询语句 所有复杂的语句都是由简单的语句组成基本都是由SELECT.FROM.WHERE.GROUP BY.HAVING.ORDER BY等组成,当然还包括一些谓词等等.比如当我们要查询某表中所有数据时我们会像如下进行. SELECT

  • 详解SQL Server数据库架构和对象、定义数据完整性

    详解SQL Server数据库架构和对象、定义数据完整性

    前言 本节我们继续SQL之旅,本节我们如题来讲讲一些基本知识以及需要注意的地方,若有不妥之处,还望指出,简短的内容,深入的理解. 数据库架构和对象 数据库包含架构,而架构又包含对象,架构可以看做是表.视图.存储过程等对象的容器.架构是一个命名空间,它被用做对象名称的前缀,比如在Cnblogs的架构中有一个名称为Blogs的表,此时我们用架构式限定式名称(即两部分式对象名称)所以Blogs表示为Cnblogs.Blogs.如果我们引用对象时省略了架构名称,SQL Server将会检查对象是否存在用

  • 图文详解SQL Server 2008 R2安装配置方法

    图文详解SQL Server 2008 R2安装配置方法

    安装sql server2008 R2的方法,轻松简单易操作,按照操作一步一步来. 工具: sql server 2008 R2安装包一个(从网上下载) 方法/步骤: 安装包解压 解压后,打开setup.exe文件,选择安装,显示如图: 选择全新安装或向现有安装添加功能 点确定 输入 企业版序列号:R88PF-GMCFT-KM2KR-4R7GB-43K4B 点我接受许可条款,点下一步 点安装,如果操作系统没有安装.net framet3.5 sp1 ,将会自动安装. 必备环境全部通过后,点下一步

随机推荐