Какие знаешь индексы в базе данных?

Индексы в базах данных

Индексы — это специальные структуры данных, которые ускоряют выполнение запросов на чтение (SELECT), но могут замедлять операции записи (INSERT, UPDATE, DELETE), поскольку требуют поддержания своей актуальности. Они работают аналогично предметному указателю в книге, позволяя находить данные без полного сканирования таблицы.

Основные типы индексов

1. B-дерево (B-Tree) и B+ дерево

Наиболее распространённый тип индекса, используемый по умолчанию в большинстве СУБД (PostgreSQL, MySQL, SQL Server). B-дерево поддерживает быстрое поиск, вставку и удаление за логарифмическое время O(log n).

-- Создание B-tree индекса
CREATE INDEX idx_users_email ON users(email);

Особенности B+ дерева (разновидность B-дерева):

• Все значения хранятся в листьях
• Листья связаны в односвязный список для быстрого диапазонного поиска
• Выше плотность данных в узлах

2. Хеш-индекс

Использует хеш-функцию для преобразования значений ключа в указатели на данные. Идеален для операций равенства (=), но не поддерживает диапазонные запросы и сортировку.

-- В PostgreSQL
CREATE INDEX idx_users_id_hash ON users USING HASH(id);

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

• Константное время поиска O(1) в идеальном случае
• Эффективен для точечных запросов

Недостатки:

• Не поддерживает диапазонные запросы (> , <, BETWEEN)
• Коллизии хешей могут снижать производительность
• Не поддерживает сортировку результатов

3. Bitmap-индекс (битовый индекс)

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

-- В Oracle
CREATE BITMAP INDEX idx_orders_status ON orders(status);

Применение:

• Колонки с малым количеством уникальных значений (пол, статус заказа)
• Сложные запросы с множественными условиями AND/OR
• Data warehouse системы

4. GiST (Generalized Search Tree)

Обобщённое сбалансированное дерево поиска, поддерживающее различные типы данных и операции. Широко используется в PostgreSQL для полнотекстового поиска, геоданных и других сложных типов.

-- Индекс для геоданных
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);

5. GIN (Generalized Inverted Index)

Обратный индекс, оптимизированный для работы с составными значениями (массивы, JSON, полнотекстовый поиск). Хранит пары (значение, список строк).

-- Индекс для JSONB поля
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

6. SP-GiST (Space-Partitioned GiST)

Пространственно-разделённое GiST для данных, которые можно разделить на непересекающиеся области. Подходит для нерегулярных структур данных.

-- Для IP-адресов или нерегулярных деревьев
CREATE INDEX idx_network_ip ON network USING SPGIST(ip_range);

7. BRIN (Block Range Index)

Индекс диапазонов блоков, хранящий минимальные и максимальные значения для каждого блока данных. Эффективен для очень больших таблиц с естественной сортировкой данных.

-- Для больших таблиц с данными, отсортированными по времени
CREATE INDEX idx_logs_timestamp ON logs USING BRIN(timestamp);

Классификация по другим критериям

По уникальности

• Уникальные индексы — гарантируют уникальность значений

CREATE UNIQUE INDEX idx_users_username ON users(username);

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

По количеству столбцов

• Простой индекс — на один столбец
• Составной (композитный) индекс — на несколько столбцов

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

По типу хранения

• Кластеризованный индекс — определяет физический порядок данных в таблице
  • В таблице может быть только один кластеризованный индекс
  • Данные физически сортируются по ключу индекса
• Некластеризованный индекс — отдельная структура, содержащая указатели на данные

По частичности

• Полный индекс — на все строки таблицы
• Частичный (partial) индекс — только на подмножество строк

-- Только для активных пользователей
CREATE INDEX idx_users_active ON users(email) WHERE is_active = true;

По выражению

• Функциональный индекс — на результат выражения или функции

CREATE INDEX idx_users_lower_email ON users(LOWER(email));

Практические рекомендации

Когда создавать индексы:

• Столбцы, часто используемые в WHERE, JOIN, ORDER BY
• Внешние ключи (улучшают JOIN и каскадные операции)
• Часто используемые комбинации столбцов

Когда избегать индексов:

• Маленькие таблицы (полное сканирование быстрее)
• Столбцы с частыми изменениями
• Столбцы с низкой селективностью

Оптимизация индексов:

1. Анализ планов запросов для выявления недостающих индексов
2. Мониторинг использования индексов
3. Регулярное обслуживание (перестройка, реорганизация)
4. Баланс между производительностью чтения и записи

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

-- Найти неиспользуемые индексы
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Правильное проектирование индексов — это баланс между ускорением запросов чтения и влиянием на производительность операций записи. Каждая СУБД имеет свои особенности реализации индексов, поэтому важно изучать документацию конкретной системы.