Введение в статистику

Пересказ статьи Johnny Bieber. Introduction to Statistics

SQL Server использует статистику для создания планов запроса, которые улучшают производительность. При написании запросов есть простые правила, например, когда использовать левые соединения, когда внутренние и т.д. Оптимизатор запросов, с другой стороны, использует статистику, чтобы решить, каким именно образом извлекать данные и возвращать их пользователю. Если вы выполняете один и тот же запрос на двух разных базах данных с аналогичной схемой, одной размером 500Мб, а другой — 500Гб, вы, вероятно, можете получить два различных плана выполнения, несмотря на то, что соединяете те же самые таблицы. План выполнения генерируется на основе статистики.

Прежде чем вникать в то, как SQL Server создает и использует статистику, познакомимся с понятием гистограммы. Гистограмма — это графическое представление распределения данных. Простой пример гистограммы, созданной в Excel, показан ниже. В этом примере у нас есть группа из 188 людей, и мы хотим разделить их на бункеры по возрасту. Имеется формула для ширины бункера, но для этого примера я просто выбрал бункеры равной ширины. Вы можете увидеть 6 групп, или бункеров, наряду с числом людей, которые попали в каждый из этих возрастных диапазонов. Гистограмма — это график, построенный на данных и показывающий распределение людей по каждому из возрастных диапазонов. Как видно, в диапазоне 26-35 содержится наибольшее число людей.

Теперь, понимая что представляет собой гистограмма, перейдем к её применению в SQL Server. Статистика — это объекты, хранящие в базе данных информацию о распределении данных в заданных полях таблицы. Статистика создается в нескольких сценариях. Во-первых, когда создаются индексы, статистика будет генерироваться для ключевого столбца (столбцов) индекса. Во-вторых, статистика будет генерироваться для отдельных столбцов, которые используются в предикатах запросов, когда в базе данных включен параметр AUTO_CREATE_STATISTICS. Наконец, статистика может создаваться вручную по команде CREATE STATISTICS.

Теперь мы рассмотрим базовый пример статистики в SQL Server. Сначала мы создадим таблицу с именем Ages и наполнил ее 188 записями, т.е. то же самое количество, что и в ранее рассмотренном примере из Excel. Я только делаю возрастной диапазон от 0 до 10, чтобы легче было наблюдать статистику на этом примере.

IF EXISTS
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Ages', N'U'))
BEGIN
    DROP TABLE dbo.Ages
    ;
END
;
GO
CREATE TABLE dbo.Ages (AGE INT)
;
INSERT INTO dbo.Ages (AGE)
SELECT TOP 188 ABS(CHECKSUM(NEWID())) % 10 FROM sys.objects
;
SELECT AGE FROM dbo.Ages
;

Теперь, когда таблица создана, мы можем взглянуть на sys.stats для этого объекта. Как можно увидеть ниже, пока нет никакой статистики для этой таблицы, поскольку мы ничего не создавали вручную, не создавали индекс, и не использовали столбец в предикате.

SELECT
      object_id
    , name
    , stats_id
    , auto_created
    , user_created
    , no_recompute
    , has_filter
    , filter_definition
    , is_temporary
    , is_incremental
FROM  sys.stats
WHERE object_id = OBJECT_ID(N'dbo.ages', N'U')
;

Теперь мы можем выполнить запрос к этой таблице и использовать столбец в предикате. Как только мы выполним этот запрос, мы можем опять посмотреть sys.stats для этого объекта и увидеть, что там появился вход.

SELECT * 
FROM   dbo.ages 
WHERE  age > 5;
SELECT * 
FROM   sys.stats 
WHERE  object_id = object_id(N'dbo.ages', N'U');

Теперь, когда мы знаем имя созданной статистики, мы можем посмотреть фактическую информацию, хранящуюся об этом столбце в таблице Ages, с помощью команды DBCC SHOW_STATISTICS, например:

DBCC SHOW_STATISTICS (N'dbo.ages', N'_WA_Sys_00000001_551CD3E1');

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

  • RANGE_HI_KEY — это верхний предел диапазона значений, которые попадают в этот шаг. Так в нашем примере первая строка имеет 0. Поскольку все значения в нашей таблице положительны, это означает, что этот шаг содержит только значение 0.
  • RANGE_ROWS — это будет приблизительное число строк, которые попадают в этот шаг, не включая верхний предел. Как можно увидеть, большинство значений тут нулевые, за исключением RANGE_HI_KEY=5, которое имеет несколько интересных результатов. Как видно, тут нет шага для RANGE_HI_KEY=4, поэтому 4 и 5 попадают в шаг с RANGE_HI_KEY=5. Число 19 относится к 19-ти записям со значением 4 в этом шаге.
  • EQ_ROWS — это приблизительное число строк, которые равны значению RANGE_HI_KEY. В данном примере опять нет ничего особо интересного за исключением RANGE_HI_KEY=5, где приближенное число со значением 5 равно 19.
  • DISTINCT_RANGE_ROWS — это будет приближенное число различных значений за исключением верхних границ. В примере можно увидеть только одну строку со значением, отличным от нуля — это RANGE_HI_KEY=5, поскольку, как упоминалось ранее, этот шаг включает 4 и 5.
  • AVG_RANGE_ROWS — это вычисленное значение по формуле RANGE_ROWS / DISTINCT_RANGE_ROWS для DISTINCT_RANGE_ROWS > 0.

Мы познакомились с основами статистики и тем, как она создается. Это важно, так как оптимизатор запросов использует её для выбора правильного индекса и, что более эффективно, для принятия решения о сканировании или же поиска по индексу и т.д.

Добавить комментарий