Настройка производительности запросов к хранилищу данных

Статистическая коллекция MS SQL Server

Оптимизатор СУБД семейства MS SQL Server собирает следующую коллекцию статистической информации уровня таблиц, которая является частью объекта статистики, и использует ее и для оценки стоимости запроса:

• число строк в таблице или индексе (поле rows в sys.sysindexes );
• число страниц, занятых таблицей или индексом (поле dpages в sys.sysindexes ).

Оптимизатор СУБД семейства MS SQL Server собирает следующую статистику по столбцам таблицы и сохраняет ее в объекте статистики (statblob):

• время, когда были собраны статистические данные;
• число строк, используемое для создания гистограммы, и информация о плотности (описано ниже);
• средняя длина ключа;
• гистограмма отдельного столбца, включая номера шагов;
• резюме по строке, если поле содержит символьные данные. Результат, выводимый DBCC SHOW_STATISTICS, содержит столбец String Index, который принимает значение YES, если объект статистики содержит резюме для строки.

Гистограмма — это набор значений данного поля, ограниченный до 200 значений. Все значения поля, или выборка из них, отсортированы, и эта упорядоченная последовательность может быть разделена не более чем на 199 интервалов так, чтобы фиксировалась наиболее статистически важная информация. Как правило, эти интервалы имеют разные размеры. Ниже представлены значения или информация, достаточная для получения такой информации и сохраняемая для каждого шага в гистограмме.

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

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

• Histogram initialization: инициализация гистограммы является первым шагом, на котором идет работа по сбору последовательности значений, начинающихся с начала отсортированного набора и до 200 значений RANGE_HI_KEY, EQ_ROWS, RANGE_ROWS и DISTINCT_RANGE_ROWS ( RANGE_ROWS и DISTINCT_RANGE_ROWS на этом шаге всегда равны нулю). Первый шаг заканчивается, если были пройдены все полученные на входе значения или если были найдены первые 200 значений.
• Scan with bucket merge: сканирование со слиянием в диапазоны является вторым шагом, на котором, в порядке сортировки, обрабатывается каждое дополнительное значение первого столбца ключа статистики. Каждое значение в последовательности может быть добавлено к последнему диапазону или в новый диапазон, создаваемый в конце существующих диапазонов (это возможно потому, что входные значения отсортированы). Если был создан новый диапазон, то одна пара из существующих соседних диапазонов будет объединена в единый диапазон. Эта пара диапазонов выбирается из соображений предотвращения потери информации. Число шагов после слияния диапазонов остается в пределах 200. Этот метод основан на вариации maxdiff гистограммы.
• Histogram consolidation: консолидация гистограммы составляет третий шаг, на котором может быть подвержено слиянию еще больше число диапазонов, если при этом не будет потерян существенный объем информации. Поэтому, даже если столбец имеет более 200 уникальных значений, число шагов гистограммы может быть меньше 200.

Если гистограмма была сформирована с использованием выборки, то значения RANGE_ROWS, EQ_ROWS, DISTINCT_RANGE_ROWS и AVG_RANGE_ROWS будут иметь оценки и поэтому не будут целыми числами.

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

В дополнение к timestamp (показывающему время, когда были собраны статистические данные), числу строк в таблице, числу отобранных для создания гистограммы строк, плотности, информационной и средней длине ключа и непосредственно к самой гистограмме, статистическая информация по одному столбцу включает еще значение All density, формируемое для каждого набора столбцов и определяющее префикс набора статистики столбца. Это значение можно увидеть во втором блоке строк, выводимом командой DBCC SHOW_STATISTICS. All density представляет из себя оценку: 1 / (число различающихся значений в префиксном наборе столбца). В следующем абзаце поясняется смысл этого значения.

Каждый набор статистики по нескольким столбцам (гистограмма и два или более значения плотности) хранится в одном statblob вместе с timestamp последнего обновления статистики, числом строк в типичной для сбора статистике выборке, числом шагов в гистограмме и средней длиной ключа. Резюме по строке создается только для первого столбца, если он содержит символьные данные.

sp_helpindex и sp_helpstats показывают списки статистик, доступные для анализируемой таблицы. sp_helpindex показывает все индексы таблицы, а sp_helpstats — список всех статистик по таблице. Каждый индекс также имеет статистическую информацию для ее столбцов. Создаваемая с использованием команды CREATE STATISTICS статистическая информация эквивалентна статистике, сформированной командой CREATE INDEX, если индекс создается на тех же столбцах. Единственная разница — при использовании команды CREATE STATISTICS будет задействована используемая по умолчанию выборка, в то время как для команды CREATE INDEX сбор статистики будет сопровождаться полным сканированием таблицы, так как в любом случае для построения индекса будут обработаны все строки таблицы.

Анализ запросов с целью повышения скорости их выполнения

Рассмотрим теперь общую процедуру настройки команды SELECT, результат выполнения которой не удовлетворяет требованиям производительности. Эта процедура является итерацией на пути построения оптимального набора индексов и состоит из семи шагов. При обсуждении этой процедуры мы будем ориентироваться на СУБД семейства MS SQL Server.

