Sql Server: селективный XML-индекс используется неэффективно

Я изучаю способы повышения производительности приложения, на которое я могу повлиять лишь в ограниченной степени на уровне базы данных. Версия SQL Server — 2012 SP2, а рассматриваемая структура таблицы и представления (я не могу повлиять на это + обратите внимание, что документ xml может иметь всего несколько сотен элементов):

CREATE TABLE Orders(
    id nvarchar(64) NOT NULL,
    xmldoc xml NULL,
    CONSTRAINT PK_Order_id PRIMARY KEY CLUSTERED (id)
);

CREATE VIEW V_Orders as
SELECT 
    a.id, a.xmldoc
    ,a.xmldoc.value('data(/row/c1)[1]', 'nvarchar(max)') "Stuff"
    ,a.xmldoc.value('data(/row/c2)[1]', 'nvarchar(max)') "OrderType"
etc..... many columns
from Orders a;

Типичный запрос (и тот, который используется для тестирования ниже):

SELECT id FROM V_Orders WHERE OrderType = '30791'

Все запросы выполняются к представлению, и я не могу повлиять ни на запросы, ни на структуру таблицы/представления.

Я думал, что добавление выборочного XML-индекса в таблицу будет моим спасением:

CREATE SELECTIVE XML INDEX I_Orders_OrderType ON Orders(xmldoc)
FOR(
    pathOrderType = '/row/c2' as SQL [nvarchar](20)
)

Но даже после обновления статистики план выполнения выглядит странно. Не удалось опубликовать изображение как новую учетную запись, поэтому соответствующие детали в виде текста:

  • Поиск кластеризованного индекса из selectedXml (Стоимость: 2% от общей суммы). Ожидаемое количество строк 1, но ожидаемое количество раз выполнения 1269 (количество строк в таблице)
  • -> Сортировка Top N (Стоимость: 95% от общей суммы)

  • -> Вычислить скаляр (Стоимость 0)

  • Отдельная ветвь: сканирование кластерного индекса PK_Order_id (стоимость: 3% от общей суммы). Ожидаемое количество строк 1269

  • -> Слияние скалярных результатов с компьютером с вложенными циклами (левое внешнее соединение)
  • -> Фильтр
  • -> Окончательный результат (ожидаемое количество строк 1269)

На самом деле с моими тестовыми данными запрос даже не возвращает никаких результатов, но не имеет значения, возвращает ли он один или несколько. Время выполнения поддерживает запрос, действительно занимающий столько времени, сколько можно вывести из плана выполнения, и количество прочтений исчисляется тысячами.

Итак, мой вопрос: почему оптимизатор не использует селективный xml-индекс должным образом? Или у меня что-то не так? Как мне оптимизировать производительность этого конкретного запроса с помощью выборочной индексации xml (или, возможно, сохраняемого столбца)?

Изменить: я провел дополнительное тестирование с большими выборочными данными (~ 274 тыс. строк в таблице с XML-документами, близкими к средним производственным размерам) и сравнил выборочный XML-индекс с продвигаемым столбцом. Результаты взяты из трассировки Profiler с упором на использование ЦП и количество прочтений. План выполнения выборочной индексации xml в основном идентичен описанному выше.

Выборочный XML-индекс и 274 тыс. строк (выполнение запроса выше): ЦП: 6454, чтение: 938521

После того, как я обновил значения в искомом поле, чтобы они были уникальными (всего записей по-прежнему 274 КБ), я получил следующие результаты:

Выборочный XML-индекс и 274 тыс. строк (выполнение запроса выше): CPU: 10077, чтение: 1006466

Затем с помощью продвигаемого (т.е. сохраняемого) отдельно проиндексированного столбца и использования его непосредственно в представлении: CPU: 0, считывает: 23

Производительность выборочного XML-индекса ближе к полному сканированию таблицы, чем к правильной выборке индексированного столбца SQL. Я где-то читал, что использование схемы для таблицы может помочь исключить шаг TOP N из плана выполнения (при условии, что мы ищем неповторяющееся поле), но я не уверен, что это реалистичная возможность в данном случае.


