mysql-patterns

Modèles MySQL (MySQL Patterns)

Utilisez cette compétence 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. Privilégiez la vérification exacte de la version 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 verrou, d'interblocages ou d'épuisement des connexions
• Ajout de pagination par clé, d'upserts, de recherche plein texte, de colonnes JSON ou de files d'attente
• Configuration des pools de connexions applicatives, des réplicas de lecture, de TLS ou des journaux de requêtes lentes

Vérification de version

Commencez par identifier le moteur et la version :

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

Gardez les conseils pour MySQL et MariaDB séparés lorsque la syntaxe diffère :

• Les documents MySQL présentent les alias de ligne comme le remplacement de VALUES(col) dans ON DUPLICATE KEY UPDATE ; VALUES(col) y est déprécié.
• Les documents MariaDB présentent VALUES(col) comme la méthode prise en charge pour 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 les tâches de type file d'attente. Il ignore les lignes verrouillées et peut renvoyer une vue incohérente, donc ne l'utilisez pas pour la comptabilité générale ou les lectures sensibles à l'intégrité.

Valeurs par défaut des schémas

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Choix par défaut :

Cas d'utilisation	Préférez	Évitez
Clés primaires de substitution	BIGINT UNSIGNED AUTO_INCREMENT	INT pour les tables pouvant dépasser 2B lignes
Clés de recherche UUID	BINARY(16) avec fonctions de conversion	VARCHAR(36) comme clé primaire sur les tables chaudes
Argent et quantités exactes	DECIMAL(p, s)	FLOAT ou DOUBLE
Texte destiné à l'utilisateur	Tables et index utf8mb4	Les valeurs par défaut MySQL utf8 / utf8mb3
Horodatages applicatifs	DATETIME avec UTC géré par l'application	Supposer que DATETIME stocke des métadonnées de fuseau horaire
Suppressions logiques	deleted_at DATETIME NULL plus 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 des index composites suit généralement d'abord les prédicats d'égalité, puis les colonnes de plage ou de tri :

CREATE INDEX idx_orders_account_status_created
    ON orders (account_id, status, created_at);

SELECT id, total
FROM orders
WHERE account_id = ?
  AND status = 'pending'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

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

EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Signaux à examiner :

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

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

Modèles de requêtes

Upsert

Forme compatible entre moteurs :

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = CURRENT_TIMESTAMP;

Forme avec alias de ligne MySQL :

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at = 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 les parcs mixtes MySQL/MariaDB.

Pagination par clé (Keyset Pagination)

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Accompagnez-la d'un index correspondant à la clé de pagination :

CREATE INDEX idx_products_created_id ON products (created_at, id);

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

Champs JSON

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

CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    event_type VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_events_type (event_type)
) 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 multitenance et les champs de cycle de vie en tant que colonnes relationnelles.

Recherche plein texte

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);

SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) 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 inter-tables ou d'une analyse linguistique au-delà du comportement intégré de la recherche plein texte.

Transactions

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

START TRANSACTION;

SELECT id, balance
FROM accounts
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

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

• Verrouillez les lignes dans un ordre déterministe à travers 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 la transaction entière avec un budget de tentatives limité.
• Exécutez SHOW ENGINE INNODB STATUS\G peu après un interblocage ; il est écrasé par les événements ultérieurs.

Réclamation de tâche de type file d'attente :

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'processing', started_at = 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 orders WHERE account_id = ? LIMIT 50',
  [accountId],
);

Gardez le recyclage du pool applicatif 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.

Diagnostic

Commandes de premier passage utiles :

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

Activez le journal 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 taille production.

Réplication

Les réplicas de lecture peuvent avoir un retard. N'acheminer pas les chemins de lecture-après-écriture, les flux de paiement, les vérifications de permissions ou les lectures de clé 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/la version avant de vous standardiser sur une commande. Surveillez la santé du thread SQL du réplica, du thread IO et le retard, pas seulement si la connexion TCP est active.

Sécurité

CREATE USER 'app'@'%' IDENTIFIED BY 'use-a-secret-manager';
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 vérification 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, des scripts ou des fichiers de 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 révision, pas comme un préréglage universel. Dimensionnez la mémoire, les connexions, la rétention des journaux 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 patron
SELECT * dans les chemins chauds	Surcharge de lecture et clients fragiles	Sélectionnez des colonnes explicites
Pagination profonde avec OFFSET	Analyses linéaires et pages lentes	Pagination par clé
Pas d'index sur les jointures de clés étrangères	Jointures lentes et suppressions avec verrous lourds	Indexez intentionnellement les colonnes FK
Transactions longues	Attentes de verrou et historique d'annulation volumineux	Validez de petites unités de travail
DML direct sur mysql.user	Risque de corruption de la table des privilèges	Utilisez CREATE USER, ALTER USER, DROP USER
Utilisateur applicatif avec droits admin	Rayon d'explosion élevé	Utilisateur d'exécution avec le moins de privilèges
Recyclage du pool au-dessus de wait_timeout	Connexions périmées dans le pool	Recyclez en dessous du délai d'attente et pré-vérifiez
Lectures sur réplica après écritures	État utilisateur obsolète	Verrouillez les flux de lecture-après-écriture sur le primaire

Résultats attendus

Lorsque cette compétence est utilisée pour une révision, renvoyez :

1. Les hypothèses sur le moteur/la version.
2. Les problèmes de plus haut risque liés à la correction, aux verrous, à la sécurité et aux migrations.
3. Les modifications exactes de SQL ou de code pour le chemin sûr.
4. Le 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.

Voir aussi

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