Eine kurze Diskussion über die Optimierung von MySQL-Paging für Milliarden von Daten

Hintergrund

Nach Feierabend saß ich glücklich in der U-Bahn auf dem Weg nach Hause und dachte darüber nach, wie ich mein Wochenendleben planen könnte.

Plötzlich klingelte das Telefon. Ich sah, dass es einer unserer Mitstudenten war, und wurde sofort nervös. Die Version dieser Woche wurde bereits veröffentlicht, und ein Anruf zu diesem Zeitpunkt bedeutet im Allgemeinen, dass es ein Problem mit der Leitung gibt.

Tatsächlich bestand die Kommunikationssituation darin, dass eine Online-Datenabfrageschnittstelle wie verrückt und irrational aufgerufen wurde, was direkt zu einer Verlangsamung des Online-MySql-Clusters führte.
Nun, dieses Problem ist ernst. Ich stieg aus der U-Bahn, eilte nach Hause, schaltete den Computer ein und rief mit meinen Kollegen das langsame Abfrageprotokoll auf Pinpoint ab. Ich habe eine sehr seltsame Abfrage gesehen, wie folgt

POST-Domäne/v1.0/Modul/Methode?order=condition&orderType=desc&offset=1800000&limit=500

Domäne, Modul und Methode sind alle Aliase, die den Domänen-, Modul- und Instanzmethodennamen der Schnittstelle darstellen. Der folgende Offset und die folgende Grenze stellen den Offset und die Seitenzahl des Paging-Vorgangs dar, was bedeutet, dass der Schüler die (1800000/500+1=3601)te Seite aufschlägt. Nach einer vorläufigen Suche in den Protokollen fand ich mehr als 8.000 solcher Anrufe.

Das ist erstaunlich. Die Anzahl der Seiten mit Seitenumbrüchen auf unserer Seite beträgt nicht 500, sondern 25 pro Seite. Dies wird definitiv nicht dadurch verursacht, dass auf der Funktionsseite manuell Seiten umgeblättert werden, sondern die Daten werden aktualisiert (zur Erklärung: Unsere Produktionsumgebungsdaten umfassen mehr als 100 Millionen). Ein detaillierter Vergleich der Protokolle ergab, dass sich viele Paging-Zeiten überschnitten und es sich bei der anderen Partei um einen Multithread-Anruf handeln sollte.

Durch die Analyse des Authentifizierungstokens haben wir im Wesentlichen festgestellt, dass die Anfrage von einem Client-Programm namens ApiAutotest kam und dass das Konto, das das Authentifizierungstoken generiert hat, von einem QA-Studenten stammte. Ich habe sofort meine Klassenkameraden angerufen, um mit ihnen zu sprechen und das Problem zu klären.

analysieren

Tatsächlich ist die Gesamteffizienz unserer MySQL-Abfrageanweisungen noch akzeptabel. Die erforderlichen Abfrageoptimierungen für Join-Tabellen sind alle vorhanden, der erforderliche vereinfachte Abfrageinhalt ist ebenfalls vorhanden und die erforderlichen Indizes für Schlüsselbedingungsfelder und Sortierfelder sind ebenfalls vorhanden. Das Problem besteht darin, dass die Abfragen Seite für Seite erfolgen und je weiter die Seite zurückliegt, desto mehr Daten werden gescannt und desto langsamer wird es.
Als wir die ersten paar Seiten überprüften, stellten wir fest, dass die Geschwindigkeit sehr hoch war. Beispielsweise wurde das Limit 200,25 sofort erreicht. Aber die Geschwindigkeit wird mit der Zeit immer langsamer, und insbesondere nach einer Million Datensätzen bleibt es extrem hängen. Was ist das Prinzip dahinter? Schauen wir uns zunächst an, wie die SQL-Abfrage aussieht, wenn wir umblättern:

Wählen Sie * aus t_name, wobei c_name1 = "xxx" ist, sortiert nach c_name2, Limit 2000000,25;

