Почему не стоит делать индекс у всех столбцов в таблице в БД?

Почему не нужно индексировать все столбцы таблицы

Это частая ошибка молодых разработчиков — думать, что индексы всегда полезны. На самом деле индексы — это trade-off между скоростью чтения и скоростью записи, и важно выбирать их стратегически.

Основные проблемы с индексированием всех столбцов

1. Замедление операций записи (INSERT, UPDATE, DELETE)

Когда вы добавляете, обновляете или удаляете запись, БД должна обновить не только саму таблицу, но и все индексы на изменённые столбцы:

-- Таблица users с 5 индексами
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(255),
    age INT,
    phone VARCHAR(20),
    created_at TIMESTAMP
);

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_phone ON users(phone);
CREATE INDEX idx_created_at ON users(created_at);

-- Это INSERT будет медленнее!
INSERT INTO users VALUES (1, 'john@example.com', 'John', 30, '+1234567890', NOW());

Вместо одной операции вставки БД выполнит 6 операций (вставка в таблицу + обновление 5 индексов).

2. Увеличение используемого дискового пространства

Каждый индекс занимает место на диске. Это означает:

• Больше памяти для буфер-пула БД
• Больше I/O операций для чтения с диска
• Дороже резервные копии и восстановление

Таблица users: 100 MB
Первичный ключ (обязателен): ~10 MB
Индекс на email: ~8 MB
Индекс на name: ~8 MB
Индекс на age: ~5 MB
Индекс на phone: ~8 MB
Индекс на created_at: ~5 MB

Итого: 144 MB вместо 100 MB — +44% памяти!

3. Усложнение обслуживания БД

// При удалении таблицы нужно помнить об индексах
DROP TABLE users; // БД сама удалит индексы

// При добавлении ограничения нужно проверить индексы
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE(email);

// Неправильные индексы приводят к fragmentation
CREATEINDEX idx_redundant ON users(email, email); // Плохо!

4. Optimizer confusion (путаница оптимизатора)

Большое количество индексов затрудняет работу query planner:

-- С множеством индексов оптимизатор может выбрать неоптимальный индекс
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'completed' AND created_at > NOW() - INTERVAL 30 DAY;

-- Без стратегии индексирования БД может выбрать:
-- 1. Индекс на user_id (неправильно)
-- 2. Индекс на status (неправильно)
-- 3. Индекс на created_at (неправильно)
-- 4. Составной индекс (user_id, status, created_at) (правильно)

5. Увеличение времени резервного копирования и восстановления

# Резервная копия должна включать саму таблицу И все индексы
mysqldump mydb users > backup.sql
# Размер backup намного больше, восстановление медленнее

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

Индекс полезен только если:

1. На столбец часто выполняют поиск (WHERE, JOIN, ORDER BY)

-- Часто ищем по email → индекс полезен
SELECT * FROM users WHERE email = 'john@example.com'; -- Индекс нужен

-- Редко ищем по age → индекс бесполезен
-- Если 95% запросов ищут по email, 5% по age
-- → нужен индекс только на email

1. Столбец имеет хорошую селективность (много уникальных значений)

-- Хорошая селективность
CREATE INDEX idx_email ON users(email); -- Почти все значения уникальны

-- Плохая селективность
CREATE INDEX idx_gender ON users(gender); -- Всего 2 значения (M/F)
-- Часто БД скэнирует всю таблицу без индекса

1. Результат запроса составляет малый процент от всей таблицы

SELECT * FROM users WHERE email = 'john@example.com';
-- Результат: 1 строка из 1 млн → индекс выгоден

SELECT * FROM users WHERE is_active = true;
-- Результат: 900k строк из 1 млн → индекс может быть неэффективен

Правильная стратегия индексирования

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(20),
    created_at TIMESTAMP,
    total_amount DECIMAL(10, 2)
);

-- 1. Индекс для частых поисков по user_id (например, "показать заказы пользователя")
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 2. Составной индекс для частых фильтров вместе
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- НЕ создаём индекс на:
-- - total_amount (редко ищем по конкретной сумме)
-- - created_at отдельно (обычно ищем вместе со статусом)

Как выбрать правильные индексы

1. Анализируй slow query log

   SELECT * FROM mysql.slow_log;
   -- Вижу какие запросы долгие
   

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

   EXPLAIN SELECT * FROM orders WHERE user_id = 123;
   -- type: ALL (табличный скэн) → нужен индекс
   -- type: ref (поиск по индексу) → индекс уже есть
   

3. Измеряй реальный impact

   // До добавления индекса: 500ms
   long start = System.currentTimeMillis();
   // query
   long duration = System.currentTimeMillis() - start;
   
   // После: 10ms → стоит держать
   

Правило большого пальца

• Primary key: обязателен (автоматически индексируется)
• Foreign keys: ОЧЕНЬ полезны (для JOIN и REFERENCE integrity)
• WHERE / JOIN / ORDER BY: индексируй столбцы, используемые в этих условиях
• Все остальное: добавляй индекс только если ДОКАЗАЛ, что это улучшит производительность

Заключение

Индексы нужно выбирать стратегически, основываясь на:

• Паттернах запросов в вашем приложении
• Анализе медленных запросов
• Мониторинге реальной производительности

Лучше иметь 3 хорошо выбранных индекса, чем 10 случайных. Количество индексов не равно качеству БД!