jmaxzh/sdlc-mysql-design

MySQL-Tabellen und SQL-Entwurf

Anwendbarkeit

• Anwenden, wenn die Aufgabe oder das Projekt MySQL als Zieldatenbank festlegt und Datenmodellierung, Tabellenentwurf, SQL, Indizes, Transaktionen oder Parallelitätssteuerung umfasst; expliziter Aufruf ist nicht erforderlich.
• Unabhängig vom SDLC-Workflow verwenden. MySQL nicht für allgemeine Datenbankarbeit auswählen oder auf Aufgaben anwenden, die nur andere Datenbanken betreffen.

Verwendung

• Offizielle MySQL 8.4-Semantik als Validierungsbasis verwenden, ohne die eingesetzte Version einzuschränken. Funktionsunterstützung, Constraint-Erzwingung und Veraltung in der tatsächlichen Zielversion überprüfen, wenn Unterschiede relevant sind.
• Die geschäftliche Bedeutung, Integritätsanforderungen, Hauptzugriffsmuster, Datenumfang und -wachstum, Parallelität und Konsistenzanforderungen in Bezug auf die Aufgabe ermitteln. Vorhandene Entwürfe und Workload-Nachweise verwenden, wo verfügbar; Annahmen und deren Auswirkungen angeben, wenn wichtige Informationen fehlen.
• Transaktions- und Parallelitätsregeln auf lokale InnoDB-Transaktionen anwenden. Explizit die Verbote von Abfragen innerhalb von Transaktionen, CHECK-Constraints, Fremdschlüssel-Constraints und gespeicherten Prozeduren als Projektkonventionen und nicht als MySQL-Einschränkungen identifizieren.
• Annehmen, dass ein Datensatz höchstens einmal pro Millisekunde geändert wird. Diese Annahme garantiert nicht, dass updated_at ein nicht wiederverwendeter Versionsbezeichner ist; die Bedingungen unter Zeitstempelprüfungen anwenden, bevor es für die Parallelitätssteuerung verwendet wird.
• Nur die für die Aufgabe relevanten Regeln anwenden. Das entsprechende Thema in MySQL-Entwurfsbeispielen lesen, wenn eine Regel klärungsbedürftig ist; nicht alle Beispiele standardmäßig laden.

Datenmodellierung

Entitäten, Beziehungen und Feldbesitz

• Zeilenkörnung: das Geschäftsobjekt, die Beziehung oder das Ereignis definieren, das durch eine Zeile dargestellt wird; Körnung innerhalb einer Tabelle konsistent halten. Siehe Beispiele.

• Beziehungen und Eindeutigkeit: minimale und maximale Kardinalität auf beiden Seiten definieren. Eins-zu-Viele-Referenzen auf der Viele-Seite speichern, eindeutige Referenzen verwenden, um Eins-zu-Eins-Beziehungen auf höchstens eine Übereinstimmung zu beschränken, und Verknüpfungstabellen für Viele-zu-Viele-Beziehungen verwenden. Bei wiederholten Assoziationen derselben Objekte die geschäftliche Bedeutung, Identität und den Eindeutigkeitsbereich jeder Assoziation definieren. Siehe Beispiele.

• Feldbesitz: ein Feld mit dem Objekt, der Beziehung oder dem Ereignis platzieren, das seinen Wert bestimmt; vermeiden, aktuelle Fakten zu duplizieren. Historische Schnappschüsse als Ereigniszeit-Werte aufbewahren, die durch Aktualisierungen des aktuellen Werts nicht geändert werden. Siehe Beispiele.

• Referentielle Integrität: definieren, ob ein referenziertes Ziel existieren muss und welche Zustände es erfüllen muss, wenn eine Referenz erstellt oder geändert wird. Definieren, wie vorhandene Referenzen behandelt werden, wenn das Ziel gelöscht oder ungültig wird. Diese Anforderungen durch Geschäftsschreibabläufe unter Schlüssel und Constraints und Parallelitätssteuerung durchsetzen. Siehe Beispiele.

Tabellenentwurf

Namen und Kommentare

• Datenbanken, Tabellen und Spalten mit Kleinbuchstaben, Ziffern und Unterstrichen benennen; nicht mit Ziffern beginnen, reservierte Wörter oder unklare Abkürzungen verwenden.

• Indizes nach ihren Spalten oder ihrem Zweck benennen. uniq_ und idx_ können eindeutige und normale Indizes identifizieren; der Primärindexname ist PRIMARY.

• Spaltenkommentare und zugehörige Definitionen aktualisieren, wenn sich Bedeutungen, Zustandswerte oder Constraints ändern.

