Статистика и индексы SQL – Насколько они детализированы?

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

Предположим, у меня есть таблица со столбцом бит, называемым active, у которого есть миллион строк, но с 99,99% установленным значением false. Если у меня есть индекс в этом столбце, то Sql достаточно умен, чтобы знать, использовать индекс при поиске active = 1, но нет смысла, если вы ищете active = 0.

Другой пример, если у меня есть таблица, которая имеет 1,000,000 записей с индексированным столбцом, который содержит около 50 000 различных значений со средним числом строк на значение 10, но затем одно специальное значение, которое имеет 500 000 строк. Индекс может оказаться непригодным для поиска этой специальной записи, но будет очень полезен при поиске любого из других кодов.

Но этот особый случай разрушает эффективность индекса.

Он создает гистограмму и, таким образом, использует это.

С колонкой бит будет иметь хорошее представление, сколько 0 и 1

Со строковым столбцом у него будет общее представление о «полосах» (значение начинается с a, b, c и т. Д.). То же самое для чисел (он создает х диапазонов диапазонов значений).

Просто посмотрите, как статистика выглядит в вашей студии управления – вы можете фактически получить доступ к гистограммам.

Вы можете убедиться сами:

CREATE TABLE IndexTest ( Id int not null primary key identity(1,1), Active bit not null default(0), IndexedValue nvarchar(10) not null ) CREATE INDEX IndexTestActive ON IndexTest (Active) CREATE INDEX IndexTestIndexedValue ON IndexTest (IndexedValue) DECLARE @values table ( Id int primary key IDENTITY(1, 1), Value nvarchar(10) ) INSERT INTO @values(Value) VALUES ('1') INSERT INTO @values(Value) VALUES ('2') INSERT INTO @values(Value) VALUES ('3') INSERT INTO @values(Value) VALUES ('4') INSERT INTO @values(Value) VALUES ('5') INSERT INTO @values(Value) VALUES ('Many') INSERT INTO @values(Value) VALUES ('Many') INSERT INTO @values(Value) VALUES ('Many') INSERT INTO @values(Value) VALUES ('Many') INSERT INTO @values(Value) VALUES ('Many') DECLARE @rowCount int SET @rowCount = 100000 WHILE(@rowCount > 0) BEGIN DECLARE @valueIndex int SET @valueIndex = CAST(RAND() * 10 + 1 as int) DECLARE @selectedValue nvarchar(10) SELECT @selectedValue = Value FROM @values WHERE Id = @valueIndex DECLARE @isActive bit SELECT @isActive = CASE WHEN RAND() < 0.001 THEN 1 ELSE 0 END INSERT INTO IndexTest(Active, IndexedValue) VALUES (@isActive, @selectedValue) SET @rowCount = @rowCount - 1 END SELECT count(*) FROM IndexTest WHERE Active = 1 SELECT count(*) FROM IndexTest WHERE Active = 0 SELECT count(*) FROM IndexTest WHERE IndexedValue = '1' SELECT count(*) FROM IndexTest WHERE IndexedValue = 'Many' 

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

план запроса

Вы можете просто посмотреть статистику и посмотреть сами 🙂 DBCC SHOW_STATISTICS . См. Раздел «Замечания», в нем есть хорошее объяснение того, как фактически хранятся и используются гистограммы:

Чтобы создать гистограмму, оптимизатор запросов сортирует значения столбца, вычисляет количество значений, которые соответствуют каждому значению столбца, а затем агрегирует значения столбцов в максимум 200 смежных шагов гистограммы. Каждый шаг включает в себя диапазон значений столбцов, за которыми следует верхнее значение столбца. Диапазон включает все возможные значения столбцов между граничными значениями, за исключением самих граничных значений. Самый низкий из отсортированных значений столбца – это верхнее граничное значение для первого этапа гистограммы.

alt text

Для каждого этапа гистограммы:

  • Жирная линия представляет собой верхнее граничное значение (RANGE_HI_KEY) и количество раз (EQ_ROWS)
  • Твердая область слева от RANGE_HI_KEY представляет диапазон значений столбцов и среднее число раз, когда происходит каждое значение столбца (AVG_RANGE_ROWS). AVG_RANGE_ROWS для первого этапа гистограммы всегда 0.
  • Пунктирные линии представляют собой выборочные значения, используемые для оценки общего количества различных значений в диапазоне (DISTINCT_RANGE_ROWS) и общего количества значений в диапазоне (RANGE_ROWS). Оптимизатор запросов использует RANGE_ROWS и DISTINCT_RANGE_ROWS для вычисления AVG_RANGE_ROWS и не сохраняет выбранные значения.

Оптимизатор запросов определяет этапы гистограммы согласно их статистической значимости. Он использует алгоритм максимальной разности, чтобы минимизировать количество шагов в гистограмме, максимизируя разницу между граничными значениями. Максимальное количество шагов – 200. Количество шагов гистограммы может быть меньше числа различных значений, даже для столбцов с менее чем 200 граничными точками. Например, столбец со 100 различными значениями может иметь гистограмму с менее чем 100 граничными точками.

Interesting Posts

sql max / min запрос и преобразование данных

ошибка арифметического переполнения n (от uumeric to numeric) тип данных

Вычисление столбца при использовании ALIAS

Преобразование строк в значения столбцов – возврат одной строки

SQL Server 2005, нужен скрипт для проверки всех ограничений на таблицу

T-SQL JOIN между JOIN и ON

SQL Server показывает результаты, в которых количество разных столбцов выше 1

Альтернатива для LIKE и IN в SQL, где вы не можете отформатировать параметры ввода, предоставленные SSRS

Поставщик «SQLNCLI» не зарегистрирован на локальном компьютере, sql-сервер 2012

Установить значение переменной из case case

Самый быстрый способ проверить, является ли символ цифрой?

Соответствие sqlserver означает, что имена столбцов должны быть правильными. И как с этим бороться

Переслать пронумерованную ошибку в встроенной процедуре через внешний try catch

как шифровать столбец пароля

Обновление поведения

Давайте будем гением компьютера.