Tutorial zur MySQL-Optimierung: Große Paging-Abfrage

Hintergrund

Grundsätzlich werden Sie, solange Sie Backend-Entwicklung betreiben, mit der Anforderung bzw. Funktion des Paging in Berührung kommen. Grundsätzlich verwendet jeder MySQL LIMIT, um dies zu handhaben, und das Projekt, für das ich derzeit verantwortlich bin, ist auch auf diese Weise geschrieben. Sobald jedoch die Datenmenge zunimmt, wird die Effizienz von LIMIT äußerst gering. In diesem Artikel geht es um die Optimierung der LIMIT-Klausel.

LIMIT-Optimierung

Viele Geschäftsszenarien erfordern die Paging-Funktion, die grundsätzlich mit LIMIT implementiert wird.

Erstellen Sie eine Tabelle und fügen Sie 2 Millionen Datensätze ein:

# Erstellen Sie eine neue t5-Tabelle CREATE TABLE `t5` (
 `id` int NICHT NULL AUTO_INCREMENT,
 `name` varchar(50) NICHT NULL,
 `text` varchar(100) NICHT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `ix_name` (`Name`),
 SCHLÜSSEL `ix_test` (`text`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

# Erstellen Sie eine gespeicherte Prozedur zum Einfügen von 2 Millionen Daten CREATE PROCEDURE t5_insert_200w()
BEGINNEN
 Erklären Sie, dass ich INT;
 Setzen Sie i = 1000000;
 WÄHREND i<=3000000 DO
 INSERT INTO t5(`Name`,Text) VALUES('god-jiang666',concat('Text', i));
 Setze i = i + 1;
 ENDE WÄHREND;
ENDE;

# Rufen Sie die gespeicherte Prozedur auf, um 2 Millionen Daten einzufügen. call t5_insert_200w();

Wenn Seitenumbrüche relativ selten sind, verursacht LIMIT keine Leistungsprobleme.

Aber was, wenn der Benutzer die letzte Seite finden muss?

Normalerweise müssen wir sicherstellen, dass alle Seiten normal springen können, da wir kein umgekehrtes SQL wie „order by xxx desc“ verwenden, um die folgenden Seiten abzufragen, sondern die Vorwärtsreihenfolge verwenden, um Paging-Abfragen durchzuführen:

Wählen Sie * aus T5, sortieren Sie nach Textlimit 100000, 10;

Wenn diese Art der Paginierung von SQL-Abfragen verwendet wird, ist der Aufwand zum Extrahieren dieser 10 Datenzeilen aus 2 Millionen Daten sehr hoch. Es ist erforderlich, zuerst die ersten 1.000.010 Datensätze zu sortieren und zu finden und dann die ersten 1.000.000 Datensätze zu verwerfen. Der Betrieb meines MacBook Pro dauerte 5,578 Sekunden.

Schauen wir uns als nächstes den Ausführungsplan der obigen SQL-Anweisung an:

Erklären Sie „Select * from t5“, sortieren Sie nach Textlimit 1000000, 10;

Aus dem Ausführungsplan können wir ersehen, dass MySQL bei großem Paging kein Index-Scanning durchführt, obwohl ich dem Textfeld einen Index hinzugefügt habe.

Warum ist das so?

Zurück zum MySQL-Index (II) „So entwerfen Sie einen Index“ . Dort wird erwähnt, dass der Abfrageoptimierer der MySQL-Datenbank einen kostenbasierten Ansatz verfolgt und die Abfragekostenschätzung auf den CPU-Kosten und den IO-Kosten basiert.

Wenn MySQL bei der Schätzung der Abfragekosten davon ausgeht, dass ein vollständiger Tabellenscan effizienter ist als ein Indexscan, wird der Index aufgegeben und direkt ein vollständiger Tabellenscan durchgeführt.

Aus diesem Grund führt MySQL bei SQL-Abfragen mit großen Seiten trotz der Indizierung des Felds dennoch einen vollständigen Tabellenscan durch.

Anschließend verwenden wir die obige SQL-Abfrage weiter, um meine Vermutung zu überprüfen:

Erklären Sie „Select * from t5“, sortieren Sie nach Textlimit 7774, 10;

Erklären Sie „Select * from t5“, sortieren Sie nach Textlimit 7775, 10;

Die obigen Experimente wurden alle auf meinem MBP ausgeführt. Am kritischen Punkt von 7774 verwendete MySQL die Abfrageoptimierungsmethoden Index-Scan bzw. Full Table Scan.

Daher kann davon ausgegangen werden, dass MySQL anhand seines eigenen Kostenabfrageoptimierers bestimmt, ob der Index verwendet werden soll.

Da wir nicht manuell in den Kernalgorithmus des MySQL-Abfrageoptimierers eingreifen können, sollte sich unsere Optimierungsstrategie darauf konzentrieren, wie die Paging-Funktion am optimalen kritischen Paging-Punkt gehalten werden kann.

Optimierungsmethode

1. Verwenden Sie den Überdeckungsindex

Wenn eine SQL-Anweisung das Abfrageergebnis direkt über den Index abrufen kann, ohne für die Abfrage zur Tabelle zurückzukehren, wird dieser Index als überdeckender Index bezeichnet.

Verwenden Sie das Schlüsselwort „explain“ in der MySQL-Datenbank, um den Ausführungsplan anzuzeigen. Wenn in der zusätzlichen Spalte „Using index“ angezeigt wird, bedeutet dies, dass diese SQL-Anweisung einen überdeckenden Index verwendet.

Vergleichen wir, um wie viel die Leistung durch die Verwendung eines abdeckenden Index verbessert werden kann.

# Es wird kein überdeckender Index verwendet. Select * from t5 Order by Text Limit 1000000, 10;

Diese Abfrage dauerte 3,690 Sekunden. Sehen wir uns an, wie sehr sich die Leistung durch die abdeckende Indexoptimierung verbessern lässt.

# Verwenden Sie den abdeckenden Index, wählen Sie ID „Text“ aus t5, sortieren Sie nach Textlimit 1000000, 10;

Aus dem obigen Vergleich geht hervor, dass bei der sehr großen Paging-Abfrage nach Verwendung des abdeckenden Indexes 0,201 Sekunden vergingen, während es ohne Verwendung des abdeckenden Indexes 3,690 Sekunden dauerte, was mehr als 18-mal schneller ist. In der tatsächlichen Entwicklung ist dies eine erhebliche Leistungsoptimierung. (Diese Daten werden durch Ausführen auf meinem MBP abgerufen.)

2. Unterabfrageoptimierung

In der tatsächlichen Entwicklung sind SELECT-Operationen zum Abfragen einer oder zweier Spalten sehr selten, sodass der Anwendungsbereich des oben genannten abdeckenden Index relativ begrenzt ist.

Daher können wir die Leistung verbessern, indem wir die Paging-SQL-Anweisung in eine Unterabfrage umschreiben.

Wählen Sie * aus t5, wobei ID>=(Wählen Sie ID aus t5, Sortierung nach Textlimit 1000000, 1) Limit 10;

Tatsächlich ist die Effizienzsteigerung, die durch die Verwendung dieser Methode erzielt wird, grundsätzlich die gleiche, wie die, die durch die Verwendung des oben genannten Abdeckungsindex erzielt wird.

Allerdings hat diese Optimierungsmethode auch Einschränkungen:

• Diese Schreibmethode erfordert, dass die Primärschlüssel-ID kontinuierlich sein muss
• Die Where-Klausel erlaubt es nicht, andere Bedingungen hinzuzufügen

3. Verzögerte Assoziation

Ähnlich wie beim obigen Unterabfrageansatz können wir JOIN verwenden, um zuerst den Paging-Vorgang für die Indexspalte abzuschließen und dann zur Tabelle zurückzukehren, um die erforderlichen Spalten abzurufen.

wähle a.* aus t5, einen inneren Join (wähle ID aus t5, sortiere nach Textlimit 1000000, 10) b auf a.id=b.id;

Aus dem Experiment können wir schließen, dass nach dem Umschreiben mit JOIN die beiden oben genannten Einschränkungen behoben wurden und die Effizienz der SQL-Ausführung nicht verloren gegangen ist.

4. Notieren Sie den Ort, an dem die letzte Abfrage endete

Anders als bei den oben verwendeten Methoden besteht die Optimierungsidee zum Aufzeichnen der letzten Endposition darin, eine Variable zum Aufzeichnen der Position der letzten Daten zu verwenden und beim nächsten Paging direkt von der Position dieser Variablen aus mit dem Scannen zu beginnen. Dadurch wird vermieden, dass MySQL eine große Datenmenge scannt und dann verwirft.

wähle * aus t5, wobei ID>=1000000, Limit 10;

Basierend auf den obigen Experimenten lässt sich leicht der Schluss ziehen, dass SQL aufgrund der Verwendung von Primärschlüsselindizes für Paging-Vorgänge die schnellste Leistung aufweist.

Zusammenfassen

• Einführung in die Gründe für die schlechte Leistung bei großen Paging-Abfragen und Vorstellung mehrerer Optimierungsideen
• Die Optimierungsidee bei sehr großem Paging besteht darin, die Paging-SQL-Ausführung möglichst im besten Leistungsbereich durchzuführen, ohne einen vollständigen Tabellenscan auszulösen.
• Ich hoffe, dass die oben genannten Informationen Ihnen helfen können, Umwege auf dem Weg zu MySQL zu vermeiden~~~

Verweise

• Kapitel 6 von "MySQL Performance Optimization" - Abfrageoptimierungsleistung
• Die Kunst des Datenbankabfrageoptimierers

Dies ist das Ende dieses Artikels über das MySQL-Optimierungs-Tutorial für große Paging-Abfragen. Weitere relevante Inhalte zu großen MySQL-Paging-Abfragen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder durchsuchen Sie die folgenden verwandten Artikel weiter. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!