Heading

In diesem Artikel konzentrieren wir uns auf die einfachsten und besten Tipps, die Entwickler für ihr tägliches SQL-Performance-Tuning umsetzen können. Dieser Artikel konzentriert sich auf das Performance-Tuning von MySQL-Abfragen, aber die gleichen Konzepte lassen sich auch auf viele andere relationale Datenbanken anwenden.

Heute ist es wichtiger denn je, dass Software-Ingenieure über umfassende Kenntnisse im SQL-Performance-Tuning verfügen.
Der Wandel vollzieht sich sowohl in kleinen Startups als auch in großen Unternehmen. Heutzutage sind die Entwickler diejenigen, die die SQL-Abfragen und die Datenbankzugriffsschicht schreiben.

Es spielt keine Rolle, ob Sie eine Datenbankabstraktionsschicht (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django oder andere) verwenden oder native SQL-Abfragen schreiben, Sie werden irgendwann mit dem Tuning der Abfragen, die Sie an Ihre Datenbank senden, konfrontiert.

Was können Sie also tun, um Ihre SQL-Abfragen zu optimieren?

Erstellen Sie Indizes, aber tun Sie es mit Bedacht

Das Indizieren ist wahrscheinlich der wichtigste Teil des Abfrage-Tuning-Prozesses. Stellen Sie also zunächst sicher, dass Sie mit den verschiedenen Aspekten vertraut sind, die Sie bei der Auswahl der optimalen Indizes für Ihre Datenbank berücksichtigen sollten.

Wenn Sie darüber nachdenken, welche Indizes erstellt werden sollen, sollten Sie der WHERE-Klausel und den Tabellen-JOINs der Abfrage besondere Aufmerksamkeit schenken, da diese Anweisungen die kritischen indexierbaren Teile der Abfrage enthalten.

Außerdem können große Engpässe in den GROUP BY- und ORDER BY-Teilen entstehen. Das heißt, ein potenzieller Engpass ist, dass Sie diese in manchen Fällen nicht indizieren können, wie wir hier erklärt haben. Daher müssen Sie möglicherweise das Design Ihrer Abfrage überdenken, bevor Sie die Indizes erstellen, um sicherzustellen, dass Sie großartige Abfragen schreiben, aber auch Abfragen, die indiziert werden können.

Wenn Sie die Indizierung für eine Abfrage herausgefunden haben, sollten Sie nicht dabei stehen bleiben. Erweitern Sie Ihren Blick und schauen Sie sich andere wichtige Abfragen in Ihrer Anwendung an. Stellen Sie sicher, dass Sie Indizes kombinieren, wann immer es möglich ist, und entfernen Sie Indizes, die nicht verwendet werden. Der Blick auf den gesamten Anwendungsbereich ist immer besser als der Blick auf den Bereich einer einzelnen Abfrage.

Sie sollten auch bedenken, dass das Anlegen von mehr Indizes als benötigt auch nach hinten losgehen kann, da sie Schreiboperationen (wie INSERT / UPDATE-Anweisungen) verlangsamen können. Erstellen Sie also Indizes, um die Leistung Ihrer SQL-Abfragen zu optimieren, aber tun Sie es mit Bedacht.

Stehen Sie Indizes nicht im Weg

Wir werden oft von Kunden angesprochen, die uns fragen, „warum die Datenbank meinen Index nicht nutzt?“. Nun, das ist eine großartige Frage, mit endlosen möglichen Antworten. Aber in diesem Artikel werden wir versuchen, einige häufige Szenarien abzudecken, die wir oft sehen, so dass Sie sie hoffentlich für Ihren eigenen Anwendungsfall nützlich finden.

Beispiel 1 – Vermeiden Sie es, indizierte Spalten mit Funktionen zu umhüllen

Betrachten Sie diese Abfrage, die die Anzahl der im Jahr 2018 in den USA gekauften Hot Dogs zählt. Nur für den Fall, dass Sie neugierig sind: 18.000.000.000 Hot Dogs wurden im Jahr 2018 in den USA verkauft.

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE YEAR(purchase_time) = ‘2018’

