Frage Umgang mit dem MySQL Temp Table-Algorithmus für Sichten


Es wäre schön, wenn der Temp-Tabellenalgorithmus in einen unskalierbaren Algorithmus umbenannt würde. Vielleicht würde es Entwicklern dann eine größere Warnung bieten, wenn sie dies in einer Ansichtsdefinition sehen - ähnlich, wenn es heißt, die Tempentabelle in den EXPLAIN-Ergebnissen zu verwenden. Nur eine Augenweide Anfrage zum größten Teil, aber wirklich kann es für die Unwissenheit katastrophal sein.

Das Problem ist, dass wenn Sie bestimmte Dinge in Ihrer Sichtdefinition tun, es von dem normalen Merge-Algorithmus auf den hoffnungslos ineffizienten Temp-Tabellenalgorithmus umschalten wird. Dies ist keine große Sache, wenn die beteiligten Daten klein sind. Aber es ist eines dieser Dinge, die Ihre Leistung zunichte machen werden, wenn Ihre Daten wachsen.

Wie geht man am besten damit um? Es war ein Problem, da Ansichten vor mehr als 5 Jahren eingeführt wurden und ich keine Bemühungen kenne, es zu beheben. Tritt dieses Problem in anderen gängigen Datenbanksystemen auf?

Scrollen Sie im folgenden Link nach unten, um zu erläutern, wo der Merge-Algorithmus nicht verwendet werden kann, um zu sehen, warum MySQL mit dem schrecklichen Temp-Table-Algorithmus degeneriert: http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/create-view.html

Was ist daran so schlimm? Der temporäre Tabellenalgorithmus funktioniert folgendermaßen:

  1. Führen Sie die Ansicht "AS IS" in der Ansichtsdefinition aus, ohne die Kriterien für die Where-Klausel der Abfrage darin zusammenzuführen.
  2. Dump die resultierenden Daten in eine temporäre Tabelle.
  3. Filtern Sie die Daten in der temporären Tabelle basierend auf den Where-Klausel-Kriterien der Abfrage - auch hier keine Indizes.

Sie können sich also vorstellen, was zur Hölle hier im Spiel ist, wenn Sie einen 50 Millionen Zeilen-Tisch mit einer Sichtdefinition wie - dummes Beispiel haben, aber Sie bekommen den Punkt:

create view last_order_date as 
    select max(order_date) last_date, username from orders group by username;

Ein Benutzer könnte dann schreiben:

select * from last_order_date where username = 'joejoe22' 

2 Stunden später wird das Ergebnis zurückgegeben ...

Wie kann man am besten mit dieser Situation umgehen?


5
2018-05-28 18:22


Ursprung


Antworten:


Schreiben Sie eine gespeicherte Prozedur, die eine Ergebnismenge zurückgibt.

DELIMITER $$

CREATE PROCEDURE DoViewMyData(Param1 INT)
BEGIN
  SELECT 
    field1 as x
    ,field2 as y
    FROM table
    WHERE field1 = Param1;
END$$

DELIMITER ;

Rufen Sie die Prozedur jetzt auf, es wird die Ergebnismenge der SELECT-Anweisung zurückgeben.

CALL DoViewMyData(1);

OUTPUT:
+------+-------------------+
| x    | y                 |
+------+-------------------+
| 1    | balabablabla      |
+------+-------------------+

Mach einfach CALL, nicht so SELECT CALL das funktioniert nicht.
Du kannst auch nicht benutze die CALL in einer anderen SELECT-Anweisung.
Sie können natürlich die gespeicherte Prozedur anweisen, die Ausgabe in eine temporäre Tabelle zu setzen und diese in einem anderen SELECT zu verwenden.

Die gespeicherte Prozedur verwendet korrekte Indizes und Ihre SELECT-Anweisung ist bereits vorbereitet.
Außerdem können Sie in vorhergehenden Anweisungen Daten vorbereiten und Ihre zeitkritische Abfrage beschleunigen.

Du hast jedoch Recht: MySQL-Ansichten sind sicher scheiße


3
2018-04-15 17:34