Какие плюсы и минусы индексирования каждой колонки?

Какие плюсы и минусы индексирования каждой колонки:

Индекс — это отдельная структура данных

Индекс похож на оглавление в книге: вместо прочтения каждой страницы ищем в оглавлении.

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

1. Ускорение поиска (SELECT)

-- БЕЗ индекса: полный проход по всем 10 млн строк (O(n))
SELECT * FROM users WHERE email = 'john@example.com';
-- Время: 5-10 секунд

-- С индексом: поиск за логарифмическое время (O(log n))
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';
-- Время: 1-5 миллисекунд

Ускорение в 1000-10000 раз!

2. Ускорение сортировки

-- БЕЗ индекса: нужно сортировать все 10 млн строк
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- С индексом: уже отсортировано, берем первые 10
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Почти мгновенно

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

CREATE INDEX idx_users_role ON users(role);
SELECT role, COUNT(*) FROM users GROUP BY role;
-- С индексом: данные уже сгруппированы

4. Индекс как PRIMARY KEY (ускорение по уникальности)

CREATE UNIQUE INDEX idx_users_username ON users(username);
-- Гарантирует что username уникален И ускоряет поиск

Минусы индексирования

1. Замедление INSERT / UPDATE / DELETE

-- БЕЗ индексов: просто добавляем строку (быстро)
INSERT INTO users VALUES (...);

-- С 5 индексами: нужно обновить все 5 индексов
CREATE INDEX idx1 ON users(email);
CREATE INDEX idx2 ON users(username);
CREATE INDEX idx3 ON users(role);
CREATE INDEX idx4 ON users(created_at);
CREATE INDEX idx5 ON users(status);

INSERT INTO users VALUES (...);
-- Теперь медленнее в 5-10 раз!

Почему? Индекс — это B-tree структура. Каждый INSERT требует:

• Поиска позиции в индексе (O(log n))
• Вставки и переиндексации (балансировка дерева)

2. Огромное потребление памяти

-- Таблица users: 100 ГБ
-- Индекс на email: +10 ГБ
-- Индекс на username: +10 ГБ
-- Индекс на created_at: +10 ГБ
-- 5 индексов: +50 ГБ дополнительно!

-- Если это не умещается в RAM → медленнее чем без индекса

3. Замедление, если индекс "неправильный"

-- Плохой индекс: на column с низкой selectivity
CREATE INDEX idx_users_gender ON users(gender);  -- Только 2 значения!

SELECT * FROM users WHERE gender = 'M';
-- Индекс нужен для 5 млн строк из 10 млн.
-- БД может решить что проще читать всю таблицу!

4. Фрагментация индекса

При частых DELETE/UPDATE индекс может "разрваться" и требовать реиндексации:

-- После массового удаления старых данных
REINDEX TABLE users;
-- Может заблокировать таблицу на несколько часов

5. Затраты на обслуживание

-- Периодически нужно анализировать индексы
ANALYZE TABLE users;

-- Удалять старые индексы
DROP INDEX idx_old ON users;

-- Мониторить их использование
SELECT * FROM pg_stat_user_indexes;

Правило большого пальца: когда индексировать

ДА, индексируй если:

1. Колонка часто используется в WHERE:

-- Часто ищешь? Индексируй!
SELECT * FROM orders WHERE user_id = 123;  -- Много раз в день
CREATE INDEX idx_orders_user_id ON orders(user_id);

1. Колонка в ORDER BY:

SELECT * FROM orders ORDER BY created_at DESC;  -- Часто?
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

1. Колонка в JOIN ON:

SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;  -- user_id часто
CREATE INDEX idx_orders_user_id ON orders(user_id);

1. FOREIGN KEY:

ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id);
-- Автоматически нужен индекс на user_id

1. Высокая selectivity (много разных значений):

-- email: 10 млн разных значений из 10 млн строк (высокая selectivity)
CREATE INDEX idx_users_email ON users(email);

-- gender: 2 значения из 10 млн (низкая selectivity) — не индексируй!

НЕТ, не индексируй если:

1. Колонка редко используется:

-- Это поле запрашивают 1 раз в месяц? Не нужен индекс
SELECT * FROM users WHERE favorite_color = 'blue';

1. Много INSERT/UPDATE, мало SELECT:

-- Логирование: пишешь 10k раз в секунду, читаешь редко
-- Индекс затормозит запись
CREATE TABLE logs (id, timestamp, message);  -- Без индекса

1. Низкая selectivity:

-- Индекс на boolean займет место, но не поможет
CREATE INDEX idx_users_active ON users(is_active);
-- Лучше: не индексируй, БД прочитает всю таблицу в памяти

1. Таблица маленькая:

-- В таблице только 1000 строк
-- Полный scan в памяти быстрее чем индекс
CREATE TABLE roles (id, name);  -- Без индекса

Составные индексы (Composite Indexes)

-- Если часто ищешь по двум колонкам
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- Лучше один составной индекс чем два отдельных
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Порядок важен! user_id должен быть первым (selectivity выше)

Практический пример для Node.js приложения

// В миграции PostgreSQL:
const migration = `
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE,  -- Автоматически индексируется
  username VARCHAR(100),
  role VARCHAR(50),
  created_at TIMESTAMP,
  is_active BOOLEAN
);

-- Индексы для быстрых запросов
CREATE INDEX idx_users_email ON users(email);       -- WHERE email = ?
CREATE INDEX idx_users_username ON users(username); -- WHERE username = ?
CREATE INDEX idx_users_role ON users(role);        -- GROUP BY role
CREATE INDEX idx_users_created_at ON users(created_at DESC);  -- ORDER BY

-- НЕ индексируем is_active (низкая selectivity, мало запросов)
`;

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

-- PostgreSQL: какие индексы используются
SELECT * FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Индексы с 0 scans можно удалить
DROP INDEX idx_unused ON users;

Итоговая рекомендация

Не индексируй каждую колонку! Вместо этого:

1. Измерь: какие запросы медленные? (EXPLAIN ANALYZE)
2. Индексируй только колонки используемые в медленных запросах
3. Мониторь: использует ли БД индекс? (EXPLAIN)
4. Удали неиспользуемые индексы каждый месяц

Лучше иметь 5 необходимых индексов чем 50 ненужных.