主页 / dba / 问题 / 190585
Accepted
Greg
Asked: 2017-11-10 12:46:54 +0800 CST

为什么 XML 比 VARCHAR(MAX) 占用更多的存储空间？

  • 772

我们有将 XML 数据存储为 varchar(MAX) 的大型表。数据仅供参考/历史用途，未经查询。根据我所阅读的内容，存储为 XML 数据类型而不是 VARCHAR(MAX) 应该会节省空间，但我的测试显示并非如此。请参见下文，其中 t1_XML 的大小小于 t1_NVARCHARMAX，但大于 t1_VARCHARMAX。

set nocount on;

drop table t1_XML;
drop table t1_VARCHARMAX;
drop table t1_NVARCHARMAX;

create table t1_XML(col1 int identity primary key, col2 XML);
create table t1_VARCHARMAX(col1 int identity primary key, col2 varchar(max));
create table t1_NVARCHARMAX(col1 int identity primary key, col2 nvarchar(max));

go

declare @xml XML = '<root><element1>test</element1><element2>test</element2><element3>test</element3><element4>test</element4><element5>test</element5></root>'
    , @x int = 1;

while @x <= 10000
begin
    begin tran

    insert into dbo.t1_XML (col2) values (@xml);
    insert into dbo.t1_VARCHARMAX (col2) values (cast(@xml as varchar(max)));
    insert into dbo.t1_NVARCHARMAX (col2) values (cast(@xml as varchar(max)));

    commit tran

    set @x += 1;
end

exec sp_spaceused 'dbo.t1_XML';
exec sp_spaceused 'dbo.t1_VARCHARMAX';
exec sp_spaceused 'dbo.t1_NVARCHARMAX';

在此处输入图像描述

sql-server sql-server-2012

3 个回答

  1. Best Answer

    关于数据类型有两件事需要了解XML，它们共同解释了您所遇到的情况：

    1. 如@EvanCarroll 的回答中所述，XML数据类型已优化。意义，而不是重复元素和属性名称（它们通常会重复很多次，这也是为什么这么多人，有时是理所当然地抱怨 XML 文档如此庞大的原因），而是创建了一个字典/查找列表来给定一个数字 ID，将每个唯一名称存储一次，该 ID 用于填充文档的结构。这就是为什么XML数据类型通常是存储 XML 文档的更好方法的原因。
    2. 此外，该XML数据类型使用 UTF-16 (Little Endian) 来存储字符串值（元素和属性名称以及任何实际的字符串内容）。此数据类型不使用压缩，因此字符串本质上是每个字符 2 或 4 个字节，大多数字符是 2 字节变体。

    查看您正在使用的特定测试 XML 文档和VARCHAR数据类型（每个字符 1 到 2 个字节，最常见的是 1 字节的种类），我们现在可以解释您所看到的结果是：

    1. 您的每个元素（root、element1等）仅使用一次，因此将名称放入查找列表的唯一节省是将大小恰好减少一半。但是，XML 类型使用 UTF-16，因此每个字符串的大小是原来的两倍，从而抵消了将元素名称移动到查找列表中的节省。此时，如果仅查看文档结构（即元素名称），那么实际上XML类型和VARCHAR版本之间应该没有区别。
    2. 但是，每个元素（即test）中的字符串内容占用了两倍的字节数：8 字节 inXML而 4 字节 in VARCHAR。鉴于每行有 5 个“测试”实例，即该类型每行有 20 个额外字节XML。在 10k 行，即 600,000 字节差异中的 200,000 额外字节。其余的是XML类型的内部开销和由于每行稍大而需要存储相同数量的行的额外数据页数的额外页面开销。

    为了更好地说明这种行为，请考虑以下两种 XML 数据的变体：第一种与问题中的 XML 完全相同，第二种几乎相同，但所有元素都具有相同的名称。在第二个版本中，所有元素名称都是“element1”，因此它们与原始版本中的每个元素的长度相同。这导致VARCHAR两种情况下的数据长度相同。但是在第二个版本中元素名称相同使得内部优化更加明显。

    -- Original XML (unique element names -- "element1", "element2", ... "elementN"):
DECLARE @xml XML =  '<root><element1>test</element1><element2>test</element2>
<element3>test</element3><element4>test</element4><element5>test</element5></root>';
SELECT DATALENGTH(@xml) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml)) AS [VarcharBytes];

-- More "typical" XML (repeated element names -- all "element1"):
DECLARE @xml2 XML = '<root><element1>test</element1><element1>test</element1>
<element1>test</element1><element1>test</element1><element1>test</element1></root>';
SELECT DATALENGTH(@xml2) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml2)) AS [VarcharBytes];

    结果：

    ElementNames    XmlBytes    VarcharBytes
------------    --------    ------------
Unique          197         138
Non-Unique      109         138
    • 6

  2. 来自XML 数据类型和列 (SQL Server) 的文档

    数据存储在保留数据的 XML 内容的内部表示中。此内部表示包括有关包含层次结构、文档顺序以及元素和属性值的信息。具体来说，会保留 XML 数据的 InfoSet 内容。有关 InfoSet 的更多信息，请访问http://www.w3.org/TR/xml-infoset。InfoSet 内容可能不是文本 XML 的相同副本，因为不保留以下信息：无关紧要的空格、属性顺序、名称空间前缀和 XML 声明。

    binary_representation_size大约是data+ information about the containment hierarchy, document order, and element and attribute values- insignificant white spaces, order of attributes, namespace prefixes, and XML declaration

    如果您没有命名空间前缀，那么这不是一个明显的胜利，而空白只是存储更多数据。

    nvarchar(max)它还在文档中明确提到，如果您只是存储而不关心功能或验证，您可能只想使用它，

    如果不满足这些条件[需要高级功能]，则应使用关系数据模型。例如，如果您的数据是 XML 格式，但您的应用程序只使用数据库来存储和检索数据，[n]varchar(max)那么您只需要一列。将数据存储在 XML 列中还有其他好处。这包括让引擎确定数据格式正确或有效，还包括支持细粒度查询和更新 XML 数据。

    • 2

  3. SQL Server 2016 引入了COMPRESS功能。将此应用于@Solomon 的示例：

    ... DATALENGTH(COMPRESS(CONVERT(VARCHAR(MAX), @xml))) AS [VarcharCompressed];

... DATALENGTH(COMPRESS(CONVERT(VARCHAR(MAX), @xml2))) AS [VarcharCompressed];

    进一步节省空间：

    ElementNames    XmlBytes    VarcharBytes  VarcharCompressed
------------    --------    ------------  -----------------
Unique          197         138           72
Non-Unique      109         138           49

    值得注意的是，为唯一和重复的元素名称节省了空间。

    • 0

相关问题