36 Prinzipien der MySQL-Datenbankentwicklung (Zusammenfassung)

Vorwort

Diese Prinzipien sind aus tatsächlichen Kampfhandlungen zusammengefasst.

Hinter jedem Prinzip steckt eine blutige Lektion

Diese Grundsätze richten sich vor allem an Datenbankentwickler. Achten Sie während des Entwicklungsprozesses unbedingt darauf.

I. Grundprinzipien

1. Versuchen Sie, keine Berechnungen in der Datenbank durchzuführen

Wie das Sprichwort sagt: Lass deine Zehen nicht denken, das ist die Aufgabe deines Gehirns

Als Datenbankentwickler sollten wir der Datenbank mehr von dem überlassen, was sie gut kann:

• Versuchen Sie, keine Berechnungen in der Datenbank durchzuführen
• Komplexe Berechnungen werden auf die CPU des Programmterminals verlagert
• Verwenden Sie MySQL so einfach wie möglich

Beispiel:

Versuchen Sie, in MySQL keine Berechnungsfunktionen wie md5(), Order by Rand() usw. zu verwenden.

2. Versuchen Sie, die Datenmenge in einer einzelnen Tabelle zu kontrollieren

Jeder weiß, dass eine zu große Datenmenge in einer einzelnen Tabelle die Effizienz der Datenabfrage beeinträchtigt und in schwerwiegenden Fällen dazu führt, dass die gesamte Datenbank hängen bleibt.

Generell gilt, basierend auf dem geschätzten Datenvolumen einer einzelnen Tabelle innerhalb eines Jahres:

• Reine INT überschreitet nicht 1000W
• CHAR überschreitet nicht 5 Millionen

Gleichzeitig sollten wir versuchen, eine sinnvolle Tabellenpartitionierung vorzunehmen, um eine Überlastung der einzelnen Tabelle zu verhindern. Gängige Strategien zur Tabellenpartitionierung sind:

• Teilen Sie die Tabelle nach USERID (teilen Sie die Tabelle nach ID-Intervall): wird häufig in der Finanzbranche verwendet, bei einer großen Anzahl von Benutzern und offensichtlichen Benutzermerkmalen.
• Teilen Sie die Tabelle nach Datum auf (nach Tag, Woche, Monat): Sie wird häufig in der Telekommunikationsbranche verwendet, beispielsweise als Tabelle mit Internet-Aufzeichnungen von Benutzern, als Tabelle mit SMS-Aufzeichnungen von Benutzern, als Tabelle mit Anrufaufzeichnungen usw.
• Nach GEBIETStabelle (Provinz-, Stadt-, Bezirkstabelle)
• andere

Die Partitionstabelle ist hauptsächlich in den folgenden Szenarien anwendbar:

① Die Tabelle ist sehr groß und kann nicht im Speicher gespeichert werden, oder am Ende der Tabelle befinden sich nur heiße Daten und der Rest sind historische Daten.

② Die Daten in der Partitionstabelle sind einfacher zu verwalten und unabhängige Vorgänge können auf unabhängigen Partitionen ausgeführt werden.

③ Die Daten der Partitionstabelle können auf verschiedene Maschinen verteilt werden, wodurch Ressourcen effizient genutzt werden können.

④ Partitionstabellen können verwendet werden, um bestimmte spezielle Engpässe zu vermeiden.

⑤ Unabhängige Partitionen können gesichert und wiederhergestellt werden.

Allerdings gibt es auch bei der Verwendung von Partitionstabellen einige Einschränkungen. Bitte beachten Sie bei der Verwendung folgendes:

① Eine Tabelle kann maximal 1024 Partitionen haben;

② In Version 5.1 muss der Partitionstabellenausdruck eine Ganzzahl sein, und 5.5 kann Spaltenpartitionierung verwenden.

③ Wenn das Partitionsfeld Primärschlüssel- und eindeutige Indexspalten enthält, müssen sowohl die Primärschlüsselspalte als auch die eindeutige Spalte enthalten sein.

④ Fremdschlüsseleinschränkungen können in Partitionstabellen nicht verwendet werden.

⑤ Die Struktur der vorhandenen Tabelle muss geändert werden.

⑥ Alle Partitionen müssen dieselbe Speicher-Engine verwenden.

⑦ Es gibt einige Einschränkungen hinsichtlich der Funktionen und Ausdrücke, die in Partitionsfunktionen verwendet werden können.

⑧ Einige Speicher-Engines unterstützen keine Partitionierung.

⑨ Für MyISAM-Partitionstabellen kann das Laden des Index in den Cache nicht verwendet werden.

⑩ Für MyISAM-Tabellen müssen beim Verwenden partitionierter Tabellen mehr Dateideskriptoren geöffnet werden.

3. Versuchen Sie, die Anzahl der Tabellenfelder zu kontrollieren

Die Anzahl der Felder in einer einzelnen Tabelle sollte nicht zu groß sein. Optimieren und passen Sie sie entsprechend dem Geschäftsszenario an und versuchen Sie, die Anzahl der Tabellenfelder so anzupassen, dass sie klein und präzise sind. Dies hat die folgenden Vorteile:

