Einige Praktiken der eigenständigen MySQL-Datenbankoptimierung_MySQL

Bei der Datenbankoptimierung gibt es viel zu besprechen. Sie kann je nach unterstützter Datenmenge in zwei Phasen unterteilt werden: eigenständige Datenbank, Unterdatenbank und Untertabelle. Erstere kann im Allgemeinen Daten innerhalb von 500 W unterstützen oder 10G. Wenn dieser Wert überschritten wird, muss eine Partitionierung der Datenbanktabelle in Betracht gezogen werden. Darüber hinaus beginnen Interviews mit großen Unternehmen häufig mit Fragen zu einer Einzelmaschinendatenbank und fragen dann Schritt für Schritt nach Unterdatenbanken und Tabellen, wobei in der Mitte viele Fragen zur Datenbankoptimierung dazwischen liegen. In diesem Artikel wird versucht, einige Praktiken der eigenständigen Datenbankoptimierung zu beschreiben. Die Datenbank basiert auf MySQL. Wenn es etwas Unvernünftiges gibt, korrigieren Sie mich bitte.

1. Optimierung der Tabellenstruktur

Wenn Sie mit der Erstellung einer Anwendung beginnen, wirkt sich das Tabellenstrukturdesign der Datenbank häufig später auf die Leistung der Anwendung aus, insbesondere wenn die Anzahl der Benutzer steigt. Daher ist die Optimierung der Tabellenstruktur ein sehr wichtiger Schritt.

1.1, Zeichensatz

Versuchen Sie im Allgemeinen, UTF-8 zu wählen, wenn Sie Daten speichern, obwohl UTF-8 mit verschiedenen Sprachen kompatibel ist. Tatsächlich müssen wir für diesen Speicherplatz keine Abstriche bei der Skalierbarkeit machen . . Wenn Sie später von GBK auf UTF-8 konvertieren möchten, sind die Kosten tatsächlich sehr hoch, und es ist eine Datenmigration erforderlich, und der Speicherplatz kann durch die Ausgabe von Geld für die Erweiterung der Festplatte gelöst werden.

1.2. Primärschlüssel

Bei Verwendung von MySQLs Innodb ist das zugrunde liegende Speichermodell von Innodb ein B-Baum, der den Primärschlüssel als Clustered-Index verwendet und die eingefügten Daten als Blattknoten schnell über den Primärschlüssel finden kann Aufzeichnungen beschaffen. Daher müssen Sie beim Entwerfen der Tabelle einen Primärschlüssel hinzufügen, der am besten automatisch erhöht wird. Da der sich automatisch erhöhende Primärschlüssel es ermöglicht, die eingefügten Daten in der Reihenfolge des Primärschlüssels in die Blattknoten des zugrunde liegenden B-Baums einzufügen, ist es bei dieser Art des Einfügens fast nicht erforderlich, andere vorhandene zu verschieben Daten, daher ist die Einfügungseffizienz sehr hoch. Wenn der Primärschlüssel nicht automatisch ansteigt, ist der Wert des Primärschlüssels jedes Mal ungefähr zufällig. Zu diesem Zeitpunkt muss möglicherweise eine große Datenmenge verschoben werden, um die Eigenschaften des B-Baums sicherzustellen, wodurch unnötige Daten hinzugefügt werden Overhead.

1.3, Feld

1.3.1. Die indizierten Felder müssen mit der Nicht-Null-Einschränkung hinzugefügt werden und den Standardwert festlegen

1.3.2. Es wird nicht empfohlen, Float oder Double zum Speichern von Dezimalzahlen zu verwenden. Es wird empfohlen, Dezimalzahlen zu verwenden

1.3.3. Es wird nicht empfohlen, Text/Blob zum Speichern großer Datenmengen zu verwenden, da das Lesen und Schreiben großer Textmengen einen relativ großen E/A-Aufwand verursacht und den MySQL-Cache belegt Aus Gründen des Durchsatzes wird empfohlen, große Textdaten in einem speziellen Dateispeichersystem zu speichern. Beispielsweise können Blog-Beiträge in Dateien gespeichert werden, und MySQL speichert nur die relative Adresse der Datei.

1.3.4. Es wird empfohlen, dass die Länge des Varchar-Typs 8 KB nicht überschreitet.

1.3.5. Es wird empfohlen, Datetime anstelle von timestamp als Zeittyp zu verwenden. Obwohl Datetime nur 4 Bytes belegt, darf letzterer nicht leer sein und ist abhängig von Zeitzonen.

