Erfahrungsaustausch zur Optimierung von MySQL-Big-Data-Abfragen (empfohlen)

Ernsthafte MySQL-Optimierung!

Wenn die MySQL-Datenmenge klein ist, ist eine Optimierung unnötig. Wenn die Datenmenge groß ist, ist eine Optimierung unabdingbar. Wenn eine Abfrage nicht optimiert ist, dauert sie 10 Sekunden. Wenn sie richtig optimiert ist, dauert dieselbe Abfrage 10 Millisekunden.

Was für eine schmerzliche Erkenntnis das ist!

MySQL-Optimierung bedeutet in der Programmiersprache: Indexoptimierung und Where-Bedingungsoptimierung.

Experimentelle Umgebung: MacBook Pro MJLQ2CH/A, MySQL 5.7, Datenvolumen: 2,12 Millionen+

EINS:

 * aus Artikel auswählen
 Innerer Join (
 Wählen Sie die ID aus.
 AB Artikel
 WO
  length(content_url) > 0 und
  (Status aus Quelle auswählen, wobei ID = Artikel.Quellen-ID)=1 und
  (Status aus Kategorie auswählen, bei der ID = Artikel.Kategorie-ID)=1 und
  Status = 1 und ID < 2164931
 Sortieren nach Stick desc, Pub_Time desc
 Grenze 240,15
 ) AS t
VERWENDEN(id);

Auf den ersten Blick wird der Chef mich definitiv umbringen wollen. Warum muss ich eine Selbstassoziation oder einen inneren Join durchführen? Leute im XX. Stock, bringt mir mein Fleischermesser, ich will den Blogger umbringen! ! !

Ehrlich gesagt, war mein Kopf beim Ausgehen am Morgen nicht gegen die Tür gedrückt und das wollte ich auch nicht.

1. Wenn die Datenmenge groß ist, müssen Sie Paging-Abfragen mit einem großen Offset durchführen, was die Abfrage wirklich beschleunigt. Der Grund ist ---> Verwenden Sie die ID in der Join-Untertabelle, um die gesamte Tabelle abzudecken und einen vollständigen Tabellenscan zu vermeiden.

Sehen Sie sich meine „order by“ an (flüstern: es ist nur eine „order by“, wer kann das nicht schreiben), ersetzen Sie diese „order by“ durch das Feld „desc“ oder „erklären Sie es“ in Ihrer eigenen Tabelle und sehen Sie, was passiert. Extra ---> Dateisortierung! Scheiße!

2. Für diese Art von Order by mit mehreren Bedingungen fügen wir den beiden Feldern normalerweise direkt Indizes hinzu, verwenden aber dennoch Extra ---> Filesort. Gehen Sie einen anderen Weg und fügen Sie allen Bedingungen nach „order by“ einen gemeinsamen Index hinzu. Beachten Sie, dass die Reihenfolge mit Ihrer „order by“-Anordnung übereinstimmen muss. Somit bleibt für Extra nur noch die Frage nach dem Wo.

Schauen wir uns an where，(select status from source where id = article.source_id)=1 and ... was für eine seltsame Art, das zu schreiben!

3. Ich habe darüber nachgedacht, die Join+Index-Methode zu verwenden, aber nach dem Testen stellte sich heraus, dass sie fast identisch mit dieser Methode ist. Die Produktionsumgebung ist so geschrieben, also lassen wir sie einfach so wie sie ist. Wir können auch zwei Indizes speichern (source_id, category_id). Niemand kann uns davon abhalten, faul zu sein. Wenn wir in Zukunft Verluste erleiden, können wir zurückkommen und weiter optimieren.

4. Das ist mir gestern Abend erst klar geworden. Die Reihenfolge zum Erfüllen der Where-Bedingungen besteht darin, zuerst die letzte Bedingung zu erfüllen, von rechts nach links. Nachdem ich den Index zum Testen gelöscht hatte, war es tatsächlich effektiv und reduzierte die Zeit von 6 Sekunden auf 4 Sekunden. Nachdem ich den Index optimiert hatte, testete ich erneut und stellte fest, dass die Auswirkung der Reihenfolge auf den Zeitverbrauch fast vernachlässigbar ist, 0,X Millisekunden.

ZWEI:

 * aus Artikel auswählen
 Innerer Join (
 SELECT id FROM article WHERE INSTR(ifnull(title,''),'战狼') > 0 und Status != 9
 Sortieren nach pub_time desc
 Grenze 100,10

 ) ALS USING(id);

