MySQL Tabellendesign: 14 goldene Regeln

1. Fügen Sie diese allgemeinen Felder beim Entwerfen einer Tabelle ein

Im Allgemeinen sollte eine Tabelle die folgenden Felder enthalten:

• id: Primärschlüssel. Jede Tabelle muss einen Primärschlüssel haben – keine Ausnahmen.
• create_time: Erstellungszeit – erforderlich.
• modified_time: Zeitpunkt der letzten Änderung – erforderlich. Dies sollte jedes Mal aktualisiert werden, wenn der Datensatz aktualisiert wird.
• version: Versionsnummer des Datensatzes, typischerweise für optimistische Sperrung verwendet – optional.
• modifier: Die Person, die den Datensatz zuletzt geändert hat – optional.
• creator: Die Person, die den Datensatz erstellt hat – optional.

2. Jedes Feld sollte Kommentare haben, insbesondere für Aufzählungen

Beim Entwerfen einer Tabelle sollte jedes Feld einen Kommentar enthalten. Dies ist besonders wichtig für Felder, die Aufzählungen verwenden – alle Enum-Werte sollten im Kommentar aufgeführt sein. Wenn später Änderungen auftreten, sollten diese auch in den Kommentaren berücksichtigt werden.

Schlechtes Beispiel:

CREATE TABLE order_tab (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    payment_status VARCHAR(20) DEFAULT 'not_paid',
    version INT DEFAULT 0,
    created_time DATETIME,
    updated_time DATETIME,
    creator VARCHAR(255),
    modifier VARCHAR(255)
);

Gutes Beispiel:

CREATE TABLE order_tab (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Eindeutiger Bezeichner für den Bestellposten, automatisch inkrementierter Primärschlüssel',
    order_id BIGINT UNIQUE COMMENT 'Global eindeutiger Bezeichner für die Bestellung',
    user_id BIGINT NOT NULL COMMENT 'Eindeutiger Bezeichner für den Benutzer, verknüpft mit der Benutzertabelle',
    total_amount DECIMAL(10, 2) NOT NULL COMMENT 'Gesamter Bestellbetrag, genau bis auf zwei Dezimalstellen',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'Bestellstatus, z. B. PENDING, COMPLETED usw.',
    payment_status VARCHAR(20) DEFAULT 'not_paid' COMMENT 'Zahlungsstatus, z. B. not_paid, paid usw.',
    version INT DEFAULT 0 COMMENT 'Optimistische Sperrversionsnummer zur Steuerung der Parallelität',
    created_time DATETIME COMMENT 'Zeitstempel der Auftragserstellung',
    updated_time DATETIME COMMENT 'Zeitstempel der letzten Aktualisierung',
    creator VARCHAR(255) COMMENT 'Ersteller des Auftrags – typischerweise der Benutzername des Benutzers oder des Systems',
    modifier VARCHAR(255) COMMENT 'Letzte Person oder System, die/das den Auftrag geändert hat'
);

3. Namenskonventionen

Verwenden Sie klare und konsistente Namenskonventionen für Tabellennamen, Feldnamen, Indexnamen usw. Eine gute Namensgebung verbessert die Lesbarkeit und das Verständnis.

Vermeiden Sie beispielsweise Namen wie:

• acc_no, 1_acc_no (schlechte Beispiele)

Verwenden Sie stattdessen:

• account_no, account_number (gute Beispiele)

Zusätzliche Regeln:

• Tabellen- und Feldnamen sollten nur Kleinbuchstaben oder Zahlen verwenden.
• Beginnen Sie keine Namen mit Zahlen.
• Vermeiden Sie Abkürzungen.
• Index-Namenskonventionen:
  • Primärschlüssel: pk_<Feldname>
  • Eindeutiger Index: uk_<Feldname>
  • Regulärer Index: idx_<Feldname>

4. Wählen Sie geeignete Feldtypen

Wählen Sie beim Entwerfen einer Tabelle die am besten geeigneten Feldtypen:

• Verwenden Sie den kleinsten geeigneten Datentyp, um Speicherplatz zu sparen – z. B. bevorzugen Sie tinyint, smallint, int, bigint in dieser Reihenfolge.
• Verwenden Sie für Geldwerte decimal anstelle von float oder double.
• Wenn die Zeichenkettenlänge fest oder nahezu fest ist, verwenden Sie char.
• Verwenden Sie varchar für Zeichenketten variabler Länge, aber halten Sie die Länge unter 5000.
• Verwenden Sie für sehr große Werte text und speichern Sie diese in einer separaten Tabelle, die über den Primärschlüssel verknüpft ist.
• Die kombinierte Länge aller varchar-Felder in einer Tabelle darf 65535 Byte nicht überschreiten. Verwenden Sie bei Bedarf die Typen TEXT oder LONGTEXT.

