21 Best Practices zur MySQL-Standardisierung und -Optimierung!

Vorwort

Jede gute Angewohnheit ist ein Schatz. Dieser Artikel ist in drei Richtungen unterteilt: SQL-Reue-Heilmittel, SQL-Leistungsoptimierung und SQL-Standard-Eleganz. Er teilt 21 gute Angewohnheiten und Best Practices zum Schreiben von SQL!

Nach dem Schreiben von SQL erklären Sie, wie Sie den Ausführungsplan anzeigen (SQL-Leistungsoptimierung)

Versuchen Sie, sich beim Schreiben von SQL in der täglichen Entwicklung diese gute Angewohnheit anzueignen: Verwenden Sie nach dem Schreiben des SQL „explain“, um es zu analysieren, und achten Sie dabei besonders darauf, ob der Index verwendet wird.

Fügen Sie beim Ausführen einer Lösch- oder Aktualisierungsanweisung ein Limit hinzu (SQL Regret Medicine).

Versuchen Sie, beim Ausführen von Lösch- oder Aktualisierungsanweisungen ein Limit hinzuzufügen. Nehmen Sie als Beispiel das folgende SQL:

aus eUser löschen, wenn Alter > 30, Limit 200;

Denn das Hinzufügen eines Limits hat folgende Vorteile:

1. Reduzieren Sie die Kosten für das Schreiben von falschem SQL. Wenn Sie dieses SQL in der Befehlszeile ausführen und kein Limit hinzufügen, können Sie während der Ausführung versehentlich alle Daten löschen. Was passiert, wenn Sie die falschen Daten löschen? Anders sieht es bei der 200er-Grenze aus. Wenn Sie die Daten versehentlich löschen, verlieren Sie nur 200 Datensätze, die über Binlog schnell wiederhergestellt werden können.
2. Die SQL-Effizienz ist wahrscheinlich höher. Wenn Sie der SQL-Zeile Limit 1 hinzufügen und die erste Zeile die Zielrendite erreicht, wird ohne Limit mit dem Scannen der Tabelle fortgefahren.

3. Vermeiden Sie lange Transaktionen. Wenn beim Ausführen des Löschvorgangs das Alter indiziert ist, fügt MySQL allen zugehörigen Zeilen Schreibsperren und Lückensperren hinzu. Alle mit der Ausführung verbundenen Zeilen werden gesperrt. Wenn die Anzahl der Löschungen groß ist, wirkt sich dies direkt auf das zugehörige Geschäft aus und macht es unbrauchbar.
4. Wenn die Datenmenge groß ist, wird die CPU leicht überlastet. Wenn Sie eine große Datenmenge löschen, ohne die Anzahl der Datensätze zu begrenzen, wird die CPU leicht überlastet, wodurch das Löschen immer langsamer wird.

Beim Entwurf einer Tabelle alle Tabellen und Felder mit entsprechenden Kommentaren versehen (SQL-Standard und elegant)

Diese gute Angewohnheit muss entwickelt werden. Fügen Sie beim Entwerfen von Datenbanktabellen allen Tabellen und Feldern entsprechende Kommentare hinzu, um die spätere Wartung zu erleichtern.

Positives Beispiel:

