100字范文 > sql server 索引阐述系列二索引存储结构

sql server 索引阐述系列二索引存储结构

时间：2021-10-27 16:51:41

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

1.1 页存储

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

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

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

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

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

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, number,p.rows,allocation_unit_id,a.type_desc AS page_type_desc,a.total_pages,a.used_pages,a.data_pagesFROM sys.indexes i JOIN sys.partitions p ON i.object_id=p.object_id and i.index_id=p.index_idJOIN sys. units a ON p.partition_id=a.container_idWHERE 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]goCREATE TABLE [dbo].[RowText]([a] [varchar](3000) NULL) ON [PRIMARY]

insert into RowTextselect REPLICATE('a',3000)insert into RowTextselect REPLICATE('a',3000)insert into RowTextselect 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]gocreate TABLE [dbo].[RowText]([a] [varchar](3000) NULL,[b] [varchar](6000) NULL) ON [PRIMARY]

insert into RowTextselect REPLICATE('a',3000),REPLICATE('b',6000)

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

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

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

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

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