Как чистить индексы?

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

Чистка индексов — это критически важная операция для поддержания производительности базы данных. Она включает в себя удаление неиспользуемых, избыточных или неэффективных индексов с целью снижения накладных расходов на запись (INSERT/UPDATE/DELETE) и освобождения дискового пространства.

Почему важна чистка индексов?

• Снижение нагрузки на запись: Каждый индекс замедляет операции INSERT, UPDATE и DELETE, так как система должна обновлять все связанные индексы.
• Оптимизация использования дискового пространства: Неиспользуемые индексы занимают место.
• Улучшение производительности запросов: Слишком много индексов может запутать оптимизатор запросов, выбравший неоптимальный план выполнения.
• Ускорение резервного копирования и восстановления: Меньше индексов — меньше данных для обработки.

Как определить индексы для чистки?

1. Анализ использования индексов

Просмотр статистики использования индексов за определённый период. В PostgreSQL можно использовать представление pg_stat_user_indexes:

SELECT 
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0  -- Индексы, которые никогда не использовались
ORDER BY schemaname, relname;

2. Поиск дублирующих индексов

Дубликаты возникают, когда несколько индексов покрывают одни и те же или пересекающиеся столбцы. Пример для PostgreSQL:

-- Поиск индексов с одинаковыми ключевыми столбцами
SELECT 
    indrelid::regclass AS table_name,
    array_agg(indexrelid::regclass) AS duplicate_indexes,
    array_agg(indexdef) AS index_definitions
FROM pg_indexes
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;

3. Анализ селективности индексов

Индексы с низкой селективностью (например, на столбцы с малым количеством уникальных значений) часто неэффективны:

-- Определение селективности индексов
SELECT 
    tablename,
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE tablename = 'your_table'
ORDER BY n_distinct;

Процесс чистки индексов

1. Планирование и тестирование

• Анализ рабочей нагрузки: Проведите анализ в периоды пиковой нагрузки.
• Тестирование в staging-среде: Убедитесь, что удаление индекса не нарушит работу критических запросов.
• Резервное копирование: Перед удалением убедитесь в наличии актуальной резервной копии.

2. Мониторинг производительности

Используйте мониторинговые системы для отслеживания:

• QPS (Queries Per Second)
• Latency (задержка запросов)
• Load на дисковую подсистему

3. Инкрементальный подход

Удаляйте индексы по одному, наблюдая за последствиями:

-- Пример безопасного удаления индекса
BEGIN;
-- Вначале переименуем индекс (на случай отката)
ALTER INDEX idx_old_name RENAME TO idx_old_name_deprecated;
-- Наблюдаем за работой системы
-- Если проблем нет, удаляем
DROP INDEX idx_old_name_deprecated;
COMMIT;

4. Автоматизация процесса

Для регулярной чистки можно создать автоматизированный скрипт:

// Пример на Go для анализа неиспользуемых индексов
package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/lib/pq"
)

type IndexStat struct {
    Schema     string
    Table      string
    Index      string
    Scans      int64
    LastScan   sql.NullTime
}

func getUnusedIndexes(db *sql.DB, daysThreshold int) ([]IndexStat, error) {
    query := `
        SELECT 
            schemaname,
            relname,
            indexrelname,
            idx_scan,
            last_idx_scan
        FROM pg_stat_user_indexes
        WHERE idx_scan < 100  -- Пороговое значение
        AND schemaname NOT IN ('pg_catalog', 'information_schema')
        ORDER BY idx_scan ASC;
    `
    
    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    var indexes []IndexStat
    for rows.Next() {
        var idx IndexStat
        err := rows.Scan(&idx.Schema, &idx.Table, &idx.Index, &idx.Scans, &idx.LastScan)
        if err != nil {
            return nil, err
        }
        indexes = append(indexes, idx)
    }
    
    return indexes, nil
}

Лучшие практики

Регулярный мониторинг

• Установите регулярный регламент проверки индексов (например, ежеквартально).
• Используйте инструменты вроде pgBadger для PostgreSQL или EXPLAIN ANALYZE для анализа планов запросов.

Стратегии предотвращения

1. Контроль создания индексов: Внедрите процесс ревью для создания новых индексов.
2. Документация: Ведите реестр индексов с указанием причины создания и ответственного.
3. Использование частичных индексов: Создавайте индексы только для нужных подмножеств данных.
4. Покрывающие индексы (INCLUDE): Используйте для включения дополнительных столбцов без их участия в сортировке.

Особенности для разных СУБД

• PostgreSQL: Используйте расширение pg_stat_statements для детального анализа.
• MySQL/MariaDB: Анализируйте slow query log и используйте performance_schema.
• MongoDB: Используйте $indexStats и профилирование.

Риски и меры предосторожности

1. Потеря производительности чтения: Удаление нужного индекса может замедлить SELECT-запросы в сотни раз.
2. Блокировки: Операция DROP INDEX может блокировать таблицу. В продакшене используйте DROP INDEX CONCURRENTLY (в PostgreSQL).
3. Скрытые зависимости: Некоторые индексы могут использоваться только в редких, но критических запросах (например, ежемесячные отчёты).

Вывод: Чистка индексов — это балансировка между производительностью чтения и записи. Подходите к процессу систематически: анализируйте, тестируйте, мониторьте. Лучше удалить один лишний индекс и наблюдать неделю, чем удалить несколько сразу и получить проблемы с производительностью в пиковую нагрузку.