Шаг 1. Обновить статистику. До того, как добавить индексы, необходимо убедиться, что статистика БД в системном каталоге корректна. Если вы выполняете запрос без учета действительной производительности БД, вам следовало бы обновить статистику для всех таблиц, указанных в предложении FROM, используя команду UPDATE STSTISTICS или другую специальную команду СУБД. С другой стороны, если вы используете небольшую тестовую БД, то можно вручную вычислить необходимые статистические показатели и внести их в системный каталог.

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

Шаг 2. Упростить команду SELECT. Перед добавлением индексов или переписыванием плана выполнения следует попытаться упростить запрос. Задача состоит в том, чтобы сделать выражение SELECT как можно проще, сократив по мере возможностей число переменных в нем. Упростив запрос, скомпилируйте команду, чтобы посмотреть план запроса. Сравните новый план запроса со старым. Определите, увеличилась ли производительность запроса, выполнив его.

Для того чтобы упростить SELECT, необходимо:

• исключить ненужные предикаты и предложения;
• расставить скобки в арифметических и логических выражениях;
• преобразовать связанные переменные в константы.

Исключение ненужных предикатов и предложений. Обычно в команду SELECT включают предложений больше, чем это необходимо на самом деле. Так делается для того, чтобы гарантировать корректность ответа, или из-за плохого понимания синтаксиса SQL. До попытки настроить запрос проектировщик базы данных должен удалить все ненужные предложения и предикаты.

Примерами предложений, которые обычно включаются в запрос, но не являются необходимыми, являются:

• предложение ORDER BY. Часто это предложение включается, даже если определенный порядок в результирующем множестве не требуется приложением или конечным пользователем;
• предикаты предложения WHERE. Часто это предложение содержит избыточное множество предикатов ограничения. Например, предикаты в следующем предложении WHERE являются избыточными, так как DEPT_NO есть первичный ключ, и, следовательно, будет уникально идентифицировать только одну строку:

  WHERE DEPT_NO = 10 AND DEPT_NAME = 'PERATIONS'.

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

Преобразование связанных переменных в константы. Связанные переменные обычно применяются, когда компилируется команда SQL. Часто желательно использовать хранимые команды для того, чтобы исключить перекомпиляцию утверждения SQL, которое будет выполняться много раз. Эти хранимые команды являются скомпилированными, и их планы выполнения сохраняются в системном каталоге вместе с исходным текстом. Приложения тоже выполняют предкомпиляцию для многократно применяемых команд выборки. В этих ситуациях связанные переменные используются для передачи некоторых значений в команду во время ее выполнения. Однако когда связанные переменные задействуются, оптимизатор не знает, какие границы переменных положены при выполнении команды. Это вынуждает оптимизатор запросов делать грубые предположения при вычислении фактора селективности. Такие предположения могут привести оптимизатор к вычислению большого фактора селективности и применению специфических индексов.

Следовательно, при настройке запроса следует преобразовать все связанные переменные в константы таким образом, чтобы оптимизатор имел определенные значения для своей работы.

При использовании связанных переменных в предикате селекции оптимизатор запросов вынужден использовать умалчиваемый фактор селективности 1/3.

Рассмотрим следующий пример.

Пример 24.2. Предикаты: Amount > :BindVar и Amount > 1000. Преимущество применения первого предиката состоит в том, что команда может быть откомпилирована один раз и затем много раз использована с различными значениями. Недостаток состоит в том, что оптимизатор запросов имеет меньше информации для его оценки во время компиляции. Он не знает, будет ли 10 или 10000000 стоять вместо связанной переменной. Следовательно, он не способен вычислить корректно фактор селективности. В этой ситуации будет установлено значение по умолчанию, которое не обязательно будет отражать истинную ситуацию в данных. Поскольку значение фактора селективности, равное 1/3, относительно высокое (при отсутствии других предикатов), оптимизатор не может использовать какой-либо индекс, связанный с колонкой в этом предикате.

С другой стороны, во втором предикате оптимизатор знает сравниваемое значение и во время компиляции, и во время выполнения и, следовательно, способен более точно вычислить фактор селективности.

Связанные переменные также приводят к проблеме при использовании предиката оператора LIKE. Этот оператор может включать символ подстановки.

Пример 24.3. Рассмотрим запрос на поиск всех продавцов, чье имя начинается с буквы "А":

SELECT * FROM CUSTOMER WHERE NAME LIKE 'A%';

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

Преобразовывайте связанные переменные в предикате LIKE в константы для увеличения производительности такого запроса.

Шаг 3. Пересмотреть план запроса. Выполните запрос так, чтобы посмотреть его план. Вы должны хорошо понимать план запроса, чтобы использовать его. Несколько элементов этого плана требуют особого внимания.

