Die 10 klassischen Optimierungsfälle und -szenarien von MySQL

1. Allgemeine Schritte zur SQL-Optimierung

Lokalisieren Sie SQL-Anweisungen mit geringer Ausführungseffizienz, indem Sie Protokolle usw. langsam überprüfen.

1. Erklären Sie die Analyse des SQL-Ausführungsplans

Sie müssen besonders type , rows , filtered und extra achten.

Von oben nach unten tippen, die Effizienz wird immer höher

• ALLE Vollständiger Tabellenscan
• Vollständiger Index-Scan
• Bereichsindex-Bereichsscan, häufig verwendete Begriffe <, <=, >=, zwischen, in und andere Operationen
• ref verwendet einen nicht eindeutigen Index-Scan oder einen eindeutigen Index-Präfix-Scan, um einen einzelnen Datensatz zurückzugeben, der häufig in einer relationalen Abfrage vorkommt.
• eq_ref ist ähnlich wie ref, außer dass es einen eindeutigen Index und einen Primärschlüssel für zugehörige Abfragen verwendet.
• const/system Einzelner Datensatz, das System behandelt andere Spalten in der übereinstimmenden Zeile als Konstanten, z. B. Primärschlüssel oder eindeutige Indexabfrage
• null MySQL greift nicht auf Tabellen oder Indizes zu und gibt das Ergebnis direkt zurück

Obwohl die Effizienz von oben nach unten immer höher wird, lautet das SQL gemäß dem Kostenmodell: Angenommen, es gibt zwei Indizes idx1 (a, b, c) und idx2 (a, c). Dann lautet es „select * from t where a = 1 and b in (1, 2) order by c“; wenn idx1 verwendet wird, ist der Typ range, wenn idx2 verwendet wird, ist der Typ ref; wenn die Anzahl der zu scannenden Zeilen etwa fünfmal größer als idx1 ist, wird idx1 verwendet, andernfalls wird idx2 verwendet.

Extra

• Verwenden von Filesort: MySQL benötigt einen zusätzlichen Durchlauf, um herauszufinden, wie die Zeilen in sortierter Reihenfolge abgerufen werden. Die Sortierung erfolgt, indem alle Zeilen entsprechend dem Verknüpfungstyp durchgegangen werden und der Sortierschlüssel und die Zeilenzeiger für alle Zeilen gespeichert werden, die der WHERE-Klausel entsprechen. Anschließend werden die Schlüssel sortiert und die Zeilen in sortierter Reihenfolge abgerufen.
• Temporäre Tabellen verwenden: Temporäre Tabellen werden zum Speichern von Zwischenergebnissen verwendet . Die Leistung ist besonders schlecht und muss optimiert werden.
• Index verwenden: Gibt an, dass bei der entsprechenden select Coveing Index verwendet wird, wodurch der Zugriff auf die Datenzeilen der Tabelle vermieden wird. Dies ist sehr effizient! Die gleichzeitige Verwendung von „where“ bedeutet, dass die Daten, die die Bedingungen erfüllen, nicht direkt über die Indexsuche abgefragt werden können.
• Indexbedingung verwenden: ICP wurde nach MySQL5.6 hinzugefügt. using index condtion bedeutet, dass ICP (Index Push Down) verwendet wird, um Daten auf der Speichermodulebene statt auf der Serviceebene zu filtern, und dass die vorhandenen Daten im Index verwendet werden, um die an die Tabelle zurückgegebenen Daten zu reduzieren.

2. Profilanalyse anzeigen

Informieren Sie sich über den Status von SQL-Ausführungsthreads und die dafür benötigte Zeit.
Die Standardeinstellung ist „Aus“. Aktivieren Sie die Anweisung „set profiling = 1;“.

PROFILE ANZEIGEN;
PROFIL FÜR ABFRAGE #{id} ANZEIGEN;

3. Spur

Der Trace-Analysator verwendet die Trace-Datei, um zu analysieren, wie der Optimierer einen Ausführungsplan auswählt. Die Trace-Datei kann verwendet werden, um besser zu verstehen, warum der Coupon Ausführungsplan A statt Ausführungsplan B auswählt.

Setzen Sie optimizer_trace="enabled=on";
setze optimizer_trace_max_mem_size=1000000;
Wählen Sie * aus information_schema.optimizer_trace;

4. Identifizieren Sie das Problem und ergreifen Sie entsprechende Maßnahmen

• Indizes optimieren
• Optimieren Sie SQL-Anweisungen: Ändern Sie SQL, IN-Abfragesegmentierung, Zeitabfragesegmentierung, Filtern basierend auf den letzten Daten
• Verwenden Sie andere Implementierungsmethoden: ES, Data Warehouse usw.
• Datenfragmentierungsverarbeitung

2. Szenarioanalyse (Fallstudie)

1. Ganz linke Übereinstimmung

Index

