Проверки схемы MySQL

Эта страница является оригинальной документацией PmaControl. Она сохраняет практичную структуру индекса для работ по оптимизации MySQL, не копируя сторонний контент и не завися от внешнего продукта.

Цель

Проверки схемы ищут структуры, дорогие в production: отсутствующие индексы, слишком широкие колонки, отсутствие первичных ключей, исторические таблицы MyISAM и неиспользуемые индексы.

Что проверять

1. Таблицы InnoDB без первичного ключа.
2. Дублирующиеся или сильно избыточные индексы.
3. Колонки VARCHAR или TEXT в слишком широких индексах.
4. Колонки join с разными типами или collations.
5. Большие таблицы без стратегии архивирования.
6. Отсутствующие foreign keys там, где бизнес-целостность этого требует.

Полезные запросы

SELECT table_schema, table_name
FROM information_schema.tables t
WHERE engine = 'InnoDB'
  AND table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
  AND NOT EXISTS (
      SELECT 1
      FROM information_schema.table_constraints c
      WHERE c.table_schema = t.table_schema
        AND c.table_name = t.table_name
        AND c.constraint_type = 'PRIMARY KEY'
  );

Главное

Полезная проверка схемы связывает каждую аномалию с запросом, планом выполнения или измеримыми операционными затратами.

Соберите размеры, движки и владельцев

Начните с критичных бизнес-таблиц и обращающихся к ним запросов. Запишите назначение, срок хранения, скорость записи и окно обслуживания. Метаданные выделяют кандидатов, но не доказывают проблему производительности. Число строк и размеры InnoDB оценочные: используйте их для ранжирования, а не точного учёта. Избегайте дорогих полных подсчётов ради видимости точности.

SELECT table_schema, table_name, engine, table_rows,
       ROUND(data_length / 1024 / 1024, 1) AS data_mib,
       ROUND(index_length / 1024 / 1024, 1) AS index_mib
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('mysql', 'sys', 'performance_schema',
                           'information_schema')
ORDER BY data_length + index_length DESC
LIMIT 20;

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

SELECT table_schema, table_name, redundant_index_name,
       dominant_index_name
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql', 'sys');

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys');

Эти представления MySQL sys требуют установленной схемы и необходимой инструментации. Индекс не используется только в наблюдавшейся нагрузке: включите отчёты, закрытие месяца, чтение с реплик и роли после переключения. Перед удалением проверьте уникальность и зависимости внешних ключей. Общий префикс — повод для анализа, а не автоматического удаления. В MySQL 8+ допустимые индексы можно сначала сделать невидимыми в контролируемом испытании, но стоимость их обновления сохраняется.

Проверьте доступ и совместимость столбцов

SELECT table_name, column_name, column_type, is_nullable,
       character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'app'
ORDER BY table_name, ordinal_position;

EXPLAIN FORMAT=JSON
SELECT id, created_at
FROM app.orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Замените app.orders и столбцы реальным типичным запросом. Проверьте возможные и выбранный ключи, оценку строк, остаточные фильтры и сортировку. Здесь индекс с началом (customer_id, created_at) — кандидат для испытания, не универсальный рецепт. Согласуйте типы, знаковость, кодировки и сопоставления столбцов соединения. Неявные преобразования и функции могут менять способ доступа. Проверяйте план с реалистичными распределениями параметров, включая крупных клиентов.

Классифицируйте проблемы структуры

Подготовьте контролируемую миграцию

• Сохраните SHOW CREATE TABLE, зависимые запросы, внешние ключи и исходный план.
• Оцените временный диск, время, ожидания блокировок метаданных и влияние на реплики для конкретного DDL и версии. Онлайн-DDL также может ждать этих блокировок.
• Испытайте на типичных данных: сравните чтение, стоимость записи, размер и корректность.
• Спланируйте откат: восстановление индекса требует времени, а отмена схемы — преобразования данных или восстановления.

Продолжение диагностики

• Свяжите находки с настройкой и запишите в оценке.

Официальные источники

• MySQL redundant indexes
• MySQL unused indexes
• MySQL online DDL