Datenbankabfrageoptimierung: So verbessern Sie die Abfrageleistung in SQL

[vish234]

Ich habe eine SQL-Datenbank, die große Datenmengen speichert, und beim Abrufen von Informationen ist die Abfrageleistung langsam. Welche Strategien und Best Practices gibt es zur Optimierung von Datenbankabfragen in SQL, um eine bessere Leistung zu erzielen?

Hier ist eine Beispielabfrage, mit der ich arbeite:

SELECT first_name, last_name, email
FROM users
WHERE registration_date >= '2023-01-01'
ORDER BY last_name ASC;

Diese Abfrage funktioniert zwar, wird jedoch mit zunehmendem Datensatz langsamer. Könnten Sie Hinweise geben, wie Sie diese Abfrage optimieren und möglicherweise Indizes oder andere Techniken verwenden können, um den Datenabruf zu beschleunigen? Gibt es außerdem allgemeine Fallstricke, die ich bei der Optimierung von SQL-Abfragen beachten sollte?
Wir würden uns über Ihre Erkenntnisse, gegebenenfalls einschließlich Codebeispielen, sehr freuen. Danke schön!

 

[Robert Zenz]

EXPLAIN wird von so ziemlich allen Datenbanken unterstuetzt, und wird dir erlaeutern wie die Datenbankabfrage ausgefuehrt wird. Darueber hinaus solltest du dir ein paar Lektueren zu dem Thema zulegen, zum Beispiel High Performance MySQL, in solchen Buechern werden eigentlich immer auch die Datenbank-uebergreifenden Grundlagen erklaert.

Das gesagt, ich moechte wetten dass es keinen kombinierten Index auf registration_date und last_name gibt. "Kombiniert" ist in dem Fall wichtig weil zuerst nach registration_date gefiltert wird, und danach erst nach last_name (fuer die Sortierung).

 

[Robert Zenz]

"Kombiniert" ist in dem Fall wichtig weil zuerst nach registration_date gefiltert wird, und danach erst nach last_name (fuer die Sortierung).

Oh, und desweiteren bedeutet eine Sortierung immer das alle Datensaetze geholt werden muessen, also wenn du zum Beispiel

select *
from USERS
where USER_TYPE = 'Something'
order by NAME
limit 5 offset 50

hast, dann werden nicht 5 Datensaetze verarbeitet, sondern alle, weil ja erstmal alle sortiert werden muessen um die Reihenfolge und Position zu bestimmen.

 

[KonradN]

Neben dem bereits erwähnten: Man sollte sich intensiv mit den verwendeten Datenbankservern auseinander setzen. Viele Datenbanken fahren im Hintergrund ständig Analysen. MS SQL kann einem z.B. Hinweise geben, was für Indizes bei Abfragen unterstützen würden.

Generell ist eine Optimierung aber immer nur global zu betrachten. Ein zusätzlicher Index beschleunigt zwar die Abfrage, aber es verlangsamt z.B. Inserts.

desweiteren bedeutet eine Sortierung immer das alle Datensaetze geholt werden muessen

Es sei denn, ein Index sorgt dafür, dass dies nicht mehr notwendig ist. (Diesbezüglich fand ich Deinen Text etwas missverständlich. Aber das hast Du ja teilweise schon erläutert.)

Daher kann man nur unterstreichen, was @Robert Zenz gesagt hat:

• Lass Dir von SQL Server die Details der Abarbeitung deiner Abfrage zeigen. Dann siehst Du, was der Server da eigentlich macht.
• Lies Dich ein! Es gibt da sehr viele Quellen, bei denen man nachlesen kann. So ist die Information bezüglich ORDER BY in Bezug auf MS SQL Server z.B. unter: https://www.sqlshack.com/sql-server-order-by-performance-tips/

 

[M.L.]

Bzgl. Literatur (pdf) gibt es z.B. auch https://sql-performance-explained.de/

 

[Robert Zenz]

Generell ist eine Optimierung aber immer nur global zu betrachten. Ein zusätzlicher Index beschleunigt zwar die Abfrage, aber es verlangsamt z.B. Inserts.

Nur aus Interesse, hast du das tatsaechlich mal gesehen in der Praxis? Also, wirklich relevant gesehen, klar kann man es messen, aber so das man wirklich ueberlegen musste einen Index rauszunehmen? Ich habe schon Tabellen mit 50GB aufwaerts gesehen, und da war es nie relevant. Re-indexieren, klar, kannste dir schnell einen Kaffee holen, aber einfuegen nie.

