Warum wirkt sich die Verwendung von Limits in MySQL auf die Leistung aus?

Lassen Sie mich zunächst die MySQL-Version erklären:

mysql> Version auswählen();
+-------------+
| version() |
+-------------+
| 5.7.17 |
+-------------+
1 Zeile im Satz (0,00 Sek.)

Tabellenstruktur:

mysql> Beschreibungstest;
+--------+---------------------+------+-----+---------+----------------+
| Feld | Typ | Null | Schlüssel | Standard | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigniert | NEIN | PRI | NULL | auto_increment |
| val | int(10) unsigned | NEIN | MUL | 0 | |
| Quelle | int(10) unsigniert | NEIN | | 0 | |
+--------+---------------------+------+-----+---------+----------------+
3 Zeilen im Satz (0,00 Sek.)

„id“ ist der automatisch inkrementierte Primärschlüssel und „val“ ist ein nicht eindeutiger Index.

Geben Sie eine große Menge an Daten ein, insgesamt 5 Millionen:

mysql> wähle count(*) aus Test;
+----------+
| Anzahl(*) |
+----------+
|5242882|
+----------+
1 Reihe im Satz (4,25 Sek.)

Wir wissen, dass Effizienzprobleme auftreten , wenn offset in limit offset rows groß ist:

mysql> Auswahl * aus Test, wobei Wert = 4, Limit 300000,5;
+---------+-----+--------+
| ID | Wert | Quelle |
+---------+-----+--------+
| 3327622 | 4 | 4 |
| 3327632 | 4 | 4 |
| 3327642 | 4 | 4 |
| 3327652 | 4 | 4 |
| 3327662 | 4 | 4 |
+---------+-----+--------+
5 Reihen im Satz (15,98 Sek.)

Um dasselbe Ziel zu erreichen, schreiben wir es normalerweise wie folgt um:

mysql> wähle * aus Test, ein innerer Join (wähle ID aus Test, wo Wert=4, Limit 300000,5) b auf a.id=b.id;
+---------+-----+--------+---------+
| ID | Wert | Quelle | ID |
+---------+-----+--------+---------+
| 3327622 | 4 | 4 | 3327622 |
| 3327632 | 4 | 4 | 3327632 |
| 3327642 | 4 | 4 | 3327642 |
| 3327652 | 4 | 4 | 3327652 |
| 3327662 | 4 | 4 | 3327662 |
+---------+-----+--------+---------+
5 Reihen im Satz (0,38 Sek.)

Der Zeitunterschied ist deutlich zu erkennen.

Warum wird das obige Ergebnis angezeigt? Schauen wir uns den Abfragevorgang von select * from test where val=4 limit 300000,5;

Die Daten des Index-Blattknotens werden abgefragt.
Fragen Sie alle erforderlichen Feldwerte im gruppierten Index basierend auf dem Primärschlüsselwert im Blattknoten ab.

Ähnlich wie das folgende Bild:

Wie oben gezeigt, müssen Sie den Indexknoten 300005 Mal abfragen, die gruppierten Indexdaten 300005 Mal abfragen und schließlich die ersten 300000 Ergebnisse herausfiltern und die letzten 5 herausnehmen. MySQL führt eine Menge zufälliger E/A-Vorgänge durch, um die Daten des Clusterindex abzufragen. Die durch 300000 zufällige I/O abgefragten Daten werden nicht im Ergebnissatz angezeigt.

Jemand wird bestimmt fragen: Da der Index am Anfang verwendet wird, warum nicht zuerst entlang der Indexblattknoten bis zu den letzten 5 erforderlichen Knoten abfragen und dann die eigentlichen Daten im gruppierten Index abfragen. Hierzu sind nur 5 zufällige I/Os erforderlich, ähnlich dem Vorgang in der folgenden Abbildung:

Bestätigt:

Lassen Sie uns einige Operationen durchführen, um die obige Schlussfolgerung zu bestätigen:

Um zu beweisen select * from test where val=4 limit 300000 , 5“ 300005 Indexknoten und 300005 Datenknoten im gruppierten Index durchsucht, müssen wir wissen, ob MySQL über eine Möglichkeit verfügt, die Anzahl der Abfragen von Datenknoten über Indexknoten in einem SQL zu zählen. Ich habe zuerst Handler_read_* Reihe ausprobiert, aber leider erfüllte keine der Variablen die Bedingungen.

Ich kann das nur indirekt bestätigen:

