Индекс — это служебная структура данных, которая позволяет ускорить операции поиска, сортировки и фильтрации данных в реляционных базах данных. Индексы создаются для одного или нескольких столбцов таблицы, с целью ускорения выполнения запросов к базе данных.
Индексы обычно реализуются на основе различных структур данных, каждая из которых подходит для определённых типов запросов. Наиболее распространённые структуры данных для индексов:
- B-деревья (B-tree). Широко применяются для ускорения операций поиска, сортировки и диапазонных запросов.
- Хэш-индексы. Хорошо подходят для поиска по точным значениям, но неэффективны для диапазонных запросов.
- GiST и R-деревья. Используются для индексации пространственных данных, таких как координаты или географические объекты.
СУБД автоматически выбирает наиболее подходящий индекс для выполнения запроса. Важно следить за тем, чтобы индекс покрывал нужные столбцы и типы запросов, так как неправильный выбор индекса может замедлить выполнение запросов.
Польза:
- Ускорение запросов. Индексы значительно ускоряют выполнение запросов, особенно для операций равенства, сравнений и сортировки данных.
- Пример:
SELECT * FROM employees WHERE department = 'HR';— индекс на столбцеdepartmentускорит выполнение этого запроса.
- Пример:
- Покрытие запросов. В некоторых случаях индексы могут содержать все данные, необходимые для выполнения запроса, что позволяет избежать чтения самих строк таблицы (так называемый “Покрывающий индекс”).
- Пример:
SELECT name, age FROM employees WHERE department = 'IT';— если индекс покрывает столбцыname,ageиdepartment, то чтение строк таблицы не потребуется.
- Пример:
- Поддержка ограничений. Индексы могут использоваться для реализации ограничений на уникальность данных (UNIQUE), а также для обеспечения ссылочной целостности (FOREIGN KEY).
Накладные расходы:
- Влияние на производительность: добавление индексов замедляет операции вставки, обновления и удаления, так как индекс должен быть обновлен при каждой модификации данных. При создании индексов необходимо учитывать характер нагрузки на таблицу, особенно если часто выполняются операции записи. Например, при OLTP нагрузке чтение данных значительно преобладает над записью (80% чтения и 20% записи), что оправдывает использование индексов.
- Блокировка таблицы: добавление индекса блокирует таблицу, что может негативно сказаться на производительности, особенно в высоконагруженных системах.
- Дополнительное использование дискового пространства. Индексы требуют значительного объема дискового пространства для хранения. Обычно объем индексов составляет около половины размера таблицы. Если размер индексов превышает размер таблицы, это может указывать на необходимость оптимизации.
- Необходимость технического обслуживания. Со временем индексы могут фрагментироваться и требовать пересоздания для поддержания эффективности. В некоторых системах управления базами данных (СУБД) это может происходить автоматически, однако в других случаях требуется ручное вмешательство.
Частые ошибки:
- Низкая селективность.: индексы на колонках с низкой селективностью могут оказаться неэффективными и не принести ожидаемого увеличения производительности.
- Индекс на столбце
genderс двумя возможными значениями (M,F) не даст значительного выигрыша в производительности.
- Индекс на столбце
- Неиспользуемые индексы. Индексы, которые не используются, создают дополнительные накладные расходы на хранение и обслуживание. Рекомендуется периодически проверять активность индексов и удалять те, что не используются. Найти такие индексы поможет pageinspect.
- Высокие накладные расходы. В некоторых случаях затраты на обновление индексов могут превышать выгоды от их использования. Наличие большого количества индексов на одной таблице может вызвать накладные расходы и ухудшить производительность, особенно при частых изменениях данных.
Когда индексы не работают:
- Использование вычисляемых выражений. Например, выражение
WHERE column + 1 = 10не будет эффективно использовать индекс. В таких случаях могут применяться специальные методы, такие как индексы по выражению или генерация вычисляемых колонок.- Пример: Для выражения
column + 1можно создать вычисляемый столбец и индексировать его.
- Пример: Для выражения
- Обработка большого количества записей. Если запрос возвращает слишком много строк, использование индекса может быть неэффективным.
- Агрегатные функции. Лишь некоторые агрегатные функции, такие как
MIN()иMAX(), могут эффективно использовать индексы. - Логические операторы. Индексы хорошо работают с условиями, использующими логический оператор И (
AND), но менее эффективны с логическим ИЛИ (OR): - Логические операторы. Индексы хорошо работают с условиями, использующими логический оператор И (
AND), но менее эффективны с логическим ИЛИ (OR).- Пример запроса с ИЛИ:
SELECT * FROM tb WHERE a = 0 OR b = 0;— в этом случае индексы для каждого столбца могут быть использованы независимо (индексы наaиb), но составной индекс по этим столбцам не будет работать эффективно.
- Пример запроса с ИЛИ:
Какие бывают индексы?
- Уникальные и неуникальные. Уникальные индексы обеспечивают уникальность значений в столбцах. Неуникальные индексы допускают повторяющиеся значения.
- Простые и составные. Простые индексы создаются для одного столбца, а составные — для нескольких столбцов.
- Кластерные и некластерные. Кластерные индексы определяют физическое расположение данных на диске, а некластерные индексы хранят указатели на строки таблицы.
Реализации:
Мета информация
Область:: 00 Базы Данных
Родитель::
Источник::
Создана:: 2024-10-11
Автор::