Почему SQL Server не создает сложные гистограммы статистики столбцов?

SQL Server имеет вещь, называемую «статистикой с несколькими столбцами», но это не то, о чем, думаю, это будет означать.

Давайте рассмотрим следующую примерную таблицу:

CREATE TABLE BadStatistics 
(
    IsArchived BIT NOT NULL,
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    Mystery VARCHAR(200) NOT NULL
);

CREATE NONCLUSTERED INDEX BadIndex 
    ON BadStatistics (IsArchived, Mystery);

При этом создаются две статистики по двум указанным ниже индексам:

Статистика для BadIndex:

+--------------+----------------+-------------------------+
| All density  | Average Length | Columns                 |
+--------------+----------------+-------------------------+
| 0.5          | 1              | IsArchived              |
+--------------+----------------+-------------------------+
| 4.149378E-06 | 37             | IsArchived, Mystery     |
+--------------+----------------+-------------------------+
| 4.149378E-06 | 41             | IsArchived, Mystery, Id |
+--------------+----------------+-------------------------+

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| 0            | 0          | 24398   | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+
| 1            | 0          | 216602  | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+

Статистика для кластеризованного индекса:

+--------------+----------------+---------+
| All density  | Average Length | Columns |
+--------------+----------------+---------+
| 4.149378E-06 | 4              | Id      |
+--------------+----------------+---------+

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| 1            | 0          | 1       | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+
| 240999       | 240997     | 1       | 240997              | 1              |
+--------------+------------+---------+---------------------+----------------+
| 241000       | 0          | 1       | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+

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

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

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

10 голосов | спросил John 23 J000000Sunday17 2017, 13:46:02

1 ответ


8

Фон

Текущая модель SQL Server использует только одностолбцовые гистограммы и информацию о плотности нескольких столбцов. Гистограммы с одним столбцом используются для оценки избирательности для подходящих предикатов, например. a = 1 или b > 50. Запрос с несколькими предикатами просто сочетает в себе индивидуальную селективность (с предположениями) для получения оценки общей избирательности.

Пример см. в моей статье Оценка мощности: объединение статистики плотности

Многостолпная плотность далее информирует модель, предоставляя слабую корреляционную информацию для множественных предикатов равенства и группирующих мощностей для агрегаций.

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

Гистограммы для не ведущих столбцов в индексе могут автоматически создаваться процессором запросов по требованию или заранее использовать sp_createstats с параметр @indexonly (среди прочих).

Гистограммы с несколькими колонками

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

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

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

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

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

Все это добавляет размер, сложность и накладные расходы на обслуживание.

Статистика нескольких столбцов может быть смоделирована (в ограниченной степени) с использованием статистики, созданной на тщательно построенном вычисленном столбце, ссылающемся на несколько столбцов. Запрос должен включать предикат в вычисленный столбец (или точное текстовое соответствие для базовой формулы), чтобы воспользоваться этой статистикой. Вероятно, существуют только очень ограниченные ситуации, когда такой подход практичен. Тем не менее, он имеет некоторые из тех же проблем с реализацией, что и автоматические многоколонные гистограммы.

В конечном счете, единственные люди, которые могли бы точно сказать, почему SQL Server не поддерживает статистику с несколькими столбцами, были бы самими дизайнерами. Если вы чувствуете, что можете сделать сильный аргумент в пользу улучшения продукта в этой области с широкой применимостью, вы можете предложить его на Connect или через ваш обычный канал поддержки.

Сноска

  

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

Гистограмма по-прежнему предоставляет полезную информацию о распределении значений в ведущем столбце: при построении статистики было 24 398 строк, где IsArchived был false и 216,602 строк, где было true .

Кроме того, объект статистики сообщает нам, что существует (1 /0,5) = 2 различных значения для IsArchived, (1 /4.149378 E-06) ~ = 241000 различных значений для (IsArchived, Mystery) со средним размером строки 37 байт и одинаковой частотой для (IsArchived, Mystery, Id) с 4 дополнительными байтами в строке.

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

ответил Paul White 31 J000000Monday17 2017, 14:49:15

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

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

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