Огромное замедление запросов SQL Server при добавлении шаблона (или верхнего)

У меня есть зоопарк из 20 миллионов животных, которые я отслеживаю в своей базе данных SQL Server 2005. Около 1% из них черные, и около 1% из них - лебеди. Я хотел получить детали всех черных лебедей и поэтому, не желая загружать страницу результатов, которую я сделал:

  выберите топ 10 *
от животных
где цвет как "черный"
и такие виды, как «лебедь»,
 

(Да, заранее, эти поля являются freetext, но они оба индексируются). Оказывается, у нас нет таких животных, как запрос возвращает пустой набор примерно за 300 миллисекунд. Это было бы примерно в два раза быстрее, если бы я использовал «=», а не «как», но у меня есть предчувствие, которое последнее собирается спасти меня.

Оказывается, главный зоопарк думает, что он, возможно, ввел некоторых из лебедей как «черноватый», поэтому я соответствующим образом изменяю запрос:

  выберите топ 10 *
от животных
где цвет «черный%»
и такие виды, как «лебедь»,
 

Оказывается, нет ни одного из них (и на самом деле нет «черных%» животных, кроме «черных»), но теперь запрос занимает около 30 секунд, чтобы вернуть пустой.

Кажется, что комбинация «top» и «like%» вызывает проблемы, потому что

  выберите count (*)
от животных
где цвет «черный%»
и такие виды, как «лебедь»,
 

возвращает 0 очень быстро, и даже

  выберите *
от животных
где цвет «черный%»
и такие виды, как «лебедь»,
 

возвращает пустое число за долю секунды.

Есть ли у кого-нибудь идеи, почему «верх» и «%» должны сговариваться, чтобы вызвать такую ​​резкую потерю производительности, особенно в пустом наборе результатов?

EDIT: просто чтобы уточнить, я не использую индексы FreeText, я просто имел в виду, что поля являются freetext в точке входа, то есть не нормализованы в базе данных. Извините за путаницу, плохая формулировка с моей стороны.

51 голос | спросил stovroz 3 TueEurope/Moscow2013-12-03T03:12:32+04:00Europe/Moscow12bEurope/MoscowTue, 03 Dec 2013 03:12:32 +0400 2013, 03:12:32

3 ответа


75

Это решение оптимизатора, основанного на затратах.

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

Это похоже на проблему, описанную в Строке Goals Gone Rogue , где четные и нечетные числа отрицательно коррелируют.

Легко воспроизвести.

  CREATE TABLE dbo.animal (
    id int IDENTITY (1,1) NOT NULL PRIMARY KEY,
    цвет varchar (50) NOT NULL,
    вид varchar (50) NOT NULL,
    Filler char (10) NULL
);

