sql-server – 使用XML索引的奇怪性能

为了给出答案,我做了以下测试场景.

测试场景

首先,我创建一个测试表并用100.000行填充它.随机数(0到1000)应该导致每个随机数约100行.此数字将放入varchar col并作为XML的值.

然后我做了一个类似OP的调用,需要它与.exist()和.nodes(),第二个小优势,但都需要5到6秒.事实上,我做了两次调用：第二次以交换顺序和略微改变的搜索参数和“// item”而不是完整路径,以避免通过缓存结果或计划出现误报.

然后我创建一个XML索引并执行相同的调用

现在 – 让我感到惊讶的是什么！ – 具有完整路径的.nodes比之前(9秒)慢得多,但.exist()下降到半秒,完整路径甚至低至约0.10秒. (而短路径的.nodes()更好,但仍远远落后于.exist())

问题：

我自己的测试简而言之：XML索引可以极大地破坏数据库.他们可以极速加速(s.编辑2),但也会减慢你的查询速度.我想了解它们的工作原理……何时应该创建XML索引？为什么带索引的.nodes()会比没有索引更糟糕？怎么可以避免负面影响？

CREATE TABLE #testTbl(ID INT IDENTITY PRIMARY KEY,SomeData VARCHAR(100),XmlColumn XML);
GO

DECLARE @RndNumber VARCHAR(100)=(SELECT CAST(CAST(RAND()*1000 AS INT) AS VARCHAR(100)));

INSERT INTO #testTbl VALUES('Data_' + @RndNumber,'<error application="application" host="host" type="exception" message="message" >
  <serverVariables>
    <item name="name1">
      <value string="text" />
    </item>
    <item name="name2">
      <value string="text2" />
    </item>
    <item name="name3">
      <value string="text3" />
    </item>
    <item name="name4">
      <value string="text4" />
    </item>
    <item name="name5">
      <value string="My test ' +  @RndNumber + '" />
    </item>
    <item name="name6">
      <value string="text6" />
    </item>
    <item name="name7">
      <value string="text7" />
    </item>
  </serverVariables>
</error>');

GO 100000

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_no_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_no_index;
GO

CREATE PRIMARY XML INDEX PXML_test_XmlColum1 ON #testTbl(XmlColumn);
CREATE XML INDEX IXML_test_XmlColumn2 ON #testTbl(XmlColumn) USING XML INDEX PXML_test_XmlColum1 FOR PATH;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_with_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_with_index;
GO

DROP TABLE #testTbl;

编辑1 – 结果

这是SQL Server 2012本地安装在中型笔记本电脑上的一个结果
在这个测试中,我无法重现对NodesFullPath_with_index的极端负面影响,尽管它比没有索引时要慢……

NodesFullPath_no_index    6.067
ExistFullPath_no_index    6.223
ExistShortPath_no_index   8.373
NodesShortPath_no_index   6.733

NodesFullPath_with_index  7.247
ExistFullPath_with_index  0.217
ExistShortPath_with_index 0.500
NodesShortPath_with_index 2.410

编辑2使用更大的XML进行测试

根据TT的建议,我使用了上面的XML,但复制了项目节点以达到约450项.我让命中节点在XML中非常高(因为我认为.exist()会在第一次命中时停止,而.nodes()会继续)

创建XML索引将mdf文件炸成~21GB,~18GB似乎属于索引(!!!)

NodesFullPath_no_index    3min44
ExistFullPath_no_index    3min39
ExistShortPath_no_index   3min49
NodesShortPath_no_index   4min00

NodesFullPath_with_index  8min20
ExistFullPath_with_index  8,5 seconds !!!
ExistShortPath_with_index 1min21
NodesShortPath_with_index 13min41 !!!

解决方法