Gut, jetzt muss ich auch dazusagen dass ich ERPs gesehen habe, die haben halt meistens wenig einfuegen und viel abfragen. Wahrscheinlich sieht das mit sowas wie Logging/Monitoring-Loesungen anders aus. Das wuerde mich jetzt durchaus interessieren ob da jemand Erfahrung mit hat.

 

[KonradN]

Nur aus Interesse, hast du das tatsaechlich mal gesehen in der Praxis? Also, wirklich relevant gesehen, klar kann man es messen, aber so das man wirklich ueberlegen musste einen Index rauszunehmen? Ich habe schon Tabellen mit 50GB aufwaerts gesehen, und da war es nie relevant. Re-indexieren, klar, kannste dir schnell einen Kaffee holen, aber einfuegen nie.

Ja, wir hatten schon einmal eine Tabelle mit so vielen Inserts pro Sekunde. Da war dies mit ein Problem.

Wahrscheinlich sieht das mit sowas wie Logging/Monitoring-Loesungen anders aus.

In dem Bereich war ich vor einigen Jahren tätig. Da ging es um Auswertungen des Betriebs. Und wir haben da teilweise massiv Events von zig hundert Systemen eingesammelt. Da war unsere Entwicklungstätigkeit vor allem das Daten einsammeln, vereinheitlichen um darauf dann Reports aufzubauen.

Das, was dann später auch nach und nach dann mit Splunk gemacht wurde, aber wir hatten mit dem Kunden ganz spezielle SLAs und auch Abrechnungsmodalitäten (Große Filer wurden nach Nutzung gegenüber mehreren Kostenstellen abgerechnet.)

Und natürlich hatten wir auch so die üblichen anderen Dinge a.la. Konfigurationsdatenbank mit Frontend und automatische Prozesse, die dann Änderungen automatisch durchgeführt haben und so. Oder Business Prozesse (Was da an Erinnerungen an Microsofts Windows Workflow Foundation aufkommen.)


Aber wenn wir mit Datenbank Performance zu tun hatten, dann war es mehr die Unterstützung von Nicht Entwicklern. Wir haben da öfters dann auch Datenbank Feuerwehr gespielt, weil wir als Dienstleister mit unserer kleinen Datenbank dann schon mal volle Last auf dem SQL Server produziert haben ... Da war dann der eine oder andere Punkt, da ist man dann doch vor dem Kunden im Boden versunken

 

[Robert Zenz]

Ja, wir hatten schon einmal eine Tabelle mit so vielen Inserts pro Sekunde. Da war dies mit ein Problem.

Interessant. Habt ihr die dann entfernt oder umgelegt, oder eine andere Loesung gefunden (zum Beispiel eine gespiegelte Tabelle die "spaeter" mit den Daten nachbefuellt wurde)?

Wir haben da öfters dann auch Datenbank Feuerwehr gespielt, weil wir als Dienstleister mit unserer kleinen Datenbank dann schon mal volle Last auf dem SQL Server produziert haben ... Da war dann der eine oder andere Punkt, da ist man dann doch vor dem Kunden im Boden versunken

Ah, das Gefuehl kenn' ich. Aber primaer wenn man etwas auf der Produktiv-Datenbank machen musste und sich entweder verschaut oder vertippt hatte. Nicht, dass mir sowas jemals passiert waere!

 

[KonradN]

Interessant. Habt ihr die dann entfernt oder umgelegt, oder eine andere Loesung gefunden (zum Beispiel eine gespiegelte Tabelle die "spaeter" mit den Daten nachbefuellt wurde)?

Ich bin mir da nicht mehr so sicher. Ich meine, dass mit ein Kernproblem ein fehlerhafter Primary Key war (der dann die physikalische Order der Datensätze auf der Platte festgelegt hatte. War eine UUID und der Server hatte bei Inserts dann öfters Aufwand mit zusätzlichen Pages und so ...) und ich meine, das wir ind er Zeit ein Hardware Refresh hatten und der Cluster dann komplett auf SSD umgestellt wurde. Aber ich einfach zu lange her.

 

[Robert Zenz]

Ich meine, dass mit ein Kernproblem ein fehlerhafter Primary Key war (der dann die physikalische Order der Datensätze auf der Platte festgelegt hatte. War eine UUID und der Server hatte bei Inserts dann öfters Aufwand mit zusätzlichen Pages und so ...) und ich meine, das wir ind er Zeit ein Hardware Refresh hatten und der Cluster dann komplett auf SSD umgestellt wurde.

