mysql-patterns

MySQL Patterns

Verwenden Sie diese Skill bei der Arbeit an MySQL- oder MariaDB-Schema-Design, Migrationen, Untersuchung langsamer Abfragen, Queue-ähnlichen Transaktionen, Connection Pools oder produktiven Datenbankkonfigurationen. Führen Sie vor der Anwendung eines funktionsspezifischen Musters eine exakte Versionsprüfung durch, da MySQL und MariaDB in mehreren SQL-Details auseinandergegangen sind.

Aktivierung

• Entwerfen von MySQL- oder MariaDB-Tabellen, -Indizes und -Constraints
• Überprüfen von Migrationen, bevor sie auf großen Produktionstabellen ausgeführt werden
• Debuggen von langsamen Abfragen, Lock-Waits, Deadlocks oder Verbindungserschöpfung
• Hinzufügen von Keyset-Paginierung, Upserts, Volltextsuche, JSON-Spalten oder Queues
• Konfigurieren von Anwendungs-Connection-Pools, Read-Replicas, TLS oder Slow Logs

Versionsprüfung

Identifizieren Sie zunächst die Engine und die Version:

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

Halten Sie MySQL- und MariaDB-Anleitungen getrennt, wenn die Syntax abweicht:

• MySQL dokumentiert Zeilenaliase als Ersatz für VALUES(col) in ON DUPLICATE KEY UPDATE; VALUES(col) ist dort veraltet.
• MariaDB dokumentiert VALUES(col) als die unterstützte Methode, um eingefügte Werte in ON DUPLICATE KEY UPDATE zu referenzieren; verwenden Sie es für die Cross-Engine-Kompatibilität.
• SKIP LOCKED ist nur für queue-ähnliche Arbeiten geeignet. Es überspringt gesperrte Zeilen und kann eine inkonsistente Ansicht zurückgeben. Verwenden Sie es daher nicht für allgemeine Buchhaltung oder integritätssensitive Lesevorgänge.

Schema-Standardeinstellungen

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;

Standardauswahl:

Anwendungsfall	Bevorzugt	Vermeiden
Surrogat-Primärschlüssel	BIGINT UNSIGNED AUTO_INCREMENT	INT für Tabellen, die über 2B Zeilen hinauswachsen können
UUID-Suchschlüssel	BINARY(16) mit Konvertierungshilfen	VARCHAR(36) Primärschlüssel auf heißen Tabellen
Geld und exakte Mengen	DECIMAL(p, s)	FLOAT oder DOUBLE
Benutzerorientierter Text	utf8mb4 Tabellen und Indizes	MySQL utf8 / utf8mb3 Standardeinstellungen
Anwendungs-Zeitstempel	DATETIME mit UTC, verwaltet von der App	Annahme, dass DATETIME Zeitzonen-Metadaten speichert
Soft Deletes	deleted_at DATETIME NULL plus bereichsbezogene Indizes	Filtern von soft-gelöschten Zeilen ohne Index
Erweiterbare Statuswerte	Lookup-Tabelle oder eingeschränkter VARCHAR	ENUM, wenn sich Werte häufig ändern

Indizierung

Die Reihenfolge zusammengesetzter Indizes folgt normalerweise zuerst Gleichheitsprädikaten, dann Bereichs- oder Sortierspalten:

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;

Verwenden Sie EXPLAIN, bevor Sie einen Index hinzufügen oder ändern:

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

Signale, die untersucht werden sollten:

Feld	Risikosignal
type	ALL auf einer großen Tabelle
key	NULL, obwohl ein selektives Prädikat existiert
rows	Sehr hohe Zeilenschätzung für einen interaktiven Pfad
Extra	Using temporary, Using filesort oder breites Using where

Vermeiden Sie es, blind Indizes hinzuzufügen. Jeder Index erhöht die Schreibkosten, die Migrationszeit, die Sicherungsgröße und den Druck auf den Buffer Pool.

Abfragemuster

Upsert

Cross-Engine-kompatible Form:

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

MySQL-Zeilenalias-Form:

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;

Verwenden Sie die Zeilenalias-Form nur, nachdem Sie bestätigt haben, dass das Ziel MySQL ist. Verwenden Sie VALUES(col) für MariaDB oder gemischte MySQL/MariaDB-Flotten.

Keyset-Paginierung

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

Unterstützen Sie dies mit einem Index, der zum Cursor passt:

CREATE INDEX idx_products_created_id ON products (created_at, id);

Verwenden Sie keine tiefe OFFSET-Paginierung auf großen Tabellen; dies zwingt den Server, Zeilen zu scannen und zu verwerfen, bevor die Seite zurückgegeben wird.

JSON-Felder

Verwenden Sie JSON-Spalten für Erweiterungsdaten, nicht für Felder, die starke relationale Filterung oder Constraints benötigen.

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;

Machen Sie für häufig abgefragte JSON-Pfade eine generierte Spalte zugänglich und indizieren Sie diese Spalte. Behalten Sie Fremdschlüssel, Eigentumsverhältnisse, Mandantenfähigkeit und Lebenszyklus-Felder relational.

Volltextsuche

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;

Verwenden Sie externe Suche, wenn Sie Tipptoleranz, komplexes Ranking, tabellenübergreifende Facetten oder sprachspezifische Analysen benötigen, die über das integrierte Volltextverhalten hinausgehen.

Transaktionen

Halten Sie Transaktionen kurz und sperren Sie Zeilen in einer konsistenten Reihenfolge:

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;

Checkliste für Deadlocks und Lock-Waits:

