Неожиданные проверки во время операции удаления с использованием WHERE IN

У меня есть запрос вроде:

DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
    SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)

tblFEStatsBrowsers имеет 553 строки.
tblFEStatsPaperHits имеет 47.974.301 строк.

tblFEStatsBrowsers:

CREATE TABLE [dbo].[tblFEStatsBrowsers](
    [BrowserID] [smallint] IDENTITY(1,1) NOT NULL,
    [Browser] [varchar](50) NOT NULL,
    [Name] [varchar](40) NOT NULL,
    [Version] [varchar](10) NOT NULL,
    CONSTRAINT [PK_tblFEStatsBrowsers] PRIMARY KEY CLUSTERED ([BrowserID] ASC)
)

tblFEStatsPaperHits:

CREATE TABLE [dbo].[tblFEStatsPaperHits](
    [PaperID] [int] NOT NULL,
    [Created] [smalldatetime] NOT NULL,
    [IP] [binary](4) NULL,
    [PlatformID] [tinyint] NULL,
    [BrowserID] [smallint] NULL,
    [ReferrerID] [int] NULL,
    [UserLanguage] [char](2) NULL
)

В tblFEStatsPaperHits есть кластерный индекс, который не включает BrowserID. Таким образом, выполнение внутреннего запроса потребует полного сканирования таблицы tblFEStatsPaperHits - это полностью нормально.

В настоящее время для каждой строки в tblFEStatsBrowsers выполняется полное сканирование, что означает, что у меня есть 553 полных таблицы сканирований tblFEStatsPaperHits.

Переписывание только WHERE EXISTS не изменяет план:

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
)

Однако, как предложил Адам Мачаник, добавление опции HASH JOIN приводит к оптимальному плану выполнения (просто одно сканирование tblFEStatsPaperHits):

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
) OPTION (HASH JOIN)

Теперь это не так много вопросов о том, как исправить это - я могу либо использовать OPTION (HASH JOIN), либо создать временную таблицу вручную. Мне больше интересно, почему оптимизатор запросов когда-либо использовал план, который он сейчас делает.

Поскольку QO не имеет статистики в столбце BrowserID, я предполагаю, что он принимает худшие - 50 миллионов различных значений, что требует довольно большой рабочей памяти in-memory /tempdb. Таким образом, самый безопасный способ - выполнять сканирование для каждой строки в tblFEStatsBrowsers. Между столбцами BrowserID в двух таблицах нет отношения внешнего ключа, поэтому QO не может вычитать информацию из tblFEStatsBrowsers.

Это так просто, как кажется, причина?

Обновление 1
Чтобы дать пару характеристик: ВАРИАНТ (HASH JOIN):
208.711 логических чтений (12 сканирований)

ОПЦИЯ (LOOP JOIN, HASH GROUP):
11.008.698 логическое чтение (~ сканирование для каждого браузераID (339))

Нет параметров:
11.008.775 логическое чтение (~ сканирование для каждого браузераID (339))

Обновление 2
Отличные ответы, все вы - спасибо! Трудно выбрать только один. Хотя Мартин был первым, и Ремус обеспечил отличное решение, я должен отдать его киви за умственные детали:)

39 голосов | спросил Mark S. Rasmussen 31 Maypm12 2012, 18:49:05

3 ответа


59
  

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

Иными словами, возникает вопрос, почему следующий план выглядит наиболее оптимальным для оптимизатора по сравнению с альтернативами (из которых many ).

Оригинальный план

Внутренняя сторона соединения в основном выполняет запрос следующей формы для каждого коррелированного значения BrowserID:

DECLARE @BrowserID smallint;

SELECT 
    tfsph.BrowserID 
FROM dbo.tblFEStatsPaperHits AS tfsph 
WHERE 
    tfsph.BrowserID = @BrowserID 
OPTION (MAXDOP 1);

Сканирование бумажных снимков

Обратите внимание, что оценочное число строк 185,220 (не 289,013 ), так как сравнение равенства неявно исключает NULL (если ANSI_NULLS - OFF). Ориентировочная стоимость вышеуказанного плана составляет 206,8 .

Теперь добавим предложение TOP (1):

DECLARE @BrowserID smallint;

SELECT TOP (1)
    tfsph.BrowserID 
FROM dbo.tblFEStatsPaperHits AS tfsph 
WHERE 
    tfsph.BrowserID = @BrowserID 
OPTION (MAXDOP 1);

С TOP (1)

Ориентировочная стоимость теперь 0.00452 . Добавление верхнего физического оператора устанавливает a строка цели одной строки в операторе Top. Затем возникает вопрос, как получить «цель строки» для сканирования кластерного индекса; то есть, сколько строк должно ожидать сканирование перед тем, как одна строка будет соответствовать предикату BrowserID?

Доступная статистическая информация показывает 166 различные BrowserID (1 /[All Density] = 1 /0.006024096 = 166). Расчет стоимости предполагает, что отдельные значения распределяются равномерно по физическим строкам, поэтому для цели строки в Clustered Index Scan установлено значение 166.302 (с учетом изменения мощности таблицы, поскольку собранные данные были собраны).

Ориентировочная стоимость сканирования ожидаемых 166 строк не очень велика (даже выполняется 339 раз, один раз для каждого изменения BrowserID)). Сканирование кластерного индекса показывает ориентировочную стоимость 1.3219 , показывая эффект масштабирования цели строки. Расходы на немасштабированный оператор для ввода-вывода и процессора показаны как 153.931 и 52.8698 соответственно:

Масштабируемые оценочные затраты по шкале