• IO-effizient
• Vollständige Tabellendurchquerung
• Schnelle Tischreparatur
• Verbessern der Parallelität
• alter table ist schneller

Wie viele Felder sind also für eine einzelne Tabelle geeignet?

Die Auswertung basiert auf einer einzigen Tabelle mit einer Größe von 1 GB und 5 Millionen Datenzeilen:

• Das sequentielle Lesen einer 1G-Datei dauert N Sekunden
• Nicht mehr als 200 Bytes pro Zeile
• Eine einzelne Tabelle darf nicht mehr als 50 reine INT-Felder enthalten
• Eine einzelne Tabelle kann nicht mehr als 20 CHAR(10)-Felder enthalten.

==> Es wird empfohlen, die Anzahl der Felder in einer einzelnen Tabelle auf 20~50 zu begrenzen

4. Paradigmen und Redundanz ausbalancieren

Auch das Design der Datenbanktabellenstruktur erfordert Ausgewogenheit. In der Vergangenheit haben wir oft gesagt, dass wir die drei Hauptparadigmen strikt befolgen sollten. Lassen Sie uns also zunächst darüber sprechen, was das Paradigma ist:

Erste Normalform: Ein einzelnes Feld kann nicht weiter unterteilt werden. Einzigartigkeit.

Zweite Normalform: Es gibt keine nicht-primären Attribute, die nur von einem Teil des Primärschlüssels abhängen. Beseitigen Sie unvollständige Abhängigkeiten.

Drittes Paradigma: Beseitigen Sie transitive Abhängigkeiten.

Um Paradigmen und Redundanz in einem Satz zusammenzufassen:

Redundanz bedeutet, Speicher gegen Leistung einzutauschen.

Das Paradigma besteht darin, Leistung gegen Speicher einzutauschen.

Aus diesem Grund ist die Entlassung im Berufsleben grundsätzlich beliebter.

Bei der Gestaltung des Modells muss zunächst der konkrete Kompromiss zwischen diesen beiden Aspekten auf der Grundlage der vom Unternehmen bereitgestellten Rechenleistung und Speicherressourcen gefunden werden.

Zweitens werden Data Warehouses in der Internetbranche im Allgemeinen gemäß dem Kimball-Modell implementiert, und die Modellierung ist auch aufgabengesteuert, sodass der Kompromiss zwischen Redundanz und Paradigma den Aufgabenanforderungen entspricht.

Beispielsweise muss ein Satz von Indikatordaten vor 8 Uhr morgens verarbeitet werden, aber das Berechnungszeitfenster ist sehr klein. Um die Berechnungszeit des Indikators so weit wie möglich zu reduzieren, sollte die Mehrtabellenzuordnung während des Berechnungsprozesses so weit wie möglich reduziert werden, und beim Entwurf des Modells ist mehr Redundanz erforderlich.

5. Ablehnung 3B

Datenbankparallelität ist wie Stadtverkehr, der nichtlinear wächst

Dies erfordert, dass wir bei der Entwicklung der Datenbank auf Engpässe bei hoher Parallelität achten, um eine Datenbanklähmung aufgrund hoher Parallelität zu verhindern.

Die Ablehnung von 3B bezieht sich hier auf:

• Big SQL: Zur Reduzierung
• B IG-Transaktion
• GROSSE Charge

2. Prinzipien der Feldklassen

1. Numerische Feldtypen sinnvoll nutzen

Drei Arten numerischer Werte:

• Ganzzahl: TINYINT(1Byte), TINYINT(1Byte), SMALLINT(2Byte), MEDIUMINT(3Byte), INT(4Byte), BIGINT(8Byte)
• Gleitkommatyp: FLOAT (4B), DOUBLE (8B)
• DEZIMAL(M,D)

Hier sind einige gängige Beispiele:

1) INT(1) VS INT(11)

Viele Leute können den Unterschied zwischen INT(1) und INT(11) nicht erkennen. Ich glaube, alle sind sehr neugierig. Tatsächlich sind 1 und 11 nur Unterschiede in der Anzeigelänge. Das heißt, unabhängig vom Wert von x in int(x) ist der Wertebereich der gespeicherten Zahl immer noch der Wertebereich des int-Datentyps selbst und x ist nur die Länge der Datenanzeige.

2) BIGINT AUTO_INCREMENT

Wie wir alle wissen, liegt der von einem signierten int unterstützte Maximalwert bei etwa 2,2 Milliarden, was weit über unseren Anforderungen und der Leistungsgrenze liegt, die eine einzelne MySQL-Tabelle unterstützen kann. Bei OLTP-Anwendungen wird die Größe einer einzelnen Tabelle im Allgemeinen im zweistelligen Millionenbereich gehalten und erreicht nicht die Obergrenze von 2,2 Milliarden. Wenn Sie die reservierte Menge erhöhen möchten, können Sie den Primärschlüssel in einen vorzeichenlosen Int mit einer Obergrenze von 4,2 Milliarden ändern, was bereits ausreichend ist.

