Фрагментация индексов, rebuild vs reorganize, мониторинг использования, неиспользуемые индексы
Индексы требуют обслуживания. Без неё они деградируют: запросы замедляются, место расходуется неэффективно, оптимизатор принимает неверные решения.
Фрагментация возникает при частых UPDATE и DELETE:
-- При DELETE строка помечается как удалённая, но место не освобождается сразу
DELETE FROM orders WHERE status = 'cancelled';
-- При UPDATE строка может переместиться, оставляя 'дыру'
UPDATE orders SET status = 'shipped' WHERE id = 12345;InnoDB не сразу переиспользует освободившееся место. Страницы становятся разреженными:
Влияние на производительность:
-- Размер данных vs allocated размер
SELECT
table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_mb,
ROUND(data_free / 1024 / 1024, 2) AS free_mb,
ROUND(data_free * 100.0 / data_length, 2) AS fragmentation_pct
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'orders';data_free — количество неиспользуемого (зарезервированного, но пустого) места. Высокий процент (>20-30%) сигнализирует о фрагментации.
OPTIMIZE TABLE — перестраивает таблицу и все индексы:
-- Для InnoDB это ALTER TABLE ... ENGINE=InnoDB
OPTIMIZE TABLE orders;
-- Эквивалентная команда
ALTER TABLE orders ENGINE=InnoDB;
-- Или
ALTER TABLE orders FORCE;Что происходит:
Важно: OPTIMIZE TABLE для InnoDB блокирует таблицу на время выполнения. С MySQL 5.6+ используется online DDL — таблица доступна для чтения/записи, но операция всё равно потребляет CPU, I/O и временное место (копия таблицы).
Когда оптимизировать:
Performance Schema отслеживает использование каждого индекса:
-- Статистика по каждому индексу
SELECT
object_schema,
object_name,
index_name,
count_read,
count_write,
ROUND(sum_timer_wait / 1000000000000, 3) AS wait_time_s
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
ORDER BY count_read DESC;count_read — сколько раз индекс использовался для чтения. count_write — для записи (INSERT, UPDATE, DELETE).
sys.schema_unused_indexes — неиспользуемые индексы:
-- Индексы с count_read = 0
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';Важные ограничения:
Не удаляйте индексы из schema_unused_indexes немедленно:
-- Шаг 1: Переименовать вместо удаления
ALTER TABLE orders RENAME INDEX idx_unused TO idx_unused_backup;
-- Шаг 2: Мониторить 1-2 недели
-- Проверить slow query log — не появились ли новые медленные запросы
-- Проверить EXPLAIN для критичных запросов
-- Шаг 3: Если проблем нет — удалить
ALTER TABLE orders DROP INDEX idx_unused_backup;Зачем переименовывать, а не удалять: быстрое восстановление (RENAME INDEX idx_unused_backup TO idx_unused) без перестроения индекса.
-- Обновить статистику кардинальности
ANALYZE TABLE orders;
-- Проверить результат
SHOW INDEX FROM orders;
-- Столбец Cardinality должен обновитьсяЗапускайте ANALYZE TABLE:
Далее: Кластерные и вторичные индексы InnoDB