Каковы преимущества и недостатки использования индексов в базах данных?

Индекс в базе данных — это специальная структура данных (чаще всего B-дерево), которая позволяет СУБД выполнять поиск данных значительно быстрее, чем при полном сканировании таблицы.

Преимущества (Pros):

• Ускорение чтения (операции SELECT): Это основная цель индексов. Они кардинально повышают производительность запросов с условиями WHERE, JOIN и ORDER BY, позволяя СУБД быстро находить нужные строки.
• Обеспечение уникальности данных: Уникальные индексы (UNIQUE INDEX) гарантируют, что значения в индексированном столбце (или наборе столбцов) не будут повторяться.

Недостатки (Cons):

• Замедление операций записи (INSERT, UPDATE, DELETE): При каждом изменении данных в таблице СУБД также должна обновить все связанные индексы, что создает дополнительные накладные расходы.
• Дополнительное дисковое пространство: Индексы хранятся на диске отдельно от таблицы и занимают место.
• Необходимость обслуживания: Со временем индексы могут фрагментироваться, что снижает их эффективность. Требуется периодическая реорганизация или перестроение (REINDEX).

Практический пример (PostgreSQL):

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

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Запрос на поиск пользователя по email будет медленным на большой таблице, так как СУБД придется сканировать все строки (Full Table Scan):

-- Медленно на больших таблицах
SELECT * FROM users WHERE email = 'user@example.com';

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

CREATE INDEX idx_users_email ON users(email);

Теперь тот же самый запрос будет выполнен практически мгновенно, так как СУБД использует индекс для прямого доступа к нужной строке.