Meine Meinung zu MySQL-Entwicklungsspezifikationen

Die meisten MySQL-Spezifikationen sind auch im Internet zu finden. Ich möchte hier einige Dinge mitteilen, die Lao Ye persönlich für wichtiger hält oder die leicht übersehen und leicht verwechselt werden.

1. Standardmäßig die InnoDB-Engine verwenden

[Sicht des alten Ye] Ich habe es schon oft gefordert Mal, und InnoDB ist für fast 99 % der MySQL-Anwendungsszenarien geeignet und die Systemtabellen in MySQL 5.7 wurden auf InnoDB umgestellt, es gibt keinen Grund, bei MyISAM zu bleiben.

Darüber hinaus müssen InnoDB-Tabellen, die häufig gelesen und geschrieben werden, Ganzzahlen mit automatischer Inkrementierung/sequentieller Charakteristik als explizite Primärschlüssel verwenden.

　[Referenz]: [MySQL FAQ]-Reihe – Warum wird empfohlen, automatisch inkrementierte Spalten als Primärschlüssel für InnoDB-Tabellen zu verwenden?

　2. Wählen Sie utf-8 als Zeichensatz

[Sicht von Old Yes] Wenn Sie Speicherplatz sparen möchten, wird empfohlen, latin1 zu wählen. Aufgrund der sogenannten „Universalität“ wird normalerweise empfohlen, UTF-8 zu wählen. Tatsächlich können die vom Benutzer übermittelten UTF-8-Daten jedoch auch im Zeichensatz latin1 gespeichert werden.

Das Problem, auf das Sie stoßen können, wenn Sie latin1 zum Speichern von UTF-8-Daten verwenden, besteht darin, dass ein auf Chinesisch basierender Abruf möglicherweise nicht 100 % genau ist (Lao Ye hat persönlich einfach den regulären chinesischen vollständigen Abruf getestet und). es war überhaupt kein Problem, das heißt, ein allgemeiner chinesischer Vergleich ist kein Problem).

Die Methode zur Verwendung des Zeichensatzes latin1 zum Speichern von UTF-8-Daten lautet: Der Zeichensatz auf der Webseite (Benutzerseite) ist UTF-8, und das Back-End-Programm verwendet UTF-8 auch zur Verarbeitung , aber „character_set_client“, „character_set_connection“, „character_set_results“, „character_set_database“ und „character_set_server“ sind alle latin1, und die Zeichensätze von Datentabellen und -feldern sind ebenfalls latin1. Oder die Datentabelle verwendet latin1, führen Sie einfach SET NAMES LATIN1 nach jeder Verbindung aus.

　[Referenz]: Ein kurzer Vortrag über den MySQL-Zeichensatz.

　3. Die physische Länge der InnoDB-Tabellenzeilendatensätze überschreitet nicht 8 KB

[Sicht von Old Yes] Die Standarddatenseite von InnoDB beträgt 16 KB die Eigenschaften von B+Tree, a Auf der Datenseite müssen mindestens 2 Datensätze gespeichert werden. Wenn die tatsächliche Speicherlänge 8 KB überschreitet (insbesondere TEXT/BLOB-Spalten), kommt es daher bei großen Spalten (großen Spalten) zu einem „Seitenüberlaufspeicher“, ähnlich der „Zeilenmigration“ in ORACLE.

Wenn Sie daher große Spalten (insbesondere TEXT/BLOB-Typen) verwenden und häufig lesen und schreiben müssen, ist es am besten, diese Spalten in Untertabellen aufzuteilen und sie nicht zusammen mit der Haupttabelle zu speichern. Wenn es nicht zu häufig vorkommt, sollten Sie erwägen, es in der Haupttabelle zu belassen.

Wenn die Option innodb_page_size auf 8 KB geändert wird, wird natürlich empfohlen, die physische Länge des Zeilendatensatzes 4 KB nicht zu überschreiten.

　[Referenz]: [MySQL-Optimierungsfall] ​​Serie – Optimierung der Speichereffizienz von BLOB-Spalten in InnoDB-Tabellen.

　4. Ob Partitionstabellen verwendet werden sollen

[Sicht von Old Yes] In einigen Szenarien kann die Verwendung von Partitionstabellen offensichtlich die Leistung oder den Betriebs- und Wartungskomfort verbessern , es wird weiterhin empfohlen, partitionierte Tabellen zu verwenden.

Lao Ye verwendet beispielsweise die TokuDB-Engine in der Datenbank von zabbix und verwendet Partitionstabellen basierend auf der Zeitdimension. Dies hat den Vorteil, dass die tägliche Anwendung von Zabbix nicht beeinträchtigt wird und es für Administratoren bequem ist, frühere Daten routinemäßig zu löschen. Sie müssen nur die entsprechende Partition löschen, ohne dass eine sehr langsame Ausführung erforderlich ist DELETE, das sich auf die Gesamtleistung auswirkt.

