SQLite Indexoptimierung: Datenbank Performance steigern

Einführung

In diesem Lab lernen Sie, wie Sie die Leistung von SQLite-Datenbanken mithilfe von Indizes optimieren können. Sie erstellen Single-Column-Indizes (Einzelspaltenindizes), um die Abfragegeschwindigkeit zu verbessern, wobei der Schwerpunkt auf der praktischen Anwendung und Analyse liegt. Sie lernen auch, Abfragepläne zu analysieren und redundante Indizes zu entfernen.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") sqlite/SQLiteGroup -.-> sqlite/build_index("Create Single Index") sqlite/SQLiteGroup -.-> sqlite/clear_index("Drop Single Index") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") subgraph Lab Skills sqlite/query_where -.-> lab-552552{{"SQLite Indexoptimierung"}} sqlite/sort_data -.-> lab-552552{{"SQLite Indexoptimierung"}} sqlite/build_index -.-> lab-552552{{"SQLite Indexoptimierung"}} sqlite/clear_index -.-> lab-552552{{"SQLite Indexoptimierung"}} sqlite/verify_table -.-> lab-552552{{"SQLite Indexoptimierung"}} end

Erstellen einer Datenbank und Tabelle

In diesem Schritt erstellen Sie eine SQLite-Datenbank und eine employees-Tabelle (Mitarbeitertabelle). Anschließend fügen Sie einige Beispieldaten in die Tabelle ein.

Öffnen Sie zunächst Ihr Terminal in der LabEx VM. Ihr Standardpfad ist /home/labex/project.

Um eine SQLite-Datenbank namens my_database.db zu erstellen, führen Sie den folgenden Befehl aus:

sqlite3 my_database.db

Dieser Befehl erstellt eine neue SQLite-Datenbankdatei namens my_database.db in Ihrem Projektverzeichnis und öffnet die SQLite-Shell.

Erstellen Sie als Nächstes die employees-Tabelle mit der folgenden Struktur:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    department TEXT
);

Diese SQL-Anweisung erstellt eine Tabelle namens employees mit fünf Spalten: id, first_name (Vorname), last_name (Nachname), email und department (Abteilung). Die Spalte id ist als Primärschlüssel (Primary Key) festgelegt, was bedeutet, dass sie eindeutige Werte enthalten muss.

Fügen Sie nun einige Beispieldaten in die employees-Tabelle ein:

INSERT INTO employees (first_name, last_name, email, department) VALUES
('John', 'Doe', '[email protected]', 'Sales'),
('Jane', 'Smith', '[email protected]', 'Marketing'),
('Robert', 'Jones', '[email protected]', 'Engineering'),
('Emily', 'Brown', '[email protected]', 'Sales'),
('Michael', 'Davis', '[email protected]', 'Marketing');

Dadurch werden fünf Datenzeilen in die employees-Tabelle eingefügt.

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, führen Sie den folgenden Befehl aus:

SELECT * FROM employees;

Sie sollten die folgende Ausgabe sehen:

1|John|Doe|[email protected]|Sales
2|Jane|Smith|[email protected]|Marketing
3|Robert|Jones|[email protected]|Engineering
4|Emily|Brown|[email protected]|Sales
5|Michael|Davis|[email protected]|Marketing

Erstellen eines Index

In diesem Schritt erstellen Sie einen Index für die Spalte last_name (Nachname) der Tabelle employees (Mitarbeiter).

Indizes sind spezielle Nachschlagetabellen (Lookup Tables), die die Datenbank-Suchmaschine verwenden kann, um die Datenabfrage zu beschleunigen.

Um einen Index namens idx_lastname für die Spalte last_name zu erstellen, führen Sie den folgenden Befehl aus:

CREATE INDEX idx_lastname ON employees (last_name);

Diese SQL-Anweisung erstellt einen Index namens idx_lastname für die Spalte last_name der Tabelle employees.

Um zu überprüfen, ob der Index erstellt wurde, können Sie den folgenden Befehl verwenden:

PRAGMA index_list(employees);

Dieser Befehl zeigt eine Liste der Indizes für die Tabelle employees an, einschließlich des Index idx_lastname, den Sie gerade erstellt haben. Sie sollten eine ähnliche Ausgabe wie diese sehen:

0|idx_lastname|0|c|0

Diese Ausgabe bestätigt, dass der Index idx_lastname für die Tabelle employees vorhanden ist.

Analysieren von Abfragen mit EXPLAIN QUERY PLAN

In diesem Schritt lernen Sie, wie Sie den Befehl EXPLAIN QUERY PLAN verwenden, um zu analysieren, wie SQLite eine Abfrage ausführt. Dies ist ein leistungsstarkes Werkzeug, um die Abfrageleistung zu verstehen und potenzielle Engpässe (Bottlenecks) zu identifizieren.

Um eine Abfrage zu analysieren, stellen Sie ihr EXPLAIN QUERY PLAN voran. Um beispielsweise die folgende Abfrage zu analysieren:

SELECT * FROM employees WHERE last_name = 'Smith';

Führen Sie den folgenden Befehl aus:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Smith';

Die Ausgabe sieht dann ungefähr so aus:

