SQL Server 索引基础知识 1 --- 记录数据的基本格式

最新推荐文章于 2022-12-21 17:38:47 发布

背包客啊

最新推荐文章于 2022-12-21 17:38:47 发布

阅读量221

点赞数

本文链接：https://blog.csdn.net/jfdtygv/article/details/84092050

版权

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

由于需要给同事培训数据库的索引知识，就收集整理了这个系列的博客。发表在这里，也是对索引知识的一个总结回顾吧。通过总结，我发现自己以前很多很模糊的概念都清晰了很多。

不论是缓存的数据信息，还是物理保存的信息，他们的基本单位都是数据页。所以理解数据页是最最基础的知识点，本篇博客就介绍跟索引有关的数据页的一些基础知识。

数据页的基础知识

SQL Server 中数据存储的基本单位是页（Page）。数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 每次读取或写入数据的最少数据单位是数据页。

注意：日志文件不是用这种方式存储的，而是一系列日志记录。

数据库被分成逻辑页面（每个页面8KB），并且在每个文件中，所有页面都被连续地从0到x编号，其中x是由文件的大小决定的。我们可以通过指定一个数据库ID、一个文件ID、一个页码来引用任何一个数据页。当我们使用ALTER DATABASE命令来扩大一个文件时，新的空间会被加到文件的末尾。也就是说，我们所扩大文件的新空间第一个数据页的页码是x+1。当我们使用DBCC SHRINKDATABASE或DBCC SHRINKFILE命令来收缩一个数据库时，将会从数据库中页码最高的页面（文件末尾）开始移除页面，并向页码较低的页面移动。这保证了一个文件中的页码总是连续的。

在 SQL Server 中，页的大小为 8 KB。这意味着 SQL Server 数据库中每 MB 有 128 页。依次类推。根据数据库的文件大小，我们可以算出数据库有多少数据页。

SQL Server 2005 有以下几种页类型：

页类型	内容
Data	当 text in row 设置为 ON 时，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所有数据的数据行。
Index	索引条目。
Text/Image	大型对象数据类型： text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据。数据行超过 8 KB 时为可变长度数据类型列： varchar、nvarchar、varbinary 和 sql_variant
Global Allocation Map、Shared Global Allocation Map	有关区是否分配的信息。
Page Free Space	有关页分配和页的可用空间的信息。
Index Allocation Map	有关每个分配单元中表或索引所使用的区的信息。
Bulk Changed Map	有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。
Differential Changed Map	有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。

数据页（Data 类型页）的结构示意图：

每页的开头是 96 字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。

在数据页上，数据行紧接着标头按顺序放置。页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。

SQL Server 页的数据格式

有关数据页的更多知识，可以通过下面这篇文章获得更详细的了解：

估计在堆中存储数据所需的空间量
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

另外也可以看我收集的资料：怎样查看表的数据页的结构
http://blog.joycode.com/ghj/articles/113108.aspx

对大型行的支持

在 SQL Server 2005 中，行不能跨页，但是行的部分可以移出行所在的页，因此行实际可能非常大。
（比如：一行多列时，这一行的部分列在数据页A，部分列在数据页B）
页的单个行中的最大数据量和开销是 8,060 字节 (8 KB)。但是，这不包括用 Text/Image 页类型存储的数据。
在 SQL Server 2005 中，包含 varchar、nvarchar、varbinary 或 sql_variant 列的表不受此限制的约束。
当表中的所有固定列和可变列的行的总大小超过限制的 8,060 字节时，SQL Server 将从最大长度的列开始动态将一个或多个可变长度列移动到 ROW_OVERFLOW_DATA 分配单元中的页。
每当插入或更新操作将行的总大小增大到超过限制的 8,060 字节时，将会执行此操作。
将列移动到 ROW_OVERFLOW_DATA 分配单元中的页后，将在 IN_ROW_DATA 分配单元中的原始页上维护 24 字节的指针。
如果后续操作减小了行的大小，SQL Server 会动态将列移回到原始数据页。

SQL Server 的数据页缓存

SQL Server 数据库的主要用途是存储和检索数据，因此密集型磁盘 I/O 是数据库引擎的一大特点。此外，完成磁盘 I/O 操作要消耗许多资源并且耗时较长，所以 SQL Server 侧重于提高 I/O 效率。缓冲区管理是实现高效 I/O 操作的关键环节。SQL Server 2005 的缓冲区管理组件由下列两种机制组成：用于访问及更新数据库页的缓冲区管理器和用于减少数据库文件 I/O 的缓冲区高速缓存（又称为“缓冲池”）。

