Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

. ! .

Вы должны задать имена и порядок использования для всех индексов, которые будут использованы.

Оптимизатор всегда создает план, даже если задан пользовательский план. Хотя оптимизатор не мешает созданному пользователем плану, он проверяет, какие индексы подходят для данного контекста. Альтернативные пути отбрасываются, но во всем остальном дела идут своим чередом. Таким образом, наличие пользовательского плана не отключает оптимизатор, и он все равно делает свою оценку и генерацию своего плана в тех аспектах, которые не были указаны в предложении PLAN.

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

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

Оптимизатор Firebird основан на стоимости (cost-based) и обычно создает лучший план, если ваша база данных хорошо поддерживается сервисными средствами. Поскольку геометрия индексов и данных может изменяться в процессе выполнения операторов - особенно, если изменяется или удаляется большое количество строк- никакой сгенерированный оптимизатором план не может оставаться статичным от одной подготовки запроса к другой. Если вы создаете статичное выражение PLAN, то ухудшение эффективности может стать результатом снижения производительности, что уберет все преимущества отмены работы оптимизатора.

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

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

Скверно выполняющиеся запросы в 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]: