MySQL-Abfrageoptimierung mit benutzerdefinierten Variablen

Optimieren von Sortierabfragen

Ein wichtiges Merkmal benutzerdefinierter Variablen besteht darin, dass Sie der Variablen gleichzeitig das Ergebnis mathematischer Berechnungen zuweisen können, ähnlich wie bei unserer Methode i = i + 1. Hier ist ein Beispiel zur Berechnung der Zeilennummer einer Datentabelle:

SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS rownum
VON sakila.actor LIMIT 3;

Das Ergebnis scheint möglicherweise bedeutungslos, da der Primärschlüssel von 1 an erhöht wird, sodass die Zeilennummer und der Primärschlüsselwert identisch sind. Allerdings kann diese Methode zum Sortieren verwendet werden. Wenn Sie beispielsweise die Top 10 der Schauspieler abfragen müssen, die in den meisten Filmen mitgespielt haben, lautet die übliche Vorgehensweise wie folgt:

WÄHLEN Sie actor_id, COUNT(*) als cnt
VON sakila.film_actor
GRUPPE NACH Schauspieler-ID
ORDER BY Anzahl DESC
GRENZE 10;

Das Ergebnis scheint möglicherweise bedeutungslos, da der Primärschlüssel von 1 an erhöht wird, sodass die Zeilennummer und der Primärschlüsselwert identisch sind. Allerdings kann diese Methode zum Sortieren verwendet werden. Wenn Sie beispielsweise die Top 10 der Schauspieler abfragen müssen, die in den meisten Filmen mitgespielt haben, lautet die übliche Vorgehensweise wie folgt:

WÄHLEN Sie actor_id, COUNT(*) als cnt
VON sakila.film_actor
GRUPPE NACH Schauspieler-ID
ORDER BY Anzahl DESC
GRENZE 10;

Wenn wir den entsprechenden Rankingwert erhalten möchten, können wir zur Vervollständigung Variablen einführen:

SETZEN Sie @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
Wählen Sie Schauspieler-ID aus,
	@curr_cnt := cnt ALS cnt,
  @rank := WENN(@prev_cnt <> @curr_cnt, @rank+1, @rank) als Rang,
  @prev_cnt := @curr_cnt AS Dummy
AUS (
  Wählen Sie Schauspieler-ID, COUNT(*) AS cnt
  VON sakila.film_actor
	GRUPPE NACH Schauspieler-ID
	ORDER BY Anzahl DESC
	GRENZE 10
) als der;

Dabei wird der Variable curr_cnt die Anzahl der gespielten Filme zugewiesen und in prev_cnt die Anzahl der vom vorherigen Schauspieler gespielten Rollen gespeichert. Die Rangfolge beginnt beim ersten Platz. Wenn die Anzahl der Akteure dahinter nicht mit der Anzahl der Akteure auf dem vorherigen Platz übereinstimmt, wird die Rangfolge herabgesetzt (+1). Wenn sie gleich sind, wird die Rangfolge mit der des vorherigen Akteurs identisch sein. Auf diese Weise kann das Ranking des Akteurs direkt aus den Abfrageergebnissen gewonnen werden, ohne dass eine sekundäre Verarbeitung der Datenbankabfrage erforderlich ist (dies kann natürlich auch über Programmcode erreicht werden).

Vermeiden Sie das wiederholte Abrufen der gerade geänderten Datenzeile

Wenn Sie beim Aktualisieren der Datenzeile die Informationen der Datenzeile erneut abrufen möchten, ist häufig ein erneutes Lesen der Datenbank erforderlich. Dies liegt daran, dass MySQL aktualisierte Datenzeilen nicht gleichzeitig zurückgibt wie die UPDATE RETURNING-Funktion von PostgreSQL, sondern nur die Anzahl der von der Aktualisierung betroffenen Zeilen zurückgibt. Wir können dies jedoch mit benutzerdefinierten Variablen tun. Um beispielsweise die Zeile abzurufen, deren Aktualisierungszeitpunkt gerade geändert wurde, ist eine zusätzliche Abfrage ohne Verwendung benutzerdefinierter Variablen erforderlich:

UPDATE tb1 SET lastUpdated = JETZT() WO id = 1;
Wählen Sie lastUpdated FROM tb1 WHERE id = 1;

Dies kann durch die Verwendung benutzerdefinierter Variablen vermieden werden:

UPDATE tb1 SET lastUpdated = JETZT() WO id = 1 UND @now := JETZT();
WÄHLEN SIE @jetzt;

Obwohl immer noch eine Abfrageoperation vorliegt, müssen nachfolgende Abfrageoperationen nicht mehr auf die Datenbank zugreifen.

Lazy-Loaded-Join-Abfrage

Angenommen, wir müssen eine Union-Abfrage schreiben, um die folgende Aufgabe zu erledigen: passende Datenzeilen in den Zweigen der Union finden und andere Zweige überspringen, wenn welche gefunden werden. Dies geschieht, wenn Sie nach wichtigen Daten oder selten abgerufenen Daten (wie etwa aktuellen oder historischen Bestellungen) suchen müssen. Nachfolgend sehen Sie ein allgemeines SQL für Benutzerabfragen:

Wählen Sie ID aus Benutzern, wobei ID = 123 ist.
UNION ALLE
Wählen Sie ID aus den Benutzerarchivierungsdateien aus, wobei ID = 123 ist.

Diese Abfrage fragt zuerst den Benutzer mit der ID 123 aus der aktuell verwendeten Benutzertabelle ab und sucht dann den Benutzer mit derselben ID in der archivierten Benutzertabelle. Allerdings ist diese Schreibweise ineffizient. Selbst wenn der gewünschte Benutzer in der Tabelle users gefunden wird, muss dennoch erneut in der Tabelle users_archived gesucht werden. Die eigentliche Benutzer-ID 123 existiert entweder nur in einer der Tabellen oder die Daten in beiden Tabellen sind identisch. Diese Situation kann durch die Verwendung von Lazy-Loading-Joinabfragen vermieden werden – der zweite Zweig wird nur abgefragt, wenn im ersten Zweig keine Daten gefunden werden. Daher können Sie die GREATEST-Methode von MySQL als Container für Abfrageergebnisse verwenden, um die Rückgabe mehrerer Datenspalten zu vermeiden.

SELECT GREATEST(@found := -1, id) AS id, users.name, 'users' als which_tb1
VON Benutzern, WO ID = 123
UNION ALLE
	SELECT-ID, Benutzer_archiviert.Name, 'Benutzer_archiviert'
  VON users_archived, WO id = 123 UND @found IST NULL
UNION ALLE
	Wählen Sie 1, '', 'Zurücksetzen' von Dual, wobei (@found: = NULL) nicht NULL ist;

Wenn die erste Zeile der obigen Abfrage Ergebnisse enthält, wird @found kein Wert zugewiesen und es ist NULL, wodurch die zweite Abfrage ausgeführt wird. Die dritte UNION hat eigentlich keine Auswirkung. Sie dient lediglich dazu, @found auf NULL zurückzusetzen, damit dieses SQL wiederholt ausgeführt werden kann. Eine andere Möglichkeit zur Überprüfung besteht darin, einen solchen Vorgang für dieselbe Tabelle auszuführen und festzustellen, dass tatsächlich nur eine Datenzeile zurückgegeben wird oder dass keine Daten zurückgegeben werden (wenn keine Daten gefunden werden).

SELECT GREATEST(@found := -1, `id`) AS `id`, `infocenter_city`.`name`, 'Stadt' als which_tb1 
VON `infocenter_city` WO `id` = 460100 
UNION ALLE 
	Wählen Sie `id`, `infocenter_city`.`name`, 'infocenter_city' 
	VON `infocenter_city`, WO id = 460100 UND @found NULL IST 
UNION ALLE 
	Wählen Sie 1, '', 'reset' von Dual, wobei ( @found := NULL) nicht NULL ist

Oben finden Sie Einzelheiten zur Verwendung benutzerdefinierter Variablen in MySQL zur Abfrageoptimierung. Weitere Informationen zur Verwendung benutzerdefinierter Variablen in MySQL zur Abfrageoptimierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!