Detaillierte Erläuterung der MySQL-Datenbank mit zig Millionen Datenabfragen und -speicherungen

Datenverarbeitungslösung auf Millionenebene

Entwurf der Datenspeicherstruktur

Tabellenfelddesign

• Das Tabellenfeld ist nicht null. Da Nullwerte schwer abzufragen sind und zusätzlichen Indexplatz beanspruchen, wird die Standardzahl 0 empfohlen.
• Versuchen Sie, für Datenstatustypfelder wie Status, Typ usw. keine negativen Zahlen wie -1 zu definieren. Da Sie auf diese Weise UNSIGNED hinzufügen können, wird die Wertkapazität verdoppelt.
• Verwenden Sie wenn möglich TINYINT, SMALLINT usw. anstelle von INT und versuchen Sie, BIGINT nicht zu verwenden, da es weniger Platz beansprucht.
• Felder vom Typ „String“ benötigen mehr Platz als Felder vom Typ „Numeric“. Versuchen Sie daher, anstelle von Zeichenfolgen Ganzzahlen zu verwenden. In vielen Szenarien können stattdessen Ganzzahlen über die Codierungslogik verwendet werden.
• Legen Sie die Zeichenfolgenlänge nicht willkürlich fest. Halten Sie sie so kurz wie möglich, während Sie gleichzeitig die Geschäftsanforderungen erfüllen.
• Verwenden Sie eine Ganzzahl zum Speichern der IP.
• Eine einzelne Tabelle sollte nicht zu viele Felder haben, empfohlen werden weniger als 20 Felder.
• Reservieren Sie vorhersehbare Felder, denn je größer die Datenmenge, desto aufwändiger ist die Anpassung der Datenstruktur.

Indexgestaltung

• Strategie zur Optimierung von Index, Raum und Zeit. Entwerfen Sie den Index grundsätzlich entsprechend den Geschäftsanforderungen. Er ist ausreichend, um Millionen von Datenmengen zu bewältigen. Gewöhnen Sie sich an die Verwendung von „Explain“. Sie können auch folgende Seite besuchen: „Explain“, um mehr über das Schreiben von SQL zu erfahren und es praktischer zu gestalten.
• Allgemein gilt: Je mehr Indizes, desto besser. Indizes verringern die Leistung beim Schreiben von Daten.
• Die Indexfeldlänge sollte so kurz wie möglich sein, wodurch viel Indexplatz gespart werden kann.
• Durch das Abbrechen von Fremdschlüsseln kann das Programm diese einschränken, was zu einer besseren Leistung führt.
• Der zusammengesetzte Index entspricht der Regel für die äußerste linke Spalte. Die Reihenfolge der Indizes sollte den Abfragebedingungen entsprechen. Versuchen Sie, unnötige einspaltige Indizes zu entfernen.
• Felder mit weniger verteilten Werten (weniger eindeutigen Werten) sind für die Indizierung nicht geeignet. Beispielsweise macht es wenig Sinn, ein Feld wie Geschlecht zu indizieren, das nur zwei oder drei Werte hat.
• Es wird empfohlen, den Feldern, die sortiert werden müssen, Indizes hinzuzufügen, da Indizes sortiert sind und die Abfrageleistung verbessern können.
• Zeichenfolgenfelder verwenden Präfixindizes anstelle von Vollfeldindizes, wodurch der Indexplatz erheblich reduziert werden kann.

Optimierung von Abfrageanweisungen

• Versuchen Sie, kurze Abfragen anstelle komplexer Inline-Abfragen zu verwenden.
• Verwenden Sie select * nicht für Abfragen. Versuchen Sie, Felder mit Indizes abzufragen, um eine Rückkehr zur Tabelle zu vermeiden.
• Versuchen Sie, die Anzahl der Abfragen mithilfe des Limits zu begrenzen.
• Die Abfragefelder sollten so weit wie möglich im Index platziert werden, insbesondere im zusammengesetzten Index, und der Übereinstimmung mit dem ganz linken Präfix sollte mehr Aufmerksamkeit gewidmet werden.
• Das Aufteilen großer Lösch-/Einfügevorgänge sperrt die Tabelle und beeinträchtigt andere Geschäftsvorgänge. Andererseits gibt es bei MySQL auch eine Beschränkung der Länge von SQL-Anweisungen.
• Es wird nicht empfohlen, MySQL-Funktionen und -Berechnungen zu verwenden. Diese können zunächst von Programmen verarbeitet werden. Aus den oben genannten Punkten können Sie ableiten, dass Sie, wenn das Programm damit umgehen kann, versuchen sollten, den Druck nicht auf die Datenbank zu übertragen. Denn die meisten Leistungsengpässe bei Servern liegen in der Datenbank.
• Anzahl der Abfragen, Leistung: Anzahl(1) = Anzahl(*) > Anzahl(Primärschlüssel) > Anzahl(andere Felder).
• Wenn der Abfrageoperator „between“ verwenden kann, verwenden Sie nicht „in“. Wenn „in“ verwendet werden kann, verwenden Sie „or“ nicht.
• Vermeiden Sie die Verwendung von Operatoren wie != oder <>, IS NULL oder IS NOT NULL, IN, NOT IN usw., da diese Abfragen keine Indizes verwenden können.
• Halten Sie SQL so einfach wie möglich, verwenden Sie weniger Joins und empfehlen Sie nicht mehr als zwei Joins.

