только с одним индексированным равенством
Для этого примера мы используем неиндексированные копии таблиц PROJECT и EMPLOYEE для демонстрации того, как оптимизатор будет использовать доступный индекс, когда он может применять лучший вариант условий неиндексированного эквисоединения:
SQL> SELECT PI.PROJ_NAME, DL.DEPARTMENT, PDB.PROJECTED_BUDGET FROM PROJECT1 PI
JOIN PROJ_DEPT_BUDGET PDB ON PI . PROJ_ID = PDB.PROJ_ID
JOIN DEPARTMENT1 Dl ON PDB. DEPT_NO = Dl. DEPT_NO;
PLAN MERGE (SORT
(PI NATURAL), SORT (JOIN (Dl NATURAL, PDB INDEX (RDB$FOREIGN18))))
В самом внутреннем цикле выбран индекс внешнего ключа для эквисоединения потока PDB и выбираемых подходящих строк потока DEPARTMENT. Заметьте, что выбор такого индекса ничего не выполняет с внешним ключом, для поддержки которого был создан индекс, поскольку таблица, на которую ссылается внешний ключ, даже не присутствует в операторе.
После этого результирующая река и поток PROJECT сортируются. В завершение (в самом внешнем цикле) два сортированных потока объединяются в один.
Когда в запросе задаются подзапросы и объединения, используется несколько операторов SELECT. Оптимизатор конструирует независимый план для каждого оператора SELECT. Возьмем следующий пример:
SELECT
P.PROJ_NAME,
(SELECT E.FULL_NAME FROM EMPLOYEE E
WHERE P.TEAM_LEADER = E.EMP_NO) AS LEADER_NAME
FROM PROJECT P
WHERE P.PRODUCT = 'software'
PLAN (Е INDEX (RDB$PRIMARY7) )
PLAN (Р INDEX (PRODTYPEX))
Первый план выбирает индекс первичного ключа таблицы EMPLOYEE для просмотра кодов TEAM_LEADER в первичной таблице подзапроса. Индекс PRODTYPEX для таблицы PROJECT используется для фильтрации строк в таблице PRODUCT, поскольку первым элементом ключа в этом индексе является столбец PRODUCT.
Интересно то, что если изменить тот же запрос, включив предложение упорядочения, то оптимизатор изменит свой выбор индекса для фильтрации и выберет уникальный индекс по PROJ_NAME для навигации по упорядочиваемому столбцу:
SELECT
P.PROJ_NAME,
(SELECT E.EULL_NAME FROM EMPLOYEE E
WHERE P.TEAM_LEADER = E.EMP_NO) AS LEADER_NAME
FROM PROJECT P
WHERE P.PRODUCT = 'software' ORDER BY 1;
PLAN (E INDEX (RDB$PRIMARY7))
PLAN (P ORDER RDB$11)
Синтаксис выражений, который использует оптимизатор для создания плана и передачи его серверу Firebird доступен в SQL в предложении PLAN. Это позволяет вам определять ваш собственный план, ограничивая оптимизатор в его выборе.
Предложение PLAN может быть задано почти в любом операторе SELECT, включая операторы, используемые в создании просмотров, в хранимых процедурах и подзапросах. Firebird версии 1.5 и выше также допускает предложения PLAN и в триггерах. Множество планов может быть указано независимо для запроса и любого подзапроса. При этом нет требования "все или ничего" - любое предложение плана является необязательным.
Предложение PLAN генерируется для оператора SELECT В хранимой процедуре выбора. Поскольку выходом хранимой процедуры выбора является виртуальный набор, любые условия будут основываться на доступе NATURAL. При этом любой оператор SELECT в хранимой процедуре будет оптимизирован, и для него можно применять пользовательский план.
! ! !
ПРИМЕЧАНИЕ. Конструирование пользовательского плана для оператора SELECT в просмотре создает собственные проблемы для разработчика. Более подробную информацию см. в разд. "Использование планов запросов для просмотров" главы 24.
. ! .
Вы должны задать имена и порядок использования для всех индексов, которые будут использованы.
Оптимизатор всегда создает план, даже если задан пользовательский план. Хотя оптимизатор не мешает созданному пользователем плану, он проверяет, какие индексы подходят для данного контекста. Альтернативные пути отбрасываются, но во всем остальном дела идут своим чередом. Таким образом, наличие пользовательского плана не отключает оптимизатор, и он все равно делает свою оценку и генерацию своего плана в тех аспектах, которые не были указаны в предложении PLAN.
Неверный индекс приведет к тому, что запрос не будет выполнен. Если любой предикат или условие соединения остаются после того, как все индексы, указанные в плане выражения, будут использованы, оптимизатор просто оценит потоки в соответствии с естественным порядком и порядком сортировки по умолчанию.
Представляя ваше собственное выражение плана, вы можете получить небольшое увеличение скорости за счет обхода работы оптимизатора. При этом разработка вашего собственного плана, основанная на структурных правилах управления вашими данными, может не дать ожидаемых вами удовлетворительных результатов, особенно если ваш план наследован от другой СУБД, которая использует структурные правила для оптимизации запросов.
Оптимизатор Firebird основан на стоимости (cost-based) и обычно создает лучший план, если ваша база данных хорошо поддерживается сервисными средствами. Поскольку геометрия индексов и данных может изменяться в процессе выполнения операторов - особенно, если изменяется или удаляется большое количество строк- никакой сгенерированный оптимизатором план не может оставаться статичным от одной подготовки запроса к другой. Если вы создаете статичное выражение PLAN, то ухудшение эффективности может стать результатом снижения производительности, что уберет все преимущества отмены работы оптимизатора.
Смысл сказанного здесь в том, что использование вашего собственного плана может оказаться палкой о двух концах. В процессе разработки вы можете чувствовать преимущество от использования плана, который, как вы верите, является лучшим, чем тот, который будет получен оптимизатором. В этом случае вы отключаете преимущества динамического оптимизатора, который может компенсировать последующие изменения в распределении данных или индексов.
Улучшение плана запроса
Скверно выполняющиеся запросы в Firebird наиболее часто являются следствием плохого задания индексов и неоптимальных запросов. В разд. "Тема оптимизации" главы 18 мы рассматривали влияние индексов с плохой селективностью. В настоящем разделе мы продолжим рассмотрение индексов и некоторых неприятностей, которые могут происходить с оптимизатором и разработчиком, когда индексы служат помехой эффективности поиска.
Не факт, что использование индексов в соединении или поиске сделает запрос более быстрым. В действительности существуют такие структуры метаданных и виды индексов, при которых выбор некоторых индексов резко снизит производительность в сравнении с естественным сканированием.
Дублирующиеся или перекрывающие друг друга индексы могут повлиять на использование оптимизатором других индексов. Удалите все индексы, которые дублируют автоматически создаваемые индексы для первичных или внешних ключей или для ограничений UNIQUE. Пары составных первичных и внешних ключей, особенно длинных или несущих смысловое содержание, могут сделать запросы уязвимыми с точки зрения производительности, привести к неправильному выбору индексов и к немалым человеческим ошибкам. При проектировании таблиц рассматривайте использование суррогатных ключей для отделения "осмысленных" столбцов для поиска и упорядочения от формального ключа для соединений.
Составные (сложные) индексы - это индексы, которые содержат более одного столбца. Хорошо продуманный составной индекс может увеличить скорость запросов и поиска в сложных конъюнктивных запросах (использующих логический оператор AND), особенно для больших таблиц или при низкой собственной селективности отыскиваемого столбца.