Schema checks MySQL

Cette page est une documentation originale PmaControl. Elle reprend une structure d'index pratique pour travailler sur l'optimisation MySQL, sans copier de contenu tiers ni dépendre d'un produit externe.

Objectif

Les schema checks cherchent les structures qui coûtent cher en production: absence d'index, colonnes surdimensionnées, clefs primaires manquantes, tables MyISAM historiques et index jamais utilisés.

Points à contrôler

• Tables InnoDB sans clef primaire.
• Index dupliqués ou fortement redondants.
• Colonnes VARCHAR ou TEXT utilisées dans des index trop larges.
• Colonnes de jointure avec types ou collations différents.
• Tables volumineuses sans stratégie d'archivage.
• Contraintes de foreign keys absentes là où l'intégrité est métier.

Requêtes utiles

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'
  );

A retenir

Un schema check pertinent relie chaque anomalie à une requête, un plan d'exécution ou un coût opérationnel mesurable.

Inventorier tailles, moteurs et responsables

Commencez par les tables critiques et les requêtes qui les utilisent. Notez leur fonction, rétention, débit d’écriture et fenêtre de maintenance. Les métadonnées identifient des candidats, pas une preuve de problème. Pour InnoDB, nombre de lignes et tailles sont estimés : utilisez-les pour classer, pas pour une comptabilité exacte. Évitez les comptages complets coûteux uniquement pour rendre l’inventaire plus précis.

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;

Confronter les index candidats à la charge réelle

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');

Ces vues sys MySQL exigent le schéma installé et l’instrumentation utile disponible. Un index inutilisé ne l’est que pour la charge observée : incluez reporting, clôture mensuelle, lectures sur réplicas et rôle après bascule. Vérifiez unicité et dépendances de clefs étrangères avant suppression. Un préfixe commun est un candidat à examiner, pas une consigne de suppression. Avec MySQL 8+, certains index peuvent d’abord être rendus invisibles lors d’un test contrôlé, tout en conservant leur coût en écriture.

Vérifier les accès et la compatibilité des colonnes

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;

Remplacez app.orders et ses colonnes par une requête représentative réelle. Examinez index possibles et retenu, lignes estimées, filtres résiduels et tri. Dans cet exemple, un index commençant par (customer_id, created_at) est un candidat à tester, pas une prescription universelle. Alignez types, signe, jeux de caractères et collations des colonnes jointes. Conversions implicites et fonctions peuvent modifier l’accès. Vérifiez le plan avec des distributions réalistes, y compris les gros clients.

Classer les constats structurels

Transformer un constat en migration maîtrisée

• Capturer SHOW CREATE TABLE, requêtes dépendantes, clefs étrangères et plan initial.
• Estimer espace temporaire, durée, attentes de verrous de métadonnées et impact réplica pour le DDL et la version précis. Un DDL en ligne peut attendre ces verrous.
• Tester sur données représentatives ; comparer latence en lecture, coût en écriture, taille et exactitude des résultats.
• Planifier le retour arrière : recréer un index supprimé prend du temps ; annuler un schéma peut exiger conversion ou restauration.

Poursuivre le diagnostic

• Reliez chaque constat au tuning et consignez-le dans l’évaluation.

Références officielles

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