QUERY PLAN
`--SEARCH employees USING INDEX idx_lastname (last_name=?)

Diese Ausgabe zeigt Ihnen, dass SQLite den Index idx_lastname verwendet, um die Mitarbeiter mit dem Nachnamen 'Smith' zu finden. Das Schlüsselwort SEARCH (SUCHEN) gibt an, dass SQLite einen Index verwendet, um die Suche durchzuführen.

Wenn der Index nicht verwendet würde, sähe die Ausgabe anders aus. Wenn Sie beispielsweise nach Mitarbeitern mit dem Vornamen 'John' suchen (und Sie keinen Index für die Spalte first_name (Vorname) erstellt haben), würde die Ausgabe wie folgt aussehen:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE first_name = 'John';

Die Ausgabe sieht dann ungefähr so aus:

QUERY PLAN
`--SCAN employees

Das Schlüsselwort SCAN (DURCHSUCHEN) gibt an, dass SQLite einen vollständigen Tabellenscan (Full Table Scan) durchführt, was bedeutet, dass jede Zeile in der Tabelle untersucht werden muss, um die Mitarbeiter mit dem Vornamen 'John' zu finden. Dies ist weniger effizient als die Verwendung eines Index.

Hinzufügen weiterer Daten und Analysieren der Sortierung

Fügen wir weitere Daten hinzu, um die Analyse des Abfrageplans (Query Plan) aussagekräftiger zu gestalten. Fügen Sie die folgenden Daten in die Tabelle employees (Mitarbeiter) ein:

INSERT INTO employees (first_name, last_name, email, department) VALUES
('Alice', 'Johnson', '[email protected]', 'HR'),
('Bob', 'Williams', '[email protected]', 'Finance'),
('Charlie', 'Brown', '[email protected]', 'IT'),
('David', 'Miller', '[email protected]', 'Sales'),
('Eve', 'Wilson', '[email protected]', 'Marketing'),
('John', 'Taylor', '[email protected]', 'Engineering'),
('Jane', 'Anderson', '[email protected]', 'HR'),
('Robert', 'Thomas', '[email protected]', 'Finance'),
('Emily', 'Jackson', '[email protected]', 'IT'),
('Michael', 'White', '[email protected]', 'Sales');

Analysieren wir nun eine komplexere Abfrage, die eine Sortierung beinhaltet. Angenommen, Sie möchten alle Mitarbeiter in der Abteilung 'Sales' finden und diese nach Nachnamen sortieren. Sie können die folgende Abfrage verwenden:

SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

Analysieren Sie den Abfrageplan:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

Die Ausgabe könnte wie folgt aussehen:

QUERY PLAN
`--SCAN employees USING INDEX idx_lastname

In diesem Fall führt SQLite einen vollständigen Tabellenscan (Full Table Scan) durch und sortiert dann die Ergebnisse.

Erstellen wir einen Index für die Spalte department (Abteilung):

CREATE INDEX idx_department ON employees (department);

Analysieren Sie den Abfrageplan nun erneut:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

Die Ausgabe könnte sich ändern in:

QUERY PLAN
|--SEARCH employees USING INDEX idx_department (department=?)
`--USE TEMP B-TREE FOR ORDER BY

Jetzt verwendet SQLite den Index idx_department, um die Mitarbeiter in der Abteilung 'Sales' zu finden, muss aber die Ergebnisse weiterhin sortieren.

Entfernen redundanter Indizes

In diesem Schritt lernen Sie, wie Sie redundante Indizes (Redundant Indexes) in SQLite identifizieren und entfernen. Redundante Indizes können die Datenbankleistung negativ beeinflussen, indem sie den Overhead von Schreiboperationen erhöhen, ohne einen Vorteil für Leseoperationen zu bieten.

Erstellen wir einen Index für die Spalten department (Abteilung) und last_name (Nachname):

CREATE INDEX idx_department_lastname ON employees (department, last_name);

Listen wir nun alle Indizes für die Tabelle employees (Mitarbeiter) auf:

PRAGMA index_list(employees);

Sie sollten eine ähnliche Ausgabe wie diese sehen:

0|idx_lastname|0|c|0
1|idx_department|0|c|0
2|idx_department_lastname|0|c|0

Analysieren wir nun eine Abfrage, die nach department und last_name filtert:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' AND last_name = 'Doe';

Die Ausgabe könnte wie folgt aussehen:

QUERY PLAN
`--SEARCH employees USING INDEX idx_department_lastname (department=? AND last_name=?)

Diese Ausgabe zeigt, dass SQLite den Index idx_department_lastname für diese Abfrage verwendet.

Analysieren wir nun eine Abfrage, die nur nach department filtert:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';

Die Ausgabe könnte wie folgt aussehen:

QUERY PLAN
`--SEARCH employees USING INDEX idx_department (department=?)

Diese Ausgabe zeigt, dass SQLite den Index idx_department für diese Abfrage verwendet.

In diesem Szenario ist der Index idx_department_lastname redundant, da der Index idx_department für Abfragen verwendet werden kann, die nur nach department filtern. Der Index idx_department_lastname bietet nur einen Vorteil für Abfragen, die sowohl nach department als auch nach last_name filtern.

Um den redundanten Index idx_department zu entfernen, können Sie den Befehl DROP INDEX verwenden:

DROP INDEX idx_department;

Listen wir nun erneut alle Indizes für die Tabelle employees auf:

PRAGMA index_list(employees);

Sie sollten sehen, dass der Index idx_department nicht mehr aufgeführt ist.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie die Leistung von SQLite-Datenbanken mithilfe von Indizes (Indexes) optimieren können. Sie haben Single-Column-Indizes erstellt, um die Abfragegeschwindigkeit zu verbessern, Abfragepläne (Query Plans) mit EXPLAIN QUERY PLAN analysiert und redundante Indizes entfernt. Diese Fähigkeiten werden Ihnen helfen, effizientere und reaktionsschnellere SQLite-Datenbanken zu erstellen.