Как не работают многоколоночные статистики

Автор: Brent Ozar, How Multi-Column Statistics Work

Короткий ответ: в реальном мире работает только первая колонка. Когда SQL Server нужны данные о второй колонке, он строит собственную статистику по этой колонке (предполагая, что она ещё не существует) и использует эти две статистики вместе — но они не коррелируют друг с другом.

Для более подробного ответа давайте возьмём большую версию базы данных Stack Overflow, создадим двухколоночный индекс на таблице Users, а затем посмотрим на полученную статистику:

DropIndexes;
GO
CREATE INDEX Location_Reputation
    ON dbo.Users(Location, Reputation);
GO
DBCC SHOW_STATISTICS('dbo.Users', 'Location_Reputation');
GO

Вывод команды DBCC SHOW_STATISTICS показывает, что в этой таблице около 22 миллионов строк. Итак, что же говорит гистограмма статистики о связях между местоположениями и уровнями репутации?

В первом результирующем наборе вы можете видеть, что Rows = 22 484 235, а Rows Sampled = 22 484 235 — то же самое число. Это означает, что наша статистика была построена на основе полного сканирования, что является наилучшим возможным вариантом.

Векторы плотности не очень полезны.

Второй результирующий набор — это векторы плотности (они же средние значения), и он содержит 3 строки. Первая строка говорит: Location: all density = 3.282714E-06. Если взять это число и умножить на количество строк в таблице (22 484 235), получится 73.8093. Это и есть вектор плотности: если SQL Server нужно оценить, сколько строк будет соответствовать заданному местоположению, и он не знает, что это за местоположение, он оценит 73.8093.

Вот пример запроса, доказывающий это. Я использую локальную переменную, чтобы предотвратить Sniffing параметров, поэтому во время компиляции SQL Server не будет иметь представления о том, каким будет значение @LocationUnknown:

DECLARE @LocationUnknown 
    NVARCHAR(200) = N'Las Vegas, NV';
 
SELECT COUNT(*) FROM dbo.Users 
WHERE Location = @LocationUnknown;

В полученном плане запроса при наведении мыши на оператор Index Seek и просмотре «Estimated Number of Rows Per Execution» вы получите 73.8093.

Вернёмся к нашему выводу DBCC SHOW_STATISTICS. Мы объяснили, что первая строка была вектором плотности для одной колонки Location. Что же означает вторая строка, которая говорит Location, Reputation?

Что означает число «1.058257E-06» в колонке Location, Reputation? Умножьте его на количество строк в таблице (22 484 235), и вы получите 23.794. Держу пари, вы уже догадываетесь, к чему я клоню:

DECLARE @LocationUnknown NVARCHAR(200) = N'Las Vegas, NV',
        @ReputationUnknown INT = 1234;
 
SELECT COUNT(*) FROM dbo.Users 
WHERE Location = @LocationUnknown
AND Reputation = @ReputationUnknown;

Вот наш план запроса, и, бинго, 23.794 оценочных строк:

Если вы ищете неизвестную (или непредсказуемую до выполнения) комбинацию местоположения и репутации, SQL Server использует вектор плотности в гистограмме для вычисления количества совпадающих строк. SQL Server считает, что для любого заданного местоположения и репутации, независимо от того, какие значения вы передаёте, они дадут 23.7941.

На первый взгляд это звучит абсурдно: репутация — это целое число. Если вы выполняете поиск на равенство по этому числу и передаёте случайные числа, практически нет никакой вероятности, что эта оценка может быть верной. «В среднем», возможно, в пересчёте на тысячи или миллионы поисков, это могло бы быть приблизительным средним числом, но оно никогда не будет точным. В некоторых случаях оно будет сильно завышено, а в других — сильно занижено.

Но как насчёт гистограммы?

Следующий результирующий набор в DBCC SHOW_STATISTICS — это гистограмма, которая содержит подробный список до 201 значения местоположения, поскольку Location является первой колонкой в нашей статистике:

Давайте прокрутим вниз до района Вегаса:

Лас-Вегас не настолько сильный выброс, чтобы получить собственную корзину, поэтому если мы запросим людей, живущих в Лас-Вегасе:

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'Las Vegas, NV' AND 1 = (SELECT 1);

Обратите внимание, что я использую 1 = (SELECT 1), чтобы предотвратить автоматическую параметризацию, что является совершенно другой темой для отдельного обсуждения. Наведите мышь на план выполнения, чтобы увидеть предполагаемое количество строк:

Оценка 7.04485 получена из нашей статистики. Прокрутите назад на пару изображений и обратите внимание, что в нашей статистике я выделил строку для Lebanon. «Las Vegas, NV» находится где-то между «Lahore, Pakistan» и «Lebanon» (иногда так ощущается и на дороге), поэтому SQL Server использует число AVG_RANGE_ROWS, равное 7.044848.

Когда SQL Server выполняет поиск неизвестного местоположения, он использует вектор плотности. Когда он выполняет поиск известного местоположения, находящегося между двумя ключами диапазона, он использует число AVG_RANGE_ROWS. Пока всё хорошо.

Но что произойдёт, если мы передадим поиск известного местоположения И известной репутации?

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'Las Vegas, NV' 
AND Reputation = 1234
AND 1 = (SELECT 1);