CREATE TABLE `Konto` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel-ID',
 `name` varchar(255) DEFAULT NULL COMMENT 'Kontoname',
 `balance` int(11) STANDARD NULL KOMMENTAR 'Balance',
 `create_time` datetime NICHT NULL KOMMENTAR 'Erstellungszeit',
 `update_time` datetime NICHT NULL BEIM UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Aktualisierungszeit',
 Primärschlüssel (`id`),
 SCHLÜSSEL `idx_name` (`name`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='Kontotabelle';

Gegenbeispiel:

CREATE TABLE `Konto` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 `balance` int(11) DEFAULT NULL,
 `create_time` datetime NICHT NULL,
 `update_time` datetime NICHT NULL BEIM UPDATE CURRENT_TIMESTAMP,
 Primärschlüssel (`id`),
 SCHLÜSSEL `idx_name` (`name`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8;

Achten Sie beim Schreiben von SQL auf eine konsistente Schlüsselwortgröße und verwenden Sie Einrückungen. (SQL ist elegant und standardisiert)

Positives Beispiel:

SELECT stu.name, sum(stu.score) FROM Student stu WHERE stu.classNo = 'Klasse 1' GROUP BY stu.name

Gegenbeispiel:

SELECT stu.name, sum(stu.score) von Student stu, WHERE stu.classNo = 'Klasse 1', gruppiert nach stu.name.

Durch die Vereinheitlichung der Groß- und Kleinschreibung von Schlüsselwörtern und die Verwendung von Einrückungen sieht Ihr SQL natürlich eleganter aus.

Die INSERT-Anweisung gibt den entsprechenden Feldnamen an (SQL-Standard und elegant)

Gegenbeispiel:

in Studentenwerte einfügen ('666', 'Amateur Grass', '100');

Positives Beispiel:

in Student(student_id,name,score) Werte einfügen (,666',,100');

Der SQL-Änderungsvorgang sollte zunächst mit detaillierten Vorgangsschritten und Rollback-Plan in der Testumgebung durchgeführt und vor der Produktion überprüft werden. (SQL-Bedauernsmedizin)

• Testen Sie die SQL-Änderungen zunächst in einer Testumgebung, um zu vermeiden, dass sie mit Syntaxfehlern in die Produktion überführt werden.
• Beim Ändern von SQL-Operationen müssen detaillierte Operationsschritte angegeben werden, insbesondere wenn Abhängigkeiten bestehen, z. B.: zuerst die Tabellenstruktur ändern und dann die entsprechenden Daten hinzufügen.
• Für SQL-Änderungsvorgänge gibt es einen Rollback-Plan und die entsprechenden SQL-Änderungen werden vor der Produktion überprüft.

Fügen Sie beim Entwerfen einer Datenbanktabelle drei Felder hinzu: Primärschlüssel, Erstellungszeit und Aktualisierungszeit. (SQL-Standard-Eleganz)

Gegenbeispiel:

CREATE TABLE `Konto` (
 `name` varchar(255) DEFAULT NULL COMMENT 'Kontoname',
 `balance` int(11) STANDARD NULL KOMMENTAR 'Balance',
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='Kontotabelle';

Positives Beispiel:

CREATE TABLE `Konto` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel-ID',
 `name` varchar(255) DEFAULT NULL COMMENT 'Kontoname',
 `balance` int(11) STANDARD NULL KOMMENTAR 'Balance',
 `create_time` datetime NICHT NULL KOMMENTAR 'Erstellungszeit',
 `update_time` datetime NICHT NULL BEIM UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Aktualisierungszeit',
 Primärschlüssel (`id`),
 SCHLÜSSEL `idx_name` (`name`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='Kontotabelle';

Grund:

1. Der Primärschlüssel muss hinzugefügt werden. Eine Tabelle ohne Primärschlüssel ist seelenlos.
2. Es wird empfohlen, die Erstellungszeit und die Aktualisierungszeit hinzuzufügen. Detaillierte Prüf- und Tracking-Aufzeichnungen sind hilfreich.

Das Alibaba-Entwicklungshandbuch erwähnt diesen Punkt auch, wie in der Abbildung gezeigt

Überprüfen Sie nach dem Schreiben der SQL-Anweisung die Spalten nach „where“, „order by“ und „group by“ und ob die Spalten, die sich auf mehrere Tabellen beziehen, indiziert wurden. Dabei haben kombinierte Indizes Vorrang. (SQL-Leistungsoptimierung)

Gegenbeispiel:

Positives Beispiel:

-- Einen Index hinzufügen alter table user add index idx_address_age (Adresse, Alter)

Bevor Sie wichtige Daten ändern oder löschen, sichern Sie sie zuerst, sichern Sie sie zuerst, sichern Sie sie zuerst (SQL-Bedauernsmedizin)

Wenn Sie Daten ändern oder löschen möchten, müssen Sie vor der Ausführung von SQL eine Sicherungskopie der zu ändernden Daten erstellen. Im Falle einer falschen Operation können Sie es später bereuen.

Achten Sie auf die implizite Konvertierung der Datentypen der Felder nach where (SQL-Leistungsoptimierung).

Gegenbeispiel:

//Benutzer-ID ist vom Typ Varchar-String. Select * from user where userid =123;

Positives Beispiel:

Wählen Sie * vom Benutzer, wobei Benutzer-ID = „123“ ist.

Grund:

Denn wenn keine einfachen Anführungszeichen hinzugefügt werden, erfolgt der Vergleich zwischen einer Zeichenfolge und einer Zahl, und ihre Typen stimmen nicht überein. MySQL führt eine implizite Typkonvertierung durch und wandelt sie vor dem Vergleich in Gleitkommazahlen um, was letztendlich dazu führt, dass der Index ungültig wird.

Versuchen Sie, alle Spalten als NOT NULL zu definieren (SQL-Standard-Eleganz)

NOT NULL-Spalten sparen mehr Platz, während NULL-Spalten ein zusätzliches Byte als Flag erfordern, um zu bestimmen, ob es NULL ist.
Bei NULL-Spalten muss auf das Nullzeigerproblem geachtet werden. Beim Berechnen und Vergleichen von NULL-Spalten muss auf das Nullzeigerproblem geachtet werden.

Um SQL zu ändern oder zu löschen, schreiben Sie zuerst WHERE zur Überprüfung und fügen Sie dann nach der Bestätigung „Löschen“ oder „Aktualisieren“ hinzu (SQL-Bedauernsmedizin).
Insbesondere wenn Sie Produktionsdaten verarbeiten und auf SQL zum Ändern oder Löschen stoßen, fügen Sie zuerst eine Where-Abfrage hinzu und führen Sie nach der Bestätigung mit OK den Aktualisierungs- oder Löschvorgang aus.

Reduzieren Sie unnötige Feldrückgaben, z. B. durch die Verwendung von „select <spezifisches Feld>“ anstelle von „select *“ (SQL-Leistungsoptimierung).

Gegenbeispiel:

Wählen Sie * vom Mitarbeiter aus;

Positives Beispiel:

Wählen Sie ID und Namen des Mitarbeiters aus.

Grund:

Sparen Sie Ressourcen und reduzieren Sie den Netzwerk-Overhead.
Durch das Abdecken von Indizes können Tabellenrückgaben reduziert und die Abfrageeffizienz verbessert werden.

Alle Tabellen müssen die Innodb-Speicher-Engine verwenden (SQL-Standard-Eleganz)

Innodb unterstützt Transaktionen, Sperren auf Zeilenebene, bessere Wiederherstellbarkeit und bessere Leistung bei hoher Parallelität. Sofern keine besonderen Anforderungen bestehen (d. h. Funktionen, die Innodb nicht erfüllen kann, wie z. B. Spaltenspeicherung, Speicherplatzdaten usw.), müssen daher alle Tabellen die Innodb-Speicher-Engine verwenden.

Der Zeichensatz der Datenbank und der Tabelle wird auf UTF8 (eleganter SQL-Standard) vereinheitlicht.

Verwenden Sie einheitlich die UTF8-Kodierung

• Kann Probleme mit verstümmeltem Code vermeiden
• Dadurch kann das Problem der Indexungültigkeit vermieden werden, das durch den Vergleich und die Konvertierung verschiedener Zeichensätze entsteht.

Wenn Sie Ausdrücke speichern, können Sie utf8mb4 in Betracht ziehen.

Versuchen Sie, varchar statt char zu verwenden. (SQL-Leistungsoptimierung)

Gegenbeispiel:

`deptName` char(100) DEFAULT NULL COMMENT 'Abteilungsname'

Positives Beispiel:

`deptName` varchar(100) DEFAULT NULL COMMENT 'Abteilungsname'

Grund:

Erstens benötigen Felder mit variabler Länge weniger Speicherplatz, wodurch Speicherplatz gespart werden kann.

Wenn Sie die Bedeutung eines Feldes ändern oder dem Feld zusätzliche Status hinzufügen, müssen Sie die Feldanmerkung zeitnah aktualisieren. (SQL ist elegant und standardisiert)
Dieser Punkt ist die Mysql-Spezifikation im Alibaba-Entwicklungshandbuch. Ihre Felder, insbesondere diejenigen, die Aufzählungszustände darstellen, müssen ihre Anmerkungen sofort aktualisieren, wenn ihre Bedeutung geändert wird oder Zustände hinzugefügt werden, um später eine bessere Wartung zu ermöglichen.

SQL ändert Daten und entwickelt die Gewohnheit, Transaktionen zu beginnen und festzuschreiben. (SQL-Medizin gegen Bedauern)

Positives Beispiel:

Beginnen;Konto aktualisieren, Kontostand festlegen = 1000000, wobei Name = „Kontoauszug“;Commit;

Gegenbeispiel:

Kontostand aktualisieren, Guthaben = 1000000, wobei Name = „Amateur Grass“;

Die Benennung von Indizes sollte standardisiert werden. Der Name des Primärschlüsselindex lautet pk_Feldname, der eindeutige Indexname lautet uk_Feldname und der gemeinsame Indexname lautet idx_Feldname. (SQL ist elegant und standardisiert)
Hinweis: pk_ ist der Primärschlüssel; uk_ ist der eindeutige Schlüssel; idx_ ist die Abkürzung für Index.

Es wird keine Funktionskonvertierung oder Ausdrucksberechnung für Spalten in der WHERE-Klausel durchgeführt.

Gehen Sie davon aus, dass loginTime indiziert ist.

Gegenbeispiel:

Wählen Sie Benutzer-ID, Anmeldezeit aus Anmeldebenutzer, wobei Date_ADD (Anmeldezeit, Intervall 7 TAGE) >= jetzt ();

Positives Beispiel:

Erläutern Sie „select userId, loginTime from loginuser“, wobei loginTime >= Date_ADD(NOW(), INTERVAL - 7 DAY);

Wenn zu viele Daten geändert oder aktualisiert werden müssen, sollten Sie dies stapelweise tun.

Gegenbeispiel:

Löschen vom Kontolimit 100000;

Positives Beispiel:

für jedes (200-mal) {vom Kontolimit 500 löschen;}

Grund:

• Große Batch-Operationen führen zu Master-Slave-Verzögerungen.
• Der Vorgang erzeugt große Transaktionen und Blöcke.
• Bei zu großen Datenmengen kommt es zur Vollauslastung der CPU.

Ich hoffe, dass die obigen Inhalte den Lesern auf ihrem Weg zum Programmieren hilfreich sein werden!

Das ist alles für diesen Artikel zu 21 Best Practices zur MySQL-Standardisierung und -Optimierung! Dies ist das Ende des Artikels. Weitere relevante Inhalte zur MySQL-Standardoptimierung finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!