5. Entwerfen Sie sinnvolle Primärschlüssel

Vermeiden Sie es, Primärschlüssel an die Geschäftslogik zu binden. Es wird beispielsweise nicht empfohlen, eine Benutzer-ID (auch wenn sie eindeutig ist) als Primärschlüssel zu verwenden. Verwenden Sie stattdessen einen bedeutungslosen, aber eindeutigen Bezeichner wie:

• Eine UUID,
• Ein automatisch inkrementierender Primärschlüssel,
• Ein Primärschlüssel, der von einem Snowflake-Algorithmus generiert wurde.

6. Wählen Sie geeignete Feldlängen

Darf ich Ihnen zuerst eine Frage stellen: Stellt im Datenbankbereich die Feldlänge die Zeichenlänge oder die Bytelänge dar?

In MySQL:

• varchar und char geben die Zeichenlänge an.
• Andere Typen geben typischerweise die Bytelänge an.

Zum Beispiel:

• char(10) bedeutet 10 Zeichen.
• bigint(4) bezieht sich auf die Anzeigebreite (nicht die Speichergröße), aber bigint benötigt immer 8 Byte.

Berücksichtigen Sie beim Entwerfen von Tabellen die Feldlänge sorgfältig. Für ein Benutzernamenfeld, das voraussichtlich zwischen 5 und 20 Zeichen lang sein wird, können Sie es beispielsweise als username varchar(32) definieren.

Tipp: Feldlängen werden im Allgemeinen am besten als Potenz von 2 (d. h. 2ⁿ) festgelegt.

7. Bevorzugen Sie logisches Löschen gegenüber physischem Löschen

Physisches Löschen: Daten werden dauerhaft von der Festplatte entfernt, wodurch Speicherplatz freigegeben wird.

Logisches Löschen: Fügen Sie ein Feld wie is_deleted hinzu, um Daten als gelöscht zu markieren.

Beispiel für physisches Löschen:

DELETE FROM account_info_tab WHERE account_no = '666';

Beispiel für logisches Löschen:

UPDATE account_info_tab SET is_deleted = 1 WHERE account_no = '666';

Warum logisches Löschen bevorzugen?

• Die Datenwiederherstellung ist bei physischem Löschen schwierig.
• Autoinkrementierte Primärschlüssel werden nicht zusammenhängend.
• Für Kerngeschäftstabellen ist es besser, Statusfelder zu aktualisieren, als Datensätze physisch zu löschen.

8. Vermeiden Sie zu viele Felder in einer einzigen Tabelle

Versuchen Sie beim Entwerfen einer Tabelle die Anzahl der Felder zu begrenzen – im Allgemeinen nicht mehr als 20.

Zu viele Felder können zu Folgendem führen:

• Große Zeilengrößen,
• Schlechte Abfrageleistung.

Wenn die Geschäftslogik viele Felder erfordert, sollten Sie die große Tabelle in kleinere Tabellen mit demselben Primärschlüssel aufteilen.

Wenn eine Tabelle eine große Anzahl von Feldern enthält, sollten Sie sie in Folgendes aufteilen:

• Eine „Abfragebedingungs“-Tabelle (zur schnellen Filterung),
• Eine „Detail“-Tabelle (für den vollständigen Inhalt), um die Leistung zu verbessern.

9. Verwenden Sie nach Möglichkeit NOT NULL

Sofern es keinen bestimmten Grund gibt, wird empfohlen, Felder als NOT NULL zu definieren.

Warum?

• Verhindert Null-Pointer-Probleme.
• NULL benötigt zusätzlichen Speicherplatz.
• Vergleiche mit NULL sind komplexer und behindern die Abfrageoptimierung.
• NULL-Werte können zu Indexfehlern führen.
• Wenn Sie ein Feld sicher auf eine leere Zeichenkette oder eine Konstante setzen können, sollten Sie es als NOT NULL definieren.

10. Bewerten Sie, welche Felder Indizes benötigen

Bewerten Sie zunächst die Größe Ihres Datensatzes. Wenn eine Tabelle nur ein paar hundert Zeilen hat, ist eine Indizierung möglicherweise nicht erforderlich.

Im Allgemeinen sollte ein Feld, das häufig in Abfragebedingungen verwendet wird, einen Index haben. Die Indizierung sollte jedoch nicht überstrapaziert werden:

• Vermeiden Sie es, zu viele Indizes zu erstellen – beschränken Sie sich auf maximal 5 pro Tabelle.
  • Zu viele Indizes verlangsamen Einfüge- und Aktualisierungsvorgänge.
