Что такое индексация в БД?

Что такое индексация в БД

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

Основная идея

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

-- Без индекса: должны проверить все 1М строк
SELECT * FROM users WHERE email = 'john@example.com';
-- Time: 5000ms (полная таблица)

-- С индексом: находим мгновенно
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';
-- Time: 10ms (через индекс)

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

Без индекса (Sequential Scan):

Таблица users:
[id=1] [id=2] [id=3] [id=4] ... [id=1000000]

Поиск email='john@example.com':
Проверить id=1? Нет
Проверить id=2? Нет
Проверить id=3? Нет
... проверить все 1М строк ...
Найдено на строке 500000
Время: O(n) — линейное

С индексом (B-Tree поиск):

Индекс (B-Tree):
           Root
          /    \
        A-M    N-Z
       /  |    |  \
      a  b j  n  z
      ↓  ↓ ↓  ↓  ↓
    [rows with matching email]
    
Поиск email='john@example.com':
Шаг 1: Начальный узел, 'j' в диапазоне N-Z? Да
Шаг 2: Перейди в узел N-Z
Шаг 3: Найди лист с 'j'
Шаг 4: Перейди к строкам в таблице
Время: O(log n) — логарифмическое

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

1. Primary Key Index (по умолчанию B-Tree)

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

-- Очень быстрый поиск по id
SELECT * FROM users WHERE id = 123;  -- O(log n)

2. Простой индекс (Single Column Index)

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Ускоряет WHERE и JOIN по этому полю
SELECT * FROM users WHERE email = 'john@example.com';
SELECT * FROM orders WHERE user_id = 5;

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

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Помогает этому запросу
SELECT * FROM orders 
WHERE user_id = 5 AND created_at > '2024-01-01';

-- Важен порядок! (user_id должен быть первым для фильтрации)
-- Может помочь и этому
SELECT * FROM orders WHERE user_id = 5;

-- Но НЕ поможет этому (user_id не первый)
SELECT * FROM orders WHERE created_at = '2024-01-01';

4. Unique Index

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Гарантирует уникальность
INSERT INTO users (email) VALUES ('john@example.com');
INSERT INTO users (email) VALUES ('john@example.com');  -- ERROR

5. Full Text Index

CREATE FULLTEXT INDEX idx_articles_content ON articles(title, content);

-- Поиск текста (работает в MySQL/PostgreSQL)
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('search query');

6. Partial Index (PostgreSQL)

CREATE INDEX idx_orders_active ON orders(id) 
WHERE status = 'active';

-- Индекс только для активных заказов
-- Меньше место на диске и быстрее вставка

Плюсы индексов

1. Ускорение поиска (главный плюс)

// Запрос становится в 100-1000 раз быстрее
const users = await db.query(
  'SELECT * FROM users WHERE email = ?',
  [email]
);
// С индексом: 10ms
// Без индекса: 5000ms

2. Ускорение JOIN

SELECT u.name, COUNT(o.id) 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'RU'
GROUP BY u.id;

-- Индекс на orders.user_id ускоряет JOIN
CREATE INDEX idx_orders_user_id ON orders(user_id);

3. Ускорение SORT и GROUP BY

SELECT * FROM orders ORDER BY created_at LIMIT 10;
-- Индекс на created_at позволяет получить 10 последних за O(10) вместо O(n)

CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Минусы индексов

1. Требует дополнительное место на диске

TABLE size: 1GB
INDEX size: 500MB (50% от таблицы)
-- Каждый индекс занимает место

2. Замедляет INSERT, UPDATE, DELETE

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- БД должна обновить все индексы, содержащие email
-- 5 индексов = 5 раз больше работы на вставку

3. Требует обслуживания

-- Индексы могут фрагментироваться
REINDEX TABLE users;  -- PostgreSQL
OPTIMIZE TABLE users; -- MySQL

Best Practices

1. Индексируйте колонки в WHERE

-- часто используется в WHERE
SELECT * FROM users WHERE status = 'active';
CREATE INDEX idx_users_status ON users(status);

-- часто используется в WHERE
SELECT * FROM posts WHERE user_id = ? AND created_at > ?;
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

2. Индексируйте колонки в JOIN

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_products_category_id ON products(category_id);

3. Не индексируйте всё подряд

-- Плохо: индекс на редко используемое поле
CREATE INDEX idx_users_created_at ON users(created_at);
-- Если это поле редко используется в WHERE, индекс не поможет

-- Хорошо: анализируйте slow queries
SELECT * FROM users WHERE created_at = ?;  -- часто?
ENABLE slow_query_log;

4. Используйте EXPLAIN для анализа

-- Проверяет, используется ли индекс
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Результат с индексом:
-- Index Scan using idx_users_email

-- Результат без индекса:
-- Seq Scan on users (sequential scan — плохо!)

5. Порядок в составных индексах важен

CREATE INDEX idx_bad ON orders(created_at, user_id);
-- Помогает WHERE created_at = ?
-- НЕ помогает WHERE user_id = ?

CREATE INDEX idx_good ON orders(user_id, created_at);
-- Помогает WHERE user_id = ?
-- Помогает WHERE user_id = ? AND created_at > ?
-- Помогает WHERE user_id = ? (prefix search)

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

// Было: 5000ms (без индексов)
const orders = await db.query(`
  SELECT u.name, COUNT(o.id) as order_count
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.country = 'RU' AND u.status = 'active'
  GROUP BY u.id
  ORDER BY order_count DESC
  LIMIT 100
`);

// Добавляем индексы
CREATE INDEX idx_users_country_status ON users(country, status);
CREATE INDEX idx_orders_user_id ON orders(user_id);

// Стало: 100ms (с индексами — 50x быстрее!)

Резюме

Индексы — это критически важный инструмент оптимизации БД. Они могут сделать запрос в 100-1000 раз быстрее, но требуют внимательного анализа и планирования. Всегда используйте EXPLAIN для проверки и не индексируйте случайно.