Prinzip der MySQL-Paging-Analyse und Effizienzverbesserung

Prinzip der MySQL-Paging-Analyse und Effizienzverbesserung

Auf der PERCONA PERFORMANCE CONFERENCE 2009 präsentierten mehrere Ingenieure von Yahoo einen Vortrag mit dem Titel „Effiziente Paginierung mit MySQL“, der viele Höhepunkte enthielt. Dieser Artikel ist eine Erweiterung des ursprünglichen Berichts.

Schauen wir uns zunächst die Grundprinzipien des Paging an:

MySQL> erklären SELECT * FROM Nachricht ORDER BY id DESC LIMIT 10000, 20\G
***************** 1. Reihe **************
ID: 1
select_type: EINFACH
Tabelle: Nachricht
Typ: Index
mögliche Schlüssel: NULL
Schlüssel: PRIMARY
Schlüssellänge: 4
Ref: NULL
Reihen: 10020
Extra:
1 Zeile im Satz (0,00 Sek.)

Limit 10000,20 bedeutet, 10020 Zeilen zu scannen, die die Bedingungen erfüllen, die ersten 10000 Zeilen zu verwerfen und die letzten 20 Zeilen zurückzugeben. Hier liegt das Problem. Wenn Limit 100000,100 verwendet wird, müssen 100100 Zeilen gescannt werden. In einer stark parallelen Anwendung muss jede Abfrage mehr als 100.000 Zeilen scannen, und die Leistung wird definitiv stark reduziert. Der Artikel erwähnt auch, dass die Leistungsgrenze n kein Problem darstellt, da nur n Zeilen gescannt werden.

Der Artikel erwähnt einen „Hinweis“-Ansatz, um einige „Hinweise“ zum Umblättern bereitzustellen. Beispiel: SELECT * FROM message ORDER BY id DESC, Paginierung in absteigender Reihenfolge nach ID, 20 Elemente pro Seite, die aktuelle Seite ist die 10. Seite, die größte ID des aktuellen Seiteneintrags ist 9527 und die kleinste ist 9500. Wenn wir nur Sprünge wie „vorherige Seite“ und „nächste Seite“ bereitstellen (kein Sprung zu Seite N), kann die SQL-Anweisung bei der Verarbeitung der „vorherigen Seite“ lauten:

SELECT * FROM Nachricht WHERE ID > 9527 ORDER BY ID ASC LIMIT 20;

Bei der Verarbeitung der „nächsten Seite“ kann die SQL-Anweisung lauten:

SELECT * FROM Nachricht WHERE ID < 9500 ORDER BY ID DESC LIMIT 20;

Unabhängig davon, wie viele Seiten umgeblättert werden, werden für jede Abfrage nur 20 Zeilen gescannt.

Der Nachteil besteht darin, dass nur Links in Form von „Vorherige Seite“ und „Nächste Seite“ bereitgestellt werden können, unser Produktmanager jedoch Links wie „<Vorherige Seite 1 2 3 4 5 6 7 8 9 Nächste Seite>“ sehr mag. Was sollen wir tun?

Wenn LIMIT m,n unvermeidbar ist, besteht die einzige Möglichkeit zur Optimierung der Effizienz darin, m so klein wie möglich zu machen. Wir erweitern den vorherigen „Hinweis“-Ansatz und verwenden weiterhin SELECT * FROM message ORDER BY id DESC, wobei die Seiten in absteigender Reihenfolge nach ID mit 20 Elementen pro Seite paginiert werden. Die aktuelle Seite ist die 10. Seite, und die größte ID des aktuellen Seiteneintrags ist 9527 und die kleinste ist 9500. Wenn Sie beispielsweise zu Seite 8 springen möchten, kann die SQL-Anweisung, die ich gesehen habe, wie folgt geschrieben werden:

AUSWÄHLEN * AUS Nachricht WO ID > 9527 ORDER BY ID ASC LIMIT 20,20;

Springe zu Seite 13:

SELECT * FROM Nachricht WHERE ID < 9500 ORDER BY ID DESC LIMIT 40,20;

Das Prinzip ist immer noch dasselbe. Notieren Sie die Maximal- und Minimalwerte der aktuellen Seiten-ID und berechnen Sie den relativen Versatz zwischen der Sprungseite und der aktuellen Seite. Da die Seiten nahe beieinander liegen, ist der Versatz nicht groß, sodass der m-Wert relativ klein ist, was die Anzahl der gescannten Zeilen erheblich reduziert. Tatsächlich ist der traditionelle Grenzwert m,n, der relative Versatz, immer die erste Seite. In diesem Fall nimmt die Effizienz ab, wenn Sie nach hinten blättern. Bei der oben angegebenen Methode gibt es dieses Problem nicht.

Achten Sie auf ASC und DESC in der SQL-Anweisung. Wenn das Ergebnis per ASC abgerufen wird, denken Sie daran, es bei der Anzeige zu invertieren.

Es wurde in einer Tabelle mit insgesamt 600.000 Datenpunkten getestet und der Effekt ist sehr offensichtlich.

Vielen Dank fürs Lesen, ich hoffe, es kann Ihnen helfen, danke für Ihre Unterstützung dieser Site!