Detaillierte Erklärung der Gründe und Optimierungen für den großen Offset, der die Leistung bei MySQL-Abfragen beeinträchtigt

Vorwort

Die MySQL-Abfrage verwendet den Select-Befehl und mit den Parametern „Limit“ und „Offset“ können Sie Datensätze in einem angegebenen Bereich lesen. In diesem Artikel werden die Gründe erläutert, warum ein zu großer Offset die Leistung bei MySQL-Abfragen beeinträchtigt, und es werden Möglichkeiten zur Optimierung vorgestellt.

Vorbereiten von Testdatenblättern und Daten

1. Erstellen Sie eine Tabelle

CREATE TABLE `Mitglied` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NICHT NULL KOMMENTAR 'Name',
 `Geschlecht` tinyint(3) unsigned NOT NULL COMMENT 'Geschlecht',
 Primärschlüssel (`id`),
 SCHLÜSSEL `Geschlecht` (`Geschlecht`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

2. 1.000.000 Datensätze einfügen

<?php
$pdo = neues PDO("mysql:host=localhost;dbname=Benutzer","root",'');

für($i=0; $i<1000000; $i++){
 $name = substr(md5(Zeit().mt_rand(000,999)),0,10);
 $gender = mt_rand(1,2);
 $sqlstr = "in Mitglied (Name, Geschlecht) Werte einfügen ('".$name."','".$gender."')";
 : $stmt = $pdo->prepare($sqlstr);
 $stmt->execute();
}
?>

mysql> wähle count(*) aus Mitglied aus;
+----------+
| Anzahl(*) |
+----------+
| 1000000 |
+----------+
1 Zeile im Satz (0,23 Sek.)

3. Aktuelle Datenbankversion

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

Analysieren Sie, warum große Offsets die Leistung beeinträchtigen

1. Wenn der Versatz klein ist

mysql> Auswahl * vom Mitglied, wobei Geschlecht=1, Limit 10,1;
+----+------------+--------+
| ID | Name | Geschlecht |
+----+------------+--------+
| 26 | 509e279687 | 1 |
+----+------------+--------+
1 Zeile im Satz (0,00 Sek.)

mysql> Auswahl * vom Mitglied, wobei Geschlecht=1, Limit 100,1;
+-----+------------+--------+
| ID | Name | Geschlecht |
+-----+------------+--------+
| 211 | 07c4cbca3a | 1 |
+-----+------------+--------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle * aus Mitglied, wobei Geschlecht=1, Limit 1000,1;
+------+------------+--------+
| ID | Name | Geschlecht |
+------+------------+--------+
| 1975 | e95b8b6ca1 | 1 |
+------+------------+--------+
1 Zeile im Satz (0,00 Sek.)

Wenn der Offset klein ist, ist die Abfragegeschwindigkeit schnell und die Effizienz hoch.

2. Großer Versatz

mysql> wähle * aus Mitglied, wobei Geschlecht=1, Limit 100000,1;
+--------+------------+--------+
| ID | Name | Geschlecht |
+--------+------------+--------+
| 199798 | 540db8c5bc | 1 |
+--------+------------+--------+
1 Zeile im Satz (0,12 Sek.)

mysql> wähle * aus Mitglied, wobei Geschlecht=1, Limit 200000,1;
+--------+------------+--------+
| ID | Name | Geschlecht |
+--------+------------+--------+
| 399649 | 0b21fec4c6 | 1 |
+--------+------------+--------+
1 Zeile im Satz (0,23 Sek.)

mysql> wähle * aus Mitglied, wobei Geschlecht=1, Limit 300000,1;
+--------+------------+--------+
| ID | Name | Geschlecht |
+--------+------------+--------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+
1 Zeile im Satz (0,31 Sek.)

Bei einem großen Offset treten Effizienzprobleme auf. Mit zunehmendem Offset nimmt die Ausführungseffizienz ab.

Analysieren Sie die Gründe, die die Leistung beeinträchtigen

Wählen Sie * aus dem Mitglied, bei dem Geschlecht = 1, Grenze 300000,1;

Da es sich bei der Datentabelle um InnoDB handelt, lautet der Abfragevorgang gemäß der Struktur des InnoDB-Index:

• Suchen Sie den Primärschlüsselwert über den sekundären Index (suchen Sie alle IDs mit Geschlecht=1).
• Suchen Sie dann den entsprechenden Datenblock über den Primärschlüsselindex basierend auf dem gefundenen Primärschlüsselwert (suchen Sie den entsprechenden Datenblockinhalt basierend auf der ID).
• Fragen Sie gemäß dem Offsetwert die Daten des 300001. Primärschlüsselindex ab, verwerfen Sie die vorherigen 300000 Datensätze und nehmen Sie den letzten heraus.

Da der Sekundärindex den Primärschlüsselwert jedoch bereits gefunden hat, warum müssen wir dann zuerst den Primärschlüsselindex verwenden, um den Datenblock zu finden, und dann eine Offset-Verarbeitung basierend auf dem Offset-Wert durchführen?

Wenn Sie nach dem Suchen des Primärschlüsselindex zuerst eine Offset-Verarbeitung durchführen, 300.000 überspringen und dann den Datenblock über den Primärschlüsselindex des 300.001. Datensatzes lesen, wird die Effizienz verbessert.

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

mysql> wähle ID aus Mitglied, bei dem Geschlecht=1, Limit 300000,1;
+--------+
|Ich würde|
+--------+
|599465|
+--------+
1 Zeile im Satz (0,09 Sek.)

Wenn nur der Primärschlüssel abgefragt wird, verbessert sich die Ausführungseffizienz im Vergleich zur Abfrage aller Felder offensichtlich erheblich.

Spekulation

Nur den Primärschlüssel abfragen

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

Wenn Sie alle Felder abfragen müssen

Denn der Sekundärindex findet nur den Primärschlüsselwert, für die Werte anderer Felder muss jedoch der Datenblock gelesen werden. Daher liest MySQL zuerst den Datenblockinhalt, führt dann den Offset-Vorgang aus und verwirft schließlich die vorherigen Daten, die übersprungen werden müssen, und gibt die nachfolgenden Daten zurück.

Bestätigung

InnoDB verfügt über einen Pufferpool, der kürzlich aufgerufene Datenseiten, einschließlich Datenseiten und Indexseiten, speichert.

Starten Sie MySQL zu Testzwecken zunächst neu und überprüfen Sie dann den Inhalt des Pufferpools.

mysql> wähle index_name, count(*) aus information_schema.INNODB_BUFFER_PAGE, wobei INDEX_NAME in('primary','gender') und TABLE_NAME wie '%member%' gruppiert nach index_name ist;
Leerer Satz (0,04 Sek.)

Es ist ersichtlich, dass nach dem Neustart keine Datenseiten aufgerufen wurden.

Alle Felder abfragen und dann den Inhalt des Pufferpools anzeigen

mysql> wähle * aus Mitglied, wobei Geschlecht=1, Limit 300000,1;
+--------+------------+--------+
| ID | Name | Geschlecht |
+--------+------------+--------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+
1 Zeile im Satz (0,38 Sek.)

mysql> wähle index_name, count(*) aus information_schema.INNODB_BUFFER_PAGE, wobei INDEX_NAME in('primary','gender') und TABLE_NAME wie '%member%' gruppiert nach index_name ist;
+------------+----------+
| Indexname | Anzahl(*) |
+------------+----------+
| Geschlecht | 261 |
| PRIMÄRE | 1385 |
+------------+----------+
2 Reihen im Satz (0,06 Sek.)

Es ist ersichtlich, dass sich zu diesem Zeitpunkt 1385 Datenseiten und 261 Indexseiten für die Mitgliedstabelle im Pufferpool befinden.

Starten Sie MySQL neu, um den Pufferpool zu leeren, und fahren Sie mit dem Testen fort, um nur den Primärschlüssel abzufragen.

mysql> wähle ID aus Mitglied, bei dem Geschlecht=1, Limit 300000,1;
+--------+
|Ich würde|
+--------+
|599465|
+--------+
1 Zeile im Satz (0,08 Sek.)

mysql> wähle index_name, count(*) aus information_schema.INNODB_BUFFER_PAGE, wobei INDEX_NAME in('primary','gender') und TABLE_NAME wie '%member%' gruppiert nach index_name ist;
+------------+----------+
| Indexname | Anzahl(*) |
+------------+----------+
| Geschlecht | 263 |
| GRUNDSCHULE | 13 |
+------------+----------+
2 Reihen im Satz (0,04 Sek.)

Es ist ersichtlich, dass sich zu diesem Zeitpunkt nur 13 Datenseiten und 263 Indexseiten für die Mitgliedstabelle im Pufferpool befinden. Dadurch werden mehrere E/A-Vorgänge beim Zugriff auf Datenblöcke über Primärschlüsselindizes reduziert und die Ausführungseffizienz verbessert.

Daher kann bestätigt werden , dass der Grund, warum der Offset zu groß ist und die Leistung während der MySQL-Abfrage beeinträchtigt, der E/A-Vorgang beim mehrfachen Zugriff auf den Datenblock über den Primärschlüsselindex ist . ( Beachten Sie, dass nur InnoDB dieses Problem hat und die MYISAM-Indexstruktur sich von InnoDB unterscheidet. Die sekundären Indizes verweisen alle direkt auf die Datenblöcke, daher gibt es dieses Problem nicht .)

Vergleich der Indexstrukturen der InnoDB- und MyISAM-Engine

Geben Sie hier die Bildbeschreibung ein

Optimierungsmethoden

Basierend auf der obigen Analyse wissen wir, dass die Abfrage aller Felder zu E/A-Vorgängen führt, die durch mehrere Zugriffe auf Datenblöcke durch den Primärschlüsselindex verursacht werden.

Daher ermitteln wir zuerst den versetzten Primärschlüssel und fragen dann den gesamten Inhalt des Datenblocks basierend auf dem Primärschlüsselindex ab, um eine Optimierung zu erreichen.

mysql> wähle a.* vom Mitglied als inneren Join (wähle ID vom Mitglied, bei dem Geschlecht=1, Grenze 300000,1) als b auf a.id=b.id;
+--------+------------+--------+
| ID | Name | Geschlecht |
+--------+------------+--------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+
1 Zeile im Satz (0,08 Sek.)

Anhang: MySQL-Limit, Offset-Differenz

WÄHLEN
  Stichwort
AUS
  Schlüsselwortrang
WO
  Werbe-ID = '59'
Bestellen bis
  Stichwort
GRENZE 2 OFFSET 1;

In dieser SQL-Anweisung folgen beispielsweise auf das Limit zwei Datensätze und auf den Offset der erste Datensatz.

WÄHLEN
  Stichwort
AUS
  Schlüsselwortrang
WO
  Werbe-ID = '59'
BESTELLEN BIS
  Stichwort
GRENZE 2,1;

In diesem SQL folgt auf die Begrenzung das Lesen vom zweiten Element und das Lesen von 1 Information.

Verwechseln Sie diese beiden nicht.

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. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.