• Indizieren Sie keine Felder mit geringer Kardinalität (z. B. Geschlecht).
• Beachten Sie Fälle, die zu Indexfehlern führen, z. B. die Verwendung integrierter MySQL-Funktionen für indizierte Felder.
• Um die Anzahl der Indizes zu reduzieren, sollten Sie zusammengesetzte (mehrspaltige) Indizes in Betracht ziehen.
  • Verwenden Sie Techniken wie Covering Indexes und befolgen Sie die Leftmost Prefix-Regel.

Beispiel für eine Benutzertabelle:

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Da Sie wahrscheinlich nach user_id oder name suchen und user_id eindeutig ist, können Sie Indizes wie folgt definieren:

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY un_user_id (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

11. Vermeiden Sie die Verwendung von reservierten MySQL-Wörtern

Wenn ein Datenbankname, ein Tabellenname oder ein Feldname ein reserviertes Wort enthält, muss er in SQL-Anweisungen in Backticks (`) eingeschlossen werden. Dies erschwert das Schreiben von SQL und erhöht das Fehlerrisiko, insbesondere bei der Skripterstellung oder bei der Arbeit mit Shell-Variablen.

Vermeiden Sie daher die Verwendung von reservierten MySQL-Wörtern wie select, interval, desc usw.

12. Bevorzugen Sie die InnoDB-Speicher-Engine

Beim Erstellen von Tabellen müssen Sie eine Speicher-Engine auswählen. Im Allgemeinen wird InnoDB bevorzugt, es sei denn, Ihr Lese-Schreib-Verhältnis beträgt weniger als 1 %, in diesem Fall kann MyISAM in Betracht gezogen werden.

13. Auswahl des richtigen Zeittyps

Beim Entwerfen von Tabellen fügen wir normalerweise gemeinsame zeitbezogene Felder wie create_time, modified_time usw. hinzu. Welchen MySQL-Zeittyp sollten Sie also verwenden?

Hier sind die wichtigsten Optionen:

• date: Speichert Datumswerte im Format yyyy-mm-dd. Bereich: 1000-01-01 bis 9999-12-31. Größe: 3 Byte.
• time: Speichert Zeitwerte im Format hh:mm:ss. Bereich: -838:59:59 bis 838:59:59. Größe: 3 Byte.
• datetime: Speichert Datum und Uhrzeit im Format yyyy-mm-dd hh:mm:ss. Bereich: 1000-01-01 00:00:00 bis 9999-12-31 23:59:59. Größe: 8 Byte. Nicht zeitzonenabhängig.
• timestamp: Speichert Datum und Uhrzeit als Zeitstempel (yyyymmddhhmmss). Bereich: 1970-01-01 00:00:01 bis 2038-01-19 03:14:07. Größe: 4 Byte. Zeitzonenabhängig.
• year: Speichert Jahreswerte im Format yyyy. Bereich: 1901 bis 2155. Größe: 1 Byte.

Empfehlung: Bevorzugen Sie die Verwendung des Typs datetime zum Speichern von Datum und Uhrzeit, da er einen größeren Bereich hat und unabhängig von Zeitzonen ist.

14. Sicherheitsüberlegungen

• Datenverschlüsselung: Sensible Informationen wie Benutzerpasswörter sollten in einem verschlüsselten Format gespeichert werden.
• Datenmaskierung: Für personenbezogene Daten (PII) wie Telefonnummern oder E-Mail-Adressen sollten Sie Datenmaskierung anwenden, um die Privatsphäre und Compliance zu verbessern.

---

Wir sind Leapcell, Ihre erste Wahl für das Hosting von Backend-Projekten.

Leapcell ist die Serverless-Plattform der nächsten Generation für Webhosting, asynchrone Aufgaben und Redis:

Unterstützung mehrerer Sprachen

• Entwickeln Sie mit Node.js, Python, Go oder Rust.

Stellen Sie unbegrenzt Projekte kostenlos bereit

• Zahlen Sie nur für die Nutzung – keine Anfragen, keine Gebühren.

Unschlagbare Kosteneffizienz

• Pay-as-you-go ohne Leerlaufgebühren.
• Beispiel: 25 $ unterstützen 6,94 Millionen Anfragen bei einer durchschnittlichen Antwortzeit von 60 ms.

Optimierte Entwicklererfahrung

• Intuitive Benutzeroberfläche für mühelose Einrichtung.
• Vollständig automatisierte CI/CD-Pipelines und GitOps-Integration.
• Echtzeitmetriken und -protokollierung für verwertbare Erkenntnisse.

Mühelose Skalierbarkeit und hohe Leistung

• Automatische Skalierung zur einfachen Bewältigung hoher Parallelität.
• Kein operativer Aufwand – konzentrieren Sie sich einfach auf den Aufbau.

Erfahren Sie mehr in der Dokumentation!

Folgen Sie uns auf X: @LeapcellHQ