/* Вставьте 20 миллионов строк с 1% черного и 1% лебедя, но нет черных лебедей * /
С Т
     AS (SELECT TOP 20000000 ROW_NUMBER () OVER (ORDER BY @@ SPID) AS RN
         FROM master..spt_values ​​v1,
                master..spt_values ​​v2,
                master..spt_values ​​v3)
INSERT INTO dbo.animal
            (цвет,
             виды)
ВЫБЕРИТЕ СЛУЧАЙ
         КОГДА RN% 100 = 1 THEN 'black'
         ELSE CAST (RN% 100 AS VARCHAR (3))
       КОНЕЦ,
       ДЕЛО
         КОГДА RN% 100 = 2 ТОГДА «лебедь»
         ELSE CAST (RN% 100 AS VARCHAR (3))
       КОНЕЦ
FROM T

/* Создать некоторые индексы * /
СОЗДАТЬ НЕПРЕРЫВНЫЙ ИНДЕКС ix_species ON dbo.animal (species);
СОЗДАТЬ НЕПРЕРЫВНЫЙ ИНДЕКС ix_colour ON dbo.animal (цвет);
 

Теперь попробуйте

  SELECT TOP 10 *
ИЗ животных
WHERE цвет LIKE 'черный'
       И виды LIKE 'swan'
 

Это дает нижеприведенный план стоимостью 0.0563167 .

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

<p> План может выполнять объединение слияния между результатами двух индексов в столбце <code> id </code>. (<a href= Подробнее об алгоритме объединения слиянием здесь ).

Объединить соединение требует, чтобы оба входа были заказаны с помощью соединительного ключа.

Некластеризованные индексы упорядочиваются по (species, id) и (цвет, id) соответственно (неединичные индексы без кластеризации всегда содержит локатор строк, добавленный в конец ключа неявно , если он не добавлен явно ). Запрос без каких-либо подстановочных знаков выполняет поиск равенства в species = 'swan' и color = 'black' . Поскольку каждый поиск только возвращает одно точное значение из ведущего столбца, соответствующие строки будут упорядочены по id , поэтому этот план возможен.

Операторы плана запроса выполняют слева направо . Когда левый оператор запрашивает строки у своих дочерних элементов, которые, в свою очередь, запрашивают строки из своих детей (и так далее, пока не будут достигнуты узлы листа). Итератор TOP прекратит запрашивать больше строк из своего дочернего элемента после получения 10.

SQL Server имеет статистику индексов, которые говорят, что 1% строк соответствуют каждому предикату. Он предполагает, что эти статистические данные независимы (то есть не коррелируются ни положительно, ни отрицательно), так что в среднем, как только он обработает 1000 строк, соответствующих первому предикату, он найдет 10, соответствующий второй, и сможет выйти. (план на самом деле показывает 987, а не 1000, но достаточно близко).

Фактически, когда предикаты отрицательно коррелируют, фактический план показывает, что все 200 000 совпадающих строк необходимо обрабатывать из каждого индекса, но это в некоторой степени смягчается, поскольку нулевые объединенные строки также означают, что нулевые запросы действительно необходимы.

Сравните с

  SELECT TOP 10 *
ИЗ животных
WHERE цвет LIKE 'черный%'
       И виды LIKE 'swan'
 

Что дает план ниже, который стоит по цене 0.567943

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

<p> Добавление конечного шаблона теперь вызвало сканирование индекса. Стоимость плана по-прежнему довольно низкая, хотя для сканирования таблицы из 20 миллионов строк. </p>

<p> Добавление <code> querytraceon 9130 </code> показывает дополнительную информацию </p>

<pre> <code> SELECT TOP 10 *
ИЗ животных
WHERE цвет LIKE 'черный%'
       И виды LIKE 'swan'
ВАРИАНТ (QUERYTRACEON 9130)
</code> </pre>

<p> <img src = (Дополнительная информация о неблокирующих и блокирующих итераторах здесь)

Учитывая увеличение затрат на дополнительные оценочные строки и хэш-соединение, частичное кластеризованное сканирование индекса выглядит более дешевым.

На практике, конечно, «частичное» кластеризованное сканирование индекса не является частичным вообще, и ему нужно пробивать все 20 миллионов строк, а не 100 000, принятых при сравнении планов.

Увеличение значения TOP (или удаление его полностью) в конечном итоге встречает точку опроса, где количество строк, которые он оценивает, сканирование CI должно будет покрывать, делает этот план более дорогим и возвращается к плану пересечения индексов. Для меня точка отсечения между двумя планами TOP (89) vs TOP (90) .

Для вас это может сильно отличаться, так как зависит от того, насколько широким является кластеризованный индекс.

Удаление TOP и принудительное сканирование CI

  SELECT *
От животного WITH (INDEX = 1)
WHERE цвет LIKE 'черный%'
       И виды LIKE 'swan'
 

Рассчитана на 88.0586 на моей машине для моей таблицы примеров.

Если SQL Server знал, что в зоопарке нет черных лебедей, и что ему нужно будет выполнить полную проверку, а не просто читать 100 000 строк, этот план не будет выбран.

Я пробовал статистику нескольких столбцов на животном (вид, цвет) и животном (цвет, вид) и отфильтрованной статистике на животном (цвет), где species = 'swan' , но ни один из них не помогает убедить его, что черных лебедей не существует, а сканирование TOP 10 должно обрабатывать более 100 000 строк.

Это связано с «предположением о включении», где SQL Server по существу предполагает, что, если вы ищете что-то, оно, вероятно, существует.

В 2008 году есть документированный флаг трассировки 4138 , который отключает задание строк. Эффект от этого заключается в том, что план оценивается без предположения, что TOP позволит дочерним операторам завершить работу до конца, не читая все соответствующие строки. С этим флагом следа я, естественно, получаю более оптимальный план пересечения индекса.

  SELECT TOP 10 *
ИЗ животных
WHERE цвет LIKE 'черный%'
       И виды LIKE 'swan'
ВАРИАНТ (QUERYTRACEON 4138)
 

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

<p> Этот план теперь корректно стоит для чтения полных 200 тысяч строк в обоих индексах, но по сравнению с затратами ключевых поисков (оцененный 2 тыс. против фактического 0. <code> TOP 10 </code> будет ограничивать это максимум 10, но флаг трассировки не учитывает это). Тем не менее, план стоит значительно дешевле, чем выбран полный CI-сканирование. </p>

<p> Конечно, этот план может быть не оптимальным для комбинаций, которые  являются  обычными. Такие, как белые лебеди. </p>

<p> Компонентный индекс на <code> животном (цвет, вид) </code> или идеально <code> животное (вид, цвет) </code> позволит сделать запрос намного более эффективным для обоих сценариев. </p>

<p> Чтобы наиболее эффективно использовать составной индекс, <code> LIKE 'swan' </code> также нужно будет изменить на <code> = 'swan' </code>. </p>

<p> В приведенной ниже таблице показаны предикаты поиска и остаточные предикаты, указанные в планах выполнения для всех четырех перестановок. </p>

<pre> <code> + ------------------------------------------- --- + ------------------- + -------------------------- -------------------------------------- + ----------- ----------------------------------- +
|Предложение WHERE | Индекс | Ищите Предикат | Остаточный предикат |
+ ---------------------------------------------- + - ----------------- + -------------------------------- -------------------------------- + ----------------- ----------------------------- +
| цвет LIKE 'черный%' И виды LIKE 'swan' | ix_colour_species | цвет> = 'черный' И цвет <'blacL' | цвет как «черный%» и такие виды, как «лебедь» |
| цвет LIKE 'черный%' И виды LIKE 'swan' | ix_species_colour | виды> = 'лебедь' и виды <= 'лебедь' | цвет как «черный%» и такие виды, как «лебедь» |
| цвет LIKE 'черный%' И виды = 'лебедь' | ix_colour_species | (цвет, вид)> = («черный», «лебедь»)) И цвет <'blacL' | цвет LIKE 'черный%' И виды = 'лебедь' |
| цвет LIKE 'черный%' И виды = 'лебедь' | ix_species_colour | species = 'swan' AND (color> = 'black' и color <'blacL') | цвет как «черный%» |
+ ---------------------------------------------- + - ----------------- + -------------------------------- -------------------------------- + ----------------- ----------------------------- +
</code> </pre></div>
					 
						<div class=

ответил Martin Smith 11 WedEurope/Moscow2013-12-11T21:28:34+04:00Europe/Moscow12bEurope/MoscowWed, 11 Dec 2013 21:28:34 +0400 2013, 21:28:34
14

Основываясь на этом интригующем, я немного искал и наткнулся на этот Q /A Как (и почему) TOP влияет на план выполнения?

В принципе, использование TOP меняет стоимость операторов, следующих за ним (в нетривиальном порядке), что также приводит к изменению плана overal (было бы здорово, если бы вы включили ExecPlans с и без TOP 10), что в значительной степени изменяет чрезмерное выполнение запроса.

Надеюсь, что это поможет.

Например, я попробовал это в базе данных и: -когда не задействована вершина, используется параллелизм -with TOP, параллелизм не используется

Итак, снова, показывая планы выполнения, вы получите больше информации.

Хороший день

ответил Vladislav Zalesak 11 WedEurope/Moscow2013-12-11T16:07:25+04:00Europe/Moscow12bEurope/MoscowWed, 11 Dec 2013 16:07:25 +0400 2013, 16:07:25
-1

Я считаю, что это может быть связано с базовым характером MSSQL 2005 и тем, как оптимизатор запросов решает, какой план выполнения является наиболее эффективным.

Если вы используете переменную SQL, она должна «обмануть» оптимизатор запросов с использованием хеш-совпадений вместо вложенных циклов, что приведет к гораздо большей степени параллелизма.

Try:

  DECLARE @topn INT = 10
SELECT TOP (@topn) *
ИЗ животных
WHERE цвет LIKE 'черный%'
И виды LIKE 'swan'
 
ответил 11 WedEurope/Moscow2013-12-11T15:21:59+04:00Europe/Moscow12bEurope/MoscowWed, 11 Dec 2013 15:21:59 +0400 2013, 15:21:59

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132