mysql-patterns

Modèles MySQL (Patterns)

Utilisez ce skill lorsque vous travaillez sur la conception de schémas MySQL ou MariaDB, les migrations, l'investigation de requêtes lentes, les transactions de type file d'attente, les pools de connexions, ou la configuration de bases de données de production. Préférez d'abord vérifier la version exacte avant d'appliquer un modèle spécifique à une fonctionnalité, car MySQL et MariaDB ont divergé sur plusieurs détails SQL.

Activation

• Conception de tables, index et contraintes MySQL ou MariaDB
• Révision de migrations avant leur exécution sur de grandes tables de production
• Débogage de requêtes lentes, d'attentes de verrous, d'interblocages, ou d'épuisement des connexions
• Ajout de pagination par jeu de clés, upserts, recherche plein texte, colonnes JSON ou files d'attente
• Configuration de pools de connexions applicatives, réplicas de lecture, TLS ou logs de requêtes lentes

Vérification de version

Commencez par identifier le moteur et sa version :

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

Gardez une séparation entre les conseils MySQL et MariaDB lorsque la syntaxe diffère :

• MySQL documente les alias de ligne comme remplacement de VALUES(col) dans ON DUPLICATE KEY UPDATE ; VALUES(col) y est déprécié.
• MariaDB documente VALUES(col) comme la manière prise en charge de référencer les valeurs insérées dans ON DUPLICATE KEY UPDATE ; utilisez-la pour la compatibilité entre moteurs.
• SKIP LOCKED est approprié uniquement pour un travail de type file d'attente. Elle ignore les lignes verrouillées et peut renvoyer une vue incohérente ; ne l'utilisez pas pour la comptabilité générale ou les lectures sensibles à l'intégrité.

Valeurs par défaut pour les schémas

CREATE TABLE commandes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    compte_id BIGINT UNSIGNED NOT NULL,
    statut VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    cree_le DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    mis_a_jour_le DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    supprime_le DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_commandes_compte_statut_cree (compte_id, statut, cree_le),
    KEY idx_commandes_actives (compte_id, supprime_le)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Choix par défaut :

Cas d'usage	Préférer	Éviter
Clés primaires substitutives	BIGINT UNSIGNED AUTO_INCREMENT	INT pour les tables pouvant dépasser 2 milliards de lignes
Clés de recherche UUID	BINARY(16) avec fonctions de conversion	VARCHAR(36) comme clé primaire sur les tables chaudes
Montants et quantités exactes	DECIMAL(p, s)	FLOAT ou DOUBLE
Texte destiné aux utilisateurs	Tables et index en utf8mb4	Par défaut MySQL utf8 / utf8mb3
Horodatages applicatifs	DATETIME avec UTC géré par l'application	Supposer que DATETIME stocke les métadonnées de fuseau horaire
Suppression logique	supprime_le DATETIME NULL avec index filtrés	Filtrer les lignes supprimées logiquement sans index
Valeurs de statut extensibles	Table de référence ou VARCHAR contraint	ENUM lorsque les valeurs changent souvent

Indexation

L'ordre d'un index composite suit généralement les prédicats d'égalité en premier, puis les colonnes d'intervalle ou de tri :

CREATE INDEX idx_commandes_compte_statut_cree
    ON commandes (compte_id, statut, cree_le);

SELECT id, total
FROM commandes
WHERE compte_id = ?
  AND statut = 'en_attente'
  AND cree_le >= ?
ORDER BY cree_le DESC
LIMIT 50;

Utilisez EXPLAIN avant d'ajouter ou de modifier un index :

EXPLAIN
SELECT id, total
FROM commandes
WHERE compte_id = 123 AND statut = 'en_attente'
ORDER BY cree_le DESC
LIMIT 50;

Indices nécessitant une investigation :

Champ	Signal de risque
type	ALL sur une grande table
key	NULL lorsqu'un prédicat sélectif existe
rows	Estimation de lignes très élevée pour un chemin interactif
Extra	Using temporary, Using filesort, ou Using where large

Évitez d'ajouter des indices aveuglément. Chaque index augmente le coût d'écriture, le temps de migration, la taille des sauvegardes et la pression sur le buffer pool.

Modèles de requêtes

Upsert

Forme compatible entre moteurs :