Die Langsamkeit dieser Abfrage wird tatsächlich durch den großen Offset nach dem Limit verursacht. Beispielsweise entspricht die obige Grenze von 2000000,25 dem Scannen von 2000025 Daten durch die Datenbank, wobei die ersten 20000000 Daten verworfen und die restlichen 25 Daten an den Benutzer zurückgegeben werden. Dieser Ansatz ist offensichtlich unvernünftig.

Sehen wir uns Kapitel 6 von „High Performance MySQL“ an: Optimierung der Abfrageleistung. Dort wird dieses Problem erläutert:

Paging-Operationen werden normalerweise mit „Limit plus Offset“ und einer entsprechenden „Order by“-Klausel implementiert. Dies stellt jedoch ein allgemeines Problem dar: Wenn der Offset sehr groß ist, führt dies dazu, dass MySQL eine große Anzahl unnötiger Zeilen scannt und diese dann verwirft.

Datensimulation

Nachdem wir nun das Prinzip des Problems verstanden haben, sollten wir versuchen, es zu lösen. Im Hinblick auf die Datensensitivität simulieren wir diese Situation und erstellen einige Daten zum Testen.

1. Erstellen Sie zwei Tabellen: Mitarbeitertabelle und Abteilungstabelle

/*Abteilungstabelle, löschen, falls vorhanden*/
Tabelle löschen, wenn EXISTIERT dep;
Tabelle erstellen dep(
    id int vorzeichenloser Primärschlüssel auto_increment,
    depno mediumint unsigned nicht null Standard 0,
    depname varchar(20) ungleich null Standard "",
    Memo varchar (200) nicht null Standard ""
);

/*Mitarbeitertabelle, löschen, falls vorhanden*/
Tabelle löschen, wenn emp EXISTIERT;
Tabelle emp erstellen(
    id int vorzeichenloser Primärschlüssel auto_increment,
    empno mediumint unsigned nicht null Standard 0,
    empname varchar(20) nicht null Standard "",
    Job varchar(9) nicht null Standard "",
    mgr mediumint unsigniert nicht null Standard 0,
    Einstellungsdatum, Datum und Uhrzeit sind nicht null,
    sal decimal(7,2) nicht null,
    comn decimal(7,2) nicht null,
    depno mediumint unsigniert nicht null Standard 0
);

2. Erstellen Sie zwei Funktionen: Generieren Sie Zufallszeichenfolgen und Zufallszahlen

/* Funktion zum Generieren eines zufälligen Strings */
TRENNUNGSZEICHEN $
FUNKTION löschen, wenn Rand_String EXISTIERT;
CREATE FUNCTION rand_string(n INT) gibt VARCHAR(255) zurück
BEGINNEN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WÄHREND ich < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    Setze i = i+1;
    ENDE WÄHREND;
    RETURN return_str;
ENDE $
TRENNUNGSZEICHEN;


/*Funktion zum Generieren einer zufälligen Abteilungsnummer*/
TRENNUNGSZEICHEN $
Funktion löschen, wenn rand_num EXISTIERT;
Funktion erstellen rand_num() gibt INT(5) zurück
BEGINNEN
    DECLARE i INT DEFAULT 0;
    SETZE i = FLOOR(100+RAND()*10);
    ZURÜCK i;
ENDE $
TRENNUNGSZEICHEN;

3. Schreiben Sie eine gespeicherte Prozedur, um 5 Millionen Mitarbeiterdaten zu simulieren

/*Erstellen Sie eine gespeicherte Prozedur: Fügen Sie Daten in die Tabelle emp ein*/
TRENNUNGSZEICHEN $
VERFAHREN löschen, wenn EXISTIERT insert_emp;
PROZEDUR ERSTELLEN insert_emp(IN START INT(10),IN max_num INT(10))
BEGINNEN
    DECLARE i INT DEFAULT 0;
    /*set autocommit =0 Setze Autocommit auf 0 und schalte das Standard-Commit aus*/
    SETZEN Sie Autocommit = 0;
    WIEDERHOLEN
    Setze i = i + 1;
    INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'VERKÄUFER',0001,now(),2000,400,rand_num());
    BIS i = max_num
    ENDE WIEDERHOLUNG;
    BEGEHEN;