SCHLÜSSEL `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL-Anweisungen

Wählen Sie * aus _t, wobei Bestellnummer = ''

Die Abfrage wird von links nach rechts gematcht. Um den Index order_no zu verwenden, muss die Abfragebedingung shop_id enthalten oder der Index (shop_id, order_no) muss vertauscht werden.

2. Implizite Konvertierung

Index

SCHLÜSSEL `idx_mobile` (`mobile`)

SQL-Anweisungen

Wählen Sie * von _user, wobei mobile=12345678901

Eine implizite Konvertierung ist gleichbedeutend mit der Durchführung von Operationen am Index, die den Index ungültig machen. Mobile ist ein Zeichentyp. Wenn eine Zahl verwendet wird, sollte eine Zeichenfolgenübereinstimmung verwendet werden. Andernfalls verwendet MySQL implizite Ersetzung, was zu einem Indexfehler führt.

3. Große Seiten

Index

SCHLÜSSEL `idx_a_b_c` (`a`, `b`, `c`)

SQL-Anweisungen

wähle * aus _t, wobei a = 1 und b = 2, sortiere nach c, desc-Limit 10000, 10;

Bei umfangreichen Paging-Szenarien können Sie die Produktoptimierungsanforderungen priorisieren. Wenn keine Optimierung erfolgt, gibt es zwei Optimierungsmethoden:

Eine Methode besteht darin, das letzte Datenstück, also das oben genannte c, zu übergeben und dann eine „ c < xxx “-Verarbeitung durchzuführen. Dies erfordert jedoch im Allgemeinen eine Änderung des Schnittstellenprotokolls und ist möglicherweise nicht durchführbar.

Eine andere Methode besteht darin, die SQL-Tabellenrückgabe durch verzögerte Zuordnung zu reduzieren. Denken Sie jedoch daran, dass der Index vollständig abgedeckt sein muss, um wirksam zu sein. Die SQL-Änderungen sind wie folgt

wähle t1.* aus _t t1, (wähle ID aus _t, wobei a = 1 und b = 2, sortiere nach c, Beschreibungsgrenze 10000, 10) t2, wobei t1.id = t2.id;

4. in + sortieren nach

Index

SCHLÜSSEL `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

SQL-Anweisungen

wähle * aus _order, wobei shop_id = 1 und order_status in (1, 2, 3) sortieren nach created_at desc limit 10

Im zugrunde liegenden MySQL sucht die in in einer n*m-Weise, die ähnlich wie union ist, aber effizienter als union .
Wenn die in Abfrage cost berechnet (Kosten = Anzahl der Tupel * durchschnittlicher IO Wert), wird die Anzahl der Tupel ermittelt, indem die in enthaltenen Werte nacheinander abgefragt werden. Daher ist dieser Berechnungsprozess relativ langsam. Daher legt MySQL einen kritischen Wert fest ( eq_range_index_dive_limit ). Nach 5.6 werden die Kosten der Spalte nicht mehr berechnet, wenn dieser kritische Wert überschritten wird. Dies kann zu einer ungenauen Auswahl des Ausführungsplans führen. Der Standardwert ist 200. Das heißt, wenn die In-Bedingung mehr als 200 Daten enthält, können Probleme bei der Kostenberechnung von In auftreten und der von MySQL ausgewählte Index ist möglicherweise ungenau.

Lösung: Sie können互換前后順序( order_status, created_at ) vertauschen und das SQL auf verzögerte Zuordnung anpassen.

5. Bereichsabfrage ist blockiert und nachfolgende Felder können nicht indiziert werden

Index

SCHLÜSSEL `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

SQL-Anweisungen

Wählen Sie * aus _order, wobei shop_id = 1 und created_at > '2021-01-01 00:00:00' und order_status = 10

Bereichsabfragen umfassen auch " IN、between "

6. Ungleich, nicht enthalten, schnelle Suche, die keine Indizes verwenden kann

ICP kann verwendet werden

wähle * aus _order, wobei shop_id=1 und order_status nicht in (1,2)
Wählen Sie * aus _order, wobei shop_id=1 und order_status != 1

Vermeiden Sie die Verwendung NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE in Indizes.

7. Wenn der Optimierer sich entscheidet, keinen Index zu verwenden

Wenn die Datenmenge, auf die zugegriffen werden muss, gering ist, wählt der Optimierer dennoch den Hilfsindex. Wenn die zugegriffenen Daten jedoch einen großen Teil der Daten in der gesamten Tabelle ausmachen (normalerweise etwa 20% ), sucht der Optimierer die Daten über den gruppierten Index.

Wählen Sie * aus _order, wobei order_status = 1 ist.

Abfrage aller unbezahlten Bestellungen. Im Allgemeinen gibt es sehr wenige solcher Bestellungen, sodass ein Index, selbst wenn er erstellt wird, nicht verwendet werden kann.

8. Komplexe Abfragen

Wählen Sie Summe(amt) aus _t, wobei a = 1 und b in (1, 2, 3) und c > „2020-01-01“;
wähle * aus _t, wobei a = 1 und b in (1, 2, 3) und c > '2020-01-01', Grenze 10;

Wenn Sie bestimmte Daten zählen müssen, können Sie zur Lösung des Problems ein Data Warehouse verwenden.

Wenn die Geschäftsabfrage so komplex ist, empfiehlt es sich möglicherweise nicht, weiterhin SQL zu verwenden, sondern andere Methoden zur Lösung zu nutzen, beispielsweise ES.

9. Asc und Desc mischen

wähle * aus _t, wobei a=1, sortiere nach b desc, c asc

Das Mischen von desc und asc führt zu Indexfehlern

10. Große Daten

Bei der Datenspeicherung von Push-Diensten kann die Datenmenge sehr groß sein. Wenn Sie sich für eine Lösung entscheiden, können Sie sich eventuell dafür entscheiden, sie auf MySQL zu speichern und für eine Gültigkeitsdauer von 7 Tagen zu speichern.

Dann müssen Sie darauf achten , dass häufiges Bereinigen der Daten zu einer Datenfragmentierung führt und Sie müssen sich zur Behebung der Datenfragmentierung an den DBA wenden.

Damit ist dieser Artikel über die 10 wichtigsten klassischen MySQL-Optimierungsszenarien abgeschlossen. Weitere relevante MySQL-Optimierungsszenarien finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!