SQL Server如何在变长列上存储索引

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

这篇文章我想谈下SQL Server如何在变长列上存储索引。首先我们创建一个包含变长列的表,在上面定义主键,即在上面定义了聚集索引,然后往里面插入80000条记录:

复制代码
 1 -- Create a new table
 2 CREATE TABLE Customers
 3 (
 4     CustomerName VARCHAR(255) NOT NULL PRIMARY KEY,
 5     Filler CHAR(138) NOT NULL
 6 )
 7 GO
 8 
 9 -- Insert 80.000 records
10 DECLARE @i INT = 1
11 WHILE (@i <= 80000)
12 BEGIN
13     INSERT INTO Customers VALUES
14     (
15         'CustomerName' + CAST(@i AS VARCHAR),
16         'Filler' + CAST(@i AS VARCHAR)
17     )
18     
19     SET @i += 1
20 END
21 GO
复制代码

从代码里我们可以看到,我在VARCHAR(255)列上建立了主键约束,SQL Server会强制这列为唯一聚集索引。接下来我们通过DMV sys.dm_db_index_physical_stats来获取聚集索引的相关物理信息: 

复制代码
 1 -- Retrieve physical information about the clustered index
 2 SELECT * FROM sys.dm_db_index_physical_stats
 3 (
 4     DB_ID('ALLOCATIONDB'),
 5     OBJECT_ID('Customers'),
 6     NULL,
 7     NULL,
 8     'DETAILED'
 9 )
10 GO
复制代码

从输出结果可以看出,在索引页里,min_record_size_in_bytes列的值是7,max_record_size_in_bytes列的值是28。我们据此可以得出结论:在索引记录内部,聚集键是以变长列保存的。我们建立一个帮助表来存储DBCC IND的输出信息来做进一步分析。

 

复制代码
 1 -- Create a helper table
 2 CREATE TABLE HelperTable
 3 (
 4   PageFID TINYINT, 
 5   PagePID INT,   
 6   IAMFID TINYINT, 
 7   IAMPID INT, 
 8   ObjectID INT,
 9   IndexID TINYINT,
10   PartitionNumber TINYINT,
11   PartitionID BIGINT,
12   iam_chain_type VARCHAR(30),    
13   PageType TINYINT, 
14   IndexLevel TINYINT,
15   NextPageFID TINYINT,
16   NextPagePID INT,
17   PrevPageFID INT,
18   PrevPagePID INT, 
19   PRIMARY KEY (PageFID, PagePID)
20 )
21 GO
22 
23 -- Write everything in a table for further analysis
24 INSERT INTO HelperTable EXEC('DBCC IND(ALLOCATIONDB, Customers, 1)')
25 GO
26 
27 -- Retrieve the root index page (1 page)
28 SELECT * FROM HelperTable
29 WHERE IndexLevel = 2
30 GO
复制代码

我这里的根页是15058,我们使用DBCC PAGE命令查看下这个根页(记得先执行 DBCC TRACEON(3604))。

复制代码
1 DBCC TRACEON (3604)
2 GO
3 --Dump out the root index page
4 DBCC PAGE(ALLOCATIONDB, 1, 15058, 1)
5 GO
复制代码

即如下所示的数字:

00000000:   269d3b00 00010001 001b0043 7573746f †&.;........Custo         
00000010:   6d65724e 616d6531 333533†††††††††††††merName1353   

我们来分析下这些16进制值:
26 95020000 0100 0100 1b00 43757374 6f6d6572 4e616d65 31333533

  • 26 第1个字节代表状态位
  • 95020000 这4个字节代表索引记录指向的子页id(child-page-id)
  • 0100 这2个字节代表索引记录指向的子文件id(child-file-id)
  • 0100 这2个字节代表变长列数
  • 1b00 这2个字节代表每个变长列结束为止的偏移量。每个变长列需要2字节。这个和在数据页里存储变长列一致。这里我们有1个变长列,因此SQL Server需要1个 2 byte的偏移量——27 byte的偏移量。这就是说下一个字节一直到27 byte的偏移量都是我们变长列(聚集键)的组成部分。
  • 43757374 6f6d6572 4e616d65 31333533 聚集键的16进制值,即CustomerName列。

从上面的解释,我们可以看出SQL Server存储变长索引列格式和数据页里存储变长列格式是一样的。但你要知道有一点额外开销,因为你需要额外2 bytes 来存储变长列个数,对于每个变长列在变长列偏移数组里需要2 bytes。在设计索引和计算一个索引页存放多少索引记录时,要留意这些存储开销。



本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/4576689.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
23天前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
29天前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
28天前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
29天前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
29天前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤
|
1月前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引:详细步骤与技巧
在数据库管理和优化中,确认SQL查询是否有效利用了索引是提升性能的关键步骤
|
1月前
|
索引
SQL_创建和管理索引
SQL_创建和管理索引
25 0
|
2月前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
100 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
14天前
|
存储 数据挖掘 数据库
数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
一个运行在存储上的SQLServer数据库,有1000多个文件,大小几十TB。数据库每10天生成一个NDF文件,每个NDF几百GB大小。数据库包含两个LDF文件。 存储损坏,数据库不可用。管理员试图恢复数据库,发现有数个ndf文件大小变为0KB。 虽然NDF文件大小变为0KB,但是NDF文件在磁盘上还可能存在。可以尝试通过扫描&拼接数据库碎片来恢复NDF文件,然后修复数据库。
|
2月前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
32 4