mysql-patterns

MySQL Patterns

Verwenden Sie diese Fähigkeit bei der Arbeit an MySQL- oder MariaDB-Schema-Design, Migrationen, Untersuchung langsamer Abfragen, Warteschlangen-Transaktionen, Connection-Pools oder Produktionsdatenbankkonfiguration. Bevorzugen Sie genaue Versionsprüfungen, bevor Sie ein funktionsspezifisches Muster anwenden, 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, Sperrwartezeiten, Deadlocks oder Verbindungserschöpfung
• Hinzufügen von Schlüsselsatz-Paginierung, Upserts, Volltextsuche, JSON-Spalten oder Warteschlangen
• Konfigurieren von Anwendungs-Connection-Pools, Lese-Replikaten, 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 sich die Syntax unterscheidet:

• MySQL dokumentiert Zeilen-Aliase 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 Kompatibilität zwischen Engines.
• SKIP LOCKED ist nur für warteschlangenartige 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-Standardwerte

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;

Standardauswahlen:

Anwendungsfall	Bevorzugt	Vermeiden
Surrogat-Primärschlüssel	BIGINT UNSIGNED AUTO_INCREMENT	INT für Tabellen, die über 2B Zeilen hinauswachsen können
UUID-Nachschlageschlüssel	BINARY(16) mit Konvertierungshilfen	VARCHAR(36) Primärschlüssel auf heißen Tabellen
Geld und exakte Mengen	DECIMAL(p, s)	FLOAT oder DOUBLE
Benutzersichtbarer Text	utf8mb4-Tabellen und -Indizes	MySQL utf8 / utf8mb3-Standardwerte
Anwendungs-Zeitstempel	DATETIME mit UTC, von der App verwaltet	Annahme, dass DATETIME Zeitzonen-Metadaten speichert
Weiche Löschungen	deleted_at DATETIME NULL plus bereichsbezogene Indizes	Filtern weich gelöschter Zeilen ohne Index
Erweiterbare Statuswerte	Nachschlagetabelle oder eingeschränktes 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, wenn 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 Schreiblatenz, die Migrationszeit, die Sicherungsgröße und den Druck auf den Buffer Pool.

Abfragemuster

Upsert

Engines-übergreifend 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-Zeilen-Alias-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 Zeilen-Alias-Form nur, nachdem Sie bestätigt haben, dass das Ziel MySQL ist. Verwenden Sie VALUES(col) für MariaDB oder gemischte MySQL/MariaDB-Flotten.

Schlüsselsatz-Paginierung

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

Untermauern Sie es 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; sie 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 umfangreiches relationales Filtern 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 sichtbar und indizieren Sie diese Spalte. Halten Sie Fremdschlüssel, Eigentümerschaft, Mandantenfähigkeit und Lebenszyklusfelder 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;

Deadlock- und Lock-Wait-Checkliste:

• Sperren Sie Zeilen in einer deterministischen Reihenfolge über Codepfade hinweg.
• Führen Sie externe API-Aufrufe vor dem Öffnen der Transaktion durch, nicht darin.
• 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.

Warteschlangenartige Worker-Übernahme:

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 warteschlangenartige Arbeitslasten, 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 um 240 Sekunden sinnvoll; pool_pre_ping hilft trotzdem, sich von Netzwerk- und Failover-Ereignissen zu erholen.

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 Produktionsdaten teuer sein.

Replikation

Lese-Replikate können Verzögerungen aufweisen. Leiten Sie keine Lese-Deine-eigenen-Schreib-Pfade, Checkout-Flows, Berechtigungsprüfungen oder Idempotenz-Schlüssel-Lesevorgänge unmittelbar nach einem Schreibvorgang an ein Replikat weiter.

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

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

Überprüfen Sie Engine/Version, bevor Sie sich auf einen Befehl festlegen. Ü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 Datenverkehr Hosts oder Netzwerke überschreitet.
• Speichern Sie Anmeldeinformationen im Plattform-Secret-Manager, nicht in Beispielen, Skripten oder Repository-Dateien.
• Trennen Sie Migrations-/Admin-Benutzer von Laufzeit-Anwendungsbenutzern.
• Überprüfen Sie die Exposition des öffentlichen Netzwerks und die Bindungsadressen, bevor Sie die Leistung optimieren.

Konfiguration

Beispielausgangspunkt für einen dedizierten Datenbankhost:

[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 Arbeitslast, Hardware, Sicherungsrichtlinie und Wiederherstellungszielen.

Anti-Patterns

Anti-Pattern	Risiko	Besseres Muster
SELECT * in heißen Pfaden	Übermäßiges Abrufen und fragilen Clients	Explizite Spalten auswählen
Tiefe OFFSET-Paginierung	Lineare Scans und langsame Seiten	Schlüsselsatz-Paginierung
Kein Index für Fremdschlüssel-Joins	Langsame Joins und sperrlastige Löschungen	FK-Spalten absichtlich indizieren
Lange Transaktionen	Lock-Wartezeiten und große Undo-Verläufe	Kleine Arbeitseinheiten committen
Direktes DML gegen mysql.user	Risiko der Beschädigung der Grant-Tabelle	CREATE USER, ALTER USER, DROP USER verwenden
Anwendungsbenutzer mit Admin-Berechtigungen	Hoher Explosionsradius	Laufzeitbenutzer mit geringsten Privilegien
Pool-Recycling über wait_timeout	Veraltete gepoolte Verbindungen	Unterhalb des Timeouts recyceln und Pre-Ping durchführen
Replikat-Lesevorgänge nach Schreibvorgängen	Veralteter benutzersichtiger Zustand	Lese-nach-Schreib-Flows an die Primärinstanz binden

Ausgabeerwartungen

Wenn diese Fähigkeit zur Überprüfung verwendet wird, geben Sie zurück:

1. Engine-/Versionsannahmen.
2. Korrektheits-, Sperr-, Sicherheits- und Migrationsprobleme mit höchstem Risiko.
3. Genaue 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

• Skill: postgres-patterns - PostgreSQL-spezifische Schema- und Abfragemuster
• Skill: database-migrations - Migrationsplanung und Rolloutsicherheit
• Skill: backend-patterns - API- und Service-Schicht-Muster
• Skill: security-review - Geheimnisverwaltung, Authentifizierung und minimale Privilegien
• Agent: database-reviewer - Breiterer Datenbanküberprüfungs-Workflow