Невидимые индексы MySQL: проверяем удаление без риска

Лишний индекс расходует место и добавляет работу при INSERT, UPDATE и DELETE. Но удалять его сразу опасно: редкий отчёт, ночная задача или запрос закрытия месяца может использовать индекс, который не проявился во время короткого наблюдения.

В MySQL 8.0 и 8.4 такой индекс можно сначала сделать невидимым. Оптимизатор перестанет учитывать его в обычных планах, но физическая структура останется на месте. Если обнаружится регрессия, видимость возвращается без повторного построения индекса.

Что именно меняет INVISIBLE

Невидимость влияет на выбор плана, а не на обслуживание структуры. MySQL продолжает обновлять индекс при изменении строк. Уникальный невидимый индекс по-прежнему запрещает дубликаты. Поэтому тест показывает, могут ли запросы работать без индекса, но ещё не показывает экономию записи и диска, которая появится только после окончательного удаления.

Первичный ключ сделать невидимым нельзя. Ограничение относится и к индексу, который MySQL использует как неявный первичный ключ: например, к первому подходящему UNIQUE NOT NULL при отсутствии явного PRIMARY KEY.

Проверяем версию и выбираем кандидата

Функция появилась в MySQL 8.0. Перед изменением проверяем сервер, а не версию клиентской утилиты:

SELECT VERSION();

Далее смотрим точный состав индексов таблицы. В примере используется условная схема app и таблица orders:

SELECT
    INDEX_NAME,
    NON_UNIQUE,
    IS_VISIBLE,
    GROUP_CONCAT(
        COLUMN_NAME
        ORDER BY SEQ_IN_INDEX
        SEPARATOR ', '
    ) AS index_columns
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders'
GROUP BY INDEX_NAME, NON_UNIQUE, IS_VISIBLE
ORDER BY INDEX_NAME;

Не выбирайте кандидата только по названию или похожему набору колонок. Порядок полей в составном индексе определяет, какие условия и сортировки он обслуживает. Это подробно разобрано в статье «Составной индекс MySQL: порядок колонок начинается с запроса».

Фиксируем исходный план

Допустим, проверяется индекс idx_orders_customer_created по полям customer_id, created_at. До изменения сохраняем план характерного запроса:

EXPLAIN FORMAT=TREE
SELECT id, created_at, status
FROM app.orders
WHERE customer_id = 12345
  AND created_at >= CURRENT_DATE - INTERVAL 30 DAY
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN не доказывает, что индекс не нужен всему приложению. Нужны несколько запросов: интерактивные страницы, фоновые задания, отчёты и служебные выборки. Значения в примере фиктивные; на рабочей системе используйте безопасные тестовые параметры и не публикуйте реальные идентификаторы.

Делаем индекс невидимым

ALTER TABLE app.orders
    ALTER INDEX idx_orders_customer_created INVISIBLE;

Изменение видимости выполняется без перестроения самого индекса, однако это всё равно DDL-операция. Она может ожидать metadata lock из-за долгой транзакции. Выполняйте изменение в контролируемое время и не считайте зависший ALTER TABLE признаком большого объёма индекса — сначала проверьте блокировки и активные транзакции.

Убеждаемся, что изменился именно нужный объект:

SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders'
  AND INDEX_NAME = 'idx_orders_customer_created';

Ожидаемое значение IS_VISIBLE — NO.

Сравниваем планы двумя способами

Обычный план без индекса

Повторяем исходный EXPLAIN FORMAT=TREE. В плане может появиться другой индекс, более широкий диапазон или полный просмотр таблицы. Важны не только имя в строке key, но и предполагаемое число строк, способ доступа, дополнительная сортировка и временная таблица.

Контрольный план с невидимым индексом

Чтобы проверить один запрос с исключённым индексом, не меняя настройку всей сессии или сервера, временно разрешаем невидимые индексы через подсказку SET_VAR:

EXPLAIN FORMAT=TREE
SELECT /*+ SET_VAR(
    optimizer_switch = 'use_invisible_indexes=on'
) */
    id, created_at, status
FROM app.orders
WHERE customer_id = 12345
  AND created_at >= CURRENT_DATE - INTERVAL 30 DAY
ORDER BY created_at DESC
LIMIT 50;

Если контрольный план снова выбирает idx_orders_customer_created, а обычный план заметно тяжелее, индекс остаётся полезным кандидатом для этого запроса. Не включайте use_invisible_indexes=on глобально: иначе проверка перестанет имитировать отсутствие индекса.

Наблюдаем реальную нагрузку

Одного запуска недостаточно. Период наблюдения должен включать полный цикл работы приложения: пиковые часы, фоновые задания, регулярные отчёты и редкие операции. Сравнить агрегированную статистику можно через Performance Schema:

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
    SUM_ROWS_EXAMINED,
    SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'app'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

Таблица агрегирует запросы по digest, поэтому она удобнее списка отдельных выполнений. Смотрите не только на среднее время: рост SUM_ROWS_EXAMINED, появление SUM_NO_INDEX_USED и увеличение общей стоимости могут показать проблему раньше жалоб пользователей.

Дополнительно проверьте журнал медленных запросов. Как собрать данные без включения бессрочного подробного логирования, описано в материале «Медленные запросы MySQL: собираем журнал для расследования».

Когда индекс нужно немедленно вернуть

Возвращайте индекс в видимое состояние, если после изменения:

• критичный запрос перешёл на полный просмотр или стал просматривать существенно больше строк;
• в slow log появились ранее быстрые запросы;
• выросли задержки фоновых задач или страниц приложения;
• запросы с USE INDEX, FORCE INDEX или IGNORE INDEX начали завершаться ошибкой из-за ссылки на невидимый индекс.

ALTER TABLE app.orders
    ALTER INDEX idx_orders_customer_created VISIBLE;

После возврата повторите проверку IS_VISIBLE и планы проблемных запросов. Сам факт быстрого восстановления — главное преимущество этой процедуры перед немедленным DROP INDEX.

Почему невидимый индекс нельзя оставить навсегда

Если индекс признан ненужным, постоянная невидимость не даёт основной экономии: структура всё ещё занимает место и обслуживается при записи. После достаточного периода наблюдения удаление оформляют отдельной миграцией с резервной копией, планом отката и проверкой реплик:

ALTER TABLE app.orders
    DROP INDEX idx_orders_customer_created;

Перед этой командой ещё раз проверьте состояние индекса и актуальные планы. Восстановление после DROP INDEX потребует повторного создания структуры и может быть дорогим на большой таблице.

Короткий контрольный список

• версия сервера поддерживает невидимые индексы;
• кандидат не является явным или неявным первичным ключом;
• состав и назначение индекса подтверждены запросами, а не названием;
• исходные планы и статистика сохранены до изменения;
• после INVISIBLE проверен полный цикл нагрузки;
• команда возврата VISIBLE подготовлена заранее;
• окончательное удаление выполняется отдельным контролируемым изменением.

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

Источники и дальнейшее чтение

• MySQL 8.4 Reference Manual — Invisible Indexes
• MySQL 8.4 Reference Manual — ALTER TABLE Statement
• MySQL 8.4 Reference Manual — Statement Summary Tables
• MySQL 8.0 Release Notes — Changes in MySQL 8.0.0