xml sql-server performance sql-optimization
person Consulmagician    schedule 09.06.2015    source источник
comment
Вы можете загрузить картинку на другой фотохостинг и разместить ссылку здесь. Люди с достаточной репутацией с радостью помогут включить изображение в ваш пост, если они сочтут его ценным для вопроса.   -  person har07    schedule 10.06.2015
comment
Спасибо за совет. Я не думаю, что картинка действительно что-то добавит, так как я уже напечатал детали в тексте.   -  person Consulmagician    schedule 10.06.2015
comment
Больше информации об этом в вопрос и ответ администратору базы данных   -  person Mikael Eriksson    schedule 12.06.2015

Ответы (1)


arrow_upward
5
arrow_downward

Созданный вами выборочный XML-индекс хранится во внутренней таблице с первичным ключом из Orders в качестве ведущего столбца для кластеризованного ключа для внутренней таблицы, а указанные пути хранятся в виде разреженных столбцов.

План запроса, который вы получите, вероятно, выглядит примерно так:

введите здесь описание изображения

У вас есть сканирование всей таблицы Orders с поиском во внутренней таблице по первичному ключу для каждой строки в Orders. Последний оператор Filter отвечает за проверку значения OrderType, возвращая только совпадающие строки.

Не совсем то, что вы ожидаете от чего-то, называемого индексом.

На помощь приходит вторичный селективный XML-индекс. Они создаются для одного из путей, указанных в первичном выборочном индексе, и создают некластеризованный ключ для значений, извлеченных в выражении пути.

Однако не все так просто. SQL Server не будет использовать вторичный индекс для предикатов, используемых для значений, извлеченных функцией values(). Вместо этого вы должны использовать exists(). Кроме того, exists() требует использования типов данных XQUERY в выражениях пути, где value() использует типы данных SQL.

Ваш первичный выборочный XML-индекс может выглядеть так:

CREATE SELECTIVE XML INDEX I_Orders_OrderType ON Orders(xmldoc)
FOR 
(
  pathOrderType = '/row/c2' as sql nvarchar(20), 
  pathOrderTypeX = '/row/c2/text()' as xquery 'xs:string' maxlength (20)
)

Со вторичкой на pathOrderTypeX.

CREATE XML INDEX I_Orders_OrderType2 ON Orders(xmldoc)
  USING XML INDEX I_Orders_OrderType FOR (pathOrderTypeX)

И с запросом, который использует exist(), вы получите этот план.

select id
from V_Orders
where xmldoc.exist('/row/c2/text()[. = "30791"]') = 1

введите здесь описание изображения

Первый поиск — это поиск значения, которое вы ищете, в некластеризованном индексе внутренней таблицы. Поиск ключа выполняется по кластерному ключу во внутренней таблице (не знаю, зачем это нужно). И последний поиск выполняется по первичному ключу в таблице Orders, за которым следует фильтр, проверяющий нулевые значения в столбце xmldoc.

Если вам сойдет с рук использование рекламы собственности, создавая вычисляемые индексированные столбцы в таблице Orders из XML, я думаю, вы все равно получите лучшую производительность, чем при использовании вторичных выборочных XML-индексов.

person Mikael Eriksson    schedule 10.06.2015
comment
Отличный ответ! Спасибо. Это помогло мне понять, как выборочное индексирование xml работает под капотом и ограничениями. К сожалению, это также означает, что я практически не могу использовать его в своем текущем сценарии, но, надеюсь, это будет полезно в другом месте и другим. Причина, по которой выборочное индексирование не применимо к моей конкретной проблеме, заключается в том, что в случаях, когда используется .exist, часть запроса xpath находится в представлении, поэтому это комбинация фактического запроса и запроса представления, и это приводит к хороший план выполнения уничтожается. - person Consulmagician; 10.06.2015
comment
Не волнуйтесь. Я понимаю, что вам нужно предоставить представление какому-то пользователю, который не хочет/может иметь дело с выражениями xpath в файле exists(). Как только я решил это, создав UDF с табличным значением, который в этом случае будет принимать orderid в качестве аргумента, использовать его в запросе exists() с sql:column() и возвращать все совпадающие идентификаторы. Затем пользователям представления нужно только присоединиться к UDF к представлению и предоставить идентификатор заказа в качестве аргумента для UDF. Или UDF можно использовать в предложении where как id in (select id from UDF(123456)). - person Mikael Eriksson; 10.06.2015