Datentypen

• Spalten, die zum Referenzieren oder Vergleichen derselben Werte verwendet werden, in Datentyp und Vorzeichen kompatibel halten; nach Möglichkeit konsistente Definitionen verwenden.

• Exakte Geldbeträge mit DECIMAL mit expliziter Genauigkeit und Skalierung oder Ganzzahlen in einer definierten kleinsten Einheit darstellen; die Währung und Rundungsregeln definieren. Exakte Darstellung durch Speicherung, Parameter und Berechnungen bewahren; nicht in Fließkomma berechnen, bevor konvertiert wird.

• Kein veraltetes UNSIGNED bei FLOAT, DOUBLE oder DECIMAL setzen.

• is_xxx und TINYINT UNSIGNED für Booleans verwenden, mit Anwendungsvalidierung, die Werte auf 0 oder 1 beschränkt.

• Bei Verwendung von CHAR überprüfen, ob die Behandlung von nachgestellten Leerzeichen mit der geschäftlichen Bedeutung des Felds übereinstimmt.

• TIMESTAMP konvertiert zwischen Sitzungszeitzone und UTC; DATETIME tut dies nicht. Speichern Sie bei Bedarf ursprüngliche Zonennamen oder -versätze separat.

• created_at für die Erstellung und updated_at für die letzte Änderung verwenden. Veränderliche Geschäftssätze benötigen beide; Nur-Anhänge-Datensätze benötigen nur den Erstellungszeitpunkt. Fügen Sie sie je nach Prüfbedarf zu Verknüpfungs- oder anderen Tabellen hinzu.

• Für Audit-Zeitstempel DATETIME(3) mit Millisekunden-Genauigkeit und Pekinger Zeit verwenden, mit Verbindungszeitzone +08:00.

• Audit-Zeitstempel auf NOT NULL DEFAULT CURRENT_TIMESTAMP(3) setzen und ON UPDATE CURRENT_TIMESTAMP(3) für updated_at hinzufügen. Lassen Sie die Datenbank sie verwalten; nicht redundant in der Anwendung zuweisen.

Schlüssel und Constraints

• Explizit stabile, kurze Primärschlüssel für InnoDB-Tabellen definieren. Sekundärindex-Datensätze enthalten Primärschlüsselspalten, daher erhöhen breite Primärschlüssel die Sekundärindex-Kosten.

• Erforderliche Felder als NOT NULL markieren.

• CHECK nicht verwenden; Bereiche, Aufzählungen und Feldkombinationen in jedem Anwendungsschreib-Einstiegspunkt validieren und ungültige Schreibvorgänge ablehnen. Siehe Beispiele.

• Geschäftliche Eindeutigkeit mit eindeutigen Indizes erzwingen, nicht mit Abfrage-vor-Einfügen. Eindeutige Indizes lassen mehrere NULL-Werte zu.

• Eindeutigkeit für Idempotenz-Identifikatoren im anwendbaren Geschäftsvorgangs- und Mandantenbereich erzwingen, wobei alle beteiligten Spalten NOT NULL sind.

• Fremdschlüssel-Constraints oder Kaskaden nicht verwenden; referentielle Integrität durch Geschäftsschreibabläufe bewahren.

Zeichensätze und Sortierungen

• utf8mb4 für Text verwenden.

• Textspalten, die zum Referenzieren oder Vergleichen derselben Werte verwendet werden, in Zeichensatz und Sortierung kompatibel halten; nach Möglichkeit konsistente Definitionen verwenden.

• Vor Sortierungsänderungen auf Gleichheitsänderungen und neu doppelte Werte prüfen, die die Eindeutigkeit verletzen.

SQL-Programmierung

• Keine gespeicherten Prozeduren verwenden. Der Anwendungscode steuert Verzweigungen, Commits und Rollbacks.

Abfragen

• Erforderliche Spalten in stabilen Geschäftsabfragen explizit auswählen; SELECT * vermeiden.

• Wenn Paginierung oder Einzelzeilenauswahl eine deterministische Reihenfolge erfordert, eine Kombination von Sortierschlüsseln verwenden, die Zeilen eindeutig ordnet.

• Alle Sortierschlüssel in Cursors einbeziehen. Gleichzeitige Sortierschlüsseländerungen können seitenübergreifende Auslassungen/Duplikate verursachen; Cursor-Paginierung garantiert keinen konsistenten seitenübergreifenden Schnappschuss.

Datenänderung

• Fehler, die fehlschlagen sollten, nicht mit INSERT IGNORE oder UPDATE IGNORE verstecken.

