Когда стоит удалить индексы в БД?

Удаление индексов из БД: стратегия оптимизации

Парадокс индексов

Индексы — это двусторонний меч. Они ускоряют SELECT, но замедляют INSERT/UPDATE/DELETE и потребляют дополнительное место на диске. Удаление ненужных индексов — критическая оптимизация.

Основные сценарии для удаления индексов

1. Индекс никогда не используется (deadweight index)

-- Найти неиспользуемые индексы в PostgreSQL
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Не используется вообще
ORDER BY pg_relation_size(relid) DESC;

-- Найти неиспользуемые индексы в MySQL
SELECT 
    object_schema,
    object_name,
    count_read,
    count_write,
    count_delete,
    count_update
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE count_read = 0
AND object_name != 'PRIMARY'
ORDER BY object_schema, object_name;

Пример:

-- Когда-то создали индекс для отчёта
CREATE INDEX idx_users_registration_country 
 ON users (registration_date, country);

-- Через год выясняется, что отчёт удалили и индекс не используется
-- Статистика показывает: idx_scan = 0
DROP INDEX idx_users_registration_country;  -- Удалить!

Выигрыш:

• Освобождает место на диске (особенно если индекс на большой таблице)
• Ускоряет INSERT/UPDATE/DELETE операции
• Снижает нагрузку на维持（maintenance）индексов

2. Индекс дублирует другой индекс

-- Сценарий: есть составной и одиночный индексы
CREATE INDEX idx_orders_user ON orders (user_id);  -- Одиночный
CREATE INDEX idx_orders_user_date 
 ON orders (user_id, created_at);  -- Составной, более специфичный

-- Составной индекс может использоваться для поиска по user_id
-- (слева направо — левый префикс)
-- Одиночный индекс становится ЛИШНИМ
DROP INDEX idx_orders_user;

Правило составных индексов: Индекс (a, b, c) может использоваться для:

• WHERE a = ? ✓
• WHERE a = ? AND b = ? ✓
• WHERE a = ? AND b = ? AND c = ? ✓
• WHERE b = ? AND c = ? ✗ (b не на первой позиции)
• WHERE c = ? ✗ (c не на первой позиции)

-- Плохо: три индекса, когда нужен один
CREATE INDEX idx_product_category ON products (category);
CREATE INDEX idx_product_category_price ON products (category, price);
CREATE INDEX idx_product_category_price_stock 
 ON products (category, price, stock);

-- Хорошо: один составной индекс
CREATE INDEX idx_product_category_price_stock 
 ON products (category, price, stock);
DROP INDEX idx_product_category;
DROP INDEX idx_product_category_price;

3. Индекс замедляет операции больше, чем ускоряет

-- Сценарий: таблица с высоким turnover (много INSERT/UPDATE)
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(50),
    timestamp TIMESTAMP,
    ip_address INET
);

-- Индекс на булеву колонку (низкая селективность)
CREATE INDEX idx_events_is_active ON events (is_active);
-- Проблема: 99% записей is_active = true
-- Индекс охватывает почти всю таблицу → медленнее, чем полная скан
-- Но требует обновления при КАЖДОМ INSERT/UPDATE

DROP INDEX idx_events_is_active;

Сценарии, когда индекс вредит:

• Низкая селективность: < 5% уникальных значений (boolean, enum)
• Частые обновления: 1000+ INSERT/UPDATEs в секунду
• Маленькая таблица: < 10K строк (полная скан быстрее индекса)
• Очень широкие индексы: 5+ колонок

4. Индекс специфичен для одного быстрого доступа

-- Плохо: два индекса для двух разных запросов
CREATE INDEX idx_users_email ON users (email);     -- Для быстрого login
CREATE INDEX idx_users_phone ON users (phone);     -- Для быстрого поиска по телефону

-- Хорошо: совместить в один
CREATE INDEX idx_users_email_phone ON users (email, phone);
DROP INDEX idx_users_email;
DROP INDEX idx_users_phone;

5. Условный индекс лучше покрывает случай

-- Сценарий: таблица с deleted_at колонкой
CREATE INDEX idx_products_name ON products (name);  -- Весь индекс
-- Но 30% продуктов удалено (deleted_at IS NOT NULL)
-- Индекс содержит много мусора

