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

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

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

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

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

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

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

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

! ! !

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

. ! .

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

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

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

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

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

SELECT ...

WHERE PARENT_COUNTRY = 'AD'

Если эта база данных распространяется в Австралии (код страны 'AO'), то селективность PARENT_COONTRY будет настолько низкой, что полностью обрушит производительность, однако вы привязаны к обязательному индексу. Чтобы оптимизатор проигнорировал индекс, измените предикат поиска на [85] Подобное дополнительное условие может привести к тому, что вообще никакие индексы не будут использоваться. Более правильным в случае множества условий поиска является "отключение" низкоселективного индекса для отдельного столбца путем превращения условия сравнения в вычисляемое: для числовых столбцов - where field+0 = value, для строковых - where field || '' = value. - Прим. науч. ред. :

SELECT ...

WHERE PARENT COUNTRY = 'AU' OR 1=1

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