• Преобразование подзапроса в соединение. Оптимизатор преобразует большинство подзапросов в соединения. Нужно знать, на каких этапах выполнения запроса это преобразование происходит.
• Когда будут создаваться временные таблицы. Создание временных таблиц может указывать, что оптимизатор сортирует промежуточные результаты. Если это происходит, можно попробовать добавить индекс на одном из следующих шагов настройки, для того чтобы избежать сортировки.
• Медленные методы соединения. Хеш-соединение и методы вложенного соединения не являются столь же быстрыми, как метод слияния индексов для больших таблиц. Если эти методы используются, можно попробовать добавить индекс в шагах 5 и 6 настроек команды SELECT, так чтобы соединения применяли бы метод слияния индексов. Иногда хеш-соединение может представлять лучший метод соединения, когда обрабатывается большое количество данных.

Шаг 4. Локализовать узкие места. Запрос, который выполняется медленно, может содержать много предложений и предикатов. Если это так – нужно определить, какие предложения или предикаты приводят к плохой производительности. Если удалить одно или два предложения или предиката, производительность выполнения запроса возрастает значительно. Эти предложения являются критическими параметрами выполнения запроса.

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

Примеры.

• Если запрос содержит предложение ORDER BY, закомментируйте его и посмотрите, изменится ли план этого запроса. Если план изменился, выполните запрос, чтобы определить, увеличилась ли производительность.
• Если запрос содержит несколько соединений, локализуйте то, которое замедляет выполнение. Комментируйте последовательно все соединения, кроме одного, и выполняйте запрос. Определите, какое соединение самое критичное.
• Исключите любые АТ-функции (@), выполняющиеся в WHERE, и посмотрите, не выросла ли производительность. Может быть, индекс не работает из-за применения функции. В этом случае можно построить индекс с использованием этой функции.

Шаг 5. Создать индексы для одной колонки для критических параметров. Если завершены шаги 1-4 и запрос все еще не удовлетворяет требованиям производительности, можно попытаться создать индексы специально для этого запроса, чтобы увеличить его производительность. В общем, индексы для одной колонки предпочтительнее, чем составные индексы, так как более вероятно их использование в других запросах. Попытайтесь увеличить производительность запроса с индексом для одной колонки, прежде чем разрабатывать многоколоночные индексы.

Локализуйте следующие колонки таблицы из запроса, которые не имеют индекса.

• Колонки соединения. При этом следует рассмотреть также подзапросы, которые оптимизатор преобразует в соединения. Если первичный ключ таблицы является составным ключом, соединение специфицируется через несколько колонок и составной индекс необходим для увеличения производительности этого соединения. Этот индекс должен уже существовать.
• Колонки GROUP BY. Если предложение GROUP BY содержит более чем одну колонку, необходим составной индекс для увеличения производительности этого GROUP BY предложения. Отложите создание этих колонок до шага 6.
• Колонки ORDER BY. Если предложение ORDER BY содержит более чем одну колонку, необходим составной индекс для увеличения производительности этого ORDER BY-предложения. Отложите создание этих колонок до шага 6.
• Низкая стоимость предикатов. Это колонки таблицы, которые указываются в предикатах выборки или селекции WHERE-предложения, обладающие низким значением фактора селективности для таблиц из предложения FROM.

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

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

Шаг 6. Создать индексы для нескольких колонок. Процедура идентификации колонок для создания составных индексов состоит в следующем.

1. Создать составной индекс.
2. Определить, изменился ли план запроса. Если изменился – выполнить запрос, чтобы определить, увеличилась ли производительность.
3. Если производительность не увеличилась – создайте другой индекс и повторите процесс.

Типы составных индексов приведены ниже.

• Несколько колонок в предложении GROUP BY. Если предложение GROUP BY содержит более чем одну колонку – создайте индекс для всех этих колонок. Специфицируйте колонки в том же порядке, в котором они указаны в предложении.
• Несколько колонок в предложении ORDER BY. Если предложение ORDER BY содержит более чем одну колонку – создайте индекс для всех этих колонок. Специфицируйте колонки в том же порядке, в котором они указаны в предложении. Также не забудьте указать последовательность сортировки для индекса.
• Колонки соединения плюс низкая стоимость ограничений. Для каждой таблицы из предложения FROM, которая имеет по крайней мере один предикат в предложении WHERE, создайте составной индекс для колонок соединения и колонки из ограничивающего предиката с низким фактором селективности.
• Колонки соединения плюс все ограничения. Для каждой таблицы из предложения FROM, которая имеет по крайней мере один предикат в предложении WHERE, создайте составной индекс для колонок соединения и всех колонок из всех предикатов. Порядок колонок в индексе является критическим для оптимизатора. Колонки из предикатов равенства следует размещать ранее всех других колонок в порядке возрастания фактора селективности. Далее следует размещать колонки из предикатов неравенства, которые имеют наименьший фактор селективности, и так до последней колонки индекса. Все другие колонки в предикатах, вероятно, не будут сильно влиять на производительность предложения.

Шаг 7. Удалить все индексы, которые не используются в плане запроса. Как уже указывалось выше, индексы замедляют выполнение команд DML, а их сопровождение требует времени и увеличивает стоимость обработки. Следовательно, вам следует проследить за использованием всех созданных индексов и удалить те, которые не используются запросом.