INSERT INTO parametres_utilisateur (utilisateur_id, cle_parametre, valeur_parametre)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    valeur_parametre = VALUES(valeur_parametre),
    mis_a_jour_le = CURRENT_TIMESTAMP;

Forme avec alias de ligne MySQL :

INSERT INTO parametres_utilisateur (utilisateur_id, cle_parametre, valeur_parametre)
VALUES (?, ?, ?) AS nouvelle
ON DUPLICATE KEY UPDATE
    valeur_parametre = nouvelle.valeur_parametre,
    mis_a_jour_le = CURRENT_TIMESTAMP;

Utilisez la forme avec alias de ligne uniquement après avoir confirmé que la cible est MySQL. Utilisez VALUES(col) pour MariaDB ou pour des parcs mixtes MySQL/MariaDB.

Pagination par jeu de clés (Keyset Pagination)

SELECT id, nom, cree_le
FROM produits
WHERE (cree_le, id) < (?, ?)
ORDER BY cree_le DESC, id DESC
LIMIT 50;

Accompagnez-la d'un index correspondant au curseur :

CREATE INDEX idx_produits_cree_id ON produits (cree_le, id);

N'utilisez pas la pagination par OFFSET profond sur les grandes tables ; elle oblige le serveur à analyser et ignorer des lignes avant de renvoyer la page.

Champs JSON

Utilisez les colonnes JSON pour des données d'extension, pas pour des champs nécessitant un filtrage relationnel lourd ou des contraintes.

CREATE TABLE evenements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    type_evenement VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_evenements_type (type_evenement)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Pour les chemins JSON fréquemment interrogés, exposez une colonne générée et indexez cette colonne. Conservez les clés étrangères, la propriété, la location et les champs de cycle de vie en tant que colonnes relationnelles.

Recherche plein texte

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_titre_corps (titre, corps);

SELECT id, titre, MATCH(titre, corps) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(titre, corps) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

Utilisez un moteur de recherche externe lorsque vous avez besoin de tolérance aux fautes de frappe, d'un classement complexe, de facettes multi-tables, ou d'une analyse linguistique allant au-delà du comportement intégré du plein texte.

Transactions

Gardez les transactions courtes et verrouillez les lignes dans un ordre cohérent :

START TRANSACTION;

SELECT id, solde
FROM comptes
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE comptes SET solde = solde - ? WHERE id = ?;
UPDATE comptes SET solde = solde + ? WHERE id = ?;

COMMIT;

Liste de vérification pour interblocages et attentes de verrou :

• Verrouillez les lignes dans un ordre déterministe sur tous les chemins de code.
• Effectuez les appels API externes avant d'ouvrir la transaction, pas à l'intérieur.
• Ajoutez des index pour les prédicats utilisés dans les instructions UPDATE, DELETE et les lectures avec verrouillage.
• En cas d'interblocage, annulez et réessayez toute la transaction avec un budget de tentatives limité.
• Capturez SHOW ENGINE INNODB STATUS\G rapidement après un interblocage ; il est écrasé par les événements ultérieurs.

Réclamation de travail de type file d'attente :

START TRANSACTION;

SELECT id
FROM travaux
WHERE statut = 'en_attente'
ORDER BY cree_le
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE travaux
SET statut = 'en_cours', demarre_le = CURRENT_TIMESTAMP
WHERE id = ?;

COMMIT;

Utilisez SKIP LOCKED uniquement pour les charges de travail de type file d'attente où ignorer une ligne verrouillée est acceptable. Ce n'est pas un remplacement pour la cohérence transactionnelle normale.

Pools de connexions

Exemple SQLAlchemy :

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+mysqlconnector://app:[email protected]/app",
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=240,
    pool_pre_ping=True,
    connect_args={"connect_timeout": 5},
)

Exemple Node.js mysql2 :

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000,
});

const [rows] = await pool.execute(
  'SELECT id, total FROM commandes WHERE compte_id = ? LIMIT 50',
  [compteId],
);

Gardez le recyclage du pool d'applications en dessous du wait_timeout du serveur. Si le serveur utilise wait_timeout = 300, un pool_recycle autour de 240 secondes est cohérent ; pool_pre_ping aide toujours à récupérer des événements réseau et de basculement.