Die Verwendung von Bigint belegt mehr Festplatten- und Speicherplatz. Schließlich ist der Speicherplatz begrenzt. Eine ineffektive Belegung führt zu mehr Datenein- und -auslagerungen, was den IO-Druck erhöht und sich nachteilig auf die Leistung auswirkt.

Daher wird empfohlen, für den automatisch inkrementierten Primärschlüssel den vorzeichenlosen int-Typ zu verwenden, von der Verwendung von bigint wird jedoch abgeraten.

3) DEZIMAL(N,0)

Bei Verwendung des Datentyps DECIMAL ist die Anzahl der Dezimalstellen im Allgemeinen ungleich 0. Wenn die Anzahl der Dezimalstellen auf 0 gesetzt ist, wird empfohlen, den Typ INT zu verwenden.

2. Zeichen in Zahlen umwandeln

Numerische VS-Zeichenfolgenindizes haben mehr Vorteile:

• Effizienter
• Schnellere Abfrage
• Geringerer Platzbedarf

Beispiel: Verwenden Sie unsigned INT zum Speichern von IP anstelle von CHAR(15).

INT UNSIGNED

Sie können INET_ATON() und INET_NTOA() verwenden, um zwischen IP-Zeichenfolgen und Werten zu konvertieren.

3. Verwenden Sie zuerst ENUM oder SET

Für einige aufgezählte Daten empfehlen wir, zuerst ENUM oder SET zu verwenden. Solche Szenarien eignen sich für:

1) String-Typ

2) Die möglichen Werte sind bekannt und endlich

Lagerung:

1) ENUM belegt 1 Byte und wird in eine numerische Operation umgewandelt

2) SET ist knotenabhängig und belegt bis zu 8 Bytes

3) Beim Vergleichen sind einfache Anführungszeichen erforderlich (auch für numerische Werte).

Beispiel:

`Geschlecht` enum('F','M') KOMMENTAR 'Geschlecht';

`c1` enum('0','1','2','3') KOMMENTAR 'Prüfung';

4. Vermeiden Sie die Verwendung von NULL-Feldern

Warum versuchen wir, beim Entwerfen von Datenbanktabellenfeldern NOT NULL DEFAULT '' hinzuzufügen? Hier müssen wir über die Nachteile der Verwendung von NULL-Feldern sprechen:

Schwierig zu optimierende Abfragen

NULL-Spalten und Indizes erfordern zusätzlichen Speicherplatz

Zusammengesetzte Indizes, die NULL enthalten, sind ungültig

Beispiel:

1) `a` char(32) DEFAULT NULL [Nicht empfohlen]

2) `b` int(10) NOT NULL [Nicht empfohlen]

3) `c` int(10) NOT NULL DEFAULT 0 [Empfohlen]

5. Weniger Text und weniger BLOB verwenden

Die Verarbeitungsleistung des TEXT-Typs ist viel geringer als die des VARCHAR

• Erzwingen der Generierung einer temporären Festplattentabelle
• Mehr Platz verschwenden
• VARCHAR(65535) ==> 64K (UTF-8 beachten)

Vermeiden Sie die Verwendung von TEXT/BLOB-Datentypen

Wenn es für geschäftliche Zwecke notwendig ist, wird empfohlen, es in eine separate Tabelle aufzuteilen

Beispiel:

Tabelle erstellen t1 (
  id INT NICHT NULL AUTO_INCREMENT,
  Daten TEXT NICHT NULL,
  PRIMÄRSCHLÜSSEL(id)
)ENGINE=InnoDB;

6. Speichern Sie keine Bilder in der Datenbank

Erstes Bild:

Es ist ersichtlich, dass sich die Datenbankgröße erhöht und die Lese- und Schreibgeschwindigkeit verlangsamt, wenn alle Bilder in der Datenbank gespeichert werden.

Nachteile der Speicherung von Bildern in einer Datenbank:

1. Die Lese-/Schreibgeschwindigkeit der Datenbank wird nie mit der Geschwindigkeit der Dateisystemverarbeitung mithalten können.
2. Datenbank-Backups werden immer umfangreicher und zeitaufwändiger
3. Der Zugriff auf Dateien erfordert das Durchlaufen Ihrer Anwendungs- und Datenbankebenen.

★Empfohlene Lösung: Speichern Sie den Bildpfad in der Datenbank

Erstellen Sie einen Pfad basierend auf Jahr, Monat und Tag. Ob der Pfad nach Jahr, Monat, Tag oder Jahr und Monat generiert werden soll, hängt von Ihren Anforderungen ab (nicht unbedingt vom Datum).

Der Schlüssel liegt darin, zu verstehen, warum Sie es auf mehrere Ordner verteilen müssen. Dies wird durch ein Prinzip erklärt:

Das Betriebssystem verfügt über eine Begrenzung für die Anzahl der Dateien in einem einzelnen Verzeichnis. Wenn eine große Anzahl von Dateien vorhanden ist. Die Geschwindigkeit, mit der Dateien aus dem Verzeichnis abgerufen werden, wird immer geringer. Um die Geschwindigkeit aufrechtzuerhalten, ist es daher erforderlich, sie nach festen Regeln auf mehrere Verzeichnisse zu verteilen.

Die Bilder werden über verschiedene Datenträgerpfade verteilt. Das Datenbankfeld speichert etwas wie „images/2012/09/25/1343287394783.jpg“

Der ursprünglich hochgeladene Bilddateiname wird umbenannt und gespeichert, beispielsweise generiert entsprechend dem Zeitstempel 1343287394783.jpg. Dadurch sollen doppelte Dateinamen vermieden werden, die auftreten können, wenn mehrere Personen Bilder in dasselbe Verzeichnis hochladen.

Egal nach welchen Regeln Sie die Bilder benennen, Hauptsache der Bildname ist eindeutig.

Wenn die Website beispielsweise eine große Zahl gleichzeitiger Besucher hat, sollte die Verzeichnisgenerierung so detailliert wie möglich sein. Wenn es beispielsweise auf die Stunde genau ist, kann jede Stunde ein Ordner sein. Dabei ist es so, dass in 0,001 Sekunden zwei User gleichzeitig Bilder hochladen (da die Bilder dann im selben Stundenordner gespeichert werden). Denn der Zeitstempel ist auf die Sekunde genau. Um die Eindeutigkeit des Bildnamens sicherzustellen und ein Überschreiben zu vermeiden, können nach dem Zeitstempel Millisekunden und Mikrosekunden hinzugefügt werden. Zusammenfassend lässt sich sagen: Je größer das Volumen gleichzeitiger Zugriffe, desto höher die Je präziser, desto besser.

Abseits vom Thema:

1) Warum lautet der gespeicherte Datenträgerpfad „images/2012/09/25/1343287394783.jpg“ statt „/images/2012/09/25/1343287394783.jpg“ (mit einem Schrägstrich am Anfang)?

Wenn Sie den Bildpfad abrufen müssen, um das Bild auf der Seite anzuzeigen, können Sie, sofern es sich um einen relativen Pfad handelt, zum Zusammenstellen "./" + "images/2012/09/25/1343287394783.jpg" verwenden.

Wenn Sie einen separaten Domänennamen benötigen (z. B. bei der CDN-Beschleunigung), können Sie Domänennamen wie img1.xxx.com und img2.xxx.com verwenden.

Direkt zusammensetzen "http://img1.xxx.com/" + "images/2012/09/25/1343287394783.jpg"

2) Warum lautet der gespeicherte Datenträgerpfad „images/2012/09/25/1343287394783.jpg“ statt „http://www.xxx.com/images/2012/09/25/1343287394783.jpg“?

Dies erfordert tatsächlich CDN-Wissen, und ich werde hier nicht näher auf das spezifische CDN-Wissen eingehen. Kurz gesagt:

CDN-Dienst: Sehr gut geeignet für statische Inhalte. Wenn Sie also Produktbilder haben und die Anzahl der Besuche zunimmt, müssen Sie diese bei der Anmietung eines CDN-Dienstes nur auf den Server des Anbieters hochladen.

Beispiel: Peking greift auf den Changsha-Server zu, die Entfernung ist zu groß. Ich kann die Produktbilder vollständig auf dem Cloud-Dienst in Peking ablegen (ich glaube, der Cloud-Speicher, der Websites derzeit zur Verfügung gestellt wird, ist eigentlich ein CDN, das Umleitung und lokalen Zugriff auf Websites ermöglicht). Auf diese Weise können Benutzer in Peking, wenn sie die Website besuchen, die Bilder tatsächlich von nahegelegenen Standorten abrufen. Es ist keine Fernübertragung erforderlich.

Verwenden Sie zum Laden von Bildern den Domänennamen img.xxx.com. Dieser Domänenname wird zu einem Cloud-Dienst in Peking aufgelöst.

Praxis: In der Datenbank wird "images/2012/09/25/1343287394783.jpg" gespeichert.

Eine tatsächliche Speicherung der Bilder auf dem Webserver erfolgt nicht. Laden Sie es auf den CDN-Server in Peking hoch.

Ich habe es aus der Datenbank genommen, direkt "img.xxx.com/" + "images/2012/09/25/1343287394783.jpg"

Wenn es beispielsweise mehrere gibt, nennen Sie sie img1.xx.com und img2.xx.com

Wie auch immer, Sie können tun, was Sie wollen. Speichern Sie also direkt den Domänennamen. Es wird sehr lästig. Migrationsprobleme.

3. Indexgrundsätze

1. Fügen Sie Indizes sorgfältig und vernünftig hinzu

• Das Hinzufügen von Indizes dient der Verbesserung der Abfrage
• Das Hinzufügen von Indizes verlangsamt Updates
• Mehr Indizes sind nicht besser
• Vermeiden Sie das Hinzufügen von Indizes, die weggelassen werden können (bewerten Sie die Datendichte und Datenverteilung umfassend, vorzugsweise nicht mehr als 20 % der Anzahl der Felder).
• Erwägen Sie die Abdeckung von Indizes in Verbindung mit Core-SQL

Beispiel: Keinen Index für die Spalte „Geschlecht“ erstellen