Datenverarbeitungslösung auf Zehn-Millionen-Ebene

Entwurf der Datenspeicherstruktur

In dieser Phase haben die Daten selbst einen hohen Wert. Neben der Erfüllung regulärer Geschäftsanforderungen besteht auch Bedarf an Datenanalysen. Zu diesem Zeitpunkt ist die Datenvariabilität nicht hoch und es wird grundsätzlich keine Änderung der ursprünglichen Struktur in Betracht gezogen. Im Allgemeinen wird die Optimierung unter drei Gesichtspunkten betrachtet: Partitionierung, Tabellen-Sharding und Datenbank-Sharding:

Partition:

• Partitionierung ist eine horizontale Unterteilung, bei der die Datenbank eine Tabelle anhand bestimmter Regeln in mehrere kleinere und besser handhabbare Teile zerlegt. Es ist für die Anwendung völlig transparent und hat keinen Einfluss auf die Geschäftslogik der Anwendung, d. h. es ist nicht erforderlich, den Code zu ändern. Daher können mehr Daten gespeichert werden und Abfrage- und Löschvorgänge werden auch partitionsweise unterstützt, wodurch der Optimierungszweck erreicht wird. Wenn Sie eine Partitionierung in Erwägung ziehen, können Sie sich im Voraus darauf vorbereiten, um die folgenden Einschränkungen zu vermeiden:
• Eine Tabelle kann maximal 1024 Partitionen haben (MySQL 5.6 und höher unterstützt 8192 Partitionen). Im tatsächlichen Betrieb ist es jedoch am besten, nicht mehr als 100 Partitionen gleichzeitig zu öffnen, da das Öffnen von Partitionen auch Zeit in Anspruch nimmt.
• Wenn das Partitionsfeld einen Primärschlüssel oder eine eindeutige Indexspalte enthält, müssen alle Primärschlüsselspalten und eindeutigen Indexspalten eingeschlossen werden. Wenn die Tabelle einen Primärschlüssel oder einen eindeutigen Index enthält, muss der Partitionsschlüssel der Primärschlüssel oder der eindeutige Index sein.
• Fremdschlüsseleinschränkungen können in partitionierten Tabellen nicht verwendet werden.
• NULL-Werte machen die Partitionsfilterung ungültig und die Daten werden in der Standardpartition abgelegt. Bitte lassen Sie nicht zu, dass NULL-Werte im Partitionsfeld erscheinen.
• Alle Partitionen müssen dieselbe Speicher-Engine verwenden.

Untertabelle:

Untertabellen werden in horizontale und vertikale Untertabellen unterteilt.

Bei der horizontalen Tabellenpartitionierung wird eine Tabelle in kleinere Tabellen mit derselben Datenstruktur aufgeteilt, z. B. Tabelle1, Tabelle2 usw., um den Lese- und Schreibdruck der Datenbank zu verringern.

Bei der vertikalen Tabellenpartitionierung werden einige Felder getrennt, um eine neue Tabelle zu bilden. Die Datenstrukturen jeder Tabelle sind unterschiedlich, wodurch die Situation des Sperrens der Tabelle bei hoher Parallelität optimiert werden kann.

Wie Sie sich vorstellen können, muss die Logik des Programms geändert werden, wenn Sie die Tabelle teilen möchten. Daher werden Sie im Allgemeinen in den frühen Phasen des Projekts, wenn Sie eine große Datenmenge erwarten, das Teilen der Tabelle in Betracht ziehen. Eine spätere Aufteilung der Tabelle ist nicht zu empfehlen, da die Kosten sehr hoch sind.

Teilbibliothek:

Die Unterdatenbank befindet sich im Allgemeinen im Master-Slave-Modus. Ein Masterknoten eines Datenbankservers wird in mehrere Datenbanken eines oder mehrerer Slaveknoten repliziert. Die Masterdatenbank ist für Schreibvorgänge und die Slavedatenbank für Lesevorgänge verantwortlich. Dadurch werden Optimierungsziele wie Master-Slave-Trennung, hohe Verfügbarkeit und Datensicherung erreicht.

Natürlich weist der Master-Slave-Modus auch einige Mängel auf, z. B. eine Verzögerung der Master-Slave-Synchronisierung und Probleme, die durch zu große Binlog-Dateien verursacht werden. Ich werde hier nicht ins Detail gehen (der Autor kann es nicht mehr lernen).

andere:

Warm- und Kaltzähler isoliert. Wenn historische Daten von wenigen Personen abgefragt und verwendet werden, können sie in eine andere kalte Datenbank verschoben und nur für Abfragen bereitgestellt werden, um die große Datenmenge in der heißen Tabelle zu verringern.

Entwurf des Primärschlüssels in Datenbanktabellen

Für den Entwurf des Primärschlüssels einer Datenbank empfehle ich persönlich eine selbsterhöhende numerische ID mit einem Zeitattribut. (Verteilter Algorithmus zur selbstinkrementellen ID-Generierung)

• Snowflake-Algorithmus
• Baidu Distributed ID Algorithmus
• Verteilter Meituan-ID-Algorithmus

Warum diese Algorithmen verwenden? Dies hängt mit der MySQL-Datenspeicherstruktur zusammen.

Aus geschäftlicher Sicht:

Beim Entwerfen einer Datenbank müssen Sie nicht darüber nachdenken, welches Feld Sie als Primärschlüssel festlegen. Dann sind diese Felder nur theoretisch eindeutig. Wird beispielsweise die Buchnummer als Primärschlüssel verwendet, ist die Buchnummer nur theoretisch eindeutig, in der Praxis können jedoch Duplikate auftreten. Daher ist es besser, eine Auto-Increment-ID, die nicht mit dem Unternehmen in Zusammenhang steht, als Primärschlüssel festzulegen und dann eine eindeutige Einschränkung für die Buchnummer hinzuzufügen.

Technisch gesehen:

1. Wenn die Tabelle einen automatisch inkrementierenden Primärschlüssel verwendet, wird jedes Mal, wenn ein neuer Datensatz eingefügt wird, der Datensatz sequenziell an der nächsten Position des aktuellen Indexknotens hinzugefügt. Wenn eine Seite voll ist, wird automatisch eine neue Seite geöffnet. Im Allgemeinen kann es die Leistung von Abfragen und Einfügungen verbessern.

2. Bei InnoDB speichert der Primärschlüsselindex sowohl den Indexwert als auch die Zeilendaten im Blattknoten, was bedeutet, dass die Datendatei selbst Daten im B+-Baumformat speichert.

3. Wenn kein Primärschlüssel definiert ist, wird ein nicht leerer UNIQUE-Schlüssel als Primärschlüssel verwendet. Wenn kein nicht leerer UNIQUE-Schlüssel vorhanden ist, generiert das System eine 6-Byte-Zeilen-ID als Primärschlüssel. In einem gruppierten Index bilden N Zeilen eine Seite (eine Seite ist normalerweise 16 KB groß). Wenn unregelmäßige Daten eingefügt werden, kommt es zur Aufrechterhaltung des Gleichgewichts des B+-Baums zu häufigen Seitenaufteilungen und Seitenrotationen und die Einfügegeschwindigkeit ist langsamer. Daher sollte der Primärschlüsselwert des gruppierten Indexes ein kontinuierlich ansteigender Wert und kein Zufallswert sein (verwenden Sie keine zufällige Zeichenfolge oder UUID).

4. Versuchen Sie daher, für den Primärschlüssel von InnoDB eine Ganzzahl und eine zunehmende Ganzzahl zu verwenden. Dies ist sowohl bei der Speicherung als auch bei der Abfrage sehr effizient.

Fragen zum MySQL-Vorstellungsgespräch

Lösung zur Optimierung von MySQL-Datenbankabfragen für Millionen von Daten

Je später die Abfrage zur Begrenzung der Paging-Auslagerung erfolgt, desto langsamer ist die Abfrage. Dies führt uns auch zu einer Schlussfolgerung:

1. Die Abfragezeit der Grenzwertanweisung ist proportional zur Position des Startdatensatzes.

2. Die MySQL-Limit-Anweisung ist sehr praktisch, aber nicht für die direkte Verwendung bei Tabellen mit vielen Datensätzen geeignet.

