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索引。

-- 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

Hendry

About Hendry

不经历复杂的简单,只是一种苍白。

发表评论

电子邮件地址不会被公开。