Индексы

Понятие индекса

Индекс - это объект БД, содержащий упорядоченные значения указанных столбцов таблицы и ссылки на физическое размещение записи с данными значениями.

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

  1. Ускорение поиска:

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

  2. Оптимизация запросов:

    • Они помогают СУБД (системе управления базами данных) эффективно выполнять запросы, особенно при работе с большими объемами данных.

  3. Ускорение сортировки и группировки:

    • Если данные уже отсортированы в индексе, запросы с ORDER BY или GROUP BY выполняются быстрее.

  4. Обеспечение уникальности и ограничений целостности:

    • Уникальные индексы гарантируют, что в столбце или группе столбцов не будет дубликатов. Для обеспечения ограничений целостности – PRIMARY KEY, FOREIGN KEY, UNIQUE, СУБД может создавать индексы автоматически.

Индексы обычно реализуются с помощью сбалансированных деревьев (B-деревьев) или хэш-таблиц. Рассмотрим их подробнее:

B-дерево (B-tree):

  • Это наиболее распространенная структура для индексов.
  • B-дерево — это сбалансированное дерево, где каждый узел содержит несколько ключей и ссылок на дочерние узлы.
  • Преимущества:
    • Поддерживает быстрый поиск, вставку и удаление данных.
    • Эффективно работает для диапазонных запросов (например, WHERE column BETWEEN value1 AND value2).
  • Пример:
    • Если у вас есть индекс на столбце ID, то поиск всех строк с ID = 25 будет выполняться за время O(log n), где n — количество строк.

Хэш-индекс:

  • Использует хэш-таблицы для быстрого поиска по точному совпадению.
  • Преимущества:
    • Очень быстрый поиск по точному значению (O(1) в среднем случае).
  • Недостатки:
    • Не поддерживает диапазонные запросы.
    • Менее эффективен при частых изменениях данных.

Другие типы индексов:

  • Bitmap-индексы: Используются для столбцов с небольшим количеством уникальных значений (например, пол).
  • Полнотекстовые индексы: Для поиска по текстовым данным.
  • Пространственные индексы: Для работы с геоданными.

Создание индекса

Для создания индекса используется следующие команды:

CREATE [UNIQUE] [ASC | DESC] INDEX <имя индекса> ON <таблицы> (<поле>, <поле>, ...);

CREATE [UNIQUE] [ASC | DESC] INDEX <имя индекса> ON <таблицы> COMPUTED BY (<выражение>);

Если при создании индекса указано ключевое слово UNIQUE, то индекс гарантирует уникальность значений ключей. Такой индекс называется уникальным.

При создании индекса вместо одного или нескольких столбцов вы также можете указать одно выражение, используя предложение COMPUTED BY. Такой индекс называется вычисляемым или индексом по выражению. Вычисляемые индексы используются в запросах, в которых условие в предложениях WHERE, ORDER BY или GROUP BY в точности совпадает с выражением в определении индекса. Выражение в вычисляемом индексе может использовать несколько столбцов таблицы.

Примеры:

Создание индекса

CREATE INDEX IDX_UPDATER ON SALARY_HISTORY (UPDATER_ID);

Создание индекса с сортировкой ключей по убыванию

CREATE DESCENDING INDEX IDX_CHANGE ON SALARY_HISTORY (CHANGE_DATE);

Создание многосегментного индекса

CREATE INDEX IDX_SALESTAT ON SALES (ORDER_STATUS, PAID);

Создание индекса, не допускающего дубликаты значений

CREATE UNIQUE INDEX UNQ_COUNTRY_NAME ON COUNTRY (NAME);

Создание вычисляемого индекса

CREATE INDEX IDX_NAME_UPPER ON PERSONS COMPUTED BY (UPPER (NAME));

Такой индекс может быть использован для регистронезависимого поиска.

SELECT * FROM PERSONS WHERE UPPER(NAME) STARTING WITH UPPER('Iv');

Изменение и удаление индекса

Индекс можно перевести в неативное состояние или снова активировать.

Для этого используется оператор

ALTER INDEX <имя индекса> {ACTIVE | INACTIVE};

При выборе опции INACTIVE, индекс переводится из активного в неактивное состояние. Перевод индекса в неактивное состояние по своему действию похоже на команду DROP INDEX за исключением того, что определение индекса сохраняется в базе данных. Невозможно перевести в неактивное состояние индекс участвующий в ограничении.

Активный индекс может быть отключен, только если отсутствуют запросы использующие этот индекс, иначе будет возвращена ошибка «object in use».

Перевод индекса в неактивное состояние может быть полезен при массовой вставке, модификации или удалении записей из таблицы, для которой этот индекс построен.

При выборе альтернативы ACTIVE индекс переводится из неактивного состояния в активное. При переводе индекса из неактивного состояния в активное индекс перестраивается.

Для удаления индекса используется оператор

DROP INDEX <имя индекса>;

Пример запроса с использованием индекса

Предположим, у нас есть таблица EMPLOYEES:

CREATE TABLE EMPLOYEES (
    ID INT PRIMARY KEY,
    FIRST_NAME VARCHAR(50),
    LAST_NAME VARCHAR(50),
    DEPT_NO CHAR(3),
    SALARY DECIMAL(10, 2)
);

Создадим индекс на столбце LAST_NAME:

CREATE INDEX IDX_LAST_NAME ON EMPLOYEES (LAST_NAME);

Теперь запрос:

SELECT * FROM EMPLOYEES WHERE LAST_NAME = 'Иванов';

будет выполняться быстрее, так как СУБД использует индекс IDX_LAST_NAME для поиска.

Достоинства и недостатки индексов

Использование индексов дает следующие преимущества:

  • Ускорение поиска: Индексы значительно сокращают время выполнения запросов.
  • Эффективность: Уменьшают нагрузку на сервер за счет сокращения количества операций ввода-вывода.

Однако и сопряжено с рядом недостатков:

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

Контрольные вопросы

  1. Для чего применяются индексы?
  2. Какие структуры данных лежат в основе индексов?
  3. Назовите известные типы индексов?
  4. Какие команды используются для создания индексов?
  5. Как можно изменять уже созданный индекс?
  6. Назовите преимущества использования индексов?
  7. Назовите недостатки применения индексов?