Ausführliche Erläuterung der Gründe und Optimierungsmethoden für übermäßigen Offset, der sich auf die Leistung während der MySQL-Abfrage auswirkt

MySQL-Abfrage verwendet den Befehl select in Kombination mit den Parametern limit und offset, um Datensätze im angegebenen Bereich zu lesen. In diesem Artikel werden die Gründe und Optimierungsmethoden für übermäßigen Offset vorgestellt, der die Leistung bei MySQL-Abfragen beeinträchtigt.

Testdatentabelle und Daten vorbereiten

1. Tabelle erstellen

CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL COMMENT '姓名', `gender` tinyint(3) unsigned NOT NULL COMMENT '性别', PRIMARY KEY (`id`), KEY `gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 1.000.000 Datensätze

<?php
$pdo = new PDO("mysql:host=localhost;dbname=user","root",&#39;&#39;);for($i=0; $i<1000000; $i++){    $name = substr(md5(time().mt_rand(000,999)),0,10);    $gender = mt_rand(1,2);    $sqlstr = "insert into member(name,gender) values(&#39;".$name."&#39;,&#39;".$gender."&#39;)";    $stmt = $pdo->prepare($sqlstr);    $stmt->execute();}
?>mysql> select count(*) from member;
+----------+| count(*) |
+----------+|  1000000 |
+----------+1 row in set (0.23 sec)

3. Aktuelle Datenbankversion

mysql> select version();
+-----------+| version() |
+-----------+| 5.6.24    |
+-----------+1 row in set (0.01 sec)

Analysieren Sie die Gründe, warum ein übermäßiger Offset die Leistung beeinträchtigt

1. Wenn der Offset klein ist

mysql> select * from member where gender=1 limit 10,1;
+----+------------+--------+| id | name       | gender |
+----+------------+--------+| 26 | 509e279687 |      1 |
+----+------------+--------+1 row in set (0.00 sec)mysql> select * from member where gender=1 limit 100,1;
+-----+------------+--------+| id  | name       | gender |
+-----+------------+--------+| 211 | 07c4cbca3a |      1 |
+-----+------------+--------+1 row in set (0.00 sec)mysql> select * from member where gender=1 limit 1000,1;
+------+------------+--------+| id   | name       | gender |
+------+------------+--------+| 1975 | e95b8b6ca1 |      1 |
+------+------------+--------+1 row in set (0.00 sec)

Wenn der Offset klein ist, ist die Abfragegeschwindigkeit ist sehr schnell Schnell und effizient.

2. Wenn der Offset groß ist

mysql> select * from member where gender=1 limit 100000,1;
+--------+------------+--------+| id     | name       | gender |
+--------+------------+--------+| 199798 | 540db8c5bc |      1 |
+--------+------------+--------+1 row in set (0.12 sec)mysql> select * from member where gender=1 limit 200000,1;
+--------+------------+--------+| id     | name       | gender |
+--------+------------+--------+| 399649 | 0b21fec4c6 |      1 |
+--------+------------+--------+1 row in set (0.23 sec)mysql> select * from member where gender=1 limit 300000,1;
+--------+------------+--------+| id     | name       | gender |
+--------+------------+--------+| 599465 | f48375bdb8 |      1 |
+--------+------------+--------+1 row in set (0.31 sec)

Wenn der Offset groß ist, treten Effizienzprobleme auf nimmt ab.

Analysieren Sie die Gründe, die sich auf die Leistung auswirken

select * from member where gender=1 limit 300000,1;

Weil die Datentabelle laut InnoDB ist Struktur des InnoDB-Index. Der Abfrageprozess ist:

• Finden Sie den Primärschlüsselwert über den Sekundärindex (finden Sie alle IDs mit Geschlecht = 1).

• Suchen Sie dann den entsprechenden Datenblock über den Primärschlüsselindex basierend auf dem gefundenen Primärschlüsselwert (finden Sie den entsprechenden Datenblockinhalt basierend auf der ID).

• Fragen Sie je nach Offset-Wert 300.001 Mal die Daten des Primärschlüsselindex ab, verwerfen Sie schließlich die vorherigen 300.000 Einträge und nehmen Sie den letzten heraus.

Aber da der Sekundärindex den Primärschlüsselwert gefunden hat, warum müssen wir dann zuerst den Primärschlüsselindex verwenden, um den Datenblock zu finden, und dann eine Offset-Verarbeitung basierend darauf durchführen? auf den Offsetwert?

Wenn Sie nach dem Finden des Primärschlüsselindex zunächst eine Offset-Verarbeitung durchführen, 300.000 Datensätze überspringen und dann den Datenblock über den Primärschlüsselindex des 300.001. Datensatzes lesen, kann dies die Effizienz verbessern.

Wenn wir nur den Primärschlüssel abfragen, sehen Sie, was der Unterschied ist

mysql> select id from member where gender=1 limit 300000,1;
+--------+| id     |
+--------+| 599465 |
+--------+1 row in set (0.09 sec)

Wenn wir nur den Primärschlüssel abfragen, ist die Ausführungseffizienz natürlich erheblich verbessert im Vergleich zur Abfrage aller Felder .

Spekulation

Nur ​​Primärschlüssel abfragen
Weil der Sekundärindex den bereits gefunden hat Primärschlüsselwert, und die Abfrage muss nur den Primärschlüssel lesen, daher führt MySQL zuerst die Offset-Operation aus und liest dann den Datenblock basierend auf dem nachfolgenden Primärschlüsselindex.

Wenn alle Felder abgefragt werden müssen
Da der Sekundärindex nur den Primärschlüsselwert findet, müssen die Werte anderer Felder jedoch aus dem Datenblock gelesen werden erhalten. Daher liest MySQL zuerst den Inhalt des Datenblocks, führt dann die Offset-Operation aus und verwirft schließlich die vorherigen Daten, die übersprungen werden müssen, und gibt die nachfolgenden Daten zurück.

Bestätigt

Es gibt einen Pufferpool in InnoDB, der kürzlich besuchte Datenseiten speichert, einschließlich Datenseiten und Indexseiten.

Starten Sie zum Testen zuerst MySQL neu und überprüfen Sie dann den Inhalt des Pufferpools.

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
Empty set (0.04 sec)

Sie sehen, dass nach dem Neustart keine Datenseiten aufgerufen wurden.

Fragen Sie alle Felder ab und überprüfen Sie dann den Inhalt des Pufferpools

mysql> select * from member where gender=1 limit 300000,1;
+--------+------------+--------+| id     | name       | gender |
+--------+------------+--------+| 599465 | f48375bdb8 |      1 |
+--------+------------+--------+1 row in set (0.38 sec)mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
+------------+----------+| index_name | count(*) |
+------------+----------+| gender     |      261 || PRIMARY    |     1385 |
+------------+----------+2 rows in set (0.06 sec)

Es ist zu sehen, dass zu diesem Zeitpunkt die Mitgliedertabelle im Pufferpool hat 1385 Datenseiten, 261 Indexseiten.

MySQL neu starten, um den Pufferpool zu löschen, und den Test fortsetzen, um nur den Primärschlüssel abzufragen

mysql> select id from member where gender=1 limit 300000,1;
+--------+| id     |
+--------+| 599465 |
+--------+1 row in set (0.08 sec)mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
+------------+----------+| index_name | count(*) |
+------------+----------+| gender     |      263 || PRIMARY    |       13 |
+------------+----------+2 rows in set (0.04 sec)

Das ist hier zu sehen Zur Zeit gibt es nur 13 Datenseiten, 263 Indexseiten. Daher werden mehrere E/A-Vorgänge für den Zugriff auf Datenblöcke über den Primärschlüsselindex reduziert und die Ausführungseffizienz verbessert.

Daher kann bestätigt werden, dass

Der Grund, warum ein übermäßiger Offset die Leistung während einer MySQL-Abfrage beeinträchtigt, auf mehrere E/A-Vorgänge beim Zugriff auf den Datenblock über den Primärschlüsselindex zurückzuführen ist. (Beachten Sie, dass dieses Problem nur bei InnoDB auftritt und sich die Indexstruktur von MYISAM von der von InnoDB unterscheidet. Die sekundären Indizes verweisen direkt auf Datenblöcke, sodass kein solches Problem besteht.)

Vergleichstabelle der Indexstrukturen zwischen InnoDB- und MyISAM-Engines

Optimierungsmethode

Basierend auf der obigen Analyse wissen wir, dass die Abfrage aller Felder E/A-Vorgänge verursachen wird, die dadurch verursacht werden, dass der Primärschlüsselindex mehrmals auf den Datenblock zugreift.

Daher ermitteln wir zunächst den Offset-Primärschlüssel und fragen dann zur Optimierung den gesamten Inhalt des Datenblocks basierend auf dem Primärschlüsselindex ab.

mysql> select a.* from member as a inner join (select id from member where gender=1 limit 300000,1) as b on a.id=b.id;
+--------+------------+--------+| id     | name       | gender |
+--------+------------+--------+| 599465 | f48375bdb8 |      1 |
+--------+------------+--------+1 row in set (0.08 sec)

In diesem Artikel werden die Gründe und Optimierungsmethoden für übermäßigen Offset erläutert, der sich auf die Leistung bei MySQL-Abfragen auswirkt. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website.

Verwandte Empfehlungen:

Über die Methode zur Verwendung von regulärem PHP zum Entfernen von Breiten- und Höhenstilen

Detaillierte Erläuterung der Deduplizierung und Sortierung von Dateiinhalten

Interpretation von MySQL-Konfigurationsproblemen, bei denen zwischen Groß- und Kleinschreibung unterschieden wird

Das obige ist der detaillierte Inhalt vonAusführliche Erläuterung der Gründe und Optimierungsmethoden für übermäßigen Offset, der sich auf die Leistung während der MySQL-Abfrage auswirkt. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!