• Verhindern, dass UPDATE/DELETE aufgrund leerer Parameter oder fehlender dynamischer Prädikate zu Volltabellenoperationen degenerieren. LIMIT ersetzt keine Geschäftsbereichsbedingungen.

• Unter der obigen Audit-Zeitstempeldefinition ändert sich updated_at automatisch, wenn sich der Zeileninhalt ändert; es zeichnet nicht die Ausführungszeit jeder Update-Anweisung auf.

• Normale Updates nicht durch REPLACE INTO ersetzen: Bei Primär-/Eindeutigkeitskonflikt können seine Ersetzungssemantiken nicht angegebene Felder auf Standardwerte zurücksetzen und Lösch-/Einfügeverhalten auslösen.

Ausdrücke und Funktionen

• Vergleichstypen abgleichen; implizite Konvertierung keine kritischen Geschäftsvergleiche bestimmen lassen.

• Bytes, Zeichen und benutzersichtbare Grapheme in Längenprüfungen unterscheiden. LENGTH zählt Bytes und CHAR_LENGTH Zeichen, nicht sichtbare Grapheme.

• Wenn die von NOT IN verwendete Menge NULL enthält, kann ein Wert, der keinem Element entspricht, UNKNOWN ergeben; dies nicht als bestätigte Nichtzugehörigkeit interpretieren.

• COUNT(DISTINCT col) schließt NULL aus; seine Multispaltenform zählt unterschiedliche Kombinationen nur, wenn alle beteiligten Spalten nicht-NULL sind.

• SUM gibt NULL für leere oder vollständig NULL-Eingaben zurück. Wenn GROUP BY keine Gruppe bildet, wird dafür keine Zeile zurückgegeben. Null nur ersetzen, wenn Geschäftsregeln eine leere Aggregation als Null definieren.

• Halboffene Zeitintervalle bevorzugen: den Start einschließen und das Ende ausschließen. Grenzen in der Geschäftszeitzone mit derselben Zeitbasis wie die gespeicherten Werte berechnen.

Transaktionen und Konsistenz

Transaktionsverwaltung

• Anweisungen innerhalb von Transaktionen: Abfragen, Geschäftsvalidierung und Schreibvorbereitung außerhalb expliziter Transaktionen durchführen. Innerhalb der Transaktion nur notwendige Datenänderungen und Transaktionssteuerung zulassen. Gewöhnliche Abfragen, Sperrabfragen und Abfragekomponenten innerhalb von Schreibvorgängen, einschließlich Unterabfragen, verbieten. Siehe Beispiele.

• Transaktionsverbindung: Schreibvorgänge, die gemeinsam erfolgreich sein müssen, in derselben Verbindung innerhalb derselben Transaktion ausführen. Verbindungswechsel, asynchrone Ausführung und Weitergabeeinstellungen müssen diese Grenze bewahren. Siehe Beispiele.

• Implizite Commits: geschachtelte Transaktionen nicht mit START TRANSACTION simulieren oder autocommit zwischen Geschäftsschreibvorgängen von 0 auf 1 umschalten; beide führen einen impliziten Commit durch. DDL wie ALTER TABLE und TRUNCATE TABLE aus Geschäftsschreibtransaktionen heraushalten. Siehe Beispiele.

• Fehlerbehandlung: bei Fehlern bei erforderlichen Schreibvorgängen oder nicht erfüllten Geschäftsbedingungen spätere Schreibvorgänge stoppen und die gesamte Transaktion zurückrollen. Nicht annehmen, dass eine fehlgeschlagene Anweisung frühere Schreibvorgänge rückgängig macht. Nach einem Deadlock oder Sperrzeitüberschreitung vollständigen Rollback sicherstellen und die gesamte Transaktion nur wiederholen, wenn es sicher ist, mit einer endlichen Wiederholungsgrenze. Bei unsicheren Commits zuerst mit Idempotenz-Identifikatoren überprüfen. Siehe Beispiele.

• Externe Operationen: lokale Transaktionen führen keinen atomaren Commit/Rollback von externen Aufrufen oder Nachrichtensendungen durch. Siehe Beispiele.

Parallelitätssteuerung

• Geschäftsbedingte Aktualisierungen: Schreibzeit-Geschäftsfeld-Prädikate bevorzugen. Wenn neue Werte aus außerhalb der Transaktion gelesenen Daten berechnet werden, Prädikate einschließen, die die relevanten ursprünglichen Werte oder einen gültigen Versionsbezeichner überprüfen, um Überschreiben gleichzeitiger Änderungen zu vermeiden. Keine expliziten Transaktionen hinzufügen, wenn eine einzelne bedingte Anweisung ausreicht. Siehe Beispiele.

