Книга: Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Улучшение плана запроса

Улучшение плана запроса

Скверно выполняющиеся запросы в Firebird наиболее часто являются следствием плохого задания индексов и неоптимальных запросов. В разд. "Тема оптимизации" главы 18 мы рассматривали влияние индексов с плохой селективностью. В настоящем разделе мы продолжим рассмотрение индексов и некоторых неприятностей, которые могут происходить с оптимизатором и разработчиком, когда индексы служат помехой эффективности поиска.

Не факт, что использование индексов в соединении или поиске сделает запрос более быстрым. В действительности существуют такие структуры метаданных и виды индексов, при которых выбор некоторых индексов резко снизит производительность в сравнении с естественным сканированием.

Дублирующиеся или перекрывающие друг друга индексы могут повлиять на использование оптимизатором других индексов. Удалите все индексы, которые дублируют автоматически создаваемые индексы для первичных или внешних ключей или для ограничений UNIQUE. Пары составных первичных и внешних ключей, особенно длинных или несущих смысловое содержание, могут сделать запросы уязвимыми с точки зрения производительности, привести к неправильному выбору индексов и к немалым человеческим ошибкам. При проектировании таблиц рассматривайте использование суррогатных ключей для отделения "осмысленных" столбцов для поиска и упорядочения от формального ключа для соединений.

Составные (сложные) индексы - это индексы, которые содержат более одного столбца. Хорошо продуманный составной индекс может увеличить скорость запросов и поиска в сложных конъюнктивных запросах (использующих логический оператор AND), особенно для больших таблиц или при низкой собственной селективности отыскиваемого столбца.

В составных индексах важным является ранг (количество элементов и относительная позиция слева направо). Оптимизатор может использовать один столбец составного индекса или подгруппу столбцов в операциях поиска, соединения или упорядочения, не включая оставшиеся столбцы индекса в операции. На рис. 22.8 представлены доступные для оптимизатора возможности по использованию составного индекса (COL1, C0L2, COL3).

Рис. 22.8. Возможности частичного индексного ключа

Если составной индекс может быть выгодно использован на месте одного или более индексов, состоящих из одного столбца, то имеет смысл создавать и тестировать такой индекс. Не существует преимущества в создании составных индексов для дизъюнктивных условий (использующих логический оператор OR) - они не будут использованы. Не поддавайтесь искушению создать составной индекс в надежде, что "один индекс подойдет во всех случаях". Создавайте индексы для определенных потребностей и будьте готовы уничтожить любой из них, который не будет работать хорошо.

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

Убедитесь в правильности ваших предположений по поводу эффективности добавления составного индекса путем тестирования не только одного запроса, а также всех регулярных или больших задач поиска, которые включают один или более тех самых столбцов[84].

! ! !

СОВЕТ. Не будет плохой идеей сохранить все индексные структуры, которые вы тестировали, вместе с записями об их эффектах. Это поможет снизить накал страстей в тестирующей лаборатории!

. ! .

Индексы из одного столбца являются гораздо более гибкими, чем составные индексы. Они являются предпочтительными для всех условий, которые не имеют особых потребностей в составных индексах, они также нужны для каждого отыскиваемого столбца, который является частью дизъюнктивного условия (содержащего логическую операцию OR).

Не расстраивайтесь, увидев NATURAL В плане запроса, и не бойтесь его использовать при тестировании ваших планов. Естественный (natural) порядок задает сканирование таблицы сверху вниз, страница за страницей. Зачастую это бывает быстрее для некоторых данных, особенно при нахождении соответствия и поиске по столбцам, для которых индекс имеет очень низкую селективность, а также для статичных таблиц с небольшим количеством строк.

При соединении двух или более потоков порядок, в котором эти потоки отыскиваются, бывает более важным, чем все другие факторы вместе взятые. Потоки извлекаются в порядке слева направо и являются, вместе с крайним левым потоком конкретного (парного) объединения, "важнейшим контроллером", который определяет логику поиска для всех остальных соединений и слияний, связанных с ним.

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

В ситуациях, когда оптимизатор выбирает индекс, который вы хотели бы проигнорировать, вы можете обмануть его, добавив пустое условие OR. для простого примера предположим, что у вас есть предложение WHERE, задающее столбец с очень низкой селективностью индекса, который вы хотели бы сохранить, например, для поддержания ссылочной целостности:

SELECT ...

WHERE PARENT_COUNTRY = 'AD'

Если эта база данных распространяется в Австралии (код страны 'AO'), то селективность PARENT_COONTRY будет настолько низкой, что полностью обрушит производительность, однако вы привязаны к обязательному индексу. Чтобы оптимизатор проигнорировал индекс, измените предикат поиска на[85]:

SELECT ...

WHERE PARENT COUNTRY = 'AU' OR 1=1

Получение "картины" соединения не является точной наукой, в процессе работы вы получите интуитивное понимание создания спецификаций соединений. Однако с этой техникой борются многие люди. Для всех спецификаций запросов, особенно для запросов к множеству таблиц, секрет заключается в добросовестном их проектировании. Если у вас мало опыта в SQL, не полагайтесь на инструменты CASE или утилиты конструирования запросов, чтобы научиться их создавать. Это классическая ситуация: пока вы не приобретете опыт, вы не сможете оценить, насколько глупы данные инструменты. Если вы всегда полагаетесь на тупые инструменты, вы никогда не приобретете интуицию.

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

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

Оглавление книги