Составные индексы

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

Рис. 6.5. Таблица с составным индексом

Поскольку в этом примере мы обрабатываем данные о работниках разных компаний, то таблица EMPLOYEE_SKILLS имеет индекс для трех столбцов внешнего ключа (F_EMP_CO#, F_EMP_EMP#, F_SKILL). Важно, чтобы столбцы в этом индексе располагались в оптимальном порядке. Необходимо также рассмотреть возможность дополнения этого индекса еще одним. Многие годы в документации Oracle утверждалось, что столбцы в индексе должны располагаться по порядку их избирательности. Это совершенно бессмысленный совет, полностью игнорирующий цели, для которых создается и применяется индекс! Помните, мы говорили, что Oracle будет использовать индекс только в том случае, если в запросе указана лидирующая часть ключа. В нашем примере с таблицей EMPLOYEE_SKILLS Oracle будет использовать индекс для поиска конкретного работника в конкретной компании, а не для поиска конкретной специальности (если только не указан и конкретный работник). Ниже приведены примеры SQL-запросов, в которых применяется часть индекса, весь индекс и вообще не используется индекс:

select ems.* /* используются первые две части ключа */
FROM employee_skills ems
WHERE ems.f_emp_co# = 23
AND ems.f_emp_emp# = 1234;

SELECT ems.* /* используется весь индекс */
FROM employee_skills ems
WHERE ems.f_emp_co# = 23
AND ems.f_emp_emp# = 1234;
AND ems.f_skill = 'WRITING';

SELECT ems.* /* индекс не используется */
FROM employee_skills ems
WHERE ems.f_skill = 'WRITINGS';

SELECT ems.* /* используется лидирующая часть индекса для поиска компании, а затем производится прямое сканирование индекса - поиск 'WRITING' производиться не будет, 'WRITING' будет найдено в индексе при сканировании */
FROM employee_skills ems
WHERE ems.f_emp_co# = 23
ADD ems.f_skill = 'WRITING';

Если мы хотим произвести поиск в EMPLOYEE_SKILLS только по специальности или соединить эту таблицу с таблицей SKILLS, не соединяя ее с таблицей EMPLOYEES, то лучше создать неуникальный индекс для столбца F_SKILL. Можно создать уникальный индекс для столбцов (F_SKILL, F_EMP_CO#, F_ЕМР_ЕМР#), но мы не рекомендуем так делать. Это повлечет за собой лишние затраты при вставке новой строки, поскольку Oracle придется проверять два индекса на предмет наличия значения-дубликата. Если в вашем приложении (или в приложении, требующем оптимальной производительности) часто используется последняя из приведенных выше форм запроса, то рекомендуем создать неуникальный индекс для столбцов (F_SKILL, F_EMP_CO#).

В данном примере нам вряд ли когда-нибудь потребуется осуществлять поиск по столбцу F_ЕМР_ЕМР#, но бывают случаи, где такая "средняя часть" ключа полезна сама по себе. Руководящий принцип здесь — стремиться к так называемой треугольной модели *** индексных ключей, включающей наиболее часто используемые (или самые критичные для производительности) индексные ключи. Треугольная модель для нашего примера будет выглядеть так:

(F_EMP_CO#, F_EMP_EMP#, F_SKILL)
(F_SKILL, F_EMP_CO#)
(F_EMP_EMP#)

Принимая решение об индексировании, соблюдайте следующие общие правила.

• Должно быть в наличии ограничение PRIMARY KEY (которое, конечно, сгенерирует уникальный индекс).

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

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

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

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

Дата добавления: 2015-08-09