В составных индексах важным является ранг (количество элементов и относительная позиция слева направо). Оптимизатор может использовать один столбец составного индекса или подгруппу столбцов в операциях поиска, соединения или упорядочения, не включая оставшиеся столбцы индекса в операции. На рис. 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 по упорядочиванию наборов и по агрегированию промежуточных выходных наборов в объединенные или статистические группы.
ГЛАВА 23. Упорядоченные и агрегатные наборы.
В этой главе мы рассмотрим синтаксис и правила задания запросов, которые выводят упорядоченные и сгруппированные наборы.
Наборы, заданные оператором SET, по умолчанию читаются в произвольном порядке. Часто, особенно при отображении данных для пользователя или при печати отчетов, вам нужен определенный вид сортировки. Ясно, что список телефонов будет более полезным, если фамилии будут находиться в алфавитном порядке. Группы чисел по продажам или тестовые результаты более понятны, если они упорядочены, сгруппированы и просуммированы. В SQL существует два предложения по заданию формирования выходных наборов.
Предложение ORDER BY используется для сортировки наборов в возрастающем или убывающем порядке по одному или большему количеству столбцов. Предложение GROUP BY может разделять набор на вложенные группы, или уровни, в соответствии со столбцами из списка SELECT и (по желанию) выполняя агрегатные вычисления на множестве числовых столбцов в границах группы.
Обсуждение сортировки
Хотя упорядочение и агрегирование являются операциями с различными результатами, на некотором уровне они взаимодействуют, когда обе используются в запросе и расположение их предложений является важным. Внутри системы они применяют некоторые общие характеристики в отношении формирования промежуточных наборов и использования индексов.
Задание порядка в предложениях сортировки
Следующая упрощенная структура синтаксиса оператора SELECT показывает позицию предложений ORDER BY и GROUP BY В спецификациях упорядочения или группирования. Оба предложения являются необязательными и оба могут присутствовать в операторе:
SELECT [FIRST m] [SKIP N] | [DISTINCT | ALL ]
{<список-столбцов>}
84
Оптимизатор InterBase, Firebird 1.0 и 1.5 считает составные индексы более селективными, чем они есть на самом деле. В этом виновата особенность подсчета селективности, которая производится целиком для всего ключа. В Firebird 2.0 селективность составных индексов учитывается отдельно по каждому сегменту составного ключа, что позволяет избежать сильных проколов в оптимизации запросов. - Прим. науч. ред.
85
Подобное дополнительное условие может привести к тому, что вообще никакие индексы не будут использоваться. Более правильным в случае множества условий поиска является "отключение" низкоселективного индекса для отдельного столбца путем превращения условия сравнения в вычисляемое: для числовых столбцов - where field+0 = value, для строковых - where field || '' = value. - Прим. науч. ред.