Die Auswirkungen des Limits auf die Abfrageleistung in MySQL

I. Einleitung

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 | | |
| Quelle | int(10) unsigniert | NEIN | | | |
+--------+---------------------+------+-----+---------+----------------+
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 der Offset in den Grenzoffsetzeilen 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;“ an:

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 300.005 Mal abfragen, die gruppierten Indexdaten 300.005 Mal abfragen und schließlich die ersten 300.000 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 300.000 zufällige E/A-Vorgänge 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:

Eigentlich möchte ich diese Frage auch stellen.

Bestätigung

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 scannt, 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 die Handler_read_*-Reihe ausprobiert, aber leider erfüllte keine der Variablen die Bedingungen.

Ich kann das nur indirekt bestätigen:

InnoDB hat einen Pufferpool. 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 Pufferpool 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) die Anzahl der Datenseiten im Pufferpool 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.

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 Pufferpool 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 Zeilen im Satz (0,04 Sek.)

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

select * from test a inner join (select id from test where val=4 limit 300000,5) , müssen wir den Pufferpool leeren und MySQL neu starten.

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 den Pufferpool, während das zweite SQL nur 5 Datenseiten in den Pufferpool lädt. 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 den Pufferpool führt zu einer Verschmutzung des Pufferpools und belegt Pufferpoolplatz.

Aufgetretene Probleme

Um sicherzustellen, dass der Pufferpool 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 von Pufferpooldaten beim Herunterfahren der Datenbank und das Laden von Backup-Pufferpooldaten auf die Festplatte beim Starten der Datenbank.

Quellen:

1. https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

2. https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-buffer-pool-tables.html

Weitere Informationen zur Effizienz der SQL-Ausführung finden Sie in den folgenden verwandten Artikeln