Hmm – noch ein innerer Join …

INSTR(ifnull(title,''),'Wolf Warrior') > 0, warum nicht so verwenden......

1. Da es sich um eine Suche auf der Verwaltungsplattform handelt, wurde die Suchmaschine nicht durchsucht. Die Suchmaschine synchronisiert die Daten nur einmal pro Stunde, daher sind die Daten unvollständig. Bei der Suche interessieren sich Manager nur für die gewünschten Ergebnisse. Beispielsweise kann %XX% den Index nicht verwenden und die Effizienz ist 5-mal niedriger als bei instr. Ich habe auch den regulären Ausdruck '.*XX*.' getestet, der immer noch etwas länger dauert als bei instr. Also...

desc oder Explain, Filesort … fügen Sie einen Index zu pub_time hinzu und sehen Sie, ob es funktioniert, oder Filesort …

2. Für diese Situation gibt es eine andere Lösung: SELECT id FROM article force index(pub_time) um die Verwendung dieses Indexes anzugeben. Dieser Schreibweise mangelt es jedoch an Flexibilität, RAUS! Ich habe auf Baidu gesucht und jemand hat mir einen Rat gegeben: Erstellen Sie einen gemeinsamen Index für Status und pub_time (pub_time_status, mit der Bestellbedingung davor) und erzwingen Sie diesen Index automatisch, wenn Sie eine Where-Abfrage ausführen.

DREI:

Wählen Sie * aus Artikel, wo Status != 9, sortiert nach Veröffentlichungszeit, Abstiegslimit 100000,25;
desc oder explain, oder filesort..... Haben wir nicht vorher einen gemeinsamen Index für status und pub_time erstellt? Sag mir warum...

Nun, ich weiß es auch nicht. Erstelle einen weiteren gemeinsamen Index status_pub_time für status und pub_time. Diesmal steht die Where-Bedingung vorne und es gibt keine Dateisortierung in Explain, aber dieser Index wird nicht verwendet. Er hängt pub_time_status aus. Ich verstehe nicht

Gleichzeitig habe ich das SQL von TWO erklärt, das wie folgt lautet:

Das Löschen eines dieser beiden funktioniert nicht. Wenn Sie eines löschen, führt SQL eine Dateisortierung durch!

VIER:

SELECT * aus folgen
 wobei (((Status aus Quelle auswählen, wobei id=follow.source_id)=1 und follow.type=1) oder ((Status aus Thema auswählen, wobei id=follow.source_id)=1 und follow.type=2)) UND user_id=10054
 ORDER BY Sortiergrenze 15,15;
 Wählen Sie * aus dem folgenden inneren Join(
 Wählen Sie die ID aus „Follow“ aus.
 wobei (((Status aus Quelle auswählen, wobei id=follow.source_id)=1 und follow.type=1) oder ((Status aus Thema auswählen, wobei id=follow.source_id)=1 und follow.type=2)) UND user_id=10054
 ORDER BY Sortiergrenze 15,15
 ) als t mit (id);
 (SELECT id, source_id, user_id, temporary, sort, follow_time, read_time, type from follow wobei (SELECT status FROM source WHERE id=follow.source_id)=1 und follow.type=1 und user_id=10054)
 Vereinigung alle
 (WÄHLEN Sie ID, Quell-ID, Benutzer-ID, temporär, Sortierung, Follow-Zeit, Lesezeit, Typ aus „Follow“, wobei (Status aus Thema auswählen, WO ID=follow.source_id)=1 und follow.type=2 und Benutzer-ID=10054)
 ORDER BY Sortiergrenze 15,15;

Schauen Sie sich diese drei SQL-Anweisungen an. Interessant, nicht wahr?

Der Fairness halber habe ich den Index user_id_sort(user_id,sort) optimiert, sodass where user_id verwendet, um diesen Index zu erzwingen.

Erster Satz: 0,48 ms

Zweiter Satz: 0,42 ms

Der dritte Satz: 6 ms. Der Grund, warum es so lange dauert, ist, dass nach der Vereinigung (zweimaliges Abfragen der Tabelle und Zusammenführen in eine Untertabelle) der Index nicht verwendet werden kann, um die Sortierung der Reihenfolge abzudecken.

Manchmal ist Union nicht unbedingt schneller als Oder.

Zusammenfassen

Das Obige ist die vom Herausgeber geteilte Erfahrung mit der MySQL-Big-Data-Abfrageoptimierung. Ich hoffe, es wird allen helfen. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und der Herausgeber wird Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken!