sql语句大全之sql server 索引阐述系列二索引存储

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之sql server 索引阐述系列二索引存储

一.概述、

　　"流光容易把人抛,红了樱桃,绿了芭蕉“ 转眼又年中了，感叹生命的有限，知识的无限。在后续讨论索引之前，先来了解下索引和表数据的内部结构，这一节将介绍页的存储，页分配单元类型，区的存储，最后简要介绍下系统页存储类型，页中的数据结构。

1.1 页存储

　　页是 sql server存储数据的基本单位，大小为8kb，它存储的类型包括表，索引数据，分配位图，可用空间信息等，页也是可以读写的最小I/0单位。也就是如只需访问一行数据，也会把整个页加载到内存中。一页大小是8192个字节，由三块组成分为页头，数据行，行偏移也叫页尾行指针。

　　页头：有96个字节，包含的信息如PageID（文件号及页面编号），NextPage下一个页面的文件号及页面编号等。

　　数据行：单个数据行最大为8060字节。

　　末尾行偏移： 36字节的数组，数组中的每一个值指向页中数据。当插入一行数据时，偏移量从右下角开始存储，插入第二行时偏移量为2存放于1的左边，如下图所示：

　　一个页存储有三种分配单元类型： in_row_data （行内数据）， row_overflow_data（行溢出数据）， lob_data LOB（大型数据）。

分配单元类型	描述
in_row_data	存放固定长度的列，以及可变长度的列。一行字节<= 8060字节的限制
row_overflow_data	存放<=8000字节的varchar,nvarchar, varbinary类型数据。一行字节>= 8060
lob_data	存放大型对象数据类型值 >=8000节字的varchar(max), nvarchar(max), varbinary(max), 以及xml或clr udt

1.2 区

　　区是由8个物理上连续的页组成的单元，用来有效管理页，如是区内的8个页属于同一个表，则这种区叫统一区，如果区内8个页分别属于至少两个不同的表，则这种区叫混合区。当表或索引需要更多空间时，sqlserver会为对象分配一个新区，但对象不足64kb,通常只分配一个页，当drop或truncate表时将释放整个区。

1.3 存储的关系

　　每个数据表对应有一个objectID标识符, 表中有多个索引为 sys.indexes, 表和索引存储在多个分区中 sys.partitions，但至少有一个分区。每个分区下面有8个物理连续页，页中使用最频繁的文件类型是Data数据。

　　下图展示表和索引存储结构以及对应的元数据查询。分配单元可通过SELECT * FROM sys.system_internals_allocation_units视图查看

二. 索引与表存储关系演示

　　2.1 首先建一个RowText表

CREATE TABLE [dbo].[RowText](
    [a] [varchar](3000) NULL
) ON [PRIMARY]

　　2.2 根据表名，查到表的标识符objectID： 5575058。这里显示了表的相关信息如创建时间，修改时间。

select * from sys.objects where name='RowText'

2.3 根据标识符objectID，找到表上的多个分区，这里只有一个区。找到标识hobt_id(是该区里堆或B树结构的标识)： 72057594038976512

select * from sys.partitions where object_id=5575058

　2.4 再根据hobt_id： 72057594038976512，找到页分配的单元，这里现只显示了in_row_data 行内数据，信息包括了行内数据使用的总页数，已使用页数，数据页数

select * from sys.allocation_units where container_id=72057594038976512

三. 页分配单元演示

　　3.1 上面讲到页分配单元有三种类型，通过下面的脚本可以来查看分析。

SELECT CONVERT(VARCHAR(10),OBJECT_NAME(i.object_id)) AS table_name,
     i.Name AS index_name,i.index_id,i.type_desc AS index_type,
     partition_id,partition_number,p.rows,allocation_unit_id,
     a.type_desc AS page_type_desc,
     a.total_pages,a.used_pages,a.data_pages
    FROM sys.indexes i JOIN sys.partitions p 
ON i.object_id=p.object_id and i.index_id=p.index_id
JOIN sys.allocation_units a ON p.partition_id=a.container_id
WHERE i.object_id=object_id('RowText')

　　3.2 查看页存储的行内数据

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RowText]') AND type in (N'U'))
DROP TABLE [dbo].[RowText]

