Как работают индексы в базах данных?

Индексы в базах данных: основные концепции и применение

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

Как работают индексы

Индекс — это отсортированная структура данных, которая хранит значения столбца(ов) и указатели на соответствующие строки таблицы. Вместо полного сканирования таблицы (Full Table Scan), база данных может перейти напрямую к нужным строкам.

B-Tree индекс (наиболее распространённый)

Большинство СУБД используют B-Tree структуры:

• Сбалансированное дерево, оптимизированное для дисковых операций
• Каждый узел содержит несколько ключей и указателей
• Высота дерева логарифмическая, поэтому поиск очень быстрый
• Хранится на диске, как и основная таблица

Уровень 0 (Root):        [40]
                          /  \
                        /      \
Уровень 1:        [20, 30]   [50, 60]
                   /  |  \     /  |  \
Уровень 2:      [10][20][30][40][50][60][70]

Типы индексов

1. Primary Key Index

Уникальный индекс для идентификации каждой строки:

-- Автоматически создаётся с PRIMARY KEY
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

2. Single Column Index

Индекс на одном столбце для ускорения фильтрации:

CREATE INDEX idx_customers_email ON customers(email);

-- Этот запрос будет быстрым благодаря индексу
SELECT * FROM customers WHERE email = 'user@example.com';

3. Composite (Multi-Column) Index

Индекс на нескольких столбцах для оптимизации сложных фильтров:

CREATE INDEX idx_orders_customer_date 
    ON orders(customer_id, order_date DESC);

-- Использует composite индекс
SELECT * FROM orders 
WHERE customer_id = 123 
AND order_date >= '2024-01-01'
ORDER BY order_date DESC;

4. Unique Index

Гарантирует уникальность значений:

CREATE UNIQUE INDEX idx_users_username ON users(username);

5. Full-Text Index

Для полнотекстового поиска в больших текстовых полях:

CREATE FULLTEXT INDEX idx_articles_content ON articles(content);

SELECT * FROM articles 
WHERE MATCH(content) AGAINST('data engineering' IN BOOLEAN MODE);

Покрывающие индексы (Covering Indexes)

Индекс, который содержит все столбцы, необходимые для выполнения запроса:

-- Создаём covering индекс
CREATE INDEX idx_orders_customer_covering 
    ON orders(customer_id) 
    INCLUDE (order_total, order_date);

-- Запрос целиком выполняется из индекса (Index-Only Scan)
SELECT customer_id, order_total, order_date 
FROM orders 
WHERE customer_id = 123;

Анализ плана выполнения запроса

Для оптимизации индексов нужно смотреть план выполнения:

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'test@example.com';

-- Вывод показывает использует ли индекс:
-- Index Scan using idx_customers_email (хорошо)
-- Seq Scan (плохо, нужен индекс)

Когда индексы помогают / не помогают

Индексы ускоряют:

• WHERE фильтры: WHERE customer_id = 5
• JOIN условия: ON orders.customer_id = customers.id
• ORDER BY / GROUP BY на индексированные столбцы
• Диапазонные запросы: WHERE date BETWEEN ... AND ...

Индексы замедляют:

• INSERT, UPDATE, DELETE (нужно обновлять индекс)
• Селективность <10% (лучше Full Scan)
• Булевы столбцы (только 2 значения — бесполезно)

Стратегия индексирования

-- Хорошо спроектированные индексы для Data Engineer:

-- 1. Индекс на foreign key
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 2. Индекс на дату для временных рядов
CREATE INDEX idx_events_timestamp ON events(timestamp DESC);

-- 3. Composite для частых комбинаций
CREATE INDEX idx_events_user_type 
    ON events(user_id, event_type, timestamp DESC);

-- 4. Partial индекс (только для активных записей)
CREATE INDEX idx_users_active 
    ON users(id) 
    WHERE is_deleted = false;

Мониторинг неиспользуемых индексов

-- PostgreSQL: найти неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Удаляем, чтобы сэкономить место и ускорить INSERT
DROP INDEX idx_unused_index;

В своей практике правильная индексация сокращала время выполнения аналитических запросов с 5+ минут до 100ms, что критично для больших data warehouse и real-time систем.