Praktischer Bericht zur Lösung des MySQL Deep Paging-Problems

Vorwort

Wenn wir im täglichen Leben Paging-Anforderungen haben, verwenden wir normalerweise ein Limit zur Implementierung. Wenn der Offset jedoch besonders groß ist, verringert sich die Abfrageeffizienz. In diesem Artikel wird erläutert, wie Sie das Deep Paging-Problem von Millionen von MySQL-Daten in vier Lösungen optimieren können. Außerdem ist eine aktuelle praktische Fallstudie zur Optimierung von langsamem SQL in der Produktion beigefügt.

Warum wird Limit Deep Paging langsamer?

Schauen wir uns zunächst die folgende Tabellenstruktur an:

CREATE TABLE-Konto (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel-ID',
  Name varchar (255) DEFAULT NULL COMMENT 'Kontoname',
  Balance int(11) DEFAULT NULL KOMMENTAR 'Balance',
  create_time datetime NOT NULL COMMENT 'Erstellungszeit',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Aktualisierungszeitpunkt',
  Primärschlüssel (ID),
  SCHLÜSSEL idx_name (Name),
  KEY idx_update_time (Updatezeit) //Index) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='Kontotabelle';

Nehmen wir an, dass für Deep Paging das folgende SQL ausgeführt wird:

Wählen Sie ID, Name und Kontostand vom Konto, wobei Aktualisierungszeit > „19.09.2020“ ist, Limit 100000,10;

Die Ausführungszeit dieses SQL ist wie folgt:

Die Fertigstellung dauert 0,742 Sekunden. Warum wird Deep Paging langsamer? Wenn Sie auf den Grenzwert 0,10 ändern, dauert es nur 0,006 Sekunden

Schauen wir uns zunächst den Ausführungsprozess dieses SQL an:

1. Filtern Sie die Update-Time-Bedingung über den gemeinsamen sekundären Indexbaum idx_update_time und suchen Sie die Datensatz-ID, die die Bedingung erfüllt.
2. Gehen Sie über die ID zurück zum Primärschlüsselindexbaum, suchen Sie die Zeile, die den Datensatz erfüllt, und rufen Sie dann die angezeigten Spalten ab (kehren Sie zur Tabelle zurück).
3. Scannen Sie die 100.010 Zeilen, die die Bedingung erfüllen, verwerfen Sie dann die ersten 100.000 Zeilen und kehren Sie zurück.

SQL-Ausführungsprozess

Der Ausführungsplan sieht wie folgt aus:

Es gibt zwei Gründe, warum SQL langsam wird:

1. Die Limit-Anweisung durchsucht zuerst die Zeilen „Offset+n“, verwirft dann die Zeilen vor dem Offset und gibt die nächsten n Datenzeilen zurück. Dies bedeutet, dass bei einem Grenzwert von 100000,10 100010 Zeilen gescannt werden, während bei einem Grenzwert von 0,10 nur 10 Zeilen gescannt werden.
2. Limit 100000,10. Das Scannen von mehr Zeilen bedeutet auch mehr Tabellenrückgaben.

Optimierung durch Unterabfragen

Da das obige SQL die Tabelle 100010-mal zurückgibt, benötigen wir tatsächlich nur 10 Daten, d. h. wir müssen die Tabelle nur 10-mal zurückgeben. Daher können wir eine Optimierung durchführen, indem wir die Anzahl der Tabellenrückgaben reduzieren.

Überprüfung der B+-Baumstruktur

Wie lässt sich also die Anzahl der Tabellenrückgaben reduzieren? Lassen Sie uns zunächst die B+-Baumindexstruktur überprüfen ~

In InnoDB werden Indizes in Primärschlüsselindizes (Clusterindizes) und Sekundärindizes unterteilt.

• Primärschlüsselindex, der Blattknoten speichert die gesamte Datenzeile
• Sekundärindex, der Blattknoten speichert den Wert des Primärschlüssels.

Übertragen der Bedingung in den Primärschlüsselindexbaum

Wenn wir die Abfragebedingungen zurück in den Primärschlüssel-Indexbaum übertragen, können wir die Anzahl der Tabellenrückgaben reduzieren. Wenn wir zur Abfrage des Primärschlüsselindexbaums wechseln, muss die Abfragebedingung in die Primärschlüssel-ID geändert werden. Was ist mit den vorherigen SQL update_time-Bedingungen? Wo ist die Unterabfrage?

Wie extrahieren Sie die Unterabfrage? Da die Blattknoten des Sekundärindex Primärschlüssel-IDs besitzen, können wir die Primärschlüssel-ID direkt auf Basis von update_time abfragen. Gleichzeitig übergeben wir auch die Bedingung des Limits 100000 an die Unterabfrage. Das komplette SQL lautet wie folgt:

Wählen Sie ID, Name, Guthaben AUS Konto, wobei ID >= (Wählen Sie a.ID aus Konto a, wobei a.Aktualisierungszeit >= „2020-09-19“ Limit 100000, 1) LIMIT 10;

Der Abfrageeffekt ist derselbe und die Ausführungszeit beträgt nur 0,038 Sekunden!

Schauen wir uns den Ausführungsplan an.

Aus dem Ausführungsplan wissen wir, dass die Unterabfragetabelle einer Abfrage den Index idx_update_time verwendet. Zuerst erhalten wir die Primärschlüssel-ID des gruppierten Indexes aus dem Index, wodurch die Notwendigkeit entfällt, zur Tabelle zurückzukehren, und dann kann die zweite Abfrage basierend auf der ID der ersten Abfrage direkt 10 weitere abfragen!

Daher ist dieser Plan realisierbar~

INNER JOIN Verzögerter Join

Die Optimierungsidee des verzögerten Joins ist eigentlich dieselbe wie die der Unterabfrage: Beide übertragen die Bedingungen in den Primärschlüsselindexbaum und reduzieren die Anzahl der Tabellenrückgaben. Der Unterschied besteht darin, dass der verzögerte Join einen inneren Join statt einer Unterabfrage verwendet.

Das optimierte SQL lautet wie folgt:

Wählen Sie acct1.id, acct1.name, acct1.balance aus dem Konto acct1. INNER JOIN (wählen Sie a.id aus dem Konto a, wo a.update_time >= '2020-09-19' ist. ORDER BY a.update_time LIMIT 100000, 10) als acct2 auf acct1.id = acct2.id;

Der Abfrageeffekt wird ebenfalls genutzt und dauert nur 0,034 Sekunden

Der Ausführungsplan sieht wie folgt aus:

Die Abfrageidee besteht darin, zuerst die Primärschlüssel-ID abzufragen, die die Bedingungen über den sekundären Indexbaum idx_update_time erfüllt, und dann über die Primärschlüssel-ID eine Verbindung mit der Originaltabelle herzustellen. Auf diese Weise wird der Primärschlüsselindex später direkt verwendet und die Anzahl der Tabellenrückgaben wird ebenfalls reduziert.

Label-Aufnahme

Der grundlegende Grund für das Problem der begrenzten Tiefe des Paging liegt darin, dass MySQL umso mehr Zeilen scannt und dann verwirft, je größer der Offset ist. Dies führt zu einer Verringerung der Abfrageleistung.

Tatsächlich können wir die Methode der Etikettenaufzeichnung verwenden, das heißt, markieren, welches Element zuletzt abgefragt wurde, und bei der nächsten Überprüfung beginnen wir mit dem Scannen bei diesem Element. Es ist wie beim Lesen eines Buches. Sie können es zusammenfalten oder ein Lesezeichen an die Stelle setzen, an der Sie das letzte Mal aufgehört haben, sodass Sie beim nächsten Lesen direkt zu dieser Seite springen können.

Angenommen, der letzte Datensatz war 100000, dann kann das SQL wie folgt geändert werden:

Wählen Sie ID, Name, Guthaben VON Konto, wobei ID > 100000, sortieren Sie nach ID-Limit 10;

Auf diese Weise ist die Leistung gut, unabhängig davon, wie viele Seiten später umgeblättert werden, da der ID-Index erreicht wird. Diese Methode weist jedoch Einschränkungen auf: Sie erfordert ein Feld, das einem kontinuierlichen, selbstinkrementierenden Feld ähnelt.

Verwenden Sie zwischen ... und ...

In vielen Fällen lässt sich die Grenzwertabfrage in eine Abfrage mit bekannter Position umwandeln, so dass MySQL über einen Bereichsscan zwischen ... und die entsprechenden Ergebnisse erhalten kann.

Wenn Sie wissen, dass die Grenzwerte 100000 und 100010 sind, können Sie es folgendermaßen optimieren:

Wählen Sie ID, Name und Kontostand AUS dem Konto, wobei die ID zwischen 100000 und 100010 liegt, sortiert nach ID absteigend;

Praxisnahe Fälle aus der Praxis

Schauen wir uns einen Fall aus dem echten Leben an. Nehmen Sie an, dass die Tabellenstruktur wie folgt ist und dass 2 Millionen Daten vorhanden sind.

CREATE TABLE-Konto (
 id varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'Primärschlüssel',
 account_no varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'Kontonummer'
 Betrag Dezimalzahl (20,2) STANDARD NULL KOMMENTAR 'Betrag'
 Typ varchar (10) COLLATE utf8_bin DEFAULT NULL COMMENT 'Typ A, B'
 create_time datetime DEFAULT NULL COMMENT 'Erstellungszeit',
 update_time datetime DEFAULT NULL COMMENT 'Aktualisierungszeitpunkt',
 Primärschlüssel (ID),
 SCHLÜSSEL `idx_account_no` (Kontonummer),
 SCHLÜSSEL `idx_create_time` (Erstellungszeit)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Kontotabelle'

Die Geschäftsanforderung lautet wie folgt: Holen Sie sich die neuesten Kontodaten des Typs A für 2021 und melden Sie sie an die Big-Data-Plattform.

So implementieren Sie die Grundidee

Viele Partner werden diese Anforderung direkt umsetzen, wenn sie sie erhalten:

//Gesamtzahl der Berichte abfragen Integer total = accountDAO.countAccount();

//Frage das SQL ab, das der Gesamtzahl der Berichte entspricht
<select id ='AnzahlAccount' resultType="java.lang.Integer">
  Anzahl auswählen(1)
   vom Konto
  wobei Erstellungszeit >='2021-01-01 00:00:00'
  und Typ = "A"
</Auswählen>

//Anzahl der Seiten berechnen int pageNo = total % pageSize == 0 ? total / pageSize : (total / pageSize + 1);

//Paging-Abfrage, Bericht für (int i = 0; i < Seitennummer; i++) {
 List<AcctountPO> Liste = KontoDAO.listAccountByPage(Startzeile, Seitengröße);
 startRow = (Seitennummer-1)*Seitengröße;
 // Big Data melden postBigData(Liste);
}

 // Paging-Abfrage-SQL (es kann Probleme mit der Paging-Tiefe geben, da die Kontotabelle Millionen von Daten enthält)
<ID auswählen ='listAccountByPage' >
  auswählen *
   vom Konto
  wobei Erstellungszeit >='2021-01-01 00:00:00'
  und Typ = "A"
  Begrenzung #{startRow},#{pageSize}
</Auswählen>

Praktischer Optimierungsplan

Bei der obigen Implementierung tritt das Problem der begrenzten Tiefenpaging auf, da die Kontotabelle Millionen von Daten enthält. Wie kann es also optimiert werden?

Tatsächlich können Sie die Tag-Aufzeichnungsmethode verwenden. Einige Freunde sind möglicherweise verwirrt. Der ID-Primärschlüssel ist nicht kontinuierlich. Können Sie die Tag-Aufzeichnung wirklich verwenden?

Wenn die ID nicht kontinuierlich ist, können wir sie natürlich durch „order by“ kontinuierlich machen. Das Optimierungsschema ist wie folgt:

//Abfrage der Mindest-ID
String lastId = accountDAO.queryMinId();

//Frage das SQL ab, das der maximalen ID entspricht
<select id="queryMinId" returnType="java.lang.String">
wähle MIN(id)
 vom Konto
wobei Erstellungszeit >='2021-01-01 00:00:00'
und Typ = "A"
</Auswählen>

//Anzahl der Einträge pro Seite Integer pageSize = 100;

Liste<ActountPO>-Liste;
Tun{
   Liste = listAccountByPage(letzteId,Seitengröße);
   //Tag-Aufzeichnungsmethode, zeichnen Sie die zuletzt abgefragte ID auf
   letzteId = Liste.get(Liste,Größe()-1).getId();
    // Big Data melden postBigData(Liste);
}während(CollectionUtils.isNotEmpty(Liste));

<Wählen Sie id="listAccountByPage">
  wählen *
   vom Konto
   wobei Erstellungszeit >='2021-01-01 00:00:00'
  und id > #{lastId}
  und Typ = "A"
  Sortieren nach ID aufsteigend
    Begrenzung #{pageSize}
</Auswählen>

Zusammenfassen

Dies ist das Ende dieses Artikels über MySQL Deep Paging-Probleme. Weitere Informationen zu MySQL Deep Paging-Problemen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!