SQL Server 2008 - 为什么使用xml索引会导致性能变差？

Question

真是令人惊讶，但在我将一个主 XML 索引添加到我的 xml 字段后，查询性能竟然降低了约 50%。

这是我的操作步骤：

• 我有一个包含 XML 字段 ActivityStepLog 的表（包含 LogData, XML）。

• 我通过运行以下命令生成示例数据以插入该表中：

  INSERT INTO dbo.ActivityStepLog (
  LogGUID
  ,LogContextID
  ,LogTypeID
  ,LogSourceName
  ,LogContent
  ,LogDate
  ,CreateDate
  ,CreatedBy
  )
  select
  LogGUID = newid()
  ,LogContextID = newid()
  ,LogTypeID = 2
  ,LogSourceName = 'test test test'
  ,LogContent = (SELECT top 1 * FROM ##SampleData SampleData1 where DecisionLogID = SampleData.DecisionLogID FOR XML AUTO, ELEMENTS, ROOT('BusinessRule') )
  ,LogDate = current_timestamp
  ,CreateDate = current_timestamp
  ,CreatedBy = 'test create by'
  from ##SampleData SampleData

SampleData 具有 100,000 行，我将其循环运行 5 次，因此最终得到 500,000 行。

• LogContent 字段将最终包含如下数据：

  -2147483643 0569281A-D1A3-49E3-9E68-BCAC62E2C1C3 1016 2 0 -2147483495 1 2009-05-18T11:47:00 none

（抱歉，我不确定这是否会被正确地格式化——它只是一个简短的元素集合）。

然后我只运行了一个非常简单的 SQL 查询 -

SELECT *
FROM ActivityStepLog
WHERE LogContent.value('(/BusinessRuleDecisionLog/SampleData1/DecisionLogID)[1]', 'int') = -2147483535

在 LogContent 上创建主 XML 索引之前，需要 8 秒，之后大约需要 12 秒。我清除了缓存等（DROPCLEANBUFFERS 和 FREEPROCCACHE），虽然它确实影响了总时间，但似乎不影响比例。

以下是我的统计数据：

使用 XML 索引 表 'xml_index_nodes_325576198_256000'。扫描次数 1000000，逻辑读取数 3517272，物理读取数 0，预读取数 0，LOB 逻辑读取数 0，LOB 物理读取数 0，LOB 预读取数 0。 表 'ActivityStepLog'。扫描次数 1，逻辑读取数 71694，物理读取数 0，预读取数 0，LOB 逻辑读取数 0，LOB 物理读取数 0，LOB 预读取数 0。

不使用 XML 索引

(5 行受影响) 表 'ActivityStepLog'。扫描次数 1，逻辑读取数 71694，物理读取数 0，预读取数 0，LOB 逻辑读取数 0，LOB 物理读取数 0，LOB 预读取数 0。

因此，没有使用 XML 索引时逻辑读取量要少得多。我尝试添加所有可用的二级索引，但与具有主 XML 索引相比，性能并没有提高。

我将继续研究此问题，但我真的很感谢任何指针或评论。

谢谢， Sylvia

- Sylvia

---

因为你需要除主索引之外的第二索引 - 对于路径 IIRC - OMG Ponies

感谢您的评论，但正如我在帖子中提到的，我已经添加了所有可用的次要 XML 索引，但并没有看到任何性能提升。 - Sylvia

Answer 1

经过进一步研究，发现对于未指定类型的xml字段，在我的测试案例中，xml索引会降低性能。但是对于指定了类型的xml来说，情况可能不同，我没有深入研究。

有一个显著提高性能的方法（感谢msdn sql xml论坛上的wBob提供的想法！），那就是在xml字段上创建全文索引。这样一来，性能可以达到亚秒级别。为了保证准确性，我还添加了一个xml过滤器。

SELECT *
FROM dbo.ActivityStepLog
WHERE 
   CONTAINS (LogContent, '-2147483535')
   and LogContent.value('(/BusinessRuleDecisionLog/SampleData1/DecisionLogID)[1]', 'int') = -2147483535

我还需要研究一下它是否符合我所有的过滤需求，但目前看起来不错。

Sylvia

- Sylvia