Kurs
Datenbearbeitung in SQL
4 Std.
335.5K
Als Data Scientist hast du oft mit großen Datenmengen zu tun. Diese zu bewältigen ist alles andere als einfach. Damit du sie möglichst effizient verarbeiten kannst, brauchst du ein klares Verständnis davon, wie Daten physisch organisiert sind – so kannst du sie mit den richtigen Techniken performant bearbeiten.
SQL ist für moderne Softwareentwickler unverzichtbar, denn die meisten Anwendungen arbeiten mit Daten und integrieren sich gut in ein RDBMS (Relational Database Management System). Ob Web-App, API oder interne Anwendung – ein RDBMS ist fast immer im Spiel. Und SQL ist die Sprache, um ein RDBMS abzufragen.
Für dich als Data Scientist ist es entscheidend, SQL und die zugehörigen Techniken zu beherrschen. Um ein RDBMS zu befragen und gezielte Antworten zu deinen Daten zu erhalten, ist SQL die Grundvoraussetzung.
In seinem neuesten Video mit DataCamp zeigt David Robinson (Chief Data Scientist @ DataCamp), wie er SQL in einem Data-Science-Problem einsetzt. Schau es dir an – sein Workflow ist sehr spannend.
Heute lernst du eine Technik namens Indexierung kennen, die sich in erster Linie mit der Organisation von Daten innerhalb einer Datenbank befasst. Du setzt einige dieser Techniken mit SQL um. So bekommst du ein Gefühl dafür, wie Indexe Informationen in einer Datenbank ablegen und zu deutlich schnelleren Ausführungszeiten führen können.
Hinweis: Bevor du loslegst, solltest du die SQL-Grundlagen beherrschen. DataCamps Kurs Intro to SQL for Data Science ist ideal, um dein Wissen aufzufrischen.
Kurz erklärt: Wie Datensätze in einer Datei organisiert sind
Bevor du in die Indexierung einsteigst, ist es wichtig zu verstehen, wie Daten physisch in Dateien organisiert sind. Du musst nicht jedes Detail kennen, aber ein gutes Grundverständnis hilft dir, Indexierung klarer zu begreifen. Los geht’s.
Die Organisation von Datensätzen/Daten dreht sich darum, wie Datensätze gespeichert werden. Grob lassen sich zwei Arten unterscheiden:
-
Geordnete Organisation: Alle Datensätze in einer Datei sind nach einem Suchschlüssel sortiert. Für die Suche wird meist die Binärsuche eingesetzt. Das macht die Suche sehr effizient, da die Laufzeit logarithmisch bleibt. Einfügen wird dafür teuer, weil die Datei ggf. umorganisiert werden muss.
-
Ungeordnete Organisation: Datensätze werden dort eingefügt, wo Platz ist – meist am Ende der Datei. Da hier linear gesucht wird, ist die Suche weniger effizient, Einfügen dafür günstig.
Aber was, wenn die Blockgrößen der Dateien und die Größe jedes Datensatzes sehr groß sind – selbst bei geordneter Organisation? Das sehen wir im nächsten Abschnitt.
Warum Indexe?
Hier lernst du die Motivation kennen, mit Indexen Dateien/Informationen effizient zu speichern und wie sie die zugehörigen Operationen beschleunigen.
Angenommen, du hast in der Datenbank eine Tabelle (Relation) mit vielen Datensätzen. Diese Datensätze sind in 1000 Blöcke aufgeteilt. Bildlich sieht das so aus:
Daraus folgt:
- Die Sortierung bezieht sich auf die erste Spalte der Datensätze (denk daran: in einem relationalen System sind die Daten tabellarisch organisiert).
- Die Gesamtzahl der Datenblöcke beträgt 1000 Blöcke.
Jeder Datensatz enthält weitere Spalten, aber hier ist die Reihenfolge nach der ersten Spalte festgelegt und die Blöcke entsprechend aufgeteilt. Führst du nun eine Binärsuche in dieser Struktur aus, beträgt die Suchzeit $\log_{2}1000$, also 10 Zeiteinheiten. Geht das schneller?
Klar.
Denk an das Lesen eines Buchs ohne Indexseite. Du schlägst eine zufällige Seite auf (oder in der Binärsuche die Mitte) und blätterst hin und her, bis du fündig wirst. Das kostet Zeit, oder?
Mit einer Indexseite ginge es viel schneller: Ein Blick in den Index, und du springst direkt zur richtigen Seite. Genau so hilft Indexierung im obigen Fall.
So wendest du Indexierung hier an:
- Für jeden Block wird der geordnete Schlüsselwert samt Blockzeiger gespeichert.
Dadurch brauchst du weniger Blöcke, um die Indexdatei zu speichern. Um einen Datensatz zu finden, suchst du erst in dieser kleineren Indexstruktur und springst dann direkt zu den Originaldaten – insgesamt deutlich schneller. Visualisieren wir das. Wenn die neuen Indexeinträge in 8 Blöcken landen, sieht die Organisation so aus:
Durch die geringere Blockzahl sinkt die Suchzeit drastisch. Willst du zum Beispiel den Datensatz 90 finden, lokalisierst du im Index zuerst den passenden Blockzeiger – hier 3 – und springst damit in einem Zug zu den Originaldatensätzen.
Die Suchzeit beträgt damit $\log_{2}8 + 1$ = 4 Zeiteinheiten – deutlich weniger als zuvor.
Das Beispiel zeigt, warum Indexierung sinnvoll ist. Wichtige Punkte dazu:
- Die Sortierung der Originaldaten kann nur über ein Feld erfolgen. Indexiert wird dann genau dieses Feld. Nur bei Suchen über dieses Feld verbessert sich die Suchzeit. (Sehr wichtig)
- Auch die Indexeinträge sind sortiert.
- Ein Indexdatensatz besteht aus zwei Feldern (Struktur der Indexdatei):
- Schlüssel des Originaldatensatzes
- Zeiger auf den Block, in dem der Schlüssel in den Originaldaten liegt
- Für die Indexsuche wird die Binärsuche verwendet.
- Für den Zugriff über den Index beträgt die durchschnittliche Anzahl an Blockzugriffen:
$\log_{2}B_i + 1$ , wobei $B_i$ die Anzahl der Blöcke der Indexeinträge ist - Ein Index kann auf jedem Feld der Relation liegen (Primärschlüssel, Kandidatenschlüssel, Nicht-Schlüssel).
Abhängig von der Sortierung der Originaldaten und der Anzahl der Indexeinträge gibt es verschiedene Indexierungsschemata. Im nächsten Abschnitt lernst du die gängigsten kennen.
Verschiedene Indexierungsstrategien:
- Dichte Indexierung (Dense Indexing): Es gibt einen Indexeintrag für jeden Suchschlüsselwert. Das folgende Diagramm veranschaulicht das.
- Sparse Indexing: Es wird nur für einige Datensätze ein Indexeintrag angelegt. Hier ist die bildliche Darstellung.
Die Diagramme machen beide Schemata gut nachvollziehbar. Wichtig: Das obige Beispiel für sparse indexing ist eine Kombination aus dichter und spärlicher Indexierung. Für jeden eindeutigen Suchschlüsselwert (1, 2 und 3) gibt es einen Index, aber nicht für jeden einzelnen Datensatz.
Als Nächstes betrachten wir die Einteilung nach Indexebenen. Bei der einstufigen Indexierung gibt es genau eine Indexdatei. Wird diese zu groß, wird sie selbst indexiert – dann spricht man von mehrstufiger Indexierung. Schauen wir genauer hin.
Hier ein Überblick der weiteren Strategien nach Ebenen.
Einstufige Indexierung:
- Primärindex
- Clustering-Index
- Sekundärindex
Mehrstufige Indexierung:
- B-Tree
- B+-Tree
In diesem Tutorial behandeln wir die Strategien der einstufigen Indexierung. Am Ende findest du einen Link, um mehrstufige Schemata zu erkunden. Starten wir mit dem Primärindex.
Primärindex:
Ein Primärindex ist eine geordnete Datei mit Festlängendatensätzen aus zwei Feldern:
- Das erste Feld entspricht dem Primärschlüssel der Datendatei.
- Das zweite Feld ist ein Zeiger auf den Datenblock, in dem der Primärschlüssel liegt. – Quelle
Indexeinträge, die für den ersten Datensatz jedes Blocks angelegt werden, heißen Blockanker. Beim Primärindex gilt: Anzahl der Indexeinträge = Anzahl der ursprünglichen Datenblöcke. Die durchschnittliche Zahl der Blockzugriffe mit Primärindex ist:
Sieh dir das folgende Diagramm zur Verdeutlichung an: 
Rechts siehst du die Originaldaten in Blöcke aufgeteilt. Die Spalte mit 1,2,3,...,9 sind die Primärschlüssel. Links sind die Indexeinträge; jeder Eintrag besteht aus:
- Dem ersten Eintrag jedes Datenblocks (Blockanker)
- Dem Blockzeiger.
Überlege kurz: Handelt es sich hier um dichte oder spärliche Indexierung? Poste deine Antwort gern in den comments.
Weiter geht’s mit dem Clustering-Index.
Clustering-Index:
Ein Clustering-Index wird auf einer Datendatei angelegt, deren Datensätze physisch nach einem Nicht-Schlüsselfeld geordnet sind, das nicht für jeden Datensatz eindeutig ist. Dieses Feld heißt Clustering-Feld und dient als Basis der Indexierung – daher der Name.
Diagramme helfen beim Verständnis: Die Originaldaten sind nach einem Nicht-Schlüsselattribut geordnet, und für jeden unterschiedlichen Wert dieses Attributs gibt es einen Indexeintrag. Die durchschnittlichen Blockzugriffe, um einen bestimmten Datensatz zu finden, betragen $\geq$ $\log_{2}B_i + 1$, wobei $B_i$ die Zahl der Blöcke in den Indexeinträgen ist. Beachte das $\geq$.
Beim Clustering-Index kann es sein, dass du nach dem Finden des Blocks mit dem betreffenden Schlüssel noch weitere Blöcke durchlaufen musst (das zeigt die Grafik).
Auch hier die Frage: Dicht oder spärlich? Teile deine Gedanken in den Comments. Als Nächstes kommt der Sekundärindex.
Sekundärindex:
Angenommen, es gibt eine Tabelle Employee mit den Attributen:
- employee_id
- employee_name
- employee_department
- employee_salary
employee_id ist der Primärschlüssel. Du hast bereits einen Primärindex auf employee_id angelegt. Bei der Anwendungsentwicklung stellst du jedoch fest, dass die meisten Abfragen das Attribut employee_name verwenden. Dann hilft der Primärindex wenig, und es ist sinnvoll, einen separaten Index über die Werte von employee_name anzulegen. Da Mitarbeiternamen in der Datenbank nicht geordnet vorliegen, beschleunigt ein Index die entsprechenden Abfragen deutlich.
Das ist ein klassischer Sekundärindex. So könnte eine passende Darstellung aussehen:
Wenn du eine bessere Visualisierung hast, poste sie gern in den comments.
Als Nächstes siehst du, wie du Indexe in PostgreSQL anlegst. Falls du die Grundlagen auffrischen willst: DataCamps Kurs Joining Data in PostgreSQL hilft dir weiter.
Indexe in PostgreSQL anlegen
Bevor du Indexe in einer PostgreSQL-Datenbank erstellst, brauchst du Daten in einer Tabelle. Legen wir eine einfache Tabelle Student mit folgenden Spalten an:
- student_id
- student_name
- student_year
student_id wird der Primärschlüssel, und student_name sowie student_year dürfen nicht NULL sein.
Die passende Abfrage dafür:
CREATE TABLE STUDENT(
student_id TEXT PRIMARY KEY,
student_name TEXT NOT NULL,
student_year TEXT NOT NULL
);
Nach dem Anlegen der Tabelle musst du Daten einfügen. Der Einfachheit halber kannst du eine .csv-Datei verwenden und in Student importieren. Einen kompatiblen CSV-Import führst du in PostgreSQL so aus:
COPY STUDENT FROM '/path/to/csv/Student.csv' WITH (FORMAT csv);
Die Abfrage geht davon aus, dass die CSV-Datei Student heißt.
Schauen wir uns die Daten an. select * from STUDENT; liefert z. B. diese Datensätze: 
Die select-Abfrage sollte insgesamt 86 Datensätze zurückgeben. Jetzt kannst du Indexe erstellen. Einspaltige Indexe erzeugst du so:
CREATE INDEX index_name
ON table_name (column_name);
Legen wir einen Index auf student_id an (Primärindexierung):
CREATE INDEX id_index
ON STUDENT (student_id);
Auch Mehrspaltenindexe sind möglich:
CREATE INDEX id_index
ON STUDENT (student_id,student_name);
Einen Index entfernst du so:
DROP INDEX index_name;
In einer kleinen Tabelle wie STUDENT ist der Effekt der Indexierung schwer spürbar. In großen Tabellen (etwa einer Universität mit vielen Datensätzen) macht Indexierung jedoch einen großen Unterschied.
Geschafft!
Glückwunsch bis hierher. In diesem Tutorial hast du gelernt, was Indexe sind, warum sie gebraucht werden und welche Indexierungsschemata es gibt. Außerdem hast du gesehen, wie du einfache Indexe in PostgreSQL anlegst.
Die mehrstufige Indexierung haben wir nicht behandelt. Hier sind gute Ressourcen, wenn du tiefer einsteigen willst:
Hilft Indexierung immer? Es gibt Fälle, in denen sie nicht ratsam ist.
Ich hoffe, das Tutorial hat dir die Grundlagen der Indexierung klar gemacht. Teile gern deine Erkenntnisse in den comments.
Schau dir auch DataCamps Learn SQL Hub an.
Themen