• Sperren Sie Zeilen in einer deterministischen Reihenfolge über Codepfade hinweg.
• Führen Sie externe API-Aufrufe vor dem Öffnen der Transaktion durch, nicht innerhalb.
• Fügen Sie Indizes für Prädikate hinzu, die in UPDATE, DELETE und sperrenden Lesevorgängen verwendet werden.
• Rollen Sie bei einem Deadlock zurück und wiederholen Sie die gesamte Transaktion mit einem begrenzten Wiederholungsbudget.
• Erfassen Sie SHOW ENGINE INNODB STATUS\G kurz nach einem Deadlock; es wird durch spätere Ereignisse überschrieben.

Queue-ähnlicher Worker-Claim:

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;

Verwenden Sie SKIP LOCKED nur für queue-ähnliche Workloads, bei denen das Überspringen einer gesperrten Zeile akzeptabel ist. Es ist kein Ersatz für normale transaktionale Konsistenz.

Connection Pools

SQLAlchemy-Beispiel:

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},
)

Node.js mysql2-Beispiel:

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],
);

Halten Sie das Anwendungs-Pool-Recycling unter dem Server-wait_timeout. Wenn der Server wait_timeout = 300 verwendet, ist ein pool_recycle von etwa 240 Sekunden konsistent; pool_pre_ping hilft dennoch bei der Wiederherstellung von Netzwerk- und Failover-Ereignissen.

Diagnose

Nützliche erste Befehle:

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

Aktivieren Sie das Slow Log in einer kontrollierten Umgebung:

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

Verwenden Sie EXPLAIN ANALYZE nur, wenn es sicher ist, die Abfrage auszuführen. Es führt die Anweisung aus und kann bei produktiven Datenmengen teuer sein.

Replikation

Read-Replicas können in Verzug geraten. Leiten Sie keine Read-your-own-Write-Pfade, Checkout-Abläufe, Berechtigungsprüfungen oder Idempotenzschlüssel-Lesevorgänge unmittelbar nach einem Schreibvorgang an eine Replica weiter.

-- Legacy-Terminologie von MySQL, immer noch in bestehenden Flotten üblich
SHOW SLAVE STATUS\G;

-- Neuere Terminologie, wo unterstützt
SHOW REPLICA STATUS\G;

Überprüfen Sie die Engine/Version, bevor Sie sich auf einen Befehl standardisieren. Überwachen Sie die Gesundheit des Replica-SQL-Threads, des IO-Threads und die Verzögerung, nicht nur, ob die TCP-Verbindung aktiv ist.

Sicherheit

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 ''@'%';

Sicherheitsprüfpunkte:

• Gewähren Sie Anwendungsbenutzern keine ALL PRIVILEGES oder *.*.
• Verlangen Sie TLS für Anwendungsbenutzer, wenn der Datenverkehr Hosts oder Netzwerke überschreitet.
• Speichern Sie Anmeldeinformationen im Secret Manager der Plattform, nicht in Beispielen, Skripten oder Repository-Dateien.
• Trennen Sie Migrations-/Admin-Benutzer von Laufzeit-Anwendungsbenutzern.
• Überprüfen Sie die öffentliche Netzwerkexposition und die Bindungsadressen, bevor Sie die Leistung optimieren.

Konfiguration

Beispiel-Ausgangspunkt für einen dedizierten Datenbank-Host:

[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

Behandeln Sie Konfigurationswerte als Aufforderung zur Überprüfung, nicht als universelle Voreinstellung. Dimensionieren Sie Speicher, Verbindungen, Log-Aufbewahrung und Dauerhaftigkeitseinstellungen basierend auf Workload, Hardware, Backup-Richtlinie und Wiederherstellungszielen.

Antimuster

Antimuster	Risiko	Besseres Muster
SELECT * in heißen Pfaden	Übermäßiges Abrufen und brüchige Clients	Explizite Spalten auswählen
Tiefe OFFSET-Paginierung	Lineare Scans und langsame Seiten	Keyset-Paginierung
Kein Index auf Fremdschlüssel-Joins	Langsame Joins und sperrenlastige Löschvorgänge	FK-Spalten gezielt indizieren
Lange Transaktionen	Lock-Waits und großer Undo-Verlauf	Kleine Arbeitseinheiten festschreiben
Direktes DML gegen mysql.user	Risiko der Grant-Tabellenkorruption	CREATE USER, ALTER USER, DROP USER verwenden
Anwendungsbenutzer mit Admin-Rechten	Hoher Explosionsradius	Runtime-Benutzer mit minimalen Rechten
Pool-Recycling über wait_timeout	Veraltete gepoolte Verbindungen	Unterhalb des Timeouts recyceln und Pre-Ping verwenden
Replica-Lesevorgänge nach Schreibvorgängen	Veralteter benutzerseitiger Zustand	Read-after-write-Abläufe an den Primary binden

Erwartungen an die Ausgabe

Wenn diese Skill für eine Überprüfung verwendet wird, geben Sie Folgendes zurück:

1. Annahmen zu Engine/Version.
2. Risiken mit der höchsten Priorität in Bezug auf Korrektheit, Locks, Sicherheit und Migration.
3. Exakte SQL- oder Code-Änderungen für den sicheren Pfad.
4. Validierungsplan: EXPLAIN, Migrationstrockenlauf, Lock/Deadlock-Prüfung und Rollback-Kriterien.
5. Alle MySQL/MariaDB-Syntaxunterschiede, die die Empfehlung beeinflussen.

Verwandte Skills

• Skill: postgres-patterns - PostgreSQL-spezifische Schema- und Abfragemuster
• Skill: database-migrations - Migrationsplanung und Rollout-Sicherheit
• Skill: backend-patterns - API- und Service-Layer-Muster
• Skill: security-review - Geheimnisverwaltung, Authentifizierung und minimale Rechte
• Agent: database-reviewer - Umfassenderer Datenbanküberprüfungs-Workflow