ENDE $
TRENNUNGSZEICHEN;
/*Fügen Sie 5 Millionen Daten ein*/
rufen Sie insert_emp(0,5000000) auf;

4. Schreiben Sie eine gespeicherte Prozedur, um die Abteilungsdaten von 120 zu simulieren

/*Erstellen Sie eine gespeicherte Prozedur: Fügen Sie Daten in die Dep-Tabelle ein*/
TRENNUNGSZEICHEN $
VERFAHREN löschen, falls EXISTIERT: insert_dept;
PROZEDUR ERSTELLEN insert_dept(IN START INT(10), IN max_num INT(10))
BEGINNEN
    DECLARE i INT DEFAULT 0;
    SETZEN Sie Autocommit = 0;
    WIEDERHOLEN
    Setze i = i+1;
    INSERT INTO dep( depno, depname, memo) VALUES((START+i), Randzeichenfolge(10), Randzeichenfolge(8));
    BIS i = max_num
    ENDE WIEDERHOLUNG;
    BEGEHEN;
ENDE $
TRENNUNGSZEICHEN;
/*120 Datensätze einfügen*/
rufen Sie insert_dept(1,120) auf;

5. Erstellen Sie einen Index für das Schlüsselfeld. Das Erstellen des Indexes dauert nach dem Ausführen der Daten zwar lange, das Ausführen der Daten geht jedoch schneller.

/*Index für Schlüsselfelder erstellen: Sortierung, Bedingungen*/
INDEX ERSTELLEN idx_emp_id AUF emp(id);
INDEX ERSTELLEN idx_emp_depno ON emp(depno);
INDEX ERSTELLEN idx_dep_depno ON dep(depno);

prüfen

Testdaten

/*Offset ist 100, nimm 25*/
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a nach links verbinden dep b auf a.depno = b.depno, sortiert nach a.id, desc-Limit 100,25;
/*Offset ist 4800000, nimm 25*/
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a nach links verbinden dep b auf a.depno = b.depno, sortiert nach a.id, desc-Limit 4800000,25;

Ausführungsergebnisse

[SQL]
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a nach links verbinden dep b auf a.depno = b.depno, sortiert nach a.id, desc-Limit 100,25;
Betroffene Zeilen: 0
Zeit: 0,001 s
[SQL]
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a nach links verbinden dep b auf a.depno = b.depno, sortiert nach a.id, desc-Limit 4800000,25;
Betroffene Zeilen: 0
Zeit: 12.275s

Da viele Daten gescannt werden müssen, ist der Zeitaufwand hierfür offensichtlich nicht um ein Vielfaches höher.

Lösung

1. Verwenden Sie Indexabdeckung + Unterabfrageoptimierung

Da wir über die Primärschlüssel-ID verfügen und einen Index darauf erstellt haben, können wir zuerst den ID-Wert der Startposition im Indexbaum finden und dann die Zeilendaten basierend auf dem gefundenen ID-Wert abfragen.

/*Die Unterabfrage ruft die ID der um 100 verschobenen Position ab und erhält 25 nach dieser Position*/
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id >= (ID aus emp auswählen, sortiert nach ID-Limit 100,1)
Sortieren nach a.id-Limit 25;

/*Die Unterabfrage ruft die ID der Position mit dem Offset 4800000 ab und erhält 25 nach dieser Position*/
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id >= (ID aus Emp auswählen, sortiert nach ID-Limit 4800000,1)
Sortieren nach a.id-Limit 25;

Ausführungsergebnisse

Die Ausführungseffizienz wurde im Vergleich zu früher erheblich verbessert:
[SQL]
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id >= (ID aus emp auswählen, sortiert nach ID-Limit 100,1)
Sortieren nach a.id-Limit 25;
Betroffene Zeilen: 0
Zeit: 0,106 s

[SQL]
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id >= (ID aus Emp auswählen, sortiert nach ID-Limit 4800000,1)
Sortieren nach a.id-Limit 25;
Betroffene Zeilen: 0
Zeit: 1.541s

2. Definieren Sie die Ausgangsposition neu