首先,SQL Server 2012和SQL Server 2014之间的时序差异是由于SQL Server 2014中的新基数估算器.您可以在SQL Server 2014中使用跟踪标志来强制使用旧的估算器,然后您将看到相同的时序与SQL Server 2012中一样的SQL Server 2014中的特性.

比较nodes()和exist()是不公平的,因为如果XML中有一个匹配的元素多于一行,它们就不会返回相同的结果. exists()将从基表返回一行,而nodes()可能会为基表中的每一行返回多行.
我们知道数据,但SQL Server没有并且必须构建一个考虑到这一点的查询计划.

要使nodes()查询等效于exist()查询,您可以执行类似这样的操作.

SELECT testTbl.*
FROM testTbl
WHERE EXISTS (
             SELECT *
             FROM XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b)
             )

使用类似的查询使用nodes()或exists()之间没有区别,这是因为SQL Server为不使用索引的两个版本构建了几乎相同的计划,并且在使用索引时完全相同的计划.对于SQL Server 2012和SQL Server 2014都是如此.

对于我在SQL Server 2012中,使用上面nodes()查询的修改版本,没有XML索引的查询需要6秒.使用完整路径或短路径没有区别.使用XML索引时,完整路径版本最快,需要5毫秒,使用短路径大约需要500毫秒.检查查询计划将告诉您为什么存在差异但短版本是当您使用短路径时,SQL Server在短路径上寻找索引(范围搜索使用类似)并在丢弃行之前返回700000行与值不匹配.使用完整路径时,SQL Server可以直接使用路径表达式和节点的值来进行搜索,并从头开始只返回105行来处理.

使用SQL Server 2014和新的基数估计器时,使用XML索引时这些查询没有区别.如果不使用索引,查询仍然需要相同的时间,但它是15秒.使用新东西时,显然不是一个改进.

不确定我是否完全忘记了你的问题实际上是什么,因为我修改了相同的查询,但这就是我现在所相信的.

Why is the nodes() query (original version) with an XML index in place significantly
slower then when an index is not used?

好吧,答案是SQL Server查询计划优化器做了一些坏事,并引入了一个假脱机操作符.我不知道为什么,但好消息是它不再存在于SQL Server 2014中的新的基数估算器.
在没有索引的情况下,无论使用什么基数估计器,查询大约需要7秒.使用索引,使用旧估算器(SQL Server 2012)需要15秒,使用新估算器(SQL Server 2014)需要大约2秒.

注意：上述发现对您的测试数据有效.如果您更改XML的大小,形状或形式,可能会有一个完全不同的故事.如果不测试表中实际存在的数据,就无法确定.

XML索引的工作原理

SQL Server中的XML索引实现为内部表.主XML索引使用基表的主键加上节点标识列创建表,总共12列.每个元素/节点/属性等将有一行.因此,根据存储的XML的大小,表当然可以变得非常大.使用主XML索引后,SQL Server可以使用内部表的主键来查找基表中每行的XML节点和值.

辅助XML索引有三种类型.创建辅助XML索引时,在内部表上创建了非聚集索引,并且根据您创建的辅助索引的类型,它将具有不同的列和列顺序.

从CREATE XML INDEX (Transact-SQL)开始：

VALUE
Creates a secondary XML index on columns where key columns are
(node value and path) of the primary XML index.

PATH
Creates a secondary XML index on columns built on path values
and node values in the primary XML index. In the PATH secondary index,
the path and node values are key columns that allow efficient seeks
when searching for paths.

PROPERTY
Creates a secondary XML index on columns (PK,path and node
value) of the primary XML index where PK is the primary key of the
base table.

因此,在创建PATH索引时,该索引中的第一列是路径表达式,第二列是该节点中的值.实际上,路径以一种压缩格式存储并反转.它反向存储的原因是它在使用短路径表达式的搜索中很有用.在您的短路径案例中,您搜索了// item / value / @ string,// item / @ name和// item.由于路径在列中以相反方式存储,因此SQL Server可以使用范围搜索与like =’

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!