Прежде чем мы посмотрим на план выполнения этого запроса, остановитесь на мгновение, чтобы проанализировать статистику, которая здесь задействована. Какой колонкой SQL Server воспользуется для своей оценки?

Какая колонка на приведённом выше изображении сообщает SQL Server, имеет ли конкретное местоположение более высокий или более низкий средний показатель репутации или как распределены значения?

Верно: такой колонки нет!

Эта статистическая гистограмма вообще не о второй колонке объекта. Она о первой колонке! Многоколоночные статистики на самом деле таковыми не являются: на самом деле это просто одноколоночные статистики!

Вот что поражает. Вот план запроса для Лас-Вегаса и 1234:

Выглядит знакомо? Это оценка 7.04485. Точно такая же, как если бы мы вообще не фильтровали по репутации. Он использует avg_range_rows из нашей статистики, давая нам ту же оценку, которую мы получили, фильтруя только по местоположению «Las Vegas».

Значения гистограммы для первой колонки нашего объекта действительно полезны. Последующие колонки — не очень.

Значение репутации, которое я ищу, здесь тоже не имеет значения. Давайте попробуем одно из самых больших значений: Reputation = 1. Вы поймёте, почему это важно позже:

Оценка по-прежнему 7.04485: точно такая же, как при отсутствии фильтрации по репутации. Это… не очень хорошо.

Для действительно больших выбросов ситуация немного меняется.

Давайте попробуем найти самое большое значение местоположения: India. Если мы ищем только значение местоположения (пока без репутации), гистограмма полезна, потому что India является одним из наших выбросов:

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'India' 
AND 1 = (SELECT 1);

Оценки в полученном плане выполнения абсолютно точны:

Затем добавьте фильтр по заданному числу репутации, и здесь я сделаю одно за другим и для 1234, и для 1:

SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'India' 
AND Reputation = 1234
AND 1 = (SELECT 1);
 
SELECT COUNT(*) FROM dbo.Users 
WHERE Location = N'India' 
AND Reputation = 1
AND 1 = (SELECT 1);

И что же мы видим: теперь наши оценки — не 7.04485, в отличие от Лас-Вегаса:

SQL Server каким-то образом понимает, что India не только огромна, но и что Reputation = 1 — это тоже огромное значение. Как же он это сделал? Щёлкните правой кнопкой мыши по оператору SELECT второго запроса, перейдите в Свойства (Properties), а затем в OptimizerStatsUsage:

SQL Server использовал не только статистику по Location_Reputation. Чтобы понять, что Reputation = 1 является выбросом, он также автоматически создал отдельную статистику по колонке Reputation, потому что данные о репутации в статистике Location_Reputation были недостаточно полезны.

Многоколоночные статистики сами по себе не очень помогают

Чтобы действительно это доказать, давайте создадим искусственный сценарий. Допустим, каждый в Китае имеет очень высокую репутацию. И, чтобы дать SQL Server наилучшую возможную защиту, давайте создадим многоколоночный индекс (и, следовательно, статистику) по колонкам Reputation, Location. Более того, давайте даже обновим статистику на Users, чтобы наша существующая статистика Location, Reputation полностью понимала, что в Китае живут умные люди:

UPDATE dbo.Users SET Reputation = 1000000
WHERE Location = N'China';
 
CREATE INDEX Reputation_Location
    ON dbo.Users(Reputation, Location);
 
UPDATE STATISTICS dbo.Users WITH FULLSCAN;

Если бы это была диаграмма Венна, у нас был бы идеальный круг: все люди в Китае имеют ровно 1 000 000 очков репутации, и единственные люди с ровно 1 000 000 очков репутации находятся в Китае:

SELECT COUNT(*) FROM dbo.Users
WHERE Location = N'China';
 
SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000;
 
SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000
AND Location <> N'China';

Результаты:

Итак, теперь давайте спросим SQL Server:

• Как вы думаете, сколько людей живёт в Китае?
• Как вы думаете, сколько человек имеют 1 000 000 очков репутации?
• Как вы думаете, сколько людей живёт в Китае И имеет 1 000 000 очков?

SELECT COUNT(*) FROM dbo.Users
WHERE Location = N'China' AND 1 = (SELECT 1);
 
SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000 AND 1 = (SELECT 1);
 
SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000
AND Location = N'China' AND 1 = (SELECT 1);

Посмотрите на предполагаемое количество строк в планах запросов:

Или для тех, кто предпочитает мемы:

Если бы в SQL Server было что-то отдалённо напоминающее истинные многоколоночные статистики, оценка была бы ближе к реальности. Но у нас их нет, поэтому и оценки неточные.

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

CREATE STATISTICS Stat_Location_Reputation
ON dbo.Users(Location, Reputation)
WITH FULLSCAN;
 
CREATE STATISTICS Stat_Reputation_Location
ON dbo.Users(Reputation, Location)
WITH FULLSCAN;
 
DBCC FREEPROCCACHE;
GO
SELECT COUNT(*) FROM dbo.Users
WHERE Location = N'China' AND 1 = (SELECT 1);
 
SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000 AND 1 = (SELECT 1);
 
SELECT COUNT(*) FROM dbo.Users
WHERE Reputation = 1000000
AND Location = N'China' AND 1 = (SELECT 1);

Но нет, это всё равно не работает и даёт ту же оценку в 89 строк.