Diagnostics

Commandes utiles en premier passage :

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

Activez le log des requêtes lentes dans un environnement contrôlé :

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Utilisez EXPLAIN ANALYZE uniquement lorsqu'il est sûr d'exécuter la requête. Il exécute l'instruction et peut être coûteux sur des données de production.

Réplication

Les réplicas de lecture peuvent prendre du retard. N'acheminez pas les chemins de lecture-après-écriture, les flux de paiement, les vérifications de permissions, ou les lectures de clés d'idempotence vers un réplica immédiatement après une écriture.

-- Terminologie MySQL héritée, encore courante dans les parcs existants
SHOW SLAVE STATUS\G;

-- Terminologie plus récente là où elle est prise en charge
SHOW REPLICA STATUS\G;

Vérifiez le moteur/version avant de standardiser sur une commande. Surveillez la santé du thread SQL du réplica, la santé du thread IO et le retard, pas seulement si la connexion TCP est vivante.

Sécurité

CREATE USER 'app'@'%' IDENTIFIED BY 'utilisez-un-gestionnaire-de-secrets';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

ALTER USER 'app'@'%' REQUIRE SSL;

SELECT user, host
FROM mysql.user
WHERE user = '';

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

Points de revue de sécurité :

• N'accordez pas ALL PRIVILEGES ou *.* aux utilisateurs applicatifs.
• Exigez TLS pour les utilisateurs applicatifs lorsque le trafic traverse des hôtes ou des réseaux.
• Stockez les identifiants dans le gestionnaire de secrets de la plateforme, pas dans des exemples, scripts ou fichiers du dépôt.
• Séparez les utilisateurs de migration/administration des utilisateurs applicatifs d'exécution.
• Auditez l'exposition réseau publique et les adresses de liaison avant de régler les performances.

Configuration

Exemple de point de départ pour un hôte de base de données dédié :

[mysqld]
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

max_connections = 300
thread_cache_size = 50

wait_timeout = 300
interactive_timeout = 300
innodb_lock_wait_timeout = 10

slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

Considérez les valeurs de configuration comme une incitation à la revue, pas comme un préréglage universel. Dimensionnez la mémoire, les connexions, la rétention des logs et les paramètres de durabilité en fonction de la charge de travail, du matériel, de la politique de sauvegarde et des objectifs de récupération.

Anti-patrons

Anti-patron	Risque	Meilleur modèle
SELECT * dans les chemins chauds	Surcharge de données et clients fragiles	Sélectionner des colonnes explicites
Pagination OFFSET profond	Parcours linéaires et pages lentes	Pagination par jeu de clés
Absence d'index sur les jointures de clé étrangère	Jointures lentes et suppressions lourdes en verrous	Indexer intentionnellement les colonnes FK
Transactions longues	Attentes de verrous et historique d'annulation important	Valider de petites unités de travail
DML direct sur mysql.user	Risque de corruption de la table des privilèges	Utiliser CREATE USER, ALTER USER, DROP USER
Utilisateur applicatif avec privilèges d'administration	Rayon d'explosion élevé	Utilisateur d'exécution au moindre privilège
Recyclage du pool au-dessus de wait_timeout	Connexions poolées obsolètes	Recycler en dessous du délai et pré-pinger
Lectures sur réplica après écritures	État utilisateur obsolète	Verrouiller les flux lecture-après-écriture sur le primaire

Résultats attendus

Lorsque ce skill est utilisé pour une revue, renvoyez :

1. Hypothèses sur le moteur/version.
2. Problèmes de correction, de verrouillage, de sécurité et de migration à plus haut risque.
3. Modifications SQL ou de code exactes pour le chemin sûr.
4. Plan de validation : EXPLAIN, simulation de migration, vérification des verrous/interblocages, et critères de retour arrière.
5. Toute différence de syntaxe MySQL/MariaDB affectant la recommandation.

Liens

• Skill : postgres-patterns - Modèles de schéma et de requêtes spécifiques à PostgreSQL
• Skill : database-migrations - Planification des migrations et sécurité de déploiement
• Skill : backend-patterns - Modèles d'API et de couche service
• Skill : security-review - Gestion des secrets, authentification et moindre privilège
• Agent : database-reviewer - Flux de travail plus large de revue de base de données