Як безпечно видалити індекс за допомогою невидимих індексів?

Видаляти індекси страшно: якщо він таки був потрібен якомусь рідкісному, але важливому запиту, після DROP INDEX цей запит перейде на повне сканування. А відновлення індексу на великій таблиці займає хвилини чи години.

Невидимий індекс (MySQL 8.0+) продовжує існувати й оновлюватися при кожному записі, але оптимізатор його не бачить:

ALTER TABLE orders ALTER INDEX orders_status_idx INVISIBLE;
-- спостерігаємо день-тиждень: чи не з'явилися повільні запити
ALTER TABLE orders ALTER INDEX orders_status_idx VISIBLE;  -- миттєвий відкат
-- або, якщо все спокійно
ALTER TABLE orders DROP INDEX orders_status_idx;

Зміна видимості - операція з метаданими, вона виконується миттєво. Повернення індексу теж миттєве, бо його дані весь час підтримувалися в актуальному стані.

Як знайти кандидатів на видалення:

-- індекси, які не використовувалися з моменту запуску сервера
SELECT * FROM sys.schema_unused_indexes;

-- індекси, що дублюють інші (лівий префікс іншого індексу)
SELECT * FROM sys.schema_redundant_indexes;

Статистика використання береться з Performance Schema й скидається при перезапуску сервера. Якщо сервер перезапускали вчора, «невикористаний» індекс міг бути потрібен щомісячному звіту. Тому період спостереження має охоплювати всі регулярні задачі.

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

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT ...;   -- чи обрав би оптимізатор цей індекс

Так само можна підготувати новий індекс: створити його невидимим, перевірити плани в сесії й лише потім відкрити для всіх.

Обмеження:

• первинний ключ не може бути невидимим, як і унікальний індекс, що неявно виконує роль первинного ключа;
• невидимий індекс продовжує сповільнювати запис і займати місце. Це інструмент перевірки, а не постійний стан;
• унікальний невидимий індекс і далі перевіряє унікальність;
• підказка FORCE INDEX на невидимий індекс поверне помилку - корисний спосіб помітити, що десь у коді на нього посилаються.

Чому взагалі видаляти індекси: кожен індекс додає вартість кожному INSERT, UPDATE і DELETE, займає буферний пул і місце на диску. Зайві індекси - тихий податок на запис.

Докладніше в документації: Невидимі індекси