InnoDB verfügt über buffer pool . Es enthält die zuletzt aufgerufenen Datenseiten, einschließlich Datenseiten und Indexseiten. Daher müssen wir zwei SQL-Anweisungen ausführen, um die Anzahl der Datenseiten im buffer pool zu vergleichen. Das Vorhersageergebnis ist, dass nach dem Ausführen select * from test a inner join (select id from test where val=4 limit 300000,5) b> die Anzahl der Datenseiten im buffer pool viel geringer ist als die entsprechende Anzahl von select * from test where val=4 limit 300000, 5;“, weil das erstere SQL nur 5-mal auf die Datenseite zugreift, während das letztere SQL 300005 -mal auf die Datenseite zugreift.

wähle * aus Test, wobei val=4 Grenze 300000,5

mysql> wähle index_name,count(*) aus information_schema.INNODB_BUFFER_PAGE, wobei INDEX_NAME in('val','primary') und TABLE_NAME wie '%test%' sind, gruppiere nach index_name;
Leerer Satz (0,04 Sek.)

Es ist ersichtlich, dass derzeit keine Datenseite zur Testtabelle im buffer pool vorhanden ist.

mysql> Auswahl * aus Test, wobei Wert = 4, Limit 300000,5;
+---------+-----+--------+
| ID | Wert | Quelle |
+---------+-----+--------+
| 3327622 | 4 | 4 |
| 3327632 | 4 | 4 |
| 3327642 | 4 | 4 |
| 3327652 | 4 | 4 |
| 3327662 | 4 | 4 |
+---------+-----+--------+
5 Reihen im Satz (26,19 Sek.)

mysql> wähle index_name,count(*) aus information_schema.INNODB_BUFFER_PAGE, wobei INDEX_NAME in('val','primary') und TABLE_NAME wie '%test%' sind, gruppiere nach index_name;
+------------+----------+
| Indexname | Anzahl(*) |
+------------+----------+
| PRIMÄRE | 4098 |
| Wert | 208 |
+------------+----------+
2 Reihen im Satz (0,04 Sek.)

Es ist ersichtlich, dass sich zu diesem Zeitpunkt 4098 Datenseiten und 208 Indexseiten für die Testtabelle im buffer pool befinden.

select * from test a inner join (select id from test where val=4 limit 300000,5) b> , müssen wir buffer pool leeren und mysql。


mysqladmin herunterfahren
/usr/local/bin/mysqld_safe &
mysql> wähle index_name,count(*) aus information_schema.INNODB_BUFFER_PAGE, wobei INDEX_NAME in('val','primary') und TABLE_NAME wie '%test%' sind, gruppiere nach index_name;
Leerer Satz (0,03 Sek.)

Führen Sie SQL aus:

mysql> wähle * aus Test, ein innerer Join (wähle ID aus Test, wo Wert=4, Limit 300000,5) b auf a.id=b.id;
+---------+-----+--------+---------+
| ID | Wert | Quelle | ID |
+---------+-----+--------+---------+
| 3327622 | 4 | 4 | 3327622 |
| 3327632 | 4 | 4 | 3327632 |
| 3327642 | 4 | 4 | 3327642 |
| 3327652 | 4 | 4 | 3327652 |
| 3327662 | 4 | 4 | 3327662 |
+---------+-----+--------+---------+
5 Zeilen im Satz (0,09 Sek.)

mysql> wähle index_name,count(*) aus information_schema.INNODB_BUFFER_PAGE, wobei INDEX_NAME in('val','primary') und TABLE_NAME wie '%test%' sind, gruppiere nach index_name;
+------------+----------+
| Indexname | Anzahl(*) |
+------------+----------+
| GRUNDSCHULE | 5 |
| Wert | 390 |
+------------+----------+
2 Reihen im Satz (0,03 Sek.)

Wir können den Unterschied zwischen den beiden deutlich erkennen: Das erste SQL lädt 4098 Datenseiten in buffer pool , während das zweite SQL nur 5 Datenseiten in buffer pool。 Entspricht unserer Vorhersage. Dies bestätigt auch, warum die erste SQL-Anweisung langsam ist: Sie liest eine große Anzahl nutzloser Datenzeilen (300.000) und verwirft sie dann.

Und dies führt zu einem Problem: Das Laden vieler nicht sehr heißer Datenseiten in buffer pool führt zu einer Verschmutzung buffer pool und belegt buffer pool .

Aufgetretene Probleme:

Um sicherzustellen, dass buffer pool bei jedem Neustart geleert wird, müssen wir innodb_buffer_pool_dump_at_shutdown und innodb_buffer_pool_load_at_startup deaktivieren. Diese beiden Optionen steuern das Dumping der Pufferpooldaten beim Herunterfahren der Datenbank und das Laden der Backup- buffer pool auf die Festplatte beim Starten der Datenbank.

Damit ist dieser Artikel darüber, warum die Verwendung von Limits in MySQL die Leistung beeinträchtigt, abgeschlossen. Weitere Informationen zu den Auswirkungen der Verwendung von Limits in MySQL auf die Leistung finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!