SQL Server: 创建 XML 索引 提升查询效率

Scenario: 最近在工作中遇到一个问题，客户访问公司产品的某报表功能时，速度极慢，在客户环境甚至达到15+分钟的页面载入时间。经分析，问题的原因是多方面的，其中最主要的一项是产品数据库（SQL Server）的一个核心字段采用了XML类型存储，先不讨论其设计的优劣，但大量的XML查询确实大大的影响到了产品体验。

Solution: 这里为了以最快的速度解决用户问题，采用 建立 XML 索引 的方式，并通过结合其他的逻辑优化，整体的查询效率提升了近十倍，客户只用了不到两分钟便得到了结果，效率的提升还是比较喜人的。以下是XML索引的创建语句，供参考。不过需要注意的是，这是一种以空间换时间的优化方式，在应用前最好让客户了解并许可。

更多资料可参考：https://msdn.microsoft.com/zh-cn/library/ms191497.aspx

以下SQL脚本分别在名为Sites及Webs表中的Properties这个XML字段建立了主XML索引及辅助XML索引。


[sql]
-- Drop secondary indexes.
DROP INDEX IXML_Sites_Properties_Path ON Sites
GO
DROP INDEX IXML_Sites_Properties_Property ON Sites
GO
DROP INDEX IXML_Sites_Properties_Value ON Sites
GO
-- Drop primary index.
DROP INDEX IPXML_Sites_Properties ON Sites
GO

--XML主索引
CREATE PRIMARY XML INDEX IPXML_Sites_Properties ON Sites(Properties)
GO
--XML路径辅助索引
CREATE XML INDEX IXML_Sites_Properties_Path ON Sites(Properties)
USING XML INDEX IPXML_Sites_Properties FOR PATH
GO
--XML属性辅助索引
CREATE XML INDEX IXML_Sites_Properties_Property ON Sites(Properties)
USING XML INDEX IPXML_Sites_Properties FOR PROPERTY
GO
--XML内容辅助索引
CREATE XML INDEX IXML_Sites_Properties_Value ON Sites(Properties)
USING XML INDEX IPXML_Sites_Properties FOR VALUE
GO

-- Drop secondary indexes.
DROP INDEX IXML_Webs_Properties_Path ON Webs
GO
DROP INDEX IXML_Webs_Properties_Property ON Webs
GO
DROP INDEX IXML_Webs_Properties_Value ON Webs
GO
-- Drop primary index.
DROP INDEX IPXML_Webs_Properties ON Webs
GO

CREATE PRIMARY XML INDEX IPXML_Webs_Properties ON Webs(Properties)
GO
CREATE XML INDEX IXML_Webs_Properties_Path ON Webs(Properties)
USING XML INDEX IPXML_Webs_Properties FOR PATH
GO
CREATE XML INDEX IXML_Webs_Properties_Property ON Webs(Properties)
USING XML INDEX IPXML_Webs_Properties FOR PROPERTY
GO
CREATE XML INDEX IXML_Webs_Properties_Value ON Webs(Properties)
USING XML INDEX IPXML_Webs_Properties FOR VALUE
GO
[/sql]


查看原文：http://nap7.com/me/sql-server-xml-field-indexing/