go
CREATE TABLE [dbo].[RowText](
    [a] [varchar](3000) NULL
) ON [PRIMARY]

insert into RowText
select REPLICATE('a',3000)
insert into RowText
select REPLICATE('a',3000)
insert into RowText
select REPLICATE('a',3000)

　　当前一行数据3000字节,小于一行8000字节，所以存放为IN_ROW_DATA类型，新增三行共9000字节，大于了一页8060字节，所以产生了data_pages:2页。通过3.1脚本查看

　　3.2 查看页存储的行溢出数据

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RowText]') AND type in (N'U'))
DROP TABLE [dbo].[RowText]
go
create TABLE [dbo].[RowText](
    [a] [varchar](3000) NULL,
    [b] [varchar](6000) NULL
) ON [PRIMARY]

insert into RowText
select REPLICATE('a',3000),REPLICATE('b',6000)

　　当前一行数据为9000字节，大于一行8000字节,所以分配了ROW_OVERFLOW_DATA类型，通过3.1脚本查看

　　3.3 查看页存储的LOB大型数据

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RowText]') AND type in (N'U'))
DROP TABLE [dbo].[RowText]
go
CREATE TABLE [dbo].[RowText](
    [a] [varchar](3000) NULL,
    [b] [varchar](6000) NULL,
    [c] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

insert into RowText
select REPLICATE('a',3000),REPLICATE('b',6000),REPLICATE('c',12000)

　　当前表包含了text类型，所以分配了LOB_DATA大型数据，通过3.1脚本查看

最后简单总结下：在设计表字段时，一定要与业务相符合，尽量避免建大型字段，产生行溢出和LOB大型数据，从而造成页拆分，查询将需要跨页。原理是当一行超过8060字节时，这类型的值将被移动到一个称为行溢出分配单元页中，在原始页上保留一个24字节指针，指向行外数据，这样就完成了行跨页。存放大型对象数据类型值时，在原始页上保留一个16字节指针，指向该大型对象值。

　四页单元文件类型

　　我们知道页文件主要是用来存储表和索引的数据，其实还包括其它的存储，在sqlserver里页文件存储的类型也分9种，如下图所示

页类型	内容
Global Allocation Map (GAM)	它用来标识相应的区是否已经被分区,能标识64000个区，也就是4G空间，如超过4G则分配另一个GAM页 bit=0 表示当前区已被数据使用
.Shared Global Allocation Map(SGAM)	表示该区是否是混合区 bit=1 是混合区且至少有一个数据页被分配
Page Free Space	存储该文件里所有页分配情况，和可用空间信息
Index Allocation Map	表或索引使用该区的信息
Bulk Changed Map	最后一条 backup log 语句之后大容量操作所修改区的信息
Differential Changed Map	最后一条 backup database 语句之更改区的信息
Data	用来存储数据
Row Overflow Page	行溢出的数据
LOB	存放大型对象数据:text,nvarchar(max),varchar(max)…

　五.数据页的结构

　　在sql server里有这么多页文件类型，哪个页里是怎么存储的，存储了什么内容呢？下面来查看一个数据页存储的信息

　　下面通过partition_id的标识符：72057594038976512 找到first_page 字段第一页

select * from sys.system_internals_allocation_units where container_id=72057594038976512

　　由于存储关系，页码是以字节16进制显示0x720000000100，这里需要倒序后就是0x010000000072，

　　0x表示：文件组号

　　0072从16进制转成10进制为第114页（后四字节代表页的编号）

　　通过Dbcc page可以查看页的具体内容，使用之前dbcc page, 先打开dbcc traceon (3604) 告诉sql server将结果返回给客户端，dbcc Page 四个参数如下:

dbid | dbname 该页的数据库ID或数据
Filenum 页面文件号
Pagenum 页面号
Printopt =1 对每条记录打印缓冲报头，页面报头，输出行偏移表

　　具体使用如下：

　　dbcc traceon (3604)

　　dbcc page('test',1,114,1)

　　数据页结构输出分为四个主要阶段：buffer, page header, data, offset table

　　Buffer: 用来标识该数据页在内存中的位置。

　　page header: 页头信息

　　data：存储的数据

　　offset table: 偏移量

　　感兴趣的朋友，可以再深入研究。这里粗略讲下。

栏目列表

首页 > 数据库 > sql数据库 >