SQL Server: поиск или сканирование. Проблема возрастающего ключа
Содержание:
1.Проблема возрастающего ключа (Вы читаете данный раздел);
2. Если столбец неуникальный и относится к целому или числовому типу, начиная с 0;
3. Если столбец уникален и не относится к целому или числовому типу, начиная с 0.
Сейчас будет рассмотрена типовая ситуационная задача, результат которой сводится к сопоставлению сканирования и поиска. Проще всего ее продемонстрировать, используя в качестве примера таблицу Orders2 с 900000 строк. Итак, для начала создаем таблицу, используя код, представленный ниже.
Тестовая таблица Orders2
Для применения ограничения первичного ключа SQL Server создает некластеризованный индекс PK_Orders2 со столбцом orderid в качестве ключа. В процессе создания индекса строится гистограмма по столбцу orderid. Для анализа гистограммы выполним код:
Гистограмма
Результат выполнения кода показан в таблице выше.
План с высокой избирательностью
План с низкой избирательностью
В данный момент все значения orderid из таблицы моделируются гистограммой; минимальное значение — 1, максимальное — 900000. Выполняя запросы с применением фильтра к диапазону значений orderid, мы должны получить точные оценки числа элементов и, следовательно, оптимальные планы. Рассмотрим, например, два запроса, приведенные в коде ниже.
Пример двух запросов
Первый запрос отфильтровывает малое количество строк, а второй — большое. Планы запросов показаны на скриншотах выше. Посмотрим, насколько точна оценка числа элементов в обоих случаях. План запроса с высокой избирательностью (малое число отфильтрованных строк):
• предусматривает применение поиска и нескольких уточняющих запросов;
• является последовательным;
• применяет сортировку с последующим применением агрегата потока. Проверить это можно, к примеру, используя MS Access (http://microsoft-free.com/microsoft-access/ (http://microsoft-free.com/microsoft-access/));
• имеет достаточно выделенной памяти для операций сортировки (без предупреждений о сбросе данных).
План запроса с низкой избирательностью (большое число отфильтрованных строк):
• предусматривает применение сканирования;
• является параллельным;
• применяет локальный агрегат хеша и глобальный агрегат потока, чему предшествует операция сортировки;
• имеет достаточно выделенной памяти для операций хеширования и сортировки (без предупреждений о сбросе данных).
Проблема возрастающего ключа обычно возникает при запрашивании последнего диапазона в столбце, где значения постоянно возрастают. Например, в нашей таблице Orders2 постоянно увеличиваются значения ID новых заказов. Гистограмма обычно обновляется через каждые (500 + 20% числа записей) изменений в столбце. В нашей таблице в очередной раз это произойдет после добавления 180500 строк. Это означает, что обычно последний добавленный диапазон записей гистограммой не моделируется. Для примера выполним код, добавляющий 100000 строк, запросим таблицу и вновь проанализируем гистограмму (см. код ниже).
Добавление большого числа строк
В результате получаем гистограмму, аналогичную приведенной в таблице выше, согласно которой максимальным зарегистрированным значением по-прежнему является 900000, то есть 100000 новых значений гистограммой не моделируются. То, что теперь будет происходить при запросе диапазона, выходящего за рамки максимального значения в гистограмме, очень интересно, а в некоторых случаях — неожиданно. Метод, используемый SQL Server для оценки числа элементов в подобном случае, зависит от ряда факторов: версии блока оценки числа элементов (CE) (более старая, то есть до SQL Server 2014, или более новая), типа столбца (целый или числовой начиная с 0 или другой), а также от уникальности столбца. Если столбец уникален и относится к целому или числовому типу начиная с 0.
1.
2. Если столбец неуникальный и относится к целому или числовому типу, начиная с 0;
3. Если столбец уникален и не относится к целому или числовому типу, начиная с 0.
Сейчас будет рассмотрена типовая ситуационная задача, результат которой сводится к сопоставлению сканирования и поиска. Проще всего ее продемонстрировать, используя в качестве примера таблицу Orders2 с 900000 строк. Итак, для начала создаем таблицу, используя код, представленный ниже.
Тестовая таблица Orders2
Для применения ограничения первичного ключа SQL Server создает некластеризованный индекс PK_Orders2 со столбцом orderid в качестве ключа. В процессе создания индекса строится гистограмма по столбцу orderid. Для анализа гистограммы выполним код:
DBCC SHOW_STATISTICS (‘dbo.Orders2’ 'PK_Orders2') WITH HISTOGRAM;
Гистограмма
Результат выполнения кода показан в таблице выше.
План с высокой избирательностью
План с низкой избирательностью
В данный момент все значения orderid из таблицы моделируются гистограммой; минимальное значение — 1, максимальное — 900000. Выполняя запросы с применением фильтра к диапазону значений orderid, мы должны получить точные оценки числа элементов и, следовательно, оптимальные планы. Рассмотрим, например, два запроса, приведенные в коде ниже.
Пример двух запросов
Первый запрос отфильтровывает малое количество строк, а второй — большое. Планы запросов показаны на скриншотах выше. Посмотрим, насколько точна оценка числа элементов в обоих случаях. План запроса с высокой избирательностью (малое число отфильтрованных строк):
• предусматривает применение поиска и нескольких уточняющих запросов;
• является последовательным;
• применяет сортировку с последующим применением агрегата потока. Проверить это можно, к примеру, используя MS Access (http://microsoft-free.com/microsoft-access/ (http://microsoft-free.com/microsoft-access/));
• имеет достаточно выделенной памяти для операций сортировки (без предупреждений о сбросе данных).
План запроса с низкой избирательностью (большое число отфильтрованных строк):
• предусматривает применение сканирования;
• является параллельным;
• применяет локальный агрегат хеша и глобальный агрегат потока, чему предшествует операция сортировки;
• имеет достаточно выделенной памяти для операций хеширования и сортировки (без предупреждений о сбросе данных).
Проблема возрастающего ключа обычно возникает при запрашивании последнего диапазона в столбце, где значения постоянно возрастают. Например, в нашей таблице Orders2 постоянно увеличиваются значения ID новых заказов. Гистограмма обычно обновляется через каждые (500 + 20% числа записей) изменений в столбце. В нашей таблице в очередной раз это произойдет после добавления 180500 строк. Это означает, что обычно последний добавленный диапазон записей гистограммой не моделируется. Для примера выполним код, добавляющий 100000 строк, запросим таблицу и вновь проанализируем гистограмму (см. код ниже).
Добавление большого числа строк
В результате получаем гистограмму, аналогичную приведенной в таблице выше, согласно которой максимальным зарегистрированным значением по-прежнему является 900000, то есть 100000 новых значений гистограммой не моделируются. То, что теперь будет происходить при запросе диапазона, выходящего за рамки максимального значения в гистограмме, очень интересно, а в некоторых случаях — неожиданно. Метод, используемый SQL Server для оценки числа элементов в подобном случае, зависит от ряда факторов: версии блока оценки числа элементов (CE) (более старая, то есть до SQL Server 2014, или более новая), типа столбца (целый или числовой начиная с 0 или другой), а также от уникальности столбца. Если столбец уникален и относится к целому или числовому типу начиная с 0.
