sql server xml性能优化(best practices) -凯发k8官方网

1. xml数据模型
xml存储与处理查询的性能取决于数据库的设计与xml数据的结构与粒度。是否要使用xml数据模型，看你

是不是有半结构化的数据，需要保留文档结构与层次结构的标记语言数据，或可变的结构。

1.1 标记 markups
同样的数据可以以不同的方式来标记，视内容（element值）与元数据信息（attribute值）。越具体的

element名越易于阅读，且对生成有效的查询计划有帮助。冗长的标记会增加存储的成本。例如：
a.


如果要查询book，需要写为：/item[@type = "book"]
b.

如果要查询book，只需要写：/book ，这样效率更高也看起来更简单，而且减少了primary xml index（

主xml索引）的层数（少了item层）。这就是具体标记（specific markups）相对一般标记（generic

markups）的优点。

对于被类型化的的xml（typed xml），还可以缩减到两层：

像这种格式，称为element-centric markup，查询格式

为/dvd[title = "the godfather"]
像这种格式，attribute-centric markup，查询格式为/dvd[@title =

"the godfather"] ，可以减少一个join。

1.2 typed 和 untyped xml（类型化与非类型化的xml）
非类型化的xml（untyped xml，没有以xml schema来表示）在sql server内部以unicode字符串的形式存

储。对他们的操作需要数据转换到相应的类型。例如 (/book/price)[1] > 19.99，会有转换为

decimal的过程。大量类似的比较就会非常耗资源，这就引出了xml schema中类型信息的重要性。
类型信息在以下几个方面起作用：
a. 插入更新的xml数据先被验证是否符合schema，然后以二进制形式存储，方便更快地转换
b. 类型化的值被存储在xml索引中
c. 类型化的数据还可以减少范围扫描，例如(/book/price)[1]中的序数[1]在xml schema规定了

为单值时就是不必要的。

1.3 属性
可以使用udf（用户自定义函数）来获得计算列（computed column）。可以在计算列上添加索引。由于计算列是预计算好的，查询速度更快。
范例：
书籍都有isbn号，把isbn单独作为计算列的过程如下
a. 定义获取isbn号的函数
create function udf_get_book_isbn (@xdata xml) returns varchar(20)
with schemabinding
begin
   return @xdata.value('(/book/@isbn)[1]', 'varchar(20)')
end
b. 添加计算列
create table docs (id int primary key, xcol xml)
alter table docs add isbn as dbo.udf_get_book_isbn(xcol)
c. 添加非聚类索引
create index computed_idx on docs (isbn)
d. 写查询
如果不使用isbn列，查询如下：
select xcol
from   docs
where  xcol.exist ('/book/@isbn[. = "0-2016-3361-2"]') = 1
如果使用isbn列
select xcol
from   docs
where  isbn = '0-2016-3361-2'

还可以建立属性表（property table）

2. 批量载入xml数据
--可以使用bcp in，bulk insert和openrowset方法，因为和这次的case无关暂且略过。

3. xml索引
3.1 普通索引
推荐创建初级xml索引（primary xml index），实际上是创建了一个b 树。还可以对于path，property，value创建次级xml索引（secondary xml indexes）。
a. path索引
适用于有类似/book[@isbn = "0-2016-3361-2"]的路径表达式，路径越长越有效
b. property索引
适用于xml中的多properties查询
c. value索引
适用于存在子轴（//操作符）和通配符（类似(/book[@* = "novel"])）的查询
维护索引的开销也是需要整体考虑的。

--关于xml部分更新和这次的case无关暂且略过。
--关于xml全文索引和这次的case无关暂且略过。

4. 查询优化
4.1 使用exist()方法来检测是否存在
尽可能使用exist()来代替value()
比如如下的查询：
select *
from   docs
where    xcol.exist('(/book/title/text())[.="writing secure code"]') = 1
比
select *
from   docs
where  xcol.value('(/book/title)[1]', 'varchar(50)') = 'writing secure code'

4.2 优化xml blobs（二进制xml）
更多的tempdb文件可以提供更好的可扩展性。
减少额外的xml数据类型转换

4.3 指定singleton elements（单件元素）
对于类型化的xml，默认是singleton的
非类型化的xml，需要加[1]，xcol.query ('/book/title')会被自动转换为xcol.query ('(/book/title)[1]')。

4.4 对于非类型化的xml的text聚合
(/book/title[.="writing secure code"])[1]需要聚合