缓冲区管理的工作原理
一个缓冲区就是一个 8KB 大小的内存页，其大小与一个数据页或索引页相当。因此，缓冲区高速缓存被划分为多个 8KB 页。缓冲区管理器负责将数据页或索引页从数据库磁盘文件读入缓冲区高速缓存中，并将修改后的页写回磁盘。页一直保留在缓冲区高速缓存中，直到已有一段时间未对其进行引用或者缓冲区管理器需要缓冲区读取更多数据。数据只有在被修改后才重新写入磁盘。在将缓冲区高速缓存中的数据写回磁盘之前，可对其进行多次修改。

实验

下面做一个简单的实验来看你是否已经掌握的上面的知识点：

准备测试环境

在一个SQL 2005数据库中，执行下面脚本。

简单来说，就是创建了2个表，注意这两个表，一个是存储的 nchar(2019) 的字段，一个是存储的 nchar(2020) 的字段。我们将来看这两个表在同样数据下，存储所花费的空间大小。由于缓存和物理存储的基本单位都是数据页，这个表物理存储的大小跟全部缓存的大小会是一样的。

然后我们每个表填充20个数据。

-- 创建2个测试表CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL) CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL) go-- 填充数据declare @i intset @i = 0while(@i < 20)begin    insert Table_2019(Data) values('')    insert Table_2020(Data) values('')    select @i = @i + 1endgo

这里我们用 nchar 数据类型，是因为：
当指定了 NOT NULL 子句时，nchar 数据类型是一种长度固定的数据类型。
如果插入值的长度比 nchar NOT NULL 列的长度小，将在值的右边填补空格直到达到列的长度。
例如，如果某列定义为 nchar(10)，而要存储的数据是“music”，则 SQL Server 将数据存储为“music_____”，这里“_”表示空格。
http://technet.microsoft.com/zh-cn/library/ms175055.aspx
这样我们填充测试数据的脚本就非常简单。
而且计算数据行所占的空间也非常简单。
另外，我们建立的这两个表都没有索引，所以他们都是堆，有关估计在堆中存储数据所需的空间量请参看以下文章：
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

完成准备工作后，我们来查看这两个所占空间的大小。在 SQL Server Management Studio 中，我们选择测试数据库， 然后在右键菜单中依次选择 
Reports --> Standard Reports --> Disk Usage by Top Tables 或者Disk Usage by Table 就可以看到下面统计数据。


Disk Usage by Top Tables: [ghj_Demo]
on GHJ1976-PC/SQLEXPRESS at 2007/12/27 9:21:33


This report provides detailed data on the utilization of disk space by top 1000 tables within the Database.

Table Name	# Records	Reserved (KB)	Data (KB)	Indexes (KB)	Unused (KB)
dbo.Table_2020	20	200	160	8	32
dbo.Table_2019	20	136	80	8	48

这两个表同样20条记录。Table_2020 表数据占了 160kb ，即 20 个数据页。Table_2019 表数据占了 80 kb，即 10 个数据页。
为何会这样呢？
Table_2020 表的1个数据页只能放下1个数据行。
Table_2019 表的1个数据页只能放下2个数据行。
这两个表的字段长度只差2个字节，但是物理存储却是一倍的差距。

参考资料：

SQL Server数据库中存储引擎深入探讨
http://tech.ccidnet.com/art/1106/20070320/1040665_3.html

《Microsoft SQL Server 2005技术内幕：存储引擎》这本书电子版的一部分
http://book.csdn.net/bookfiles/504/10050417350.shtml

MSDN 中关于“页和区”的描述
http://technet.microsoft.com/zh-cn/library/ms190969.aspx

聚集索引结构
http://technet.microsoft.com/zh-cn/library/ms177443.aspx

行溢出数据超过 8 KB
http://technet.microsoft.com/zh-cn/library/ms186981.aspx

缓冲区管理
http://technet.microsoft.com/zh-cn/library/aa337525.aspx

估计堆的大小
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

nchar 和 nvarchar (Transact-SQL)
http://technet.microsoft.com/zh-cn/library/ms186939.aspx

