So verbessern Sie die MySQL Limit-Abfrageleistung

Bei MySQL-Datenbankoperationen hoffen wir immer, dass wir vermeiden können, dass die Datenbank-Engine bei der Ausführung einiger Abfragen einen vollständigen Tabellenscan durchführt, da ein vollständiger Tabellenscan lange dauert und die meisten Scans für den Client bedeutungslos sind. Tatsächlich können wir das Schlüsselwort „Limit“ verwenden, um vollständige Tabellenscans zu vermeiden und die Effizienz zu verbessern.

Es gibt eine Tabelle in MySQL 5.0.x mit mehreren zehn Millionen Datensätzen, und jetzt müssen wir Hunderte Millionen Datensätze auslesen. Häufig verwendete Methoden, Zyklus der Reihe nach:

Wählen Sie * aus meiner Tabelle, wobei index_col = xxx Grenzoffset, Grenz;

Erfahrung: Wenn kein Blob-/Textfeld vorhanden ist und der einzeilige Datensatz relativ klein ist, können Sie das Limit auf einen größeren Wert setzen, um den Vorgang zu beschleunigen.

Problem: Die ersten zehntausend Datensätze werden sehr schnell gelesen, die Geschwindigkeit nimmt jedoch linear ab. Gleichzeitig liegt die CPU-Auslastung des MySQL-Servers bei 99 %, was inakzeptabel ist.

Rufen Sie explain select * from mytable where index_col = xxx limit offset , limit; Anzeigetyp = ALL“

Im MySQL-Optimierungsdokument steht die Erklärung zu "Alle"

Für jede Kombination von Zeilen aus den vorherigen Tabellen wird ein vollständiger Tabellenscan durchgeführt. Dies ist normalerweise nicht gut, wenn die Tabelle die erste Tabelle ist, die nicht als const markiert ist, und in allen anderen Fällen normalerweise sehr schlecht. Normalerweise können Sie ALL vermeiden, indem Sie Indizes hinzufügen, die das Abrufen von Zeilen aus der Tabelle basierend auf konstanten Werten oder Spaltenwerten aus früheren Tabellen ermöglichen.

Es scheint, dass MySQL für all eine schwerfälligere Methode verwendet. Warum also nicht stattdessen range Bereichsmethode verwenden? Da id erhöht wird, ist es auch einfach, das SQL zu ändern.

wähle * aus meiner Tabelle, wobei ID > Offset und ID < Offset + Limit und index_col = xxx

Explain zeigt Typ = Bereich und die Ergebnisgeschwindigkeit ist ideal und gibt Ergebnisse Dutzende Mal schneller zurück.

Limit-Syntax:

SELECT * FROM Tabelle LIMIT [Offset,] Zeilen | Zeilen OFFSET Offset

Mit der LIMIT-Klausel können Sie eine SELECT-Anweisung zwingen, eine bestimmte Anzahl von Datensätzen zurückzugeben. LIMIT akzeptiert ein oder zwei numerische Argumente. Das Argument muss eine ganzzahlige Konstante sein.

Wenn zwei Argumente angegeben werden, gibt das erste Argument den Offset der ersten zurückgegebenen Zeile und das zweite Argument die maximale Anzahl der zurückgegebenen Zeilen an. Die anfängliche Datensatzzeile hat einen Offset von 0 (nicht 1).

Aus Kompatibilitätsgründen mit PostgreSQL unterstützt MySQL auch die Syntax: LIMIT # OFFSET #.

mysql> SELECT * FROM table LIMIT 5,10; // Zeilen 6-15 abrufen
//Um alle Zeilen von einem bestimmten Offset bis zum Ende des Datensatzes abzurufen, können Sie den zweiten Parameter als -1 angeben
mysql> SELECT * FROM table LIMIT 95,-1; // Zeilen 96-letzte abrufen
//Wenn nur ein Parameter angegeben wird, gibt er die maximale Anzahl der zurückzugebenden Zeilen an. Mit anderen Worten, LIMIT n entspricht LIMIT 0,n
mysql> SELECT * FROM table LIMIT 5; // Rufe die ersten 5 Zeilen ab

Die MySQL-Begrenzung erleichtert das Paging erheblich, bei großen Datenmengen sinkt die Leistung jedoch erheblich. Wenn man dieselben zehn Datenstücke betrachtet, liegen die folgenden beiden Sätze nicht in der gleichen Größenordnung.

wähle * aus Tabellenlimit 10000,10
Wählen Sie * aus der Tabelle Limit 0,10

In diesem Artikel wird das Limit nicht direkt verwendet. Stattdessen wird zuerst die Offset-ID abgerufen und dann die Grenzgröße direkt zum Abrufen der Daten verwendet. Seinen Daten zufolge ist es offensichtlich besser, als das Limit direkt zu verwenden.

Hier verwende ich Daten zum Testen in zwei Situationen.

1. Wenn der Versatz relativ klein ist:

Wählen Sie * aus Tabellenlimit 10,10 
// Mehrmals ausführen, die Zeit zwischen 0,0004 und 0,0005 halten. Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10 
// Mehrmals ausführen, die Zeit wird zwischen 0,0005 und 0,0006 gehalten, hauptsächlich bei 0,0006

Fazit: Wenn der Offset klein ist, ist es besser, das Limit direkt zu verwenden. Dies ist offensichtlich der Grund für die Unterabfrage.

2. Wenn der Versatz groß ist:

wähle * aus Tabellenlimit 10000,10 
// Mehrmals ausführen, die Zeit bleibt bei etwa 0,0187. Select * From table Where vid >=(Select vid From table Order By vid limit 10000,1) limit 10
//Nach mehreren Durchläufen bleibt die Zeit bei etwa 0,0061, was nur 1/3 der vorherigen Zeit ist. Es ist zu erwarten, dass Letzteres umso besser ist, je größer der Versatz ist.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM. Wenn Sie mehr darüber erfahren möchten, schauen Sie sich bitte die folgenden Links an