Что такое индексация в базе данных?

Что такое индексация в базе данных?

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

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

Индексы хранят отсортированные значения колонки с указателями на полные записи таблицы. Наиболее распространённая структура — B-tree (сбалансированное дерево поиска):

Индекс по полю "email":

       [M]
      /   \
    [D]    [S]
   / | \  / | \
[A][B][E][P][R][Z]
  |  |  |  |  |  |
  Row1 Row3 Row5 Row7 Row8

B-tree гарантирует O(log N) время поиска вместо O(N) при полном сканировании таблицы.

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

PRIMARY KEY

• Уникален, не может быть NULL
• Автоматически индексируется

CREATE TABLE users (
    id INT PRIMARY KEY,  -- индекс создан автоматически
    email VARCHAR(255)
);

UNIQUE INDEX

• Гарантирует уникальность значений
• Ускоряет поиск

CREATE UNIQUE INDEX idx_email ON users(email);

COMPOSITE INDEX (составной индекс)

• По нескольким колонкам
• Важен порядок колонок

CREATE INDEX idx_user_date ON orders(user_id, order_date);
-- Ускоряет: SELECT * FROM orders WHERE user_id = 5 AND order_date > '2024-01-01'
-- НО НЕ ускоряет: SELECT * FROM orders WHERE order_date > '2024-01-01'

FULL-TEXT INDEX

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

CREATE FULLTEXT INDEX idx_title ON articles(title, content);
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database');

SPATIAL INDEX

• Для географических данных

Плюсы и минусы

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

• Существенное ускорение SELECT запросов
• Ускорение JOIN, GROUP BY, ORDER BY
• Обеспечение уникальности данных

Недостатки:

• Замедление INSERT, UPDATE, DELETE (нужно обновить индексы)
• Потребление дополнительной памяти
• Требует обслуживания (дефрагментация, ANALYZE)

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

Создавайте индексы на:

• Колонки в WHERE clausе
• Колонки в JOIN условиях
• Колонки в ORDER BY
• Колонки часто используемых SELECT запросов

НЕ создавайте индексы на:

• Колонки с низкой кардинальностью (много повторений)
• Булевы колонки
• Часто NULL значения
• Малые таблицы

EXPLAIN для анализа

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
-- type: range (использует индекс)
-- rows: 1 (вернёт 1 строку)

EXPLAIN SELECT * FROM users WHERE age > 25;
-- type: ALL (полное сканирование)
-- rows: 10000 (сканирует всю таблицу)

Пример оптимизации

Таблица users (100 тыс. записей):

-- Медленно (1.5 сек)
SELECT * FROM users WHERE email = 'test@example.com';

-- Добавляем индекс
CREATE INDEX idx_email ON users(email);

-- Быстро (0.001 сек)
SELECT * FROM users WHERE email = 'test@example.com';

Для C++ разработчика

При работе с БД:

• Всегда профилируйте запросы (EXPLAIN, ANALYZE)
• Избегайте N+1 queries через JOIN и индексы
• Используйте подготовленные statements
• Регулярно проверяйте статистику индексов

Индексация — критический инструмент для масштабируемости приложения. Правильная индексация может улучшить производительность на порядки.