Teched 2007 上吴家震主讲的"微软SQL服务器Always-On Tech-nologies: 高级索引策略" 录像下载地址:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032364059&Culture=zh-CN
注意, 这个页面标示的是 "SharePoint 2007 网站性能调优" ,但是其实是高级索引策略,微软弄错文件了,害得我一个个下下来看,哪个是需要的录像.

给我老师的人工智能教程打call！http://blog.csdn.net/jiangjunshow

你好！这是你第一次使用 **Markdown编辑器** 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。

新的改变

我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：

全新的界面设计 ，将会带来全新的写作体验；
在创作中心设置你喜爱的代码高亮样式，Markdown 将代码片显示选择的高亮样式 进行展示；
增加了 图片拖拽 功能，你可以将本地的图片直接拖拽到编辑区域直接展示；
全新的 KaTeX数学公式 语法；
增加了支持甘特图的mermaid语法¹ 功能；
增加了 多屏幕编辑 Markdown文章功能；
增加了 焦点写作模式、预览模式、简洁写作模式、左右区域同步滚轮设置 等功能，功能按钮位于编辑区域与预览区域中间；
增加了 检查列表 功能。

功能快捷键

撤销：Ctrl/Command + Z
重做：Ctrl/Command + Y
加粗：Ctrl/Command + B
斜体：Ctrl/Command + I
标题：Ctrl/Command + Shift + H
无序列表：Ctrl/Command + Shift + U
有序列表：Ctrl/Command + Shift + O
检查列表：Ctrl/Command + Shift + C
插入代码：Ctrl/Command + Shift + K
插入链接：Ctrl/Command + Shift + L
插入图片：Ctrl/Command + Shift + G

合理的创建标题，有助于目录的生成

直接输入1次#，并按下space后，将生成1级标题。
输入2次#，并按下space后，将生成2级标题。
以此类推，我们支持6级标题。有助于使用TOC语法后生成一个完美的目录。

如何改变文本的样式

强调文本 强调文本

加粗文本 加粗文本

标记文本

~~删除文本~~

引用文本

H₂O is是液体。

2¹⁰ 运算结果是 1024.

插入链接与图片

链接: link.

图片:

带尺寸的图片:

当然，我们为了让用户更加便捷，我们增加了图片拖拽功能。

如何插入一段漂亮的代码片

去博客设置页面，选择一款你喜欢的代码片高亮样式，下面展示同样高亮的 代码片.

// An highlighted block var foo = 'bar';

生成一个适合你的列表

项目
- 项目
  - 项目

项目1
项目2
项目3

计划任务
完成任务

创建一个表格

一个简单的表格是这么创建的：

项目	Value
电脑	$1600
手机	$12
导管	$1

设定内容居中、居左、居右

使用:---------:居中
使用:----------居左
使用----------:居右

第一列	第二列	第三列
第一列文本居中	第二列文本居右	第三列文本居左

SmartyPants

SmartyPants将ASCII标点字符转换为“智能”印刷标点HTML实体。例如：

TYPE	ASCII	HTML
Single backticks	`'Isn't this fun?'`	‘Isn’t this fun?’
Quotes	`"Isn't this fun?"`	“Isn’t this fun?”
Dashes	`-- is en-dash, --- is em-dash`	– is en-dash, — is em-dash

创建一个自定义列表

Markdown

Text-to- HTML conversion tool

Authors

John

Luke

如何创建一个注脚

一个具有注脚的文本。²

注释也是必不可少的

Markdown将文本转换为 HTML。

KaTeX数学公式

您可以使用渲染LaTeX数学表达式 KaTeX:

Gamma公式展示 $\Gamma(n) = (n-1)!\quad\forall n\in\mathbb N$ 是通过欧拉积分

$\Gamma(z) = \int_0^\infty t^{z-1}e^{-t}dt\,.$

你可以找到更多关于的信息 LaTeX 数学表达式here.

新的甘特图功能，丰富你的文章

gantt
        dateFormat  YYYY-MM-DD
        title Adding GANTT diagram functionality to mermaid
        section 现有任务
        已完成               :done,    des1, 2014-01-06,2014-01-08
        进行中               :active,  des2, 2014-01-09, 3d
        计划一               :         des3, after des2, 5d
        计划二               :         des4, after des3, 5d