Aus theoretischen Artikeln erfahren Sie, dass Felder mit einer hohen Werteduplikationsrate nicht für die Indizierung geeignet sind. Sagen Sie nicht, dass das Geschlechtsfeld nur zwei Werte hat. Internetnutzer haben persönlich getestet, dass ein Feld die Anfangsbuchstaben des Pinyin als Wert verwendet, mit insgesamt 26 Möglichkeiten. Nach dem Hinzufügen des Index ist die Geschwindigkeit bei einem Datenvolumen von Millionen mit dem Index langsamer als ohne Index!

Warum ist das Geschlecht für die Indexierung nicht geeignet? Da Sie für den Zugriff auf den Index einen zusätzlichen IO-Overhead bezahlen müssen, erhalten Sie vom Index nur die Adresse. Wenn Sie tatsächlich auf die Daten zugreifen möchten, müssen Sie dennoch IO für die Tabelle ausführen. Wenn Sie einige Datenpunkte aus einer Tabelle mit 1 Million Zeilen abrufen möchten, lohnt es sich, den E/A-Aufwand zu nutzen, um die Daten schnell zu lokalisieren und auf den Index zuzugreifen. Wenn Sie jedoch 500.000 Datenzeilen aus 1 Million Datenzeilen nehmen, z. B. das Feld „Geschlecht“, müssen Sie 500.000 Mal auf den Index und dann 500.000 Mal auf die Tabelle zugreifen. Die Gesamtkosten sind nicht geringer als bei einem direkten Scannen der Tabelle.

2. Zeichenfelder müssen einen Präfixindex haben

Diskriminierung:

Einzelbuchstabenunterscheidung: 26

4-Buchstaben-Diskriminierung: 26*26*26*26 = 456.976

5-Buchstaben-Diskriminierung: 26*26*26*26*26 = 11.881.376

6-Buchstaben-Unterscheidung: 26*26*26*26*26*26 = 308.915.776

Zeichenfelder müssen einen Präfixindex haben, zum Beispiel:

`Pinyin` varchar(100) DEFAULT NULL COMMENT 'Bezirks-Pinyin', 
SCHLÜSSEL `idx_pinyin` (`pinyin`(8)), 
) ENGINE=InnoDB

3. Führen Sie keine Operationen an Indexspalten durch

Dafür gibt es zwei Gründe:

1) Der Index wird nicht verwendet.

2) Führt zu einem vollständigen Tabellenscan

Beispiel:

SCHLECHTES BEISPIEL:

Wählen Sie * aus der Tabelle aus 
WO heute_Tage(aktuelles_Datum) – heute_Tage(Datumsspalte) <= 10

GUTES BEISPIEL:

Wählen Sie * aus der Tabelle aus 
WO date_col >= DATE_SUB('2011-10-22',INTERVALL 10 TAGE);

4. Verwenden Sie die Auto-Increment-Spalte oder die globale ID als INNODB-Primärschlüssel

• Erstellen Sie einen gruppierten Index für den Primärschlüssel
• Sekundärindizes speichern Primärschlüsselwerte
• Primärschlüssel sollten nicht aktualisiert oder geändert werden
• Werte in aufsteigender Reihenfolge einfügen
• Vermeiden Sie die Verwendung von Zeichenfolgen als Primärschlüssel
• Clustered-Index-Aufteilung
• Es wird empfohlen, als Ersatzprimärschlüssel eine AUTO_INCREMENT-Spalte oder einen geschäftsunabhängigen globalen ID-Generator zu verwenden.
• Wenn Sie keinen Primärschlüssel angeben, verwendet InnoDB stattdessen einen eindeutigen und nicht-null-Wertindex.

5. Vermeiden Sie Fremdschlüssel so weit wie möglich

1. Online-OLTP-Systeme versuchen, keine Fremdschlüssel zu verwenden:
2. Fremdschlüssel sparen Entwicklungsaufwand
3. Es fallen zusätzliche Kosten an
4. Zeilenweise Bedienung
5. Kann andere Tabellen „erreichen“, was bedeutet, dass sie gesperrt werden
6. Bei hoher Parallelität kommt es häufig zu Deadlocks

Es wird empfohlen, dass das Programm die Einschränkungen garantiert

Beispielsweise lautet unsere ursprüngliche Anweisung zur Tabellenerstellung wie folgt:

CREATE TABLE `Benutzer` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel',
 `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Benutzername',
 PRIMÄRSCHLÜSSEL (`user_id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;
 
