Detaillierte Erläuterung der Mysql-Funktionsaufrufoptimierung

Funktionsaufrufoptimierung

MySQL-Funktionen werden intern als deterministisch oder nichtdeterministisch gekennzeichnet. Wenn eine Funktion mit festen Werten für ihre Argumente bei verschiedenen Aufrufen unterschiedliche Ergebnisse zurückgeben kann, ist sie undefiniert. Beispiele für nichtdeterministische Funktionen: RAND() , UUID() .

Wenn eine Funktion als nicht deterministisch gekennzeichnet ist, werden Verweise auf die Funktion in einer WHERE Klausel für jede Zeile (bei der Auswahl aus einer Tabelle) oder Zeilenkombination (bei der Auswahl aus einem Join mehrerer Tabellen) ausgewertet.

MySQL bestimmt außerdem anhand des Typs seiner Argumente (ob es sich bei den Argumenten um Tabellenspalten oder konstante Werte handelt), wann eine Funktion ausgewertet werden soll. Immer wenn sich der Wert einer Tabellenspalte ändert, muss die deterministische Funktion ausgewertet werden, die die Tabellenspalte als Argument verwendet.

Nicht-deterministische Funktionen können die Abfrageleistung beeinträchtigen. Beispielsweise könnten einige Optimierungen nicht verfügbar sein oder es könnten mehr Sperren erforderlich sein. Die folgende Diskussion verwendet RAND() , ist aber auch auf andere Unsicherheitsfunktionen anwendbar.

Angenommen, eine Tabelle t hat die folgende Definition:

Tabelle erstellen t (ID INT nicht NULL Primärschlüssel, Col_a VARCHAR (100));

Betrachten Sie die folgenden beiden Abfragen:

Wählen Sie * aus t, wobei id = POW (1,2);
Wählen Sie * aus t, wobei id = FLOOR (1 + RAND () * 49);

Aufgrund des Gleichheitsvergleichs mit dem Primärschlüssel scheinen beide Abfragen die Primärschlüsselsuche zu verwenden, dies gilt jedoch nur für die erste Abfrage:

• Die erste Abfrage erzeugt immer höchstens eine Zeile, da POW() mit einem konstanten Argument einen konstanten Wert annimmt und für Indexsuchen verwendet wird.
• Die zweite Abfrage enthält einen Ausdruck mit einer nichtdeterministischen Funktion, RAND() die in der Abfrage keine Konstante ist, sondern tatsächlich für jede Zeile der Tabelle einen neuen Wert t hat. Daher liest die Abfrage jede Zeile der Tabelle, wertet das Prädikat für jede Zeile aus und gibt alle Zeilen aus, bei denen der Primärschlüssel mit dem Zufallswert übereinstimmt. Abhängig vom ID-Spaltenwert und den Werten in der RAND()-Sequenz können dies null, eine oder mehrere Zeilen sein.
  

Die Auswirkungen des Nichtdeterminismus sind nicht auf SELECT -Anweisungen beschränkt. Diese UPDATE -Anweisung verwendet eine nichtdeterministische Funktion, um die zu ändernden Zeilen auszuwählen:

UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);

Vermutlich besteht die Absicht darin, höchstens eine Zeile zu aktualisieren, bei der der Primärschlüssel mit dem Ausdruck übereinstimmt. Es können jedoch null, eine oder mehrere Zeilen aktualisiert werden, abhängig vom Wert der id -Spalte und den Werten in RAND() Sequenz.

Das gerade beschriebene Verhalten hat Auswirkungen auf Leistung und Replikation:

• Da eine nichtdeterministische Funktion keinen konstanten Wert erzeugt, kann der Optimierer keine anderen möglicherweise anwendbaren Strategien verwenden, wie z. B. Indexsuchvorgänge. Das Ergebnis kann ein Tabellenscan sein.
• Anstatt eine Einzelzeilensperre für eine übereinstimmende Zeile zu erwerben, kann InnoDB auf eine Bereichsschlüsselsperre aktualisieren.
• Es konnte nicht festgestellt werden, dass das durchgeführte Update für die Replikation unsicher ist.
  

Die Schwierigkeit ergibt sich aus der Tatsache, dass RAND() die Funktion einmal für jede Zeile der Tabelle auswertet. Um die Auswertung mehrerer Funktionen zu vermeiden, verwenden Sie eine der folgenden Techniken:

• Verschieben Sie Ausdrücke, die nichtdeterministische Funktionen enthalten, in separate Anweisungen und speichern Sie die Werte in Variablen. Ersetzen Sie in der Originalanweisung den Ausdruck durch einen Verweis auf eine Variable, die der Optimierer als konstanten Wert behandeln kann:

SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;

• Weisen Sie Variablen in einer abgeleiteten Tabelle Zufallszahlen zu. Bei dieser Technik wird der Variablen ein Wert zugewiesen, bevor sie in einem Vergleich in der WHERE Klausel verwendet wird:

Setzen Sie optimizer_switch = "derived_merge=off";
UPDATE t, (SELECT @keyval := FLOOR(1 + RAND() * 49)) AS dt
SET col_a = irgendein_Ausdruck WHERE id = @keyval;

Wie bereits erwähnt, können nichtdeterministische Ausdrücke in der WHERE Klausel eine Optimierung verhindern und einen Tabellenscan verursachen. Die WHERE Klausel kann jedoch teilweise optimiert werden, wenn die anderen Ausdrücke deterministisch sind. Zum Beispiel:

SELECT * FROM t WHERE Teilschlüssel=5 AND einige_Spalten=RAND();

Wenn der Optimierer partial_key verwenden kann, um die Menge der ausgewählten Zeilen zu reduzieren, wird RAND() seltener ausgeführt, was die Auswirkungen von Unsicherheiten auf die Optimierung verringern kann.

Oben finden Sie eine ausführliche Erläuterung der MySQL-Funktionsaufrufoptimierung. Weitere Informationen zur MySQL-Funktionsaufrufoptimierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!