SQL-Ex blog

Прежде чем вникать в то, как 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.

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