О некоторых параметрах проектирования индексов
Когда проектировщик базы данных приступает к проектированию индексов, то он должен иметь некоторый способ оценки качества создаваемого индекса. Введем несколько понятий, с помощью которых проектировщик может грубо оценить качество потенциального индекса.
Кардинальностью колонки (cardinality) таблицы называется число дискретных различных значений колонки, которые встречаются в строках таблицы. Например, если в таблице EMPLOYEE мы заводим колонку для указания пола - SEX, то кардинальность этой колонки есть 2, так как в природе у людей существует только два пола - мужской и женский. Для колонки первичного ключа кардинальность будет равна числу строк в таблице.
Причиной, по которой кардинальность колонки важна для проектирования индексов, состоит в том, что кардинальность индексируемой колонки определяет число уникальных входов, которые должны сохраняться в индексе, т.е. число записей в индексе. Так, для индексируемой колонки SEX будет существовать два уникальных входа, которые будут повторяться много раз в индексе. При предположении равновероятного распределения пола сотрудников на 100000 строк в таблице EMPLOYEE каждый вход индекса будет повторяться 50000 раз. СУБД вряд ли будут принимать решение об использовании такого индекса при построении плана запроса.
Определить кардинальность потенциальной колонки индексирования в существующей базе данных достаточно просто:
SELECT COUNT (DISTINCT колонка) FROM таблица
При проектировании новой базы данных проектировщик должен оценить кардинальность всех потенциальных индексируемых колонок во всех таблицах базы данных, исходя из имеющейся документации.
Способ, с помощью которого СУБД оценивает действие кардинальности, состоит в использовании фактора селективности выборки (selectivity factor). Фактора селективности выборки индекса определяется как величина, обратная кардинальности индексной колонки:
Фактор селективности оценивает потенциальный объем операций ввода/вывода. Чем меньше фактор селективности, тем меньше требуется операций ввода/вывода для получения результирующего множества строк таблицы.
СУБД оценивает эту величину, чтобы решить, использовать индекс для доступа к строкам таблицы или нет. Какие формулы используются для оценки фактора селективности в СУБД, мы рассмотрим в последней лекции этого курса.
В заключение раздела мы приведем список правил для определения колонок, которые являются хорошими и плохими кандидатами для индексирования. Эти правила могут быть использованы проектировщиком базы данных при принятии решения о построении индексов реляционной базы данных.
Хорошими кандидатами для индексирования обычно являются:
- колонки первичного ключа. По определению, колонки первичного ключа должны иметь уникальный индекс;
- колонки внешнего ключа. Они дают хороший индекс по двум причинам. Во-первых, они часто применяются для выполнения соединений с родительскими таблицами. Во-вторых, они могут быть использованы СУБД при поддержке ссылочной целостности в операциях удаления строк родительской и дочерних таблиц;
- любые колонки, которые содержат уникальные значения;
- колонки, запросы или соединения по которым захватывают от 5 до 10% строк таблицы;
- колонки, которые часто входят как аргументы в функции агрегирования;
- колонки, которые часто используются для проверки правильности ввода данных в программах ввода/редактирования.
Факторы, влияющие на низкую эффективность индексов:
- Таблицы маленького размера. Одним из общих эмпирических правил является правило "не создавать индексы для таблиц размером менее пяти физических страниц". Для таких страниц стоимость поддержки индекса больше, чем стоимость сканирования всей таблицы. Конечно, уникальный индекс требуется для первичного ключа и поддержки ссылочной целостности.
- Интенсивные обновления таблиц в пакетном режиме. Такие таблицы обычно имеют проблемы с переполнением индекса при интенсивной модификации таблицы. Если индекс необходим для такой таблицы, то целесообразнее его удалять перед обновлением и создавать после него.
- Асимметрия значений ключей (Skewness of keys). Если распределение значений ключа имеет значительную асимметрию, то кардинальность индекса может оказаться достаточно высокой и СУБД из-за низкого фактора селективности будет часто использовать этот индекс.
СУБД оценивает эту величину, чтобы решить, использовать индекс для доступа к строкам таблицы или нет. Какие формулы используются для оценки фактора селективности в СУБД, мы рассмотрим в последней лекции этого курса.
В заключение раздела мы приведем список правил для определения колонок, которые являются хорошими и плохими кандидатами для индексирования. Эти правила могут быть использованы проектировщиком базы данных при принятии решения о построении индексов реляционной базы данных.
Хорошими кандидатами для индексирования обычно являются:- колонки первичного ключа. По определению, колонки первичного ключа должны иметь уникальный индекс;
- колонки внешнего ключа. Они дают хороший индекс по двум причинам. Во-первых, они часто применяются для выполнения соединений с родительскими таблицами. Во-вторых, они могут быть использованы СУБД при поддержке ссылочной целостности в операциях удаления строк родительской и дочерних таблиц;
- любые колонки, которые содержат уникальные значения;
- колонки, запросы или соединения по которым захватывают от 5 до 10% строк таблицы;
- колонки, которые часто входят как аргументы в функции агрегирования;
- колонки, которые часто используются для проверки правильности ввода данных в программах ввода/редактирования.
Факторы, влияющие на низкую эффективность индексов:- Таблицы маленького размера. Одним из общих эмпирических правил является правило "не создавать индексы для таблиц размером менее пяти физических страниц". Для таких страниц стоимость поддержки индекса больше, чем стоимость сканирования всей таблицы. Конечно, уникальный индекс требуется для первичного ключа и поддержки ссылочной целостности.
- Интенсивные обновления таблиц в пакетном режиме. Такие таблицы обычно имеют проблемы с переполнением индекса при интенсивной модификации таблицы. Если индекс необходим для такой таблицы, то целесообразнее его удалять перед обновлением и создавать после него.
- Асимметрия значений ключей (Skewness of keys). Если распределение значений ключа имеет значительную асимметрию, то кардинальность индекса может оказаться достаточно высокой и СУБД из-за низкого фактора селективности будет часто использовать этот индекс.
