sql-server - SQL Server : Selective XML Index not being efficiently used

我正在探索提高应用程序性能的方法，这些方法只能在一定程度上影响数据库级别。 SQL Server版本是2012 SP2，所讨论的表和 View 结构是(我不能真正影响此+请注意，xml文档总共可能包含数百个元素):

CREATE TABLE Orders(
    id nvarchar(64) NOT NULL,
    xmldoc xml NULL,
    CONSTRAINT PK_Order_id PRIMARY KEY CLUSTERED (id)
);

CREATE VIEW V_Orders as
SELECT
    a.id, a.xmldoc
    ,a.xmldoc.value('data(/row/c1)[1]', 'nvarchar(max)') "Stuff"
    ,a.xmldoc.value('data(/row/c2)[1]', 'nvarchar(max)') "OrderType"
etc..... many columns
from Orders a;

一个典型的查询(以及下面用于测试的查询):

SELECT id FROM V_Orders WHERE OrderType = '30791'

所有查询都是针对 View 执行的，我既不会影响查询，也不会影响表/ View 结构。

我以为在表中添加选择性XML索引将是我的救星:

CREATE SELECTIVE XML INDEX I_Orders_OrderType ON Orders(xmldoc)
FOR(
    pathOrderType = '/row/c2' as SQL [nvarchar](20)
)

但是，即使在更新统计信息之后，执行计划仍然看起来很奇怪。无法将图片发布为新帐户，因此相关详细信息为文本:

来自selectiveXml的聚集索引查找(成本:总计的2％)。预期的行数1，但是预期的执行次数1269(表中的行数)

->排名前N位(费用:总数的95％)

->计算标量(成本0)

单独的分支:聚集索引扫描PK_Order_id(成本:总计的3％)。预期的行数1269

->使用嵌套循环(左外部联接)合并到计算机标量结果

->过滤

->最终结果(预期行数1269)

实际上，对于我的测试数据，该查询甚至不会返回任何结果，但是无论返回一个还是几个都没有任何区别。执行时间实际上支持查询，只要从执行计划中可以推论得出，读取次数就成千上万。

所以我的问题是为什么优化器没有正确使用选择性xml索引？还是我出了点问题？如何使用选择性xml索引(或可能是持久列)优化此特定查询的性能？

编辑:
我对较大的样本数据(表中约27.4万行，XML文档接近平均生产规模)进行了额外的测试，并将选择性XML索引与升级后的列进行了比较。结果来自Profiler跟踪，重点是CPU使用率和读取计数。选择性xml索引的执行计划与上述基本相同。

选择性XML索引和274k行(执行上面的查询):
CPU:6454，读取:938521

将搜索字段中的值更新为唯一后(总记录仍为274k)，我得到以下结果:

选择性XML索引和274k行(执行上面的查询):
CPU:10077，读取:1006466

然后使用提升的(即持久化的)单独索引的列，并直接在 View 中使用它:
CPU:0，读取:23

选择性XML索引的性能似乎比适当的SQL索引的列提取更接近全表扫描。我在某处读到，对于表使用模式可能有助于从执行计划中删除TOP N步骤(假设我们正在搜索一个非重复字段)，但是我不确定在这种情况下这是否可行。

最佳答案

您创建的选择性XML索引存储在内部表中，其中Orders的主键作为内部表的集群键的前导列，指定的路径存储为稀疏列。

您获得的查询计划可能看起来像这样:

您对整个Orders表进行了扫描，并在内部表中针对Orders中的每一行在主键上进行了查找。最终的Filter运算符负责检查OrderType的值，仅返回匹配的行。

并不是您真正希望从索引中得到的东西。

急需解决的是二级选择性XML索引。它们是为主要选择索引中指定的路径之一创建的，并且将在路径表达式中提取的值上创建非聚集键。

但是，这并不是那么容易。 SQL Server将不会对values()函数提取的值所使用的谓词使用二级索引。您必须改为使用exists()。此外，exists()要求在value()使用SQL数据类型的路径表达式中使用XQUERY数据类型。

您的主要选择性XML索引可能如下所示:

CREATE SELECTIVE XML INDEX I_Orders_OrderType ON Orders(xmldoc)
FOR
(
  pathOrderType = '/row/c2' as sql nvarchar(20),
  pathOrderTypeX = '/row/c2/text()' as xquery 'xs:string' maxlength (20)
)

在pathOrderTypeX上具有辅助功能。

CREATE XML INDEX I_Orders_OrderType2 ON Orders(xmldoc)
  USING XML INDEX I_Orders_OrderType FOR (pathOrderTypeX)

通过使用exist()的查询，您将获得此计划。

select id
from V_Orders
where xmldoc.exist('/row/c2/text()[. = "30791"]') = 1

第一个搜寻是在内部表的非聚集索引中寻找您要寻找的值。键查询是在内部表上的群集键上完成的(不知道为什么这样做是必要的)。最后一次查找是在Orders表中的主键上，然后是一个过滤器，该过滤器检查xmldoc列中的空值。

如果您可以使用property promotion摆脱困境，从XML在Orders表中创建计算出的索引列，我想您仍然会比使用二级选择性XML索引获得更好的性能。