主页 / dba / 问题 / 103867
Accepted
Mikael Eriksson
Asked: 2015-06-12 23:34:06 +0800 CST

为什么 where 子句过滤 `value()` 时没有使用二级选择索引？

  • 772

设置：

create table dbo.T
(
  ID int identity primary key,
  XMLDoc xml not null
);

insert into dbo.T(XMLDoc)
select (
       select N.Number
       for xml path(''), type
       )
from (
     select top(10000) row_number() over(order by (select null)) as Number
     from sys.columns as c1, sys.columns as c2
     ) as N;

每行的示例 XML：

<Number>314</Number>

查询的工作是计算T具有指定值 的行数<Number>。

有两种明显的方法可以做到这一点：

select count(*)
from dbo.T as T
where T.XMLDoc.value('/Number[1]', 'int') = 314;

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/Number[. eq 314]') = 1;

事实证明，value()和exists()需要两个不同的路径定义才能使选择性 XML 索引起作用。

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
  pathSQL = '/Number' as sql int singleton,
  pathXQUERY = '/Number' as xquery 'xs:double' singleton
);

sql版本适用于版本适用value()于.xqueryexist()

您可能认为这样的索引会给您一个很好的搜索计划，但选择性 XML 索引是作为系统表实现的，其主键是T系统表聚集键的主键。指定的路径是该表中的稀疏列。如果您想要定义路径的实际值的索引，您需要创建一个二级选择性索引，每个路径表达式一个。

create xml index SIX_T_pathSQL on dbo.T(XMLDoc)
  using xml index SIX_T for (pathSQL);

create xml index SIX_T_pathXQUERY on dbo.T(XMLDoc)
  using xml index SIX_T for (pathXQUERY);

的查询计划exist()在辅助 XML 索引中进行查找，然后在系统表中进行选择性 XML 索引的键查找（不知道为什么需要这样做），最后它进行查找T以确保确实存在行在那里。最后一部分是必要的，因为系统表和T.

在此处输入图像描述

查询计划value()不是很好。它T使用嵌套循环连接对内部表进行搜索以从稀疏列中获取值并最终过滤该值进行聚簇索引扫描。

在此处输入图像描述

是否应该使用选择性索引是在优化之前决定的，但是是否应该使用辅助选择性索引是优化器基于成本的决定。

为什么 where 子句过滤时不使用二级选择索引value()？

更新：

查询在语义上是不同的。如果您添加一行值

<Number>313</Number>
<Number>314</Number>`

该exist()版本将计算 2 行，而values()查询将计算 1 行。但是使用singleton指令 SQL Server 将在此处指定的索引定义阻止您添加包含多个<Number>元素的行。

但是，这不允许我们在values()不指定的情况下使用该函数，[1]以保证编译器我们只会获得一个值。这[1]就是我们在value()计划中采用 Top N 排序的原因。

看起来我正在接近这里的答案......

sql-server sql-server-2012

1 个回答

  1. Best Answer

    索引的路径表达式中的声明singleton强制您不能添加多个元素，但 XQuery 编译器在解释函数<Number>中的表达式时不会考虑到这一点。value()您必须指定[1]才能让 SQL Server 满意。将类型化 XML 与模式一起使用也无济于事。正因为如此，SQL Server 构建了一个使用可称为“应用”模式的东西的查询。

    最容易演示的是使用常规表而不是 XML 来模拟我们实际执行的查询T和内部表。

    这是内部表作为真实表的设置。

    create table dbo.xml_sxi_table
(
  pk1 int not null,
  row_id int,
  path_1_id varbinary(900),
  pathSQL_1_sql_value int,
  pathXQUERY_2_value float
);

go

create clustered index SIX_T on xml_sxi_table(pk1, row_id);
create nonclustered index SIX_pathSQL on xml_sxi_table(pathSQL_1_sql_value) where path_1_id is not null;
create nonclustered index SIX_T_pathXQUERY on xml_sxi_table(pathXQUERY_2_value) where path_1_id is not null;

go

insert into dbo.xml_sxi_table(pk1, row_id, path_1_id, pathSQL_1_sql_value, pathXQUERY_2_value)
select T.ID, 1, T.ID, T.ID, T.ID
from dbo.T;

    两个表都准备就绪后，您可以执行与exist()查询等效的操作。

    select count(*)
from dbo.T
where exists (
             select *
             from dbo.xml_sxi_table as S
             where S.pk1 = T.ID and
                   S.pathXQUERY_2_value = 314 and
                   S.path_1_id is not null
             );

    查询的等效项value()如下所示。

    select count(*)
from dbo.T
where (
      select top(1) S.pathSQL_1_sql_value
      from dbo.xml_sxi_table as S
      where S.pk1 = T.ID and
            S.path_1_id is not null
      order by S.path_1_id
      ) = 314;

    top(1)and是罪魁祸首，order by S.path_1_id它[1]在 Xpath 表达式中是罪魁祸首。

    我认为 Microsoft 不可能用内部表的当前结构修复此问题，即使您被允许[1]从values()函数中省略。他们可能必须为每个具有唯一约束的路径表达式创建多个内部表，以向优化器保证每一行只能有一个<number>元素。不确定这是否足以让优化器“打破应用模式”。

    对于那些认为这很有趣和有趣的人，并且由于您仍在阅读本文，所以您可能是。

    一些查询要看内表的结构。

    select T.name, 
       T.internal_type_desc, 
       object_name(T.parent_id) as parent_table_name
from sys.internal_tables as T
where T.parent_id = object_id('T');

select C.name as column_name, 
       C.column_id,
       T.name as type_name,
       C.max_length,
       C.is_sparse,
       C.is_nullable
from sys.columns as C
  inner join sys.types as T
    on C.user_type_id = T.user_type_id
where C.object_id in (
                     select T.object_id 
                     from sys.internal_tables as T 
                     where T.parent_id = object_id('T')
                     )
order by C.column_id;

select I.name as index_name,
       I.type_desc,
       I.is_unique,
       I.filter_definition,
       IC.key_ordinal,
       C.name as column_name, 
       C.column_id,
       T.name as type_name,
       C.max_length,
       I.is_unique,
       I.is_unique_constraint
from sys.indexes as I
  inner join sys.index_columns as IC
    on I.object_id = IC.object_id and
       I.index_id = IC.index_id
  inner join sys.columns as C
    on IC.column_id = C.column_id and
       IC.object_id = C.object_id
  inner join sys.types as T
    on C.user_type_id = T.user_type_id
where I.object_id in (
                     select T.object_id 
                     from sys.internal_tables as T 
                     where T.parent_id = object_id('T')
                     );
    • 11

相关问题