Wie Sie sehen, verwenden wir die Funktion YEAR, um den Jahresanteil aus der Spalte purchase_time zu holen. Dieser Funktionsaufruf verhindert, dass die Datenbank einen Index für die Suche in der Spalte „purchase_time“ verwenden kann, weil wir den Wert von „purchase_time“ indiziert haben, aber nicht den Rückgabewert von YEAR(purchase_time).

Um diese Herausforderung zu überwinden und diese SQL-Abfrage zu optimieren, können Sie das Ergebnis der Funktion indizieren, indem Sie „Generated Columns“ verwenden, die ab MySQL 5.7.5 verfügbar sind.

Eine andere Lösung kann darin bestehen, einen alternativen Weg zu finden, dieselbe Abfrage zu schreiben, ohne den Funktionsaufruf zu verwenden. In diesem Beispiel können wir diese Bedingung in eine 2-Wege-Bereichsbedingung umwandeln, die die gleichen Ergebnisse liefert:

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE purchased_at >= '2018-01-01' AND purchased_at < '2019-01-01'

Beispiel #2 – OR-Bedingungen vermeiden

Betrachten Sie diese Abfrage, die die Anzahl der Beiträge auf Facebook auswählt, die nach Silvester gepostet wurden oder von einem Benutzer namens Mark gepostet wurden.

SELECT COUNT(*)FROM fb_postsWHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

Ein Index auf den Spalten „username“ und „post_time“ zu haben, mag hilfreich klingen, aber in den meisten Fällen wird die Datenbank ihn nicht verwenden, zumindest nicht vollständig. Der Grund dafür ist die Verbindung zwischen den beiden Bedingungen – der OR-Operator, der die Datenbank dazu bringt, die Ergebnisse jedes Teils der Bedingung separat zu holen.

Eine alternative Möglichkeit, diese Abfrage zu betrachten, kann darin bestehen, die OR-Bedingung zu „teilen“ und mit einer UNION-Klausel zu „kombinieren“. Diese Alternative ermöglicht es Ihnen, jede der Bedingungen separat zu indizieren, so dass die Datenbank die Indizes verwendet, um nach den Ergebnissen zu suchen und dann die Ergebnisse mit der UNION-Klausel zu kombinieren.

SELECT …FROM …WHERE username = ‘Mark’ UNIONSELECT …FROM …WHERE post_time > ‘2018-01-01’

Bitte beachten Sie, dass Sie auch UNION ALL verwenden können (was eine bessere Leistung als die Standardeinstellung UNION DISTINCT bringt), wenn Ihnen doppelte Datensätze in Ihrer Ergebnismenge nichts ausmachen.

Beispiel #3 – Sortieren mit einer gemischten Reihenfolge vermeiden

Betrachten Sie diese Abfrage, die alle Beiträge von Facebook auswählt und sie nach dem Benutzernamen in aufsteigender Reihenfolge und dann nach dem Beitragsdatum in absteigender Reihenfolge sortiert.

SELECT username, post_typeFROM fb_postsORDER BY username ASC , post_type DESC

MySQL (und viele andere relationale Datenbanken) können keine Indizes verwenden, wenn mit einer gemischten Reihenfolge sortiert wird (sowohl ASC als auch DESC in derselben ORDER BY Klausel). Dies änderte sich mit der Veröffentlichung der Funktionalität der umgekehrten Indizes und MySQL 8.x.

Was können Sie also tun, wenn Sie noch nicht auf die neueste MySQL-Version aktualisiert haben? Zunächst würden wir empfehlen, die Sortierung in gemischter Reihenfolge noch einmal zu überdenken. Brauchen Sie sie wirklich? Wenn nicht, vermeiden Sie sie.

