Для чего нужен индекс в БД?

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

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

Основное назначение

Без индекса база должна просканировать все строки таблицы (full table scan) для поиска нужных данных. Если таблица содержит миллион строк, это очень медленно.

С индексом база может быстро найти нужные строки, используя структуру B-tree или хеш-таблицу.

// Аналогия из C++:
// БЕЗ индекса - поиск в векторе
std::vector<User> users;
auto result = std::find_if(users.begin(), users.end(),
    [](const User& u) { return u.id == 12345; });
// Сложность: O(n) - проверяет каждый элемент

// С индексом - поиск в отсортированной структуре
std::map<int, User> users_by_id;
auto it = users_by_id.find(12345);
// Сложность: O(log n) - логарифмическая

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

1. Primary Key Index (Первичный ключ)

Основной индекс таблицы, гарантирует уникальность.

CREATE TABLE users (
    id BIGINT PRIMARY KEY,  -- автоматически создаётся индекс
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);

Когда вы делаете запрос по id, это очень быстро — база может мгновенно найти строку.

2. Unique Index

Структура как primary key, но не обязательно использовать как ключ строки.

CREATE UNIQUE INDEX idx_email ON users(email);

3. Regular Index (Обычный индекс)

Учитывает только значение столбца, без ограничения уникальности.

CREATE INDEX idx_created_at ON posts(created_at DESC);

-- Очень быстрый запрос:
SELECT * FROM posts 
WHERE created_at > '2024-01-01'
ORDER BY created_at DESC;

4. Composite Index (Составной индекс)

Индекс по нескольким столбцам. Очень мощный инструмент.

-- Для этого запроса:
SELECT * FROM orders 
WHERE user_id = 42 
AND status = 'completed'
ORDER BY created_at DESC;

-- Создаём составной индекс:
CREATE INDEX idx_user_status_date 
ON orders(user_id, status, created_at DESC);

Важно: порядок столбцов имеет значение! Базы используют индекс только для столбцов, которые начинаются с первого.

5. Full-Text Index

Для полнотекстового поиска.

CREATE FULLTEXT INDEX idx_content 
ON articles(title, body);

-- Быстрый поиск по тексту:
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST ('C++ threading');

Практические примеры оптимизации

Плохой запрос без индекса:

SELECT COUNT(*) FROM orders 
WHERE EXTRACT(YEAR FROM created_at) = 2024;
-- Сканирует ВСЕ строки, вычисляет YEAR для каждой

Оптимизированный с индексом:

CREATE INDEX idx_created_at ON orders(created_at);

SELECT COUNT(*) FROM orders 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';
-- База находит диапазон в индексе, очень быстро

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

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

• WHERE условия
• JOIN условия
• ORDER BY (если сортируется по индексированному столбцу)
• GROUP BY
• DISTINCT

// В C++ это как:
std::unordered_map<std::string, User> users_by_email;  // индекс
std::vector<User> users;  // основные данные

auto it = users_by_email.find("user@example.com");
if (it != users_by_email.end()) {
    // Нашли очень быстро через индекс
}

Затраты индексов

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

• INSERT — нужно обновить все индексы
• UPDATE — если изменяете индексированный столбец
• DELETE — нужно удалить из индексов
• Занимают дополнительную память на диске

Правила создания индексов

1. Индексируйте столбцы, используемые в WHERE:

-- Часто используемый запрос
SELECT * FROM users WHERE status = 'active';
CREATE INDEX idx_status ON users(status);

2. Используйте составные индексы вместо нескольких отдельных:

-- Плохо: 2 отдельных индекса
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);

-- Хорошо: 1 составной
CREATE INDEX idx_user_status ON orders(user_id, status);

3. Не переусложняйте — один индекс может навредить:

-- Не нужно индексировать ВСЕ столбцы
-- Индексируйте только те, которые реально используются в запросах

Анализ производительности

-- PostgreSQL: посмотреть план запроса
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- Если видите "Seq Scan" - нужен индекс
-- Если видите "Index Scan" - индекс работает

Вывод

Индексы — это инвестиция: вы тратите дополнительную память и время на вставку/обновление, чтобы ускорить чтение. Для большинства приложений это очень выгодная сделка, потому что чтение происходит намного чаще, чем изменение данных.