Optimierung der Leistung von Paging-Abfragen für MySQL mit mehreren zehn Millionen Daten

Wenn die Datenmenge in MySQL groß ist, wird die Seitenbegrenzung verwendet. Mit zunehmender Seitenzahl nimmt die Abfrageeffizienz ab.

Experiment

1. Verwenden Sie direkt die Paging-Anweisung „Limit Start, Count“:

select * from order limit start, count

Wenn die Startseite klein ist, treten bei der Abfrage keine Leistungsprobleme auf. Betrachten wir die Ausführungszeit der Seitenumbrüche von 10, 100, 1000 und 10000 (20 Datensätze pro Seite) wie folgt:

Wählen Sie * ab Bestelllimit 10, 20 0,016 Sekunden. Wählen Sie * ab Bestelllimit 100, 20 0,016 Sekunden. Wählen Sie * ab Bestelllimit 1000, 20 0,047 Sekunden. Wählen Sie * ab Bestelllimit 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 und sehen wir weiter.

select * from order limit 400000, 20 3.229秒

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

select * from order limit 800000, 20 37.44秒

Offensichtlich ist diese Zeit unerträglich.

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

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.

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 an, wie die Abfrage mit dem überdeckenden Index funktioniert:

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

select id from order limit 800000, 20 0.2秒

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 order WHERE ID > =(select id from order limit 800000, 1) limit 20

Die Abfragezeit beträgt 0,2 Sekunden, was ein qualitativer Sprung ist, haha

Eine andere Art zu schreiben

SELECT * FROM order a JOIN (select id from order limit 800000, 20) b ON a.ID = b.id

Auch die Abfragezeit ist sehr kurz