Protokollieren Sie ein langsames Abfrageereignis, das durch eine Fehleinschätzung des Online-MySQL-Optimierers verursacht wurde.

Vorwort:

Ich habe Warnungen zu wahnsinnig langsamen Abfragen und Anforderungstimeouts erhalten. Ich habe die Anomalien von MySQL-Anfragen anhand von Metriken analysiert und in der Befehlszeile viele langsame Abfragen gesehen —> Prozedurliste anzeigen. Dieses SQL gab es vorher nicht, und dieses Problem trat später aufgrund der Zunahme des Datenvolumens auf. Obwohl die Feeds-Tabelle 100 Millionen groß ist, wird die häufige IO nicht durch eine ineffiziente innodb_buffer_pool_size verursacht, da die Feeds-Stream-Informationen die Eigenschaft haben, in letzter Zeit heiß zu sein. Später, nach einer weiteren Erläuterung der Ausführungsplananalyse, wurde der Grund herausgefunden. Der MySQL-Abfrageoptimierer wählte einen Index, den er für effizient hielt.

Der MySQL-Abfrageoptimierer ist in den meisten Fällen zuverlässig! Sie sollten jedoch vorsichtig sein, wenn Ihre SQL-Sprache mehrere Indizes enthält, da das Endergebnis oft etwas verwirrend ist. Da MySQL nur einen Index für dasselbe SQL verwenden kann, welchen sollte ich wählen? Wenn die Datenmenge gering ist, platziert der MySQL-Optimierer den Primärschlüsselindex am Ende und gibt dem Index und der Eindeutigkeit Priorität. Wenn Sie eine bestimmte Datenebene erreichen und Ihr Abfragevorgang abgeschlossen ist, wählt der MySQL-Abfrageoptimierer wahrscheinlich den Primärschlüssel!

Bedenken Sie eines: Die Optimierung von MySQL-Abfragen basiert auf Überlegungen zu den Abrufkosten und nicht zu den Zeitkosten. Der Optimierer berechnet den Aufwand auf Basis des vorhandenen Datenbestands, statt die SQL-Anweisung tatsächlich auszuführen.

Daher kann der MySQL-Optimierer nicht jedes Mal den Optimierungseffekt erzielen. Die Kosten können nicht genau geschätzt werden. Wenn Sie die Kosten für das Durchlaufen jedes Indexes genau ermitteln möchten, müssen Sie ihn tatsächlich einmal ausführen, um sie zu kennen. Daher ist die Kostenanalyse nur eine Schätzung, und da es sich um eine Schätzung handelt, kommt es zu Fehleinschätzungen.

Die Tabelle, über die wir hier sprechen, ist die Feed-Informationsflusstabelle. Wir wissen, dass die Feed-Informationsflusstabelle nicht nur häufig aufgerufen wird, sondern auch eine große Datenmenge enthält. Die Datenstruktur dieser Tabelle ist jedoch sehr einfach und der Index ist auch einfach. Es gibt insgesamt nur zwei Indizes, einer ist der Primärschlüsselindex und der andere ist der eindeutige Schlüsselindex.

Wie unten gezeigt, hat die Größe dieser Tabelle 100 Millionen erreicht. Da genügend Cache-Frontends vorhanden sind und aus verschiedenen Gründen keine Zeit bleibt, die Datenbank und die Tabellen aufzuteilen.

Das Problem besteht darin, dass der MySQL-Optimierer bei einer Datenmenge von weniger als 100 Millionen den Indexindex verwendet. Wenn die Datenmenge 100 Millionen überschreitet, verwendet der MySQL-Abfrageoptimierer den Primärschlüsselindex. Das Problem dabei ist, dass die Abfragegeschwindigkeit zu langsam ist.

Dies ist die normale Situation:

mysql> erklären SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377) AND cid IN (1001,1005,1054,1092,1093,1095) AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
*************************** 1. Reihe ***************************
      ID: 1
 select_type: EINFACH
    Tabelle: Futter
  Partitionen: NULL
     Typ: Bereich
mögliche Schlüssel: PRIMARY, Feed-Benutzerziel
     Schlüssel: feed_user_target
   Schlüssellänge: 6
     Ref: NULL
     Reihen: 18
   gefiltert: 50,00
    Extra: Verwenden von „where“; Verwenden von „index“; Verwenden von „filesort“
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Wenn sich bei derselben SQL-Anweisung die Datenmenge erheblich ändert, ändert sich auch die Indexauswahl des MySQL-Abfrageoptimierers.

mysql> erklären SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377) AND cid IN (1001,1005,1054,1092,1093,1095) AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
*************************** 1. Reihe ***************************
      ID: 1
 select_type: EINFACH
    Tabelle: Futter
     Typ: Bereich
mögliche Schlüssel: PRIMARY, Feed-Benutzerziel
     Schlüssel: PRIMARY
   Schlüssellänge: 4
     Ref: NULL
     Reihen: 11873197
    Extra: Verwenden von „where“
1 Zeile im Satz (0,00 Sek.)

Die Lösung besteht darin, den Abfrageoptimierer durch die Verwendung eines Force-Index zu zwingen, den von uns angegebenen Index zu verwenden. Ich verwende hier eine Python-Entwicklungsumgebung. Gängige Python-ORMs verfügen über Force-Index-, Ignore-Index- und User-Index-Parameter.

explain SELECT * FROM `feed` force index (feed_user_target) WHERE user_id IN (116537309,116709093,116709377) ...

Wie können wir also das Problem verhindern, dass der MySQL-Optimierer aufgrund der Datenzunahme einen ineffizienten Index wählt?

Ich habe zu diesem Problem mehrere DBAs aus unterschiedlichen Fabriken konsultiert und die Antworten, die sie erhielten, stimmten mit unserer Methode überein. Das Problem kann nur durch langsame Abfragen im späteren Stadium entdeckt werden. Anschließend wird in der SQL-Anweisung ein Force-Index angegeben, um das Indexproblem zu lösen. Darüber hinaus können derartige Probleme in der frühen Phase der Systemeinführung vermieden werden. Allerdings arbeiten Geschäftsentwickler bei der anfänglichen Überprüfungsarbeit häufig mit DBAs zusammen. In der späteren Phase kommt es jedoch zu MySQL-Abfrageunfällen, um Ärger zu vermeiden, oder sie denken, es gebe kein Problem.

Ich habe nur eine vage Vorstellung von den Indexauswahlregeln des MySQL-Optimierers und habe vor, später etwas Zeit darauf zu verwenden, diese Regeln zu studieren.