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.
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",'');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('".$name."','".$gender."')"; $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)
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.
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 .
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.
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 < Mitgliedstabellen im Pufferpool 🎜>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
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)
Ü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
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!
MySQL ändert den Namen der Datentabelle
MySQL erstellt eine gespeicherte Prozedur
Der Unterschied zwischen Mongodb und MySQL
So überprüfen Sie, ob das MySQL-Passwort vergessen wurde
MySQL-Datenbank erstellen
MySQL-Standard-Transaktionsisolationsstufe
Der Unterschied zwischen SQL Server und MySQL
mysqlPasswort vergessen