CREATE TABLE `Reihenfolge` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel',
 `total_price` decimal(10,2) NICHT NULL STANDARD '0.00', 
 `user_id` int(11) NICHT NULL STANDARD '0',
 Primärschlüssel (`id`), 
 SCHLÜSSEL `for_indx_user_id` (`user_id`), 
 CONSTRAINT `for_indx_user_id` Fremdschlüssel (`user_id`) Referenzen `Benutzer` (`user_id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Ohne Fremdschlüsseleinschränkungen:

CREATE TABLE `Benutzer` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel', 
 `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Benutzername',
 PRIMÄRSCHLÜSSEL (`user_id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;
 
CREATE TABLE `Reihenfolge` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel', 
 `total_price` decimal(10,2) NICHT NULL STANDARD '0.00', 
 `user_id` int(11) NICHT NULL STANDARD '0',
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Nachdem die Fremdschlüsseleinschränkung nicht angewendet wurde, fügen wir zur Beschleunigung der Abfrage normalerweise dem Feld einen Index hinzu, der die Fremdschlüsseleinschränkung nicht festlegt.

CREATE TABLE `Reihenfolge` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel', 
 `total_price` decimal(10,2) NICHT NULL STANDARD '0.00', 
 `user_id` int(11) NICHT NULL STANDARD '0',
 PRIMÄRSCHLÜSSEL (`id`), SCHLÜSSEL `idx_user_id` (`user_id`),
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

In der tatsächlichen Entwicklung werden Fremdschlüsseleinschränkungen im Allgemeinen nicht festgelegt.

4. SQL-Klassenprinzipien

1. Halten Sie SQL-Anweisungen so einfach wie möglich

Während des Entwicklungsprozesses versuchen wir, die SQL-Anweisungen einfach zu halten. Vergleichen wir große SQL-Anweisungen und mehrere einfache SQL-Anweisungen.

1. Traditionelle Designideen
2. BUG MySQL NICHT
3. Eine SQL-Anweisung kann nur auf einer CPU ausgeführt werden.
4. Was bedeutet eine SQL-Anweisung von 1 Sekunde bei einer hohen Parallelität von über 5000 QPS?
5. Möglicherweise blockiert ein großes SQL die gesamte Datenbank

Lehnen Sie große SQL-Anweisungen ab und zerlegen Sie sie in mehrere einfache SQL-Anweisungen.

1. Die Trefferquote für den Simple-SQL-Cache ist höher
2. Reduzieren Sie die Tabellensperrzeit, insbesondere MyISAM
3. Verwenden Sie mehrere CPUs

2. Halten Sie Transaktionen (Verbindungen) kurz

1. Prinzip der Transaktions-/Verbindungsnutzung: Öffnen und nutzen, Schließen nach Nutzung
2. Um die Nutzung von Sperrressourcen zu reduzieren, werden alle nicht mit der Transaktion in Zusammenhang stehenden Vorgänge außerhalb der Transaktion platziert.
3. Verwenden Sie mehrere kurze Transaktionen anstelle von langen Transaktionen, ohne die Konsistenz zu zerstören

Beispiel:

1) Warten auf den Bild-Upload beim Posten

2) Eine große Anzahl von Schlafverbindungen

3. Vermeiden Sie die Verwendung von SP/TRIG/FUNC so weit wie möglich

In Online-OLTP-Systemen sollten wir:

• Verwenden Sie gespeicherte Prozeduren so wenig wie möglich
• Verwenden Sie so wenig Auslöser wie möglich
• Reduzieren Sie die Verwendung von MySQL-Funktionen zur Ergebnisverarbeitung

Überlassen Sie alle oben genannten Aufgaben dem Client-Programm

4. Versuchen Sie, SELECT * nicht zu verwenden

Bei Verwendung von SELECT * werden mehr CPU, Speicher, IO und Netzwerkbandbreite verbraucht.

Beim Schreiben von Abfrageanweisungen sollten wir versuchen, SELECT * nicht zu verwenden und nur die erforderlichen Datenspalten zu verwenden:

• Sichereres Design: Reduzierung der Auswirkungen von Tabellenänderungen
• Bietet die Möglichkeit, abdeckende Indexe zu verwenden
• Select/JOIN reduziert die Generierung temporärer Festplattentabellen, insbesondere bei TEXT/BLOB

Beispiel:

Nicht empfohlen:

SELECT * FROM-Tag
WO id = 999148

empfehlen:

SELECT-Schlüsselwort FROM-Tag
WO id = 999148

5. OR in IN() umschreiben

Für dasselbe Feld schreiben Sie oder als in() um.

ODER Effizienz: O(n)

IN-Effizienz: O(Log n)

Wenn n groß ist, ist OR viel langsamer

Achten Sie darauf, die Anzahl der INs zu kontrollieren. Es wird empfohlen, dass n kleiner als 200 ist.

Beispiel:

Nicht empfohlen:

Wählen Sie * von opp WHERE phone='12347856' oder phone='42242233'

empfehlen:

Wählen Sie * von opp WHERE phone in ('12347856' , '42242233')

6. Schreiben Sie OR als UNION um

Für unterschiedliche Felder ersetzen Sie „oder“ durch „Vereinigung“.

1. Reduzieren Sie „oder“-Abfragen in verschiedenen Feldern
2. Merge-Index ist oft sehr dumm
3. Wenn Sie sich sicher genug sind: setzen Sie global optimizer_switch='index_merge=off';

Beispiel:

Nicht empfohlen:

Wählen Sie * aus opp 
WO Telefon='010-88886666' 
oder 
Handy='13800138000';

empfehlen:

Wählen Sie * aus opp 
WO Telefon='010-88886666' 
Union 
Wählen Sie * aus opp 
WO Handy='13800138000';

7. Vermeiden Sie negative Abfragen und %-Präfix-Fuzzy-Abfragen

Bei der tatsächlichen Entwicklung sollten wir versuchen, negative Abfragen zu vermeiden. Was sind negative Abfragen? Sie lauten hauptsächlich wie folgt:

NICHT, !=, <>, !<, !>, EXISTIERT NICHT, NICHT IN, NICHT WIE usw.

Gleichzeitig müssen wir auch Fuzzy-Abfragen mit dem Präfix % vermeiden, da hierdurch ein B+-Baum verwendet wird, was dazu führt, dass der Index nicht verwendet wird und ein vollständiger Tabellenscan durchgeführt wird. Die Leistung und Effizienz sind denkbar schlecht.

Beispiel:

8. Reduzieren Sie COUNT(*)

In der Entwicklung verwenden wir häufig COUNT(*), wissen aber nicht, ob diese Verwendung zu einer großen Ressourcenverschwendung führt. Da COUNT(*) einen großen Ressourcenaufwand verursacht, sollten wir versuchen, es so wenig wie möglich zu verwenden.

Für die Zählstatistik empfehlen wir:

1. Echtzeitstatistiken: Verwenden Sie Memcache, bidirektionale Updates und führen Sie Benchmarks am frühen Morgen aus
2. Nicht-Echtzeit-Statistiken: Versuchen Sie, eine separate Statistiktabelle zu verwenden und regelmäßig neu zu berechnen

Vergleichen wir COUNT(*) mit mehreren anderen COUNTs:

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'Firmen-ID',
`sale_id` int(10) unsigned DEFAULT NULL, 

abschließend:

ANZAHL(*)=ANZAHL(1)

ANZAHL(0)=ANZAHL(1)

ANZAHL(1)=ANZAHL(100)

ANZAHL(*)!=ANZAHL(Spalte)

9. LIMIT effizientes Paging

Traditionelles Paging:

Wählen Sie * aus dem Tabellenlimit 10000,10;

LIMIT-Prinzip:

1. Grenze 10000,10
2. Je größer der Versatz, desto langsamer

Empfohlene Seitenauslagerung:

Wählen Sie * aus der Tabelle, wobei ID>=23423, Limit 11 ist. 
#10+1 (10 Artikel pro Seite)
Wählen Sie * aus der Tabelle, wobei ID>=23434, Limit 11 ist.

Paging-Methode 2:

Wählen Sie * aus der Tabelle, wobei ID >= (Wählen Sie ID aus der Tabelle, Limit 10000,1) Limit 10 ist;

Paging-Methode drei:

Wählen Sie * aus der Tabelle INNER JOIN (wählen Sie ID aus der Tabelle LIMIT 10000,10) USING (ID);

Paging-Methode vier:

#Verwenden Sie zuerst das Programm, um die ID zu erhalten:
Wähle ID aus Tabellenlimit 10000,10;
#Verwenden Sie „in“, um den Datensatz zu erhalten, der der ID entspricht. Select * from table WHERE id in (123,456…);

Die spezifische Notwendigkeit besteht darin, den Index entsprechend dem tatsächlichen Szenario zu analysieren und neu zu organisieren

Beispiel:

10. Verwenden Sie UNION ALL statt UNION

Wenn Sie die Ergebnisse nicht deduplizieren müssen und nur mehrere Tabellen gemeinsam abfragen und anzeigen möchten, verwenden Sie UNION ALL, da UNION einen Deduplizierungs-Overhead verursacht.

Beispiel:

MySQL>SELECT * FROM detail20091128 UNION ALL 
WÄHLEN SIE * VON detail20110427 UNION ALL 
WÄHLEN SIE * VON detail20110426 UNION ALL 
WÄHLEN SIE * VON detail20110425 UNION ALL 
WÄHLEN SIE * VON detail20110424 UNION ALL 
WÄHLEN SIE * AUS detail20110423;

11. Zerlegen Sie die Verbindung, um eine hohe Parallelität sicherzustellen

Es wird nicht empfohlen, JOIN auf mehr als zwei Tabellen in einer Datenbank mit hoher Parallelität auszuführen.

Die ordnungsgemäße Zerlegung von Verknüpfungen gewährleistet eine hohe Parallelität:

• Möglichkeit zum Zwischenspeichern großer Mengen früherer Daten
• Verwenden mehrerer MyISAM-Tabellen
• IN() für kleine IDs großer Tabellen
• Ein Join verweist mehrfach auf dieselbe Tabelle

Beispiel:

Ursprüngliches SQL:

MySQL> Wählen Sie * aus dem Tag aus 
BEITRETEN tag_post 
auf tag_post.tag_id=tag.id 
Beitrag beitreten 
auf tag_post.post_id=post.id 
WHERE tag.tag='Gebrauchtspielzeug';

Aufschlüsselung des SQL:

MySQL> Select * from tag WHERE tag='Gebrauchtspielzeug'; 
MySQL> Wählen Sie * aus tag_post, wobei tag_id=1321; 
MySQL> Wählen Sie * aus dem Beitrag aus, wobei post.id in (123,456,314,141) ist.

12. GROUP BY entfernt die Sortierung

Verwenden Sie GROUP BY, um Gruppierung und automatische Sortierung zu erreichen

Keine Sortierung erforderlich: Order by NULL

Spezifische Sortierung: Gruppieren nach DESC/ASC

Beispiel:

13. Vergleich von Spaltenwerten desselben Datentyps

Prinzip: Zahlen für Zahlen, Zeichen für Zeichen

Vergleich von numerischen Spalten und Zeichentypen: Konvertieren in doppelte Genauigkeit zum Vergleich

Vergleich zwischen Zeichenspalten und numerischen Typen: Die gesamte Zeichenspalte wird in einen numerischen Wert umgewandelt, und Indexabfragen werden nicht verwendet

Beispiel:

Feld: `remark` varchar(50) NOT NULL COMMENT 'Bemerkungen, standardmäßig leer',

MySQL>SELECT `id`, `Geschenkcode` FROM Geschenk 
WO `deal_id` = 640 UND Bemerkung=115127; 
1 Zeile im Satz (0,14 Sek.)
 
 
MySQL>SELECT `id`, `Geschenkcode` FROM Poolgeschenk 
WO `deal_id` = 640 UND remark='115127'; 
1 Zeile im Satz (0,005 Sek.)

14. Daten laden

Schneller Batch-Datenimport:

1. Das Laden im Stapelbetrieb ist schneller als das Laden einzelner Zeilen und erfordert nicht jedes Mal eine Aktualisierung des Caches.
2. Das Laden ohne Index ist schneller als das Laden mit Index
3. Werte einfügen, Werte, Werte Indexaktualisierung reduzieren
4. Das Laden von Daten ist etwa 20-mal schneller als das Einfügen

Versuchen Sie, INSERT ... SELECT nicht zu verwenden. Ein Grund dafür ist, dass es zu Verzögerungen kommt, und ein anderer Grund ist, dass Synchronisierungsfehler auftreten können.

15. Große Batch-Updates aufteilen

• Um Spitzenzeiten zu vermeiden, sollten große Batch-Updates am frühen Morgen durchgeführt werden.
• Keine Einschränkungen am frühen Morgen
• Die Standardrate während des Tages beträgt 100 Nachrichten pro Sekunde (besondere Bedingungen werden später besprochen)

Beispiel:

Beitrag aktualisieren, Tag=1 festlegen, wobei ID in (1,2,3) ist; 
Schlaf 0,01; 
Beitrag aktualisieren, Tag=1 festlegen, wobei ID in (4,5,6) ist; 
Schlaf 0,01;
…

16. Kennen Sie jedes SQL

Als DBA oder sogar Datenbankentwickler müssen wir mit jedem SQL der Datenbank sehr vertraut sein. Zu den gängigen Befehlen gehören:

• PROFIL ANZEIGEN
• MySQLsla
• MySQL-Dumpslow
• erklären
• Slow-Log anzeigen
• Prozessliste anzeigen
• QUERY_RESPONSE_TIME ANZEIGEN (Percona)

5. Grundsätze der Vereinbarung

1. Isolation zwischen Online und Offline

Erstellen Sie ein Datenbank-Ökosystem, um Berechtigungen für den drahtlosen Datenbankbetrieb sicherzustellen

Prinzip: Online verbindet online, offline verbindet offline

1. Pro-Bibliothek für Produktionsdaten
2. Die Vorproduktionsumgebung verwendet die Vorbibliothek
3. Testbibliothek
4. Entwicklungsbibliothek

2. Unterabfragen ohne DBA-Bestätigung verbieten

1. Die meisten Fälle sind schlecht optimiert
2. Spezielle WHERE-Unterabfrage mit IN-ID
3. Im Allgemeinen kann es mit JOIN umgeschrieben werden

Beispiel:

MySQL> wähle * aus Tabelle1, wobei ID in (wähle ID aus Tabelle2 aus); 
MySQL> in Tabelle1 einfügen (* aus Tabelle2 auswählen); // kann Replikationsanomalien verursachen

3. Sperren Sie das Programm niemals explizit

1. Externe Sperren haben keine Kontrolle über die Datenbank
2. Hohe Burst-Raten sind eine Katastrophe
3. Extrem schwierig zu debuggen und Fehler zu beheben

Bei Konsistenzproblemen, wie beispielsweise gleichzeitigen Abzügen, behandeln wir diese mithilfe von Transaktionen und führen vor dem Commit eine zweite Konfliktprüfung durch.

4. Der einheitliche Zeichensatz ist UTF8

5. Einheitliche Namenskonventionen

1) Bibliotheks- und Tabellennamen sind alle in Kleinbuchstaben

2) Der Standardindexname ist „idx_field name“

3) Verwenden Sie Abkürzungen für Bibliotheksnamen, vorzugsweise zwischen 2 und 7 Buchstaben

Datenfreigabe ==> ds

4) Vermeiden Sie die Verwendung reservierter Wörter bei der Benennung

Es wird empfohlen, dass Datenbankentwickler alle oben genannten Fallstricke im Hinterkopf behalten. Ich hoffe, dass es für jedermanns Studium hilfreich sein wird, und ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.