　[Referenz]: Zabbix-Datenbank nach TokuDB migrieren.

　5. Ob gespeicherte Prozeduren und Trigger verwendet werden sollen

　[Lao Yes Standpunkt] In einigen geeigneten Szenarien ist die Verwendung gespeicherter Prozeduren und Trigger kein Problem.

Früher haben wir Speicher verwendet, um die Verarbeitung der Spielgeschäftslogik abzuschließen. Die Leistung stellt kein Problem dar. Sobald sich die Anforderungen ändern, müssen wir nur die gespeicherte Prozedur ändern, und die Änderungskosten sind sehr gering. Wir verwenden auch Trigger, um eine häufig aktualisierte Tabelle zu verwalten. Alle Änderungen an dieser Tabelle aktualisieren einige Felder synchron in einer anderen Tabelle (ähnlich der verschleierten Implementierung materialisierter Ansichten), und es gibt keine Leistungsprobleme.

Betrachten Sie die gespeicherten Prozeduren und Trigger von MySQL nicht als Geißel. Wenn Sie sie gut nutzen, wird es keine Probleme geben, es ist noch nicht zu spät, sie zu optimieren. Darüber hinaus verfügt MySQL nicht über materialisierte Ansichten. Verwenden Sie daher so wenig Ansichten wie möglich.

　 6. Wählen Sie den richtigen Typ

[Sicht des alten Ye] Zusätzlich zu den allgemeinen Vorschlägen gibt es noch einige andere Punkte:

6.1 . Verwenden Sie INT UNSIGNED zum Speichern der IPV4-Adresse und verwenden Sie INET_ATON() und INET_NTOA() zur Konvertierung. Es besteht grundsätzlich keine Notwendigkeit, CHAR(15) zum Speichern zu verwenden.

6.2. Der interne Speichermechanismus von ENUM verwendet TINYINT oder SMALLINT. Die Leistung ist überhaupt nicht schlecht. Geben Sie Daten an.

6.3. Was die früher verbreiteten „gesunden Menschenverstand“ betrifft, wird empfohlen, TIMESTAMP anstelle von DATETIME zu verwenden. Tatsächlich wird ab 5.6 empfohlen, DATETIME zum Speichern von Datum und Uhrzeit Vorrang einzuräumen, da der verfügbare Bereich größer als TIMESTAMP ist und der physische Speicher nur 1 Byte größer als TIMESTAMP ist, sodass der Gesamtleistungsverlust nicht groß ist .

6.4. In allen Felddefinitionen werden standardmäßig NOT NULL-Einschränkungen hinzugefügt, es sei denn, es muss NULL sein (aber ich kann mir kein Szenario vorstellen, in dem NULL-Werte in der Datenbank gespeichert werden müssen, was durch dargestellt werden kann 0). Wenn Sie eine COUNT()-Statistik für dieses Feld durchführen, sind die statistischen Ergebnisse genauer (die Werte mit NULL-Werten werden von COUNT nicht gezählt), oder wenn Sie eine WHERE-Spalte IS NULL abrufen, können die Ergebnisse auch schnell zurückgegeben werden.

6.5. Versuchen Sie, nicht direkt * auszuwählen, um alle Felder zu lesen, insbesondere wenn die Tabelle große TEXT-/BLOB-Spalten enthält. Es besteht möglicherweise keine Notwendigkeit, diese Spalten zu lesen, aber weil ich faul war und SELECT * schrieb, wurde der Speicherpufferpool durch diese „Junk“-Daten ausgewaschen, und die heißen Daten, die wirklich gepuffert werden mussten, wurden ausgewaschen.

　8. Über den Index

[Sicht des alten Yes] Zusätzlich zu allgemeinen Vorschlägen gibt es mehrere wichtige Punkte:

8.1, Für Spalten mit Längenzeichenfolgen ist es am besten, einen Präfixindex anstelle eines vollständigen Spaltenindex zu erstellen (z. B. ALTER TABLE t1 ADD INDEX(user(20))), was jedoch die Indexnutzung effektiv verbessern kann ist, dass es beim Sortieren dieser Spalte nach dem Präfixindex nicht verwendet werden muss. Die Länge des Präfixindex kann anhand der Feldstatistiken bestimmt werden und ist im Allgemeinen etwas größer als die durchschnittliche Länge.

8.2. Verwenden Sie regelmäßig das Tool pt-duplicate-key-checker, um doppelte Indizes zu überprüfen und zu löschen. Wenn beispielsweise der Index idx1(a, b) bereits den Index idx2(a) abdeckt, kann der Index idx2 gelöscht werden.

