Я хотел бы знать, существуют ли общие правила создания индекса или нет. Как выбрать, какие поля следует включать в этот индекс, а когда не включать?
Я знаю, что это всегда зависит от среды и объема данных, но мне было интересно, можем ли мы установить некоторые общепринятые правила создания индексов в Oracle.
В документации Oracle есть отличный набор соображений по выбору индексации: noreferrer">http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004
Обновление для версии 19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/designing-and-developing-for-performance.html#GUID-99A7FD1B-CEFD.-4E91-9486-2CBBFC2B7A1D
Цитата:
Рассмотрите возможность индексирования ключей, которые часто используются в предложениях WHERE.
Рассмотрите возможность индексирования ключей, которые часто используются для соединения таблиц в операторах SQL. Дополнительные сведения об оптимизации объединений см. в разделе Использование хэш-кластеров для повышения производительности.
Выбирайте индексные ключи с высокой селективностью. Селективность индекса — это процент строк в таблице, имеющих одинаковое значение индексированного ключа. Избирательность индекса оптимальна, если несколько строк имеют одинаковое значение. Примечание: Oracle автоматически создает индексы или использует существующие индексы для ключей и выражений уникальных и первичных ключей, которые вы определяете с ограничениями целостности. Индексация столбцов с низкой селективностью может быть полезна, если распределение данных асимметрично, так что одно или два значения встречаются гораздо реже, чем другие значения.
Не используйте стандартные индексы B-дерева для ключей или выражений с несколькими различными значениями. Такие ключи или выражения обычно имеют низкую селективность и, следовательно, не оптимизируют производительность, если только часто выбираемые значения ключей не появляются реже, чем другие значения ключей. В таких случаях можно эффективно использовать растровые индексы, если только индекс не изменяется часто, как в приложении OLTP с высокой степенью параллелизма.
Не индексируйте столбцы, которые часто изменяются. Операторы UPDATE, изменяющие индексированные столбцы, и операторы INSERT и DELETE, изменяющие индексированные таблицы, занимают больше времени, чем если бы индекса не было. Такие операторы SQL должны изменять данные в индексах, а также данные в таблицах. Они также генерируют дополнительные отмены и повторы.
Не индексируйте ключи, которые появляются только в предложениях WHERE с функциями или операторами. Предложение WHERE, в котором используется функция, отличная от MIN или MAX, или оператор с индексированным ключом, не делает доступным путь доступа, который использует индекс, за исключением индексов на основе функций.
Рассмотрите возможность индексации внешних ключей ограничений ссылочной целостности в случаях, когда большое количество одновременных инструкций INSERT, UPDATE и DELETE обращается к родительской и дочерней таблицам. Такой индекс допускает UPDATE и DELETE в родительской таблице без общей блокировки дочерней таблицы.
При выборе индексирования ключа подумайте, стоит ли выигрыш в производительности для запросов потери производительности для операций INSERT, UPDATE и DELETE, а также использования пространства, необходимого для хранения индекса. Вы можете поэкспериментировать, сравнив время обработки операторов SQL с индексами и без них. Вы можете измерить время обработки с помощью средства трассировки SQL.
Есть некоторые вещи, которые вы всегда должны индексировать:
После этого найдите другие столбцы, которые часто используются для фильтрации запросов: типичный пример — фамилии людей.
Из руководства разработчиков приложений для баз данных Oracle 10g — основы, глава 5:
В общем, вы должны создать индекс для столбца в любой из следующих ситуаций:
Используйте следующие рекомендации для определения времени создания индекса:
Некоторые столбцы являются хорошими кандидатами на индексацию. Столбцы с одной или несколькими из следующих характеристик являются хорошими кандидатами на индексацию:
Столбец содержит много пустых значений, но запросы часто выбирают все строки, содержащие значение. В этом случае сравнение, которое соответствует всем ненулевым значениям, например:
WHERE COL_X >= -9,99 *power(10,125) предпочтительнее WHERE COL_X IS NOT NULL
Это связано с тем, что первый использует индекс для COL_X (при условии, что COL_X является числовым столбцом).
Столбцы со следующими характеристиками менее подходят для индексации:
Ого, это просто такая огромная тема, в таком формате сложно ответить. Я настоятельно рекомендую эту книгу.
Дизайн индекса реляционной базы данных и оптимизаторы Тапио Лахденмаки
Вы не просто используете индексы для ускорения доступа к таблицам, иногда вы создаете индексы, чтобы вообще избежать доступа к таблицам. Что-то еще не упомянутое, но жизненно важное.
В этом есть целая наука, если вы действительно хотите, чтобы ваша база данных работала максимально эффективно.
А, одна конкретная оптимизация для Oracle — это построение индексов с обратным ключом. Если у вас есть PK-индекс с моноатомно увеличивающимся значением, например последовательность, и у вас есть много параллельных вставок и вы не планируете сканирование диапазона этого столбца, сделайте его индексом обратного ключа.
Видите, насколько конкретными могут быть эти оптимизации?
Загляните в раздел Нормализация базы данных — вы найдете много хороших стандартных правил о том, какие ключи должны существовать, как базы данных должны быть связаны, а также советы по индексам.
-Адам
Обычно столбцы идентификаторов помещаются впереди, и они обычно однозначно идентифицируют строки. Комбинация столбцов также может делать то же самое. Например, используя автомобили... метки или номерные знаки уникальны и подходят для индексации. Они (столбец тегов) могут претендовать на первичный ключ. Имя владельца может претендовать на индекс, если вы собираетесь искать по имени. Марка автомобиля действительно не должна иметь индекс в начале, так как он не будет сильно различаться. Индексы не помогают, если данные в столбце не слишком сильно различаются.
Взгляните на SQL - на что смотрят предложения where. Им может понадобиться индекс.
Мера. В чем проблема - страницы/запросы занимают слишком много времени? что используется для запросов. Создайте индекс для этих столбцов.
Предостережения: для обновления индексов требуется время и место.
и иногда полное сканирование таблицы выполняется быстрее, чем индекс. небольшие таблицы можно сканировать быстрее, чем получить индекс, а затем обратиться к таблице. Посмотрите на свои соединения.