1.3.6. Es wird empfohlen, der Tabelle zwei Felder, gmt_create und gmt_modified, hinzuzufügen, um den Änderungszeitpunkt der Datenerstellung aufzuzeichnen. Der Grund für die Einrichtung dieser beiden Felder besteht darin, die Fehlerbehebung zu erleichtern.

1.4. Indexerstellung

1.4.1. Da Sie das Geschäft nicht verstehen, versuchen Sie, nicht blind Indizes hinzuzufügen, sondern nur einige Felder, die definitiv verwendet werden, mit gewöhnlichen Indizes zu versehen.

1.4.2. Die Länge der Erstellung eines innodb-Einzelspaltenindex sollte 767 Bytes nicht überschreiten. Wenn sie länger ist, werden die ersten 255 Bytes als Präfixindex verwendet

1.4.3. Die Länge jedes Spaltenindex sollte beim Erstellen eines kombinierten Innodb-Index 767 Bytes und die Gesamtlänge 3072 Bytes nicht überschreiten

2. SQL-Optimierung

Im Allgemeinen gibt es nur wenige SQL-Typen: einfaches Hinzufügen, Löschen, Ändern, Abfragen, Paging-Abfragen, Bereichsabfragen, Fuzzy-Suche, Verbindungen mit mehreren Tabellen

2.1. Grundabfrage

Allgemeine Abfragen müssen indiziert werden. Wenn dieses Feld aufgrund des Geschäftsszenarios nicht verwendet werden kann, wird empfohlen, die Abfrage zu ändern Wenn das Abfrageaufrufvolumen groß ist, beispielsweise 10 W, muss ein neuer Index hinzugefügt werden. Wenn es nicht groß ist, beispielsweise 100, wird es jeden Tag aufgerufen Ist. Darüber hinaus sollte select * so wenig wie möglich verwendet werden. Fügen Sie einfach alle Felder hinzu, die in der SQL-Anweisung verwendet werden. Überprüfen Sie keine unnötigen Felder, da dadurch E/A und Speicherplatz verschwendet werden.

2.2. Effizientes Paging

Die Essenz von limit m,n besteht darin, zuerst limit m n auszuführen und dann n Zeilen aus der m-ten Zeile zu übernehmen. Auf diese Weise wird m größer und die Leistung geringer. Zum Beispiel

wählen Sie * aus A-Limit 100000,10. Die Leistung dieser SQL-Anweisung ist sehr schlecht. Es wird empfohlen, sie auf die folgende Version zu ändern:

Wählen Sie ID, Name und Alter aus A aus, wobei ID >=(ID aus A auswählen, Limit 100000,1) Limit 10

2.3. Bereichsabfrage

Bereichsabfragen umfassen zwischen, größer als, kleiner als und in. Die Anzahl der In-Abfragen in MySQL ist begrenzt. Wenn die Anzahl klein ist, kann eine Indexabfrage verwendet werden. Wenn die Anzahl groß ist, handelt es sich um einen vollständigen Tabellenscan. Was „zwischen“, „größer als“, „kleiner als“ usw. betrifft, werden diese Abfragen nicht indiziert. Versuchen Sie daher, sie nach den indizierten Abfragebedingungen einzufügen.

2.4, Fuzzy-Abfrage ähnlich

Die Verwendung von Anweisungen wie %name% entspricht nicht einem vollständigen Tabellenscan. Wenn die Datenmenge klein ist, stellt dies kein großes Problem dar Wenn die Datenmenge groß wird, wird empfohlen, diese Fuzzy-Suche durch eine Suchmaschine zu ersetzen.

2.5. Mehrtischverbindung

Sowohl Unterabfragen als auch Joins können zum Abrufen von Daten aus mehreren Tabellen verwendet werden, die Leistung der Unterabfrage ist jedoch schlecht. Es wird empfohlen, die Unterabfrage auf Join zu ändern. Für den MySQL-Join wird der Nested-Loop-Join-Algorithmus verwendet. Dies bedeutet, dass die nächste Tabelle über die Ergebnismenge der vorherigen Tabellenabfrage abgefragt wird. Die Ergebnismenge der vorherigen Tabelle umfasst beispielsweise 100 Daten und die letztere Tabelle hat 10 W Anschließend müssen Sie den 100*10W-Datensatz filtern, um den endgültigen Ergebnissatz zu erhalten. Versuchen Sie daher, eine Tabelle mit einer kleinen Ergebnismenge zu verwenden, um eine große Tabelle zu verknüpfen, und erstellen Sie gleichzeitig einen Index für das Verknüpfungsfeld. Wenn der Index nicht erstellt werden kann, müssen Sie eine ausreichend große Verknüpfungspuffergröße festlegen. Wenn keine der oben genannten Techniken das durch Join verursachte Problem der Leistungseinbußen lösen kann, beenden Sie einfach die Verwendung von Join und teilen Sie eine Join-Abfrage in zwei einfache Abfragen auf. Darüber hinaus sollten Verbindungen mit mehreren Tabellen nicht mehr als drei Tabellen umfassen. Im Allgemeinen ist die Leistung von mehr als drei Tabellen sehr schlecht. Es wird empfohlen, die SQL aufzuteilen.