8.3. Wenn ein Mehrfeld-Joint-Index vorhanden ist, muss die Feldreihenfolge der Filterbedingungen in WHERE nicht mit dem Index übereinstimmen, sie muss jedoch konsistent sein, wenn eine Sortierung und Gruppierung erfolgt.

Wenn beispielsweise ein gemeinsamer Index idx1 (a, b, c) vorhanden ist, kann das folgende SQL den Index vollständig nutzen :

SELECT ... WHERE b = ? AND c = ? AND a = ?;  --注意到，WHERE中字段顺序并没有和索引字段顺序一致
SELECT ... WHERE b = ? AND a = ? AND c = ?;
SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;
SELECT ... WHERE a = ? AND b = ? ORDER BY c;
SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;
SELECT ... WHERE a = ? ORDER BY b, c;
SELECT ... ORDER BY a, b, c;  -- 可利用联合索引完成排序

Und Das folgende SQL kann dann nur einen Teil des Index verwenden:

SELECT ... WHERE b = ? AND a = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a IN (?, ?) AND b = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a = ? AND c = ?;   -- 只能用到 (a) 部分
SELECT ... WHERE a = ? AND b IN (?, ?);    -- 只能用到 (a, b) 部分
SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?;   -- 只能用到 (a) 部分，注意BETWEEN和IN的区别
SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?;    -- 只能用到 (a, b) 部分

Das folgende SQL verwendet diesen Index überhaupt nicht :

SELECT ... WHERE b = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... ORDER BY b;
SELECT ... ORDER BY b, a;

Wie aus den obigen Beispielen ersichtlich ist, ist die „verwirrende Irreführung des gesunden Menschenverstands“, die in der Vergangenheit betonte, dass die Reihenfolge der WHERE-Bedingungsfelder mit der Indexreihenfolge übereinstimmen muss, bevor der Index verwendet werden muss, nicht erforderlich unbedingt befolgt werden.

Darüber hinaus ist der vom Abfrageoptimierer angegebene Index oder Ausführungsplan möglicherweise nicht optimal. Sie können den optimalen Index manuell angeben oder die Option „optimierer_switch“ auf Sitzungsebene ändern, um einige Faktoren auszuschalten, die zu einer Verschlechterung führen Ergebnisse (zum Beispiel ist die Indexzusammenführung normalerweise eine gute Sache, es wurde jedoch auch festgestellt, dass sie nach der Verwendung der Indexzusammenführung schlechter ist. In diesem Fall muss die Angabe eines Index oder der Indexzusammenführungsfunktion erzwungen werden kann vorübergehend ausgeschaltet werden).

　9. Andere

　9.1 Auch wenn es sich um eine bedingte Filterung basierend auf dem Index handelt, wenn der Optimierer erkennt, dass die Gesamtmenge der zu scannenden Daten 30 überschreitet % (es scheint, als ob ORACLE 20 % beträgt, MySQL derzeit 30 %, vielleicht wird es in Zukunft angepasst), es wird den Ausführungsplan direkt in einen vollständigen Tabellenscan ändern und den Index nicht mehr verwenden.

9.2. Beim Zusammenführen mehrerer Tabellen sollte die Tabelle mit der größten Filterbarkeit (nicht unbedingt die kleinste Datenmenge, aber diejenige mit der größten Filterbarkeit, nachdem nur die WHERE-Bedingung hinzugefügt wurde) als treibende Tabelle ausgewählt werden. Darüber hinaus muss bei einer Sortierung nach JOIN das Sortierfeld zur Treibertabelle gehören, damit der Index der Treibertabelle zum Abschließen der Sortierung verwendet werden kann.

9.3. In den meisten Fällen ist der Sortierindex normalerweise höher. Wenn Sie also „Filesort verwenden“ im Ausführungsplan sehen, erstellen Sie zuerst einen Sortierindex.

9.4. Verwenden Sie pt-query-digest, um das Protokoll langsamer Abfragen regelmäßig zu analysieren, und kombinieren Sie es mit Box Anemometer, um ein System zur Analyse und Optimierung langsamer Abfrageprotokolle aufzubauen.

　[Referenz]: [MySQL FAQ]-Reihe – Welche Informationen in EXPLAIN-Ergebnissen Anlass zur Sorge geben sollten.

Hinweis: Sofern nicht anders angegeben, gelten die oben genannten Spezifikationen für MySQL 5.6 und frühere Versionen. In den Versionen 5.7 und höher kann es zu einigen Änderungen kommen und einzelne Spezifikationsempfehlungen müssen entsprechend angepasst werden.

Das Obige ist meine Meinung zu den MySQL-Entwicklungsspezifikationen. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn).