SQL-Indizes erklärt, Pt. 1

Richtig eingesetzt kann ein SQL-Datenbankindex so effektiv sein, dass es wie Zauberei erscheinen mag. Aber die folgende Reihe von Übungen wird zeigen, dass die Logik der meisten SQL-Indizes – und ihre korrekte Verwendung – im Grunde ziemlich einfach ist.

In dieser Serie, SQL Indexes Explained , werden wir die Beweggründe für die Verwendung von Indizes für den Zugriff auf Daten und für das Entwerfen von Indizes so durchgehen, wie es von allen modernen RDBMSs getan wird. Wir werden uns dann die Algorithmen ansehen, die verwendet werden, um Daten für bestimmte Abfragemuster zurückzugeben.

Sie müssen nicht viel über Indizes wissen, um SQL Indexes Explained folgen zu können. Es gibt nur zwei Voraussetzungen:

• Grundlegende SQL-Kenntnisse
• Grundkenntnisse einer beliebigen Programmiersprache

Die Hauptthemen, auf die SQL Indexes Explained eingehen wird, sind:

• Warum wir SQL-Datenbankindizes brauchen; Visualisierung von Ausführungsplänen mithilfe von Indizes
• Indexdesign: Welche Indizes machen eine Abfrage schnell und effizient
• Wie wir eine Abfrage schreiben können, um Indizes effektiv zu nutzen
• Die Auswirkungen der Verwendung von Indizes in SQL auf die Lese-/Schreibeffizienz
• Indizes abdecken
• Partitionierung, ihre Auswirkung auf das Lesen und Schreiben und wann sie verwendet wird

Dies ist nicht nur ein SQL-Index-Tutorial – es ist ein tiefer Einblick in die zugrunde liegende Mechanik von Indizes.

Wir werden herausfinden, wie ein RDBMS Indizes verwendet, indem wir Übungen machen und unsere Problemlösungsmethoden analysieren. Unser Übungsmaterial besteht aus schreibgeschützten Google Sheets. Um eine Übung zu machen, können Sie das Google-Blatt kopieren ( Datei → Kopie erstellen ) oder seinen Inhalt in Ihr eigenes Google-Blatt kopieren.

In jeder Übung zeigen wir eine SQL-Abfrage, die die Oracle-Syntax verwendet. Für Datumsangaben verwenden wir das ISO 8601-Format YYYY-MM-DD .

Übung 1: Alle Reservierungen eines Kunden

Die erste Aufgabe – tun Sie es jetzt noch nicht – besteht darin, alle Zeilen aus der Reservierungstabelle für einen bestimmten Client eines Hotelreservierungssystems zu finden und sie in Ihre eigene Tabelle zu kopieren, um die Ausführung der folgenden Abfrage zu simulieren:

 SELECT * FROM Reservations WHERE ClientID = 12;

Aber wir wollen einer bestimmten Methode folgen.

Ansatz 1: Keine Sortierung, keine Filterung

Verwenden Sie beim ersten Versuch keine Sortier- oder Filterfunktionen. Bitte notieren Sie die aufgewendete Zeit. Das resultierende Blatt sollte 73 Zeilen enthalten.

Dieser Pseudocode veranschaulicht den Algorithmus zum Ausführen der Aufgabe ohne Sortieren:

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

In diesem Fall mussten wir alle 841 Zeilen überprüfen, um 73 Zeilen zurückzugeben und zu kopieren, die die Bedingung erfüllten.

Ansatz 2: Nur Sortieren

Sortieren Sie für den zweiten Versuch das Blatt nach dem Wert der ClientID Spalte. Verwenden Sie keine Filter. Notieren Sie die Zeit und vergleichen Sie sie mit der Zeit, die zum Abschließen der Aufgabe ohne Sortieren der Daten benötigt wurde.

Nach dem Sortieren sieht die Vorgehensweise so aus:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

Diesmal mussten wir „nur“ 780 Zeilen prüfen. Wenn wir irgendwie in die erste Reihe springen könnten, würde es noch weniger Zeit in Anspruch nehmen.

Aber wenn wir ein Programm für die Aufgabe entwickeln müssten, wäre diese Lösung noch langsamer als die erste. Das liegt daran, dass wir alle Daten zuerst sortieren müssten, was bedeutet, dass auf jede Zeile mindestens einmal zugegriffen werden müsste. Dieser Ansatz ist nur dann gut, wenn das Blatt bereits in der gewünschten Reihenfolge sortiert ist.

Übung 2: Die Anzahl der Reservierungen ab einem bestimmten Datum

Nun gilt es, die Anzahl der Check-Ins am 16. August 2020 zu zählen:

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

Verwenden Sie die Tabelle aus Übung 1. Messen und vergleichen Sie die Zeit, die Sie für die Bearbeitung der Aufgabe mit und ohne Sortieren aufgewendet haben. Die korrekte Zählung ist 91.

Für den Ansatz ohne Sortierung ist der Algorithmus im Grunde derselbe wie der aus Aufgabe 1.

Auch der Sortieransatz ähnelt dem aus der vorherigen Übung. Wir teilen die Schleife einfach in zwei Teile auf:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

Übung 3: Kriminalpolizei

Der Polizeiinspektor bittet um Einsicht in eine Liste der Gäste, die am 13. und 14. August 2020 im Hotel angekommen sind.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

Ansatz 1: Nur nach Datum sortiert

Der Inspektor will die Liste schnell haben. Wir wissen bereits, dass wir die Tabelle/Tabelle besser nach dem Ankunftsdatum sortieren sollten. Wenn wir gerade Übung 2 beendet haben, haben wir Glück, dass die Tabelle bereits sortiert ist. Wir wenden also den Ansatz ähnlich dem aus Aufgabe 2 an.

Versuchen Sie bitte, die Zeit, die Anzahl der Zeilen, die Sie lesen mussten, und die Anzahl der Elemente auf der Liste aufzuzeichnen.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

Bei diesem Ansatz mussten wir 511 Zeilen lesen, um eine Liste mit 46 Gästen zusammenzustellen. Wenn wir präzise nach unten rutschen könnten, müssten wir nicht wirklich 324 Lesungen aus dem Wiederholungszyklus durchführen, nur um die erste Ankunft am 13. August zu lokalisieren. Allerdings mussten wir immer noch mehr als 100 Zeilen lesen, um zu überprüfen, ob der Gast mit einer HotelID von 3 im Hotel angekommen ist.

Der Inspektor wartete die ganze Zeit, war aber nicht glücklich: Anstelle von Gästenamen und anderen relevanten Daten lieferten wir nur eine Liste mit bedeutungslosen Ausweisen.

Wir werden später in der Serie auf diesen Aspekt zurückkommen. Lassen Sie uns zuerst einen Weg finden, die Liste schneller vorzubereiten.

Ansatz 2: Sortiert nach Hotel, dann nach Datum

Um die Zeilen nach HotelID und dann nach DateFrom zu sortieren, können wir alle Spalten auswählen und dann die Google Sheets-Menüoption Data → Sort range verwenden.

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

Wir mussten die ersten 338 Ankünfte überspringen, bevor wir den ersten zu unserem Hotel ausfindig machten. Danach gingen wir 103 frühere Ankünfte durch, um den ersten am 13. August zu finden. Schließlich haben wir 46 aufeinanderfolgende Werte von ClientID kopiert. Uns hat geholfen, dass wir im dritten Schritt einen Block aufeinanderfolgender IDs kopieren konnten. Schade, dass wir von diesem Block nicht irgendwie in die erste Reihe springen konnten.

Ansatz 3: Nur nach Hotel sortiert

Versuchen Sie nun dieselbe Übung, indem Sie nur die von HotelID bestellte Tabelle verwenden.

Der Algorithmus, der nur auf die nach HotelID geordnete Tabelle angewendet wird, ist weniger effizient als wenn wir nach HotelID und DateFrom (in dieser Reihenfolge) sortieren:

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

In diesem Fall müssen wir alle 166 Ankünfte im Hotel mit einer HotelID von 3 lesen und jeweils prüfen, ob DateFrom zum angeforderten Intervall gehört.

Ansatz 4: Sortiert nach Datum, dann Hotel

Spielt es wirklich eine Rolle, ob wir zuerst nach HotelID und dann nach DateFrom oder umgekehrt? Finden wir es heraus: Versuchen Sie, zuerst nach DateFrom und dann nach HotelID zu sortieren.

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

Wir haben die erste Zeile mit dem relevanten Datum gefunden und dann weitergelesen, bis wir die erste Ankunft im Hotel gefunden haben. Danach waren für einige Zeilen beide Bedingungen erfüllt, das richtige Datum und das richtige Hotel. Nach der Ankunft in Hotel 3 hatten wir jedoch Ankünfte in den Hotels 4, 5 usw. für dasselbe Datum. Danach mussten wir die Zeilen für den nächsten Tag für die Hotels 1 und 2 erneut lesen, bis wir in der Lage waren, aufeinanderfolgende Ankünfte zu unserem interessierenden Hotel zu lesen.

Wie wir sehen können, haben alle Ansätze einen einzigen aufeinanderfolgenden Datenblock in der Mitte des vollständigen Satzes von Zeilen, der teilweise übereinstimmende Daten darstellt. Die Ansätze 2 und 4 sind die einzigen, bei denen es uns die Logik erlaubt, den Algorithmus vollständig zu stoppen, bevor wir das Ende der Teilübereinstimmungen erreichen.

Ansatz 4 hat vollständig übereinstimmende Daten in zwei Blöcken, aber Ansatz 2 ist der einzige, bei dem die Zieldaten alle in einem aufeinanderfolgenden Block sind.

Anhand der Zahlen wird deutlich, dass Ansatz 2 in diesem Fall die meisten Vorteile bietet.

Erklärte SQL-Indizes: Schlussfolgerungen und was als nächstes kommt

Durch diese Übungen sollen folgende Punkte deutlich werden:

1. Das Lesen aus einer richtig sortierten Tabelle ist schneller.
2. Wenn eine Tabelle noch nicht sortiert ist, dauert das Sortieren länger als das Lesen aus einer unsortierten Tabelle.
3. Einen Weg zu finden, um zur ersten Zeile zu springen, die einer Suchbedingung innerhalb der sortierten Tabelle entspricht, würde eine Menge Lesevorgänge einsparen.
4. Es wäre hilfreich, einen Tisch im Voraus sortiert zu haben.
5. Es wäre hilfreich, die sortierten Kopien der Tabelle für die häufigsten Abfragen beizubehalten.

Nun, eine sortierte Kopie einer Tabelle klingt fast wie ein Datenbankindex. Der nächste Artikel in SQL Indexes Explained behandelt eine rudimentäre Indeximplementierung. Danke fürs Lesen!