Die Tabelle verwendet InnoDB als Speicher-Engine, id als automatisch inkrementierten Primärschlüssel und den Standardprimärschlüsselindex

Wählen Sie ID aus Testlimit 9000000,100;

Derzeit gibt es zwei Optimierungslösungen: die Verwendung der ID als Abfragebedingung, die Verwendung einer Unterabfrage und die Verwendung eines Join.

1. id>= (Unterabfrage) Formularimplementierung

wähle * aus Test, wobei ID >= (wähle ID aus Testlimit 9000000,1)Limit 0,100

Verwenden Sie das Beitrittsformular;

Wählen Sie * aus Test a JOIN (Wählen Sie ID aus Test LIMIT 9000000,100) b ON a.id = b.id

Die für die Verwendung dieser beiden Arten optimierter Abfragen benötigte Zeit ist relativ ähnlich. Tatsächlich verwenden beide das gleiche Prinzip, sodass die Auswirkungen ähnlich sind. Ich persönlich empfehle jedoch die Verwendung von Join und die Minimierung der Verwendung von Unterabfragen. Hinweis: Derzeit liegt die Abfrage auf der Zehnmillionenebene. Wenn sie auf die Millionenebene erhöht wird, ist die Geschwindigkeit schneller.

Wählen Sie * aus Test a JOIN (Wählen Sie ID aus Test LIMIT 1000000,100) b ON a.id = b.id

Welche MySQL-Speicher-Engines haben Sie verwendet?

Was sind ihre Besonderheiten und Unterschiede?

Diese Frage wird bei Vorstellungsgesprächen mit erfahrenen Entwicklern häufig gestellt. Tatsächlich begegnen wir dem häufig in unserer täglichen Entwicklung. Es gibt so viele MySQL-Speicher-Engines, aber die, die wir am häufigsten verwenden, sind InnoDB und MyISAM. Wenn der Interviewer also fragt, über welche Speicher-Engines MySQL verfügt, müssen Sie nur die beiden am häufigsten verwendeten nennen.

Was sind also ihre Merkmale und Unterschiede?

MyISAM: Der Standardtabellentyp, der auf dem traditionellen ISAM-Typ basiert. ISAM ist die Abkürzung für Indexed Sequential Access Method, eine Standardmethode zum Speichern von Datensätzen und Dateien. Es ist nicht transaktionssicher und unterstützt keine Fremdschlüssel. Wenn eine große Anzahl von Auswahlen durchgeführt wird, ist insert MyISAM besser geeignet.

InnoDB: Eine Engine, die Transaktionssicherheit unterstützt. Ihr wichtigstes Feature ist die Unterstützung von Fremdschlüsseln, Zeilensperren und Transaktionen. Bei einer großen Anzahl von Aktualisierungen und Einfügungen wird die Verwendung von InnoDB empfohlen, insbesondere bei mehreren gleichzeitigen Vorgängen und hohen QPS-Werten. Hinweis: In Versionen vor MySQL 5.5 ist MyISAM die Standardsuchmaschine. In Versionen nach MySQL 5.5 wird die Standardsuchmaschine auf InnoDB geändert.

Unterschiede zwischen MyISAM und InnoDB

1. InnoDB unterstützt Transaktionen, MyISAM jedoch nicht. Bei InnoDB wird jede SQL-Anweisung standardmäßig in eine Transaktion gekapselt und automatisch festgeschrieben, was sich auf die Geschwindigkeit auswirkt. Daher ist es am besten, mehrere SQL-Anweisungen zwischen Begin und Commit einzufügen, um eine Transaktion zu bilden.

2. InnoDB unterstützt Fremdschlüssel, MyISAM nicht.

3. InnoDB ist ein Clustered-Index und verwendet B+Tree als Indexstruktur. Die Datendatei ist an den (Primärschlüssel-)Index gebunden (die Tabellendatendatei selbst ist eine durch B+Tree organisierte Indexstruktur). Es muss einen Primärschlüssel geben, und die Effizienz des Primärschlüsselindex ist sehr hoch. MyISAM ist ein nicht gruppierter Index und verwendet ebenfalls B+Tree als Indexstruktur. Index- und Datendateien sind getrennt und der Index speichert den Zeiger auf die Datendatei. Der Primärschlüsselindex und die Sekundärindizes sind unabhängig.

4. InnoDB speichert nicht die spezifische Anzahl von Zeilen in der Tabelle, und beim Ausführen von „select count(*) from table“ muss die gesamte Tabelle gescannt werden. MyISAM verwendet eine Variable, um die Anzahl der Zeilen in der gesamten Tabelle zu speichern. Wenn Sie die obige Anweisung ausführen, müssen Sie nur die Variable lesen, was sehr schnell ist.

5. InnoDB unterstützt keine Volltextindizierung, MyISAM hingegen schon. MyISAM hat eine höhere Abfrageeffizienz. InnoDB ab 5.7 unterstützt Volltextindizierung.

6. InnoDB unterstützt Sperren auf Tabellen- und Zeilenebene (Standard), während MyISAM Sperren auf Tabellenebene unterstützt. ;

7. InnoDB-Tabellen müssen einen Primärschlüssel haben (wenn der Benutzer keinen angibt, wird selbst einer gesucht oder generiert). Myisam hat dagegen keinen.

8.Innodb-Speicherdateien sind frm und ibd, während Myisam frm, MYD und MYI ist.

9.Innodb: frm ist die Tabellendefinitionsdatei, ibd ist die Datendatei.

10.Myisam: frm ist die Tabellendefinitionsdatei, myd ist die Datendatei und myi ist die Indexdatei.

Optimierung komplexer MySQL-Abfrageanweisungen

Bei komplexer SQL-Optimierung liegt es meistens an Multi-Table-Assoziationen, die eine große Anzahl komplexer SQL-Anweisungen verursachen. Wie also sollten wir diese Art von SQL optimieren? Es gibt tatsächlich Routinen zur Optimierung, und wir müssen nur diesen Routinen folgen. Komplexe SQL-Optimierungslösung:

1. Verwenden Sie das Schlüsselwort EXPLAIN, um SQL zu überprüfen. EXPLAIN kann Ihnen dabei helfen, die Leistungsengpässe Ihrer Abfrageanweisungen oder Tabellenstrukturen zu analysieren. Die Abfrageergebnisse von EXPLAIN zeigen Ihnen auch, wie Ihr Index-Primärschlüssel verwendet wird, wie Ihre Datentabelle durchsucht und sortiert wird, ob ein vollständiger Tabellenscan durchgeführt wird usw.

2. Versuchen Sie, Indexfelder für Abfragebedingungen zu verwenden. Wenn eine Tabelle mehrere Bedingungen hat, versuchen Sie, zusammengesetzte Indexabfragen zu verwenden. Achten Sie bei der Verwendung zusammengesetzter Indizes auf die Reihenfolge der Felder.

3. Verwenden Sie möglichst Joins, um mehrere Tabellen zu verknüpfen und die Verwendung von Unterabfragen zu reduzieren. Wenn die zugehörigen Felder der Tabelle den Primärschlüssel verwenden können, verwenden Sie den Primärschlüssel, d. h. verwenden Sie so oft wie möglich das Indexfeld. Wenn es sich bei dem verknüpften Feld nicht um ein Indexfeld handelt, können Sie je nach Situation die Hinzufügung eines Index in Erwägung ziehen.

4. Versuchen Sie, das Limit für die Paginierung von Batchabfragen zu verwenden, und rufen Sie nicht alles auf einmal ab.

5. Vermeiden Sie unbedingt die Verwendung von „select *“, versuchen Sie, bestimmte erforderliche Felder auszuwählen und reduzieren Sie die Abfrage unnötiger Felder.

6. Versuchen Sie, alles zu konvertieren oder zu vereinen.

7. Versuchen Sie die Verwendung von „ist null“ oder „ist nicht null“ zu vermeiden.

8. Achten Sie auf die Verwendung von „Gefällt mir“. Bei Vorunschärfe und Vollunschärfe wird der Index nicht verwendet.

9. Versuchen Sie, die Verwendung von Funktionen im Abfragefeld nach „Where“ zu minimieren, da Funktionen zu Indexfehlern führen.

10. Vermeiden Sie die Verwendung von „ungleich“ (!=), da hierbei der Index nicht verwendet wird.

11. Verwenden Sie „existiert“ statt „in“ und „nicht existiert“ statt „nicht in“, was effizienter ist.

12. Vermeiden Sie die Verwendung der HAVING-Klausel. HAVING filtert den Ergebnissatz erst, nachdem alle Datensätze abgerufen wurden, was Sortieren, Summieren und andere Vorgänge erfordert. Wenn Sie die Anzahl der Datensätze durch die WHERE-Klausel begrenzen können, können Sie diesen Aufwand reduzieren.

13. Verwenden Sie niemals ORDER BY RAND()

Oben finden Sie eine ausführliche Erläuterung der Abfrage und Speicherung von zig Millionen Daten in der MySQL-Datenbank. Weitere Informationen zur Abfrage und Speicherung von zig Millionen Daten in der MySQL-Datenbank finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!