MySQL-Paging-Abfragemethode für Millionen von Datenmengen und ihre Optimierungsvorschläge

Die SQL-Optimierung von Datenbanken ist ein häufiges Problem. Was sind gute Optimierungsvorschläge, wenn Sie mit Paging-Abfragen für Millionen von Datenmengen konfrontiert werden? Nachfolgend sind einige häufig verwendete Methoden zu Ihrer Information und zum Lernen aufgeführt!

Methode 1: Direkte Verwendung der von der Datenbank bereitgestellten SQL-Anweisung

• Anweisungsstil: In MySQL können Sie die folgende Methode verwenden: SELECT * FROM Tabellenname LIMIT M,N
• Anwendbare Szenarien: Geeignet für Situationen mit kleinen Datenmengen (Hunderte oder Tausende von Tupeln)
• Grund/Nachteil: Der vollständige Tabellenscan ist sehr langsam und einige Datenbankergebnissätze sind instabil (z. B. werden einmal 1, 2, 3 und einmal 2, 1, 3 zurückgegeben). Die Beschränkung besteht darin, N Ausgaben von der M-Position des Ergebnissatzes zu übernehmen und den Rest zu verwerfen.
  

Methode 2: Erstellen Sie einen Primärschlüssel oder einen eindeutigen Index und verwenden Sie den Index (unter der Annahme von 10 Einträgen pro Seite).

• Anweisungsstil: In MySQL kann die folgende Methode verwendet werden: SELECT * FROM table name WHERE id_pk > (pageNum*10) LIMIT M
• Anwendbare Szenarien: Geeignet für Situationen mit großen Datenmengen (Zehntausende Tupel)
• Grund: Das Index-Scannen wird sehr schnell sein. Einige Freunde haben darauf hingewiesen, dass es Fälle geben wird, in denen Daten fehlen, weil die Daten nicht nach pk_id sortiert sind. Es kann nur Methode 3 verwendet werden.
  

Methode 3: Neusortierung basierend auf dem Index

• Anweisungsstil: In MySQL kann die folgende Methode verwendet werden: SELECT * FROM table name WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
• Anwendbare Szenarien: Anwendbar auf Situationen mit großen Datenmengen (Zehntausende von Tupeln). Am besten ist es, wenn das Spaltenobjekt nach ORDER BY der Primärschlüssel oder eindeutig ist, sodass die ORDER BY-Operation mithilfe des Index eliminiert werden kann, der Ergebnissatz jedoch stabil ist (zur Bedeutung von Stabilität siehe Methode 1).
• Grund: Der Index-Scan ist sehr schnell. Allerdings hat die MySQL-Sortierung nur ASC und kein DESC (DESC ist Fake und wird in Zukunft ein echtes DESC sein, ich freue mich darauf ...).
  

Methode 4: Verwenden Sie die Vorbereitung basierend auf dem Index

Das erste Fragezeichen steht für Seitennummer und das zweite? Gibt die Anzahl der Tupel pro Seite an

• Anweisungsstil: In MySQL kann die folgende Methode verwendet werden: PREPARE stmt_name FROM SELECT * FROM table name WHERE id_pk > (? * ?) ORDER BY id_pk ASC LIMIT M
• Anwendbares Szenario: Große Datenmenge
• Grund: Das Index-Scannen ist sehr schnell. Vorbereitungsanweisungen sind schneller als allgemeine Abfrageanweisungen.
  

Methode 5: Mithilfe von MySQL zur Unterstützung von ORDER-Operationen können Sie mithilfe von Indizes einige Tupel schnell finden und vollständige Tabellenscans vermeiden.

Beispiel: Lesen Sie die Tupel von Zeile 1000 bis 1019 (pk ist der Primärschlüssel/eindeutige Schlüssel).

SELECT * FROM Ihre_Tabelle WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

Methode 6: Verwenden Sie Unterabfrage/Join + Index, um das Tupel schnell zu lokalisieren, und lesen Sie dann das Tupel.

Beispiel: (ID ist der Primärschlüssel/eindeutige Schlüssel, blaue Schrift ist eine Variable)

Beispiel mit Unterabfrage:

Wählen Sie * aus Ihrer_Tabelle, wobei ID <=
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc
LIMIT $Seitengröße

Beispiel für die Verwendung der Verbindung:

Wählen Sie * aus Ihrer_Tabelle als t1
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2
WO t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

MySQL verwendet Limit Paging für große Datenmengen. Mit zunehmender Seitenzahl nimmt die Abfrageeffizienz ab.

Testexperiment

1. Verwenden Sie direkt die Paging-Anweisungen „Limit Start“ und „Count“. Dies ist auch die Methode, die in meinem Programm verwendet wird:

select * ab Produktlimit Start, Anzahl

Wenn die Startseite klein ist, gibt es bei der Abfrage kein Leistungsproblem. Schauen wir uns die Ausführungszeit an, wenn die Seitennummerierung bei 10, 100, 1000 und 10000 (20 Datensätze pro Seite) beginnt.

wie folgt:

Wählen Sie * aus Produktlimit 10, 20 0,016 Sekunden. Wählen Sie * aus Produktlimit 100, 20 0,016 Sekunden. Wählen Sie * aus Produktlimit 1000, 20 0,047 Sekunden. Wählen Sie * aus Produktlimit 10000, 20 0,094 Sekunden.

Wir haben gesehen, dass mit zunehmender Anzahl der Startdatensätze auch die Zeit zunimmt. Dies zeigt, dass das Paging-Anweisungslimit eng mit der Startseitennummer zusammenhängt. Ändern wir also den Startdatensatz auf 40 W (was ungefähr der Hälfte der Datensätze entspricht).

select * from product limit 400000, 20 3.229 Sekunden

Schauen wir uns den Zeitpunkt an, als wir die letzte Seite mit Aufzeichnungen entnommen haben.

select * from product limit 866613, 20 37.44 Sekunden

Für eine Seite mit der höchsten Seitenzahl wie dieser ist diese Zeit offensichtlich unerträglich.

Daraus können wir auch zwei Dinge schließen:

1. Die Abfragezeit der Limit-Anweisung 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.
   

2. Leistungsoptimierungsmethode für das Problem des begrenzten Paging

Verwenden Sie den überdeckenden Index der Tabelle, um Paging-Abfragen zu beschleunigen

Wir alle wissen, dass die Abfrage sehr schnell ist, wenn die Anweisung, die eine Indexabfrage verwendet, nur diese Indexspalte (den abdeckenden Index) enthält.

Da die Indexsuche über einen optimierten Algorithmus verfügt und sich die Daten im Abfrageindex befinden, muss nicht nach der relevanten Datenadresse gesucht werden, was viel Zeit spart. Darüber hinaus verfügt MySQL auch über einen zugehörigen Index-Cache. Die Wirkung ist besser, wenn der Cache bei hoher Parallelität verwendet wird.

In unserem Beispiel wissen wir, dass das ID-Feld der Primärschlüssel ist, daher ist der Standard-Primärschlüsselindex eingeschlossen. Sehen wir uns nun die Leistung von Abfragen an, die die Vorteile der Abdeckungsindizes nutzen.

Diesmal fragen wir die Daten der letzten Seite ab (mithilfe eines überdeckenden Index, der nur die Spalte „id“ enthält) und zwar wie folgt:

wähle ID aus Produktlimit 866613, 20 0,2 Sekunden

Im Vergleich zu den 37,44 Sekunden, die zum Abfragen aller Spalten benötigt werden, ist die Geschwindigkeit um mehr als das Hundertfache erhöht.

Wenn wir auch alle Spalten abfragen möchten, gibt es zwei Möglichkeiten: Eine ist das Formular „id>=“, die andere ist die Verwendung von „join“, siehe die tatsächliche Situation:

SELECT * FROM Produkt WHERE ID > =(Wählen Sie die ID aus dem Produktlimit 866613, 1) Limit 20

Die Abfragezeit beträgt 0,2 Sekunden!

Eine andere Art zu schreiben

SELECT * FROM Produkt a JOIN (Wählen Sie die ID aus dem Produktlimit 866613, 20) b ON a.ID = b.id

Auch die Abfragezeit ist sehr kurz!

3. Methode zur zusammengesetzten Indexoptimierung

Wie hoch kann die MySql-Leistung sein? Die MySql-Datenbank ist definitiv für Experten auf DBA-Ebene geeignet. Im Allgemeinen können Sie auf beliebige Weise ein kleines System mit 10.000 Nachrichtenartikeln schreiben und das xx-Framework verwenden, um eine schnelle Entwicklung zu erreichen. Aber kann die Leistung immer noch so hoch sein, wenn die Datenmenge 100.000, 1 Million oder 10 Millionen erreicht? Ein kleiner Fehler kann dazu führen, dass das gesamte System neu geschrieben werden muss oder sogar nicht mehr normal funktioniert! Okay, kein Unsinn mehr.