Sie haben also beschlossen, dass Sie sie brauchen, oder Ihr Produktmanager hat gesagt: „Wir kommen auf keinen Fall ohne sie aus“? Eine andere Möglichkeit ist die Verwendung von Generated Columns (verfügbar ab MySQL 5.7.5), um eine umgekehrte Spalte zu erstellen und nach dieser Spalte anstelle der ursprünglichen zu sortieren. Angenommen, Sie sortieren nach einer numerischen Spalte, dann können Sie eine generierte Spalte mit dem negativen numerischen Wert erstellen, der mit der Originalzahl korreliert, und nach dieser neuen Spalte in umgekehrter Reihenfolge sortieren. Auf diese Weise haben alle Spalten die gleiche Sortierreihenfolge in der ORDER BY-Klausel, aber die Sortierung erfolgt so, wie sie ursprünglich durch die Anforderung Ihres Produkts definiert wurde.

Die letzte mögliche Lösung wird nicht immer eine Option sein, so dass Ihr letzter Ausweg darin besteht, auf die neueste MySQL-Version zu aktualisieren, die die Sortierung in gemischter Reihenfolge mithilfe von Indizes unterstützt.

Beispiel #4 – Bedingungen mit unterschiedlichen Spaltentypen vermeiden

Betrachten Sie diese Abfrage, die die Anzahl der roten Früchte in einem Wald selektiert.

SELECT COUNT(*)FROM forestWHERE fruit_color = 5; /* 5 = red */

Angenommen, der Typ der Spalte fruit_color ist VARCHAR, oder einfach irgendetwas nicht-numerisches, dann ist die Indizierung dieser Spalte nicht sehr hilfreich, da der erforderliche implizite Cast die Datenbank daran hindert, den Index für den Filterungsprozess zu verwenden.

Wie können Sie also diese SQL-Abfrage optimieren? Sie haben zwei Möglichkeiten, diese Abfrage zu optimieren. Die erste wäre, die Spalte mit einem konstanten Wert zu vergleichen, der zum Typ der Spalte passt, also wenn es eine VARCHAR-Spalte ist, vergleichen Sie sie mit ‚5‘ (mit einfachen Anführungszeichen) und nicht mit 5 (was ein numerischer Vergleich ist, der zu einem impliziten Cast führt).

Eine bessere Option wäre, den Typ der Spalte so anzupassen, dass er dem am besten geeigneten Typ für die Werte entspricht, die die Spalte enthält. In diesem Beispiel sollte die Spalte in einen INT-Typ geändert werden. Bitte beachten Sie, dass das Ändern des Typs einer Spalte eine komplizierte Aufgabe sein kann, lesen Sie also über die Herausforderungen dieser Aufgabe, bevor Sie sich daran machen.

Vermeiden Sie LIKE-Suchen mit Präfix-Platzhaltern

Betrachten Sie diese Abfrage, die alle Facebook-Beiträge von einem Benutzernamen sucht, der die Zeichenfolge ‚Mar‘ enthält, wir suchen also nach allen Beiträgen, die von Benutzern namens Mark, Marcus, Almar usw. geschrieben wurden.

SELECT *FROM fb_postsWHERE username LIKE '%Mar%'

Mit einem Platzhalter ‚%‘ am Anfang des Musters wird verhindert, dass die Datenbank einen Index für die Suche in dieser Spalte verwendet. Solche Suchen können eine Weile dauern.

In diesem Fall gibt es zwei Möglichkeiten, die Leistung dieser Abfrage zu verbessern. Die erste ist trivial – überlegen Sie, ob der Präfix-Platzhalter wichtig genug ist. Wenn Sie ohne ihn auskommen können, lassen Sie ihn weg.

Eine andere Möglichkeit ist die Verwendung von Volltextindizes. Beachten Sie aber, dass diese Indizes und die MATCH … AGAINST-Syntax nicht frei von Herausforderungen sind und einige Unterschiede zu den bekannten LIKE-Ausdrücken in MySQL aufweisen.

Abschluss

In diesem ersten Teil unserer Serie zur Optimierung von SQL-Abfragen haben wir die Bedeutung einer sinnvollen Indizierung behandelt, wir sind einige Beispiele für mögliche Hindernisse bei der Verwendung indizierter Spalten in Abfragen durchgegangen, und wir haben auch einige andere Tipps und Tricks aufgeführt, die für eine bessere Abfrageleistung hilfreich sein können. Wir sehen uns im nächsten Beitrag.