Zufaelliges einfuegen in die Dateien klingt selbst auf SSDs noch uncool. Ist aber ein guter weiterer Punkt den du da erwaehnst, naemlich dass Primary Keys durchaus auch die Reihenfolge der Datensaetze auf der Platte bestimmen (koennen), und wenn der eine UUID ist (oder etwas zufaelliges), dass dann die Platte vermutlich keinen Spasz hat mit dem einfuegen von neuen Datensaetzen.

 

[philanthrop]

Du könntest "registration_date" zusätzlich indizieren oder "registration_date_2" als Datentyp "DATE" anlegen, dann ist die where-Klausel schneller.

 

[Robert Zenz]

Du könntest "registration_date" zusätzlich indizieren...

Ein kombinierter Index mit last_name waere da ideal (nicht zwei, sondern kombiniert).

...oder "registration_date_2" als Datentyp "DATE" anlegen, dann ist die where-Klausel schneller.

Die Schreibweise column >= 'YYYY-MM-DD' ist valide fuer so ziemlich alle Datenbanken auch dann wenn column ein DATE (oder vergleichbarer Typ) ist. Oder zumindest bilde ich mir das ein.

 

[Oneixee5]

Falls es dabei um Oracle geht, würde ich vom Anlegen eines Index abraten. Das macht die DB selbst wenn die Abfrage einigermaßen häufig durchgeführt wird. Man erreicht mit zusätzlichen Indizes meißt nur, dass der Optimizer nicht mehr gut funktioniert.

 

[KonradN]

Man erreicht mit zusätzlichen Indizes meißt nur, dass der Optimizer nicht mehr gut funktioniert.

Ist das etwas Oracle spezifisches? Das kenne ich von MS SQL und Informix nicht. Aber was man da machen kann ist eine Prüfung, ob Indices genutzt werden und so. Es lohnt sich also, sich da im Detail mit der ganzen Thematik auch speziell für die konkret verwendete Datenbank zu vertiefen.

 

[Oneixee5]

Ist das etwas Oracle spezifisches?

Ja seit 12c gibt es ADO und so Zeug. Wir haben bei der Migration viel umgebaut, damit die ganzen Automatisierungen richtig funktionieren. Wir wurden da von Oracle-Leuten beraten. Für uns war das auch neu.
Man kann natürlich alles manuell überprüfen und für den Optimizer sogenannte Hints einbauen. Manchmal muss man doch noch von Hand etwas nachhelfen. Wir hatten den Fall kürzlich, dass für eine Abfrage 2 Ausführungspläne erstellt wurden, einer war schrecklich langsam. Man kann dann versuchen zu löschen, aber ein Automat hat da manchmal eine eigene Meinung, oder mit Hints arbeiten.

 

[Dukel]

Falls es dabei um Oracle geht, würde ich vom Anlegen eines Index abraten. Das macht die DB selbst wenn die Abfrage einigermaßen häufig durchgeführt wird. Man erreicht mit zusätzlichen Indizes meißt nur, dass der Optimizer nicht mehr gut funktioniert.

Please note that this feature is licensed only with Exadata/EE/Supercluster systems.
Siehe:

Database Licensing Information User Manual

docs.oracle.com

 

[Oneixee5]

Oracle-Lizenzen sind die Hölle, das versteht eh keiner. Es gibt Funktionen, welche wir testen "durften" aber wir dürfen sie nicht verwenden - da sie nicht zur Lizenz gehören.
Wenn einer mal Google bemüht und dort was tolles findet, muss er jedes mal erst beim Lizenzmanagement nachfragen, ob er die Funktion auch einsetzen darf. Wir dürfen z.B.: keine Tabellen partitionieren, obwohl es technisch möglich wäre.

 

[Dukel]

Zum Thema Indexing kenne ich auch die Webseite:

SQL Indexing and Tuning e-Book for developers: Use The Index, Luke covers Oracle, MySQL, PostgreSQL, SQL Server, ...

SQL indexing and tuning tutorial for developers. No unnecessary database details—just what developers need to know. Covers all major SQL databases.

use-the-index-luke.com

 

[philanthrop]

Die Schreibweise column >= 'YYYY-MM-DD' ist valide fuer so ziemlich alle Datenbanken auch dann wenn column ein DATE (oder vergleichbarer Typ) ist. Oder zumindest bilde ich mir das ein.

Jupp, aber wir kennen das Datenbankschema ja nicht, deshalb weiß ich jetzt nicht, welchen Datentyp die Column z Zt hat.