Reden wir über Fakten, sehen Sie sich Beispiele an:

Die Datentabelle „collect“ (ID, Titel, Info, VType) weist nur diese vier Felder auf, von denen der Titel eine feste Länge verwendet, Info Text verwendet, die ID graduell ist, VType Tinyint ist und VType ein Index ist. Dies ist ein einfaches Modell eines grundlegenden Nachrichtensystems. Jetzt füllen Sie es mit Daten, füllen Sie es mit 100.000 Nachrichtenartikeln. Die endgültige Sammlung enthält 100.000 Datensätze und die Datenbanktabelle belegt 1,6 GB Festplattenspeicher.

OK, sehen Sie sich die folgende SQL-Anweisung an:

Wählen Sie ID, Titel aus dem Sammellimit 1000,10;

Sehr schnell; grundsätzlich sind 0,01 Sekunden OK, siehe folgendes

Wählen Sie ID, Titel aus dem Sammellimit 90000,10;

Beginnen Sie mit der Paginierung ab 90.000 Datensätzen. Was ist das Ergebnis?

In 8–9 Sekunden erledigt, mein Gott, was ist schiefgelaufen? Wenn Sie diese Daten optimieren möchten, finden Sie die Antwort online. Sehen Sie sich die folgende Anweisung an:

Wählen Sie die ID aus der Sammelreihenfolge nach ID-Limit 90000,10;

Sehr schnell, 0,04 Sekunden sind OK. Warum? Da der ID-Primärschlüssel als Index verwendet wird, ist es natürlich schnell. Die Online-Änderungsmethode ist:

Wählen Sie ID, Titel aus Sammlung, wobei ID > = (Wählen Sie ID aus Sammlung, sortiert nach ID-Limit 90000,1) Limit 10;

Dies ist das Ergebnis der Verwendung der ID als Index. Aber wenn das Problem nur ein klein wenig komplizierter ist, ist es vorbei. Schauen Sie sich die folgende Aussage an

Wählen Sie die ID aus „Collect“, wobei vtype=1 ist, sortieren Sie nach ID-Limit 90000,10;

Sehr langsam, hat 8–9 Sekunden gedauert!

An diesem Punkt, glaube ich, geht es vielen Leuten so wie mir und sie sind überfordert! Ist vtype indiziert? Wie könnte es langsam sein? Es ist gut, dass vtype indiziert ist.

Wählen Sie die ID aus „Collect“, wobei vtype=1, Limit 1000,10 ist;

Dies ist sehr schnell, grundsätzlich 0,05 Sekunden. Wird es jedoch um das 90-fache erhöht, beträgt die Geschwindigkeit ausgehend von 90.000 0,05 x 90 = 4,5 Sekunden. Und die Testergebnisse liegen bei 8–9 Sekunden genau.

Von hier aus schlugen einige Leute die Idee vor, die Tabellen aufzuteilen, was der gleichen Idee wie im dis #cuz-Forum entspricht. Die Idee ist folgende:

Erstellen Sie eine Indextabelle: t (ID, Titel, V-Typ) und legen Sie eine feste Länge fest. Führen Sie anschließend eine Paginierung durch, paginieren Sie die Ergebnisse und suchen Sie anschließend in „Collect“ nach Informationen. Ist es machbar? Sie werden es nach dem Experimentieren wissen.

100.000 Datensätze werden in t(id, title, vtype) gespeichert und die Datentabellengröße beträgt etwa 20 MB. verwenden

Wählen Sie die ID aus „Collect“, wobei vtype=1, Limit 1000,10 ist;

Es wird bald soweit sein. Grundsätzlich kann es in 0,1–0,2 Sekunden abgeschlossen werden. Warum passiert das? Ich vermute, das liegt daran, dass zu viele Daten gesammelt wurden, sodass das Paging lange dauert. Die Begrenzung hängt vollständig von der Größe der Datentabelle ab. Tatsächlich handelt es sich dabei immer noch um einen vollständigen Tabellenscan, der jedoch schnell ist, da die Datenmenge gering ist, nur 100.000. OK, machen wir ein verrücktes Experiment, fügen 1 Million Datensätze hinzu und testen die Leistung. Nach dem 10-fachen Hinzufügen der Daten erreichte die t-Tabelle sofort mehr als 200 M und hatte eine feste Länge. Es handelt sich immer noch um dieselbe Abfrageanweisung und die Ausführung dauert 0,1 bis 0,2 Sekunden! Ist die Leistung der Untertabelle in Ordnung?