На практике very маловероятно, что первые 166 строк, отсканированных из индекса (в любом порядке, в котором они будут возвращены) будут содержать один из возможных BrowserID значения. Тем не менее, DELETE рассчитывается на уровне 1,40921 , и по этой причине оптимизатор выбирается. Барт Дункан показывает другой пример этого типа в недавнем сообщении под названием Row Goals Gone Rogue .

Также интересно отметить, что оператор Top в плане выполнения не , связанный с Anti Semi Join (в частности, упоминание «короткого замыкания» Мартина). Мы можем начать видеть, откуда приходит Top, сначала отключив правило исследования, которое называется GbAggToConstScanOrTop :

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

GbAggToConstScanOrTop Disabled

Этот план имеет ориентировочную стоимость 364,912 и показывает, что Top заменил группу по совокупности (группировка по коррелированному столбцу BrowserID). Агрегат не из-за избыточного DISTINCT в тексте запроса: это оптимизация, которая может быть введена двумя правилами исследования: LASJNtoLASJNonDist и LASJOnLclDist . Отключение этих двух также создает этот план:

DBCC RULEOFF ('LASJNtoLASJNonDist');
DBCC RULEOFF ('LASJOnLclDist');
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('LASJNtoLASJNonDist');
DBCC RULEON ('LASJOnLclDist');
DBCC RULEON ('GbAggToConstScanOrTop');

План катушки

Этот план имеет ориентировочную стоимость 40729.3 .

Без преобразования из Group By в Top оптимизатор «естественно» выбирает план хеш-соединения с агрегацией BrowserID до анти-объединения:

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

Нет Top DOP 1 Plan

И без ограничения MAXDOP 1 параллельный план:

Нет верхнего параллельного плана

Еще один способ «исправить» исходный запрос - создать отсутствующий индекс в BrowserID, который сообщает отчет о плане выполнения. Вложенные циклы лучше всего работают при индексировании внутренней стороны. Оценка мощности для полусоединений сложна в лучшие времена. Не имея правильной индексации (большая таблица даже не имеет уникального ключа!) Не поможет вообще.

Пол

ответил Paul White 3 J0000006Europe/Moscow 2012, 13:51:28
21

Когда я запускаю ваш скрипт для создания базы данных только для статистики и запроса в вопросе, я получаю следующий план.

План

Показатели таблицы, указанные в плане,

  • tblFEStatsPaperHits: 48063400
  • tblFEStatsBrowsers: 339

Итак, он оценивает, что ему нужно будет выполнить сканирование на tblFEStatsPaperHits 339 раз. Каждое сканирование имеет коррелированный предикат tblFEStatsBrowsers.BrowserID=tblFEStatsPaperHits.BrowserID AND tblFEStatsPaperHits.BrowserID IS NOT NULL, который вытолкнут в оператор сканирования.

План не означает, что будет 339 полных сканирований. Поскольку он находится под оператором anti semi join, как только будет найдена первая соответствующая строка для каждого сканирования, она может коротко закоротить оставшуюся часть. Оценочная стоимость поддерева для этого узла составляет 1.32603, а весь план оценивается по 1.41337.

Для Hash Join он дает план ниже

Hash Join

Общий план рассчитан на 418.415 (примерно в 300 раз дороже плана вложенных циклов) с одним полным сканированием с кластерным индексом на tblFEStatsPaperHits стоимостью 206.8. Сравните это с оценкой 1.32603 для 339 частичных сканирований, приведенных ранее (средняя оценочная стоимость частичного сканирования = 0.003911592).

Таким образом, это будет означать, что стоимость каждого частичного сканирования составляет 53 000 раз дешевле, чем полное сканирование. Если стоимостные затраты были линейно масштабированы с подсчетом строк, это означало бы, что он предполагает, что в среднем ему нужно будет обрабатывать только 900 строк на каждой итерации, прежде чем он найдет подходящую строку и может коротко замыкаться.

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

SELECT TOP 147 BrowserID 
FROM [dbo].[tblFEStatsPaperHits] 

147 дает ближайшую оцененную стоимость поддерева 0.003911592 в 0.0039113. В любом случае понятно, что он основывает калькуляцию исходя из предположения, что для каждого сканирования потребуется обработать только небольшую часть таблицы в порядке сотен строк, а не миллионов.

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

ответил Martin Smith 2 J0000006Europe/Moscow 2012, 16:38:51
20

В моей книге даже одно сканирование 50M строк неприемлемо ... Моя обычная трюк заключается в материализации отдельных значений и делегировании движка с сохранением его актуальности:

create view [dbo].[vwFEStatsPaperHitsBrowserID]
with schemabinding
as
select BrowserID, COUNT_BIG(*) as big_count
from [dbo].[tblFEStatsPaperHits]
group by [BrowserID];
go

create unique clustered index [cdxVwFEStatsPaperHitsBrowserID] 
  on [vwFEStatsPaperHitsBrowserID]([BrowserID]);
go

Это дает вам материализованный индекс по одной строке на один браузер, исключая необходимость сканирования строк 50M. Двигатель будет поддерживать его для вас, и QO будет использовать его как «как есть» в заявлении, которое вы разместили (без каких-либо подсказок или перезаписи запроса).

Недостатком, конечно же, является утверждение. Любая операция вставки или удаления в tblFEStatsPaperHits (и я думаю, это таблица протоколирования с тяжелыми вставками) должна будет сериализовать доступ к определенному идентификатору браузера. Есть способы сделать это работоспособным (отложенные обновления, 2-х ступенчатых протоколирования и т. Д.), Если вы захотите их купить.

ответил Remus Rusanu 3 J0000006Europe/Moscow 2012, 01:25:37

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

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

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