Merken Sie sich die Primärschlüsselposition des letzten Suchergebnisses, um die Verwendung eines Offsets zu vermeiden

/*Denken Sie daran, dass die ID der letzten Daten bei der vorherigen Paginierung 100 ist. Daher überspringen wir 100 und scannen die Tabelle ab 101*/
Wählen Sie a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id > 100, sortiert nach a.id-Limit 25;

/*Denken Sie daran, dass die ID der letzten Daten bei der vorherigen Paginierung 4800000 ist. Daher überspringen wir 4800000 und scannen die Tabelle ab 4800001*/
Wählen Sie a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id > 4800000
Sortieren nach a.id-Limit 25;

Ausführungsergebnisse

[SQL]
Wählen Sie a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id > 100, sortiert nach a.id-Limit 25;
Betroffene Zeilen: 0
Zeit: 0,001 s

[SQL]
Wählen Sie a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id > 4800000
Sortieren nach A.ID-Limit 25;
Betroffene Zeilen: 0
Zeit: 0.000s

Dies ist am effizientesten. Unabhängig davon, wie die Seiten aufgeteilt werden, ist der Zeitaufwand grundsätzlich gleich, da nach der Ausführung der Bedingungen nur 25 Daten gescannt werden.

Es gibt jedoch ein Problem: Es ist nur zum Blättern jeweils einer Seite geeignet, sodass die letzte ID der vorherigen Seite gespeichert werden kann. Es treten Probleme auf, wenn der Benutzer zwischen Seiten springt. Wenn der Benutzer beispielsweise gerade Seite 25 durchsucht hat und sofort zu Seite 35 springt, sind die Daten falsch.

Dies eignet sich für Szenarien wie die Baidu-Suche oder Tencent News, bei denen Sie das Scrollrad nach unten ziehen und kontinuierlich ziehen und laden. Durch dieses Lazy Loading wird sichergestellt, dass die Daten nicht sprunghaft abgerufen werden.

3. Downgrade-Strategie

Ich habe online eine Lösung gesehen, die ein Alibaba-DBA-Klassenkamerad geteilt hat: Konfigurieren Sie den Grenzversatz und den Maximalwert der Anzahl der Erfassungen. Wenn der Maximalwert überschritten wird, werden leere Daten zurückgegeben.
Denn er meint, wenn der Wert diesen Wert überschreitet, wird nicht mehr geblättert, sondern die Daten werden aktualisiert. Wenn Sie sicher sind, dass Sie Daten finden möchten, sollten Sie die entsprechenden Bedingungen eingeben, um den Umfang einzugrenzen, anstatt seitenweise zu blättern.
Dies ist ungefähr die gleiche Idee wie die meines Kollegen: Wenn der Offset während der Anfrage größer als ein bestimmter Wert ist, wird zuerst ein 4xx-Fehler zurückgegeben.

Zusammenfassung

In dieser Nacht haben wir die dritte oben erwähnte Lösung angewendet, um den Offset zu begrenzen. Wenn er einen bestimmten Wert überschreitet, wird ein Nullwert zurückgegeben. Am zweiten Tag wurden die Programm- und Datenbankskripte durch Kombination der ersten und zweiten Lösung weiter optimiert.

Vernünftigerweise sollten bei der Ausführung jeder Funktion Extremsituationen berücksichtigt werden und die Entwurfskapazität sollte extreme Grenztests abdecken.

Darüber hinaus müssen auch notwendige Strombegrenzungen und Downgradings berücksichtigt werden. Wenn beispielsweise ein Tool mehrthreadig aufgerufen wird und die Häufigkeit in einem kurzen Zeitraum 8.000 Mal beträgt, kann über den Zähldienst eine zu häufige Aufrufhäufigkeit des Benutzers ermittelt und eine Rückmeldung gegeben werden, sodass die Aufrufe direkt unterbrochen werden können.

Dies ist das Ende dieses Artikels über die Optimierung der MySQL-Datenpagierung auf Milliardenebene. Weitere relevante Inhalte zur MySQL-Datenpagierung auf Milliardenebene finden Sie in den vorherigen Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!