-- Хорошо: условный индекс
CREATE INDEX idx_products_name_active 
 ON products (name) 
 WHERE deleted_at IS NULL;  -- Индекс только на активные

DROP INDEX idx_products_name;

Выигрыш:

• Индекс меньше на 30%
• Быстрее обновляется
• Более целевой

6. Недавно добавленный индекс не помог

-- Добавили индекс неделю назад
CREATE INDEX idx_orders_status_date 
 ON orders (status, created_at);

-- Мониторим статистику:
SELECT idx_scan, idx_tup_read FROM pg_stat_user_indexes 
WHERE indexname = 'idx_orders_status_date';
-- Результат: idx_scan = 2, индекс используется очень редко

-- Возможные причины:
-- 1. Query planner выбирает другой путь
-- 2. Статистика таблицы неверная
-- 3. Индекс неправильный

-- Если после анализа EXPLAIN видно, что индекс не нужен:
DROP INDEX idx_orders_status_date;

Стратегия: как правильно удалять индексы

Шаг 1: Найти кандидатов на удаление

-- PostgreSQL: индексы, которые едва используются
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan < 100  -- Менее 100 использований
  AND indexname NOT LIKE '%_pkey'  -- Исключи primary keys
ORDER BY pg_relation_size(indexrelid) DESC;

Шаг 2: Проверить, используется ли в коде приложения

// Grep по коду приложения, используется ли индекс
// Если query использует WHERE user_id = ?
// Индекс idx_orders_user_id нужен

// Пример: поиск в коде Java
// grep -r "user_id" src/ | grep -i select

// Если нет упоминаний в коде OR из логов EXPLAIN ANALYZE видно,
// что индекс не используется → кандидат на удаление

Шаг 3: Протестировать удаление (для safety)

-- PostgreSQL: временно отключить индекс (не удалять!)
ALTER INDEX idx_orders_user_id UNUSABLE;  -- Oracle
-- Или в PostgreSQL нет встроенной команды, поэтому:
-- 1. Берём backup
-- 2. Удаляем на тестовой БД
-- 3. Прогоняем нагрузку 2-3 дня
-- 4. Если всё хорошо → удаляем на production

Шаг 4: Удалить с осторожностью

-- Добавить комментарий в коммит/миграцию
DROP INDEX idx_orders_user_id;
-- Комментарий: Индекс не используется (idx_scan=0 за 30 дней)
-- Экономия: 250MB на диске, ускорение INSERT на 3%

Практический пример: аудит и очистка индексов

public class IndexAudit {
    // Проверить индексы через приложение (мониторинг)
    public void analyzeIndexUsage(DataSource dataSource) {
        try (Connection conn = dataSource.getConnection()) {
            String query = """n                SELECT indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
                FROM pg_stat_user_indexes
                WHERE idx_scan = 0
                ORDER BY pg_relation_size(indexrelid) DESC""";
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            
            while (rs.next()) {
                String indexName = rs.getString(1);
                long scans = rs.getLong(2);
                String size = rs.getString(3);
                
                if (scans == 0) {
                    logger.warn("Unused index: {} (size: {})", indexName, size);
                    // Отправить алерт команде
                }
            }
        }
    }
}

Когда НЕ удалять индекс

1. Primary Key индекс — НИКОГДА не удаляй
2. Индекс на Foreign Key — нужен для ссылочной целостности и CASCADE DELETE
3. UNIQUE индекс — нужен для бизнес-правил
4. Индекс, используемый реже 1 раза в час, но критичный для одного отчёта — оставь, но задокументируй

Вывод

Удаляй индексы, когда:

1. idx_scan = 0 за 30+ дней — явно не используется
2. Индекс дублирует другой — только один нужен
3. Низкая селективность (<5% уникальных) + частые обновления
4. Таблица маленькая (<10K строк) — полная скан быстрее
5. Условный индекс эффективнее — замени на WHERE-версию
6. Тест показал улучшение при удалении — DELETE на 99% тест-дней

Дополнительная выгода:

• Сэкономишь место на диске
• Ускоришь INSERT/UPDATE/DELETE операции
• Снизишь нагрузку на I/O subsystem
• Упростишь maintenance и backup

Регулярный аудит индексов (раз в квартал) — обязательная практика оптимизации БД.