3. Optimierung des Datenbankverbindungspools

Der Datenbankverbindungspool ist im Wesentlichen ein Cache, der eine hohe Parallelität verhindert. Bei der Optimierung des Datenbankverbindungspools geht es hauptsächlich um die Optimierung von Parametern. Im Allgemeinen verwenden wir den DBCP-Verbindungspool und seine spezifischen Parameter sind wie folgt:

3.1 initialSize

Die anfängliche Anzahl von Verbindungen. Die Initiale bezieht sich hier auf das erste Mal, wenn getConnection abgerufen wird, nicht auf den Start der Anwendung. Der Anfangswert kann auf den historischen Durchschnitt der Parallelität eingestellt werden

3.2, minIdle

Mindestanzahl der beizubehaltenden inaktiven Verbindungen. DBCP startet im Hintergrund einen Thread, um inaktive Verbindungen wiederzuverwenden. Wenn der Thread inaktive Verbindungen wiederverwendet, behält er die Anzahl der minimalen inaktiven Verbindungen bei. Im Allgemeinen auf 5 eingestellt. Wenn die Parallelität sehr gering ist, kann sie auf 1 eingestellt werden.

3.3, maxIdle

Die maximale Anzahl reservierter Leerlaufverbindungen wird entsprechend der geschäftlichen Parallelitätsspitze festgelegt. Wenn die Parallelitätsspitze beispielsweise 20 beträgt, werden diese Verbindungen nach Ablauf der Spitze nicht sofort wiederverwendet. Wenn nach kurzer Zeit eine weitere Spitze auftritt, kann der Verbindungspool diese inaktiven Verbindungen wiederverwenden, ohne dass sie häufig erstellt werden müssen und enge Verbindungen.

3.4, maxActive

Die maximale Anzahl aktiver Verbindungen wird entsprechend dem akzeptablen Extremwert der Parallelität festgelegt. Der akzeptable Extremwert für die Parallelität einzelner Maschinen beträgt beispielsweise 100. Wenn maxActive dann auf 100 gesetzt ist, können nur 100 Anfragen gleichzeitig bearbeitet werden, und überschüssige Anfragen werden nach Ablauf der maximalen Wartezeit abgebrochen. Dieser Wert muss festgelegt werden, um böswillige Parallelitätsangriffe zu verhindern und die Datenbank zu schützen.

3.5, maxWarten

Es wird empfohlen, die maximale Wartezeit für den Verbindungsaufbau kürzer festzulegen, z. B. 3 Sekunden, damit die Anforderung schnell fehlschlagen kann, da der Thread nicht freigegeben werden kann, wenn eine Anforderung auf den Verbindungsaufbau wartet Die Parallelität einer einzelnen Maschine ist begrenzt, z. B. die im Internet empfohlenen 60 Sekunden. Solange es mehr solcher Anfragen gibt, sind weniger Threads verfügbar für die Anwendung, und der Dienst wird nicht mehr verfügbar sein.

3.6, minEvictableIdleTimeMillis

Die Zeit, die die Verbindung im Leerlauf bleibt, ohne wiederhergestellt zu werden, beträgt standardmäßig 30 Minuten.

3.7, validationQuery

Die SQL-Anweisung, mit der überprüft wird, ob die Verbindung gültig ist, ist normalerweise eine einfache SQL. Es wird empfohlen,

festzulegen

3.8, testOnBorrow

Die Verbindung wird bei der Beantragung einer Verbindung erkannt. Es wird nicht empfohlen, sie zu aktivieren, da dies die Leistung erheblich beeinträchtigt.

3.9, testOnReturn

Die Verbindung wird beim Wiederherstellen der Verbindung erkannt. Es wird nicht empfohlen, sie einzuschalten, da dies die Leistung ernsthaft beeinträchtigt

3.10, testWhileIdle