falsch! Denn unser Limit liegt immer noch bei 90.000, also geht es schnell. Geben Sie einen großen, beginnend bei 900.000

Wähle ID aus t, wobei vtype=1 ist, sortiere nach ID-Limit 900000,10;

Schauen Sie sich das Ergebnis an, die Zeit beträgt 1-2 Sekunden! Warum ?

Selbst nach der Aufteilung der Tabelle dauert es so lange, das ist sehr deprimierend! Manche Leute sagen, dass eine feste Länge die Leistung des Limits verbessert. Zuerst dachte ich auch, dass MySQL, da die Länge eines Datensatzes fest ist, in der Lage sein sollte, die Position 900.000 zu berechnen, oder? Aber wir haben die Intelligenz von MySQL überschätzt. Es ist keine Geschäftsdatenbank. Tatsachen haben bewiesen, dass feste und nicht feste Längen wenig Einfluss auf das Limit haben? Kein Wunder, dass manche Leute sagen, dass Discuz sehr langsam wird, wenn es 1 Million Datensätze erreicht. Ich glaube, das stimmt. Das hängt mit dem Datenbankdesign zusammen!

Ist es möglich, dass MySQL die 1-Millionen-Grenze nicht überschreiten kann? ? ? Sind 1 Million Seiten wirklich die Grenze?

Die Antwort lautet: NEIN. Der Grund, warum es 1 Million nicht überschreiten kann, ist, dass Sie nicht wissen, wie man MySQL entwirft. Hier ist die Methode ohne Tabelle. Machen wir einen verrückten Test! So paginieren Sie schnell eine Tabelle mit 1 Million Datensätzen und einer 10G-Datenbank!

OK, unser Test kehrt zur Sammeltabelle zurück und das Fazit des Tests lautet:

Bei 300.000 Daten ist die Verwendung der Tabellenpartitionierungsmethode sinnvoll. Wenn die Datenmenge jedoch 300.000 übersteigt, ist die Geschwindigkeit so langsam, dass Sie es nicht mehr ertragen können! Natürlich wäre es absolut perfekt, wenn Sie eine geteilte Tabelle + meine Methode verwenden würden. Aber nach Anwendung meiner Methode kann das Problem perfekt gelöst werden, ohne die Tabelle zu teilen!

Die Antwort lautet: zusammengesetzter Index! Als ich einmal einen MySQL-Index entwarf, entdeckte ich zufällig, dass der Indexname beliebig gewählt werden kann und mehrere Felder ausgewählt werden können. Was soll das bringen?

Start

Wählen Sie die ID aus der Sammelreihenfolge nach ID-Limit 90000,10;

Dies ist so schnell, weil der Index verwendet wird. Wenn jedoch die Where-Klausel hinzugefügt wird, wird der Index nicht verwendet. Ich habe einen Index wie search(vtype,id) hinzugefügt, nur um es auszuprobieren.

Dann testen

Wählen Sie die ID aus „Collect“, wobei vtype=1, Limit 90000,10 ist;

Sehr schnell! In 0,04 Sekunden abgeschlossen!

Erneute Prüfung:

Wählen Sie ID, Titel aus „Collect“, wobei vtype=1, Limit 90000,10 ist;

Schade, dass es 8-9 Sekunden gedauert hat und der Suchindex nicht verwendet wurde!

Nochmals testen: search(id,vtype), trotzdem noch ID auswählen, dauert leider 0,5 Sekunden.

Zusammenfassend lässt sich sagen: Wenn Sie eine Where-Bedingung haben und den Index zum Verwenden des Limits verwenden möchten, müssen Sie einen Index entwerfen, zuerst „Where“ und dann den für das Limit verwendeten Primärschlüssel eingeben und Sie können nur den Primärschlüssel auswählen!

Perfekte Lösung für das Paging-Problem. Wenn die ID schnell zurückgegeben werden kann, besteht Hoffnung auf eine Optimierung des Limits. Nach dieser Logik sollte ein Millionenlimit in 0,0x Sekunden geteilt werden. Es scheint, dass die Optimierung und Indizierung von MySQL-Anweisungen sehr wichtig sind!

Oben finden Sie Einzelheiten zur MySQL-Paging-Abfragemethode für Millionen von Datenmengen und Optimierungsvorschläge. Weitere Informationen zur MySQL-Paging-Abfrage und ihrer Optimierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!