• Zeitstempelprüfungen: wenn geeignete Geschäftsfelder fehlen, das ursprüngliche updated_at nur abgleichen, wenn jede relevante Änderung es ändert und der alte Wert während der Validierung nicht wiederverwendet werden kann. Millisekunden in Lesevorgängen, Schreibvorgängen, Serialisierung und Bindung bewahren. Andernfalls einen nicht wiederverwendeten Versionsbezeichner oder einen anderen Parallelitätsmechanismus verwenden. Siehe Beispiele.

• Referentielle Integrität: bedingte Aktualisierungen, Eindeutigkeit und notwendige Transaktionen verwenden, um die Referenzerstellung/-änderung gegen Ziellöschung/-ungültigkeit zu koordinieren. Siehe Beispiele.

• Update-Ergebnisse: feststellen, ob der Treiber abgeglichene oder geänderte Zeilen meldet, und dann bewerten, ob der erforderliche Schreibvorgang erfolgreich war. Ein nicht erfülltes Prädikat ist kein Erfolg; null geänderte Zeilen allein beweisen kein nicht erfülltes Prädikat. Siehe Beispiele.

• Schreibreihenfolge: konkurrierende Transaktionen sollten eine konsistente Tabellen-/Datensatzreihenfolge verwenden. Die SQL-Textreihenfolge legt keine tatsächliche Sperrreihenfolge fest. Siehe Beispiele.

Datenbanksicherheit

• Anwendungsdaten als Parameter binden, anstatt sie in SQL zu verketten.

• Dynamische Tabellen-/Spaltennamen und Sortierrichtungen durch Code-Allowlisten in die SQL-Struktur abbilden; Wertplatzhalter können sie nicht ersetzen.

Leistungsoptimierung

• Anforderungsrundgänge: Datenbankanforderungen pro Geschäftsvorgang reduzieren, während Korrektheit und Gesamtleistung erhalten bleiben.

Indexoptimierung

• Indexwiederverwendung und -änderungen: Indizes über primäre Abfragen hinweg wiederverwenden. Keine Indizes nur für neue SQL/Prädikate hinzufügen, wenn vorhandene Pfade die Leistungsanforderungen erfüllen. Andere Abfragen und eindeutige Constraints vor dem Zusammenführen oder Ersetzen überprüfen. Siehe Beispiele.

• Felder mit niedriger Kardinalität: Indizes nicht allein aufgrund weniger unterschiedlicher Werte ablehnen. Zielverteilung, kombinierte Filter, Sortierung, Abdeckungsvorteile, Tabellenzugriffe und Schreibkosten berücksichtigen. Siehe Beispiele.

• Präfix-Indizes: String-Präfixlängen aus tatsächlichen Daten auswählen. Eindeutige Präfix-Indizes können unterschiedliche vollständige Werte ablehnen, die ein Präfix teilen, und können die Vollwert-Eindeutigkeit nicht ersetzen. Siehe Beispiele.

• Zusammengesetzte Indizes: Filterung, Linkspräfix-Wiederverwendung, Sortierung und Gruppierung ausbalancieren. Gleichheitsspalten gehen Bereichsspalten im Allgemeinen voraus; nicht universell nach Selektivität ordnen. Siehe Beispiele.

• Sortierkosten: filesort allein bedeutet keinen schlechten Index. Sortier-, Scan- und Wartungskosten hinzugefügter Indizes vergleichen. Siehe Beispiele.

• Indizierte Spalten und Ausdrücke: direkte Zeitbereichsprädikate für die tägliche Filterung bevorzugen. Wenn Ausdrücke notwendig sind, passende funktionale oder generierte Spaltenindizes evaluieren. Siehe Beispiele.

• Führende Wildcards: gewöhnliche B-Baum-Indizes können führende Wildcard-Suchen im Allgemeinen nicht in effektive Bereichsscans verwandeln. Volltext- oder dedizierte Suche muss die ursprüngliche Übereinstimmungssemantik beibehalten. Siehe Beispiele.

Abfrageoptimierung

• Batch-Abfragen: zusammenführbare zeilenweise Abfragen durch Joins oder Unterabfragen ersetzen, die als Gruppe abgeglichen werden. Siehe Beispiele.

• Teilmengenauswahl: von Datenbanken abgerufene Zeilen auf das für die Verarbeitung notwendige Minimum beschränken. Siehe Beispiele.