Nach dem Einschalten validiert der Thread, der die Verbindung im Hintergrund bereinigt, von Zeit zu Zeit die inaktive Verbindung. Wenn die Verbindung fehlschlägt, wird sie gelöscht, ohne dass die Leistung beeinträchtigt wird

3.11, numTestsPerEvictionRun

stellt die Anzahl der jedes Mal überprüften Links dar. Es wird empfohlen, den Wert auf maxActive zu setzen, damit alle Links jedes Mal effektiv überprüft werden können.

3.12. Anschlussbecken vorheizen

Für den Verbindungspool wird empfohlen, die Anwendung beim Start vorzuwärmen und vor der Bereitstellung eines externen Zugriffs eine einfache SQL-Abfrage durchzuführen, um den Verbindungspool mit der erforderlichen Anzahl von Verbindungen zu füllen.

4. Indexoptimierung

Wenn die Datenmenge auf ein bestimmtes Niveau ansteigt, kann die Leistung nicht mehr durch SQL-Optimierung verbessert werden. Zu diesem Zeitpunkt ist ein großer Schritt erforderlich: die Indizierung. Es gibt drei Ebenen der Indizierung. Im Allgemeinen reicht es aus, diese drei Ebenen zu beherrschen. Darüber hinaus muss die Selektivität der zu indizierenden Felder berücksichtigt werden.

4.1, Primärindex

Erstellen Sie einen Index für die Bedingung hinter where. Eine einzelne Spalte kann einen normalen Index erstellen, und mehrere Spalten können einen kombinierten Index erstellen. Bei zusammengesetzten Indizes muss das Prinzip des Präfixes ganz links beachtet werden.

4.2, Sekundärindex

Wenn es ein Feld gibt, das nach „Ordnung nach“ oder „Gruppierung nach“ verwendet wird, können Sie erwägen, einen Index für dieses Feld zu erstellen. Da der Index auf natürliche Weise geordnet ist, können Sie die durch „Ordnung nach“ und „Gruppierung nach“ verursachte Sortierung vermeiden. Verbessern Sie somit die Leistung.

4.3, dreistufiger Index

Wenn die beiden oben genannten Methoden nicht ausreichen, fügen Sie den Index zum abgefragten Feld hinzu. Zu diesem Zeitpunkt wird die sogenannte Indexabdeckung gebildet. Dies kann einen E/A-Vorgang reduzieren, da MySQL die Daten abfragt. Überprüfen Sie zuerst den Primärschlüsselindex, dann den normalen Index basierend auf dem Primärschlüsselindex und dann die entsprechenden Datensätze basierend auf dem normalen Index. Wenn sich die von uns benötigten Datensätze im normalen Index befinden, ist der dritte Schritt nicht erforderlich. Natürlich ist diese Art der Indexerstellung ziemlich extrem und nicht für allgemeine Szenarien geeignet.

4.4. Indexselektivität

Versuchen Sie beim Erstellen eines Index, ihn auf hochselektiven Feldern aufzubauen. Was bedeutet hohe Selektivität? Die sogenannte hohe Selektivität bedeutet, dass die über dieses Feld gefundene Datenmenge gering ist. Wenn Sie beispielsweise die Informationen einer Person anhand des Namens überprüfen, ist die gefundene Datenmenge im Allgemeinen sehr gering, während sie bei der Überprüfung nach Geschlecht nur die Hälfte beträgt Daher ist der Name ein sehr selektives Feld und das Geschlecht ein wenig selektives Feld.

5. Archivierung historischer Daten

Wenn die Datenmenge in einem Jahr um 5 Millionen zunimmt, kann der Index derzeit nichts tun. Die allgemeine Idee besteht darin, Unterdatenbanken und Untertabellen zu berücksichtigen. Wenn das Unternehmen nicht explosionsartig wächst, die Datenmenge aber langsam zunimmt, können Sie die komplexen technischen Mittel zum Sharding von Datenbanken und Sharding-Tabellen ignorieren und stattdessen historische Daten archivieren. Wir archivieren historische Daten, deren Lebenszyklus beendet ist, beispielsweise Daten von vor 6 Monaten. Mit der Planungsaufgabe von Quartz können wir die Daten vor 6 Monaten regelmäßig am frühen Morgen überprüfen und sie dann auf dem Remote-HBase-Server speichern. Natürlich müssen wir für den Notfall auch eine Abfrageschnittstelle für historische Daten bereitstellen.

Das Obige sind die Optimierungsinformationen der eigenständigen MySQL-Datenbank. Wir werden in Zukunft weiterhin relevante Informationen hinzufügen. Vielen Dank für Ihre Unterstützung dieser Website!