SQLite Datenbankwartung

Einführung

In diesem Lab lernen Sie, wie Sie Ihre SQLite-Datenbank für eine optimale Leistung warten. Wir werden wichtige Techniken behandeln, wie z. B. die Verwendung des VACUUM-Befehls, um Speicherplatz freizugeben, das Neuerstellen von Indizes, um Abfragen zu beschleunigen, und das Analysieren von Tabellenstatistiken, um SQLite bei besseren Entscheidungen zu helfen. Los geht's!

Erstellen einer Datenbank und einer Beispieltabelle

Bevor wir uns mit der Wartung befassen, erstellen wir eine SQLite-Datenbank und eine Beispieltabelle mit einigen Daten. Dies gibt uns etwas, womit wir arbeiten können.

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

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

sqlite3 mydb.db

Dieser Befehl erstellt sowohl die Datenbankdatei (falls sie nicht existiert) als auch öffnet das SQLite-Befehlszeilentool und verbindet Sie mit der Datenbank. Sie sehen die sqlite>-Eingabeaufforderung.

Erstellen wir nun eine Tabelle namens users, um Benutzerinformationen zu speichern. Führen Sie den folgenden SQL-Befehl aus:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);

Dieser Befehl erstellt eine Tabelle mit drei Spalten: id (eine Ganzzahl, die jeden Benutzer eindeutig identifiziert), name (der Name des Benutzers) und email (die E-Mail-Adresse des Benutzers). Die PRIMARY KEY-Beschränkung (Primärschlüssel-Beschränkung) stellt sicher, dass jede id eindeutig ist.

Fügen wir als Nächstes einige Beispieldaten in die Tabelle users ein:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');

Diese Befehle fügen der Tabelle users drei Zeilen hinzu.

Um zu überprüfen, ob die Tabelle und die Daten erfolgreich erstellt wurden, führen Sie den folgenden Befehl aus:

SELECT * FROM users;

Erwartete Ausgabe:

1|Alice|alice@example.com
2|Bob|bob@example.com
3|Charlie|charlie@example.com

Diese Ausgabe zeigt den Inhalt der Tabelle users.

Simulieren des Löschens von Daten

Um die Wirkung von VACUUM zu demonstrieren, müssen wir das Löschen von Daten simulieren, was zu einer Fragmentierung in der Datenbankdatei führen kann.

Löschen wir eine Zeile aus der Tabelle users:

DELETE FROM users WHERE id = 2;

Dieser Befehl entfernt die Zeile, in der die id 2 ist (Bobs Datensatz).

Um das Löschen zu bestätigen, führen Sie den folgenden Befehl aus:

SELECT * FROM users;

Erwartete Ausgabe:

1|Alice|alice@example.com
3|Charlie|charlie@example.com

Sie werden feststellen, dass Bobs Datensatz nicht mehr in der Tabelle vorhanden ist. Dieses Löschen hinterlässt eine Lücke in der Datenbankdatei, die VACUUM beheben kann.

VACUUM ausführen, um Speicherplatz freizugeben

Verwenden wir nun den Befehl VACUUM, um die Datenbankdatei zu defragmentieren und den durch den gelöschten Datensatz freigewordenen Speicherplatz zurückzugewinnen.

Führen Sie den folgenden Befehl in der SQLite-Shell aus:

VACUUM;

Dieser Befehl schreibt die gesamte Datenbankdatei neu, konsolidiert die Daten und entfernt den leeren Speicherplatz.

Sie sehen keine spezifische Ausgabe nach dem Ausführen von VACUUM, aber es arbeitet im Hintergrund, um Ihre Datenbank zu optimieren.

Um die SQLite-Shell zu verlassen, führen Sie Folgendes aus:

.exit

Sie befinden sich nun wieder in Ihrem Terminal.

Einen Index erstellen

Indizes (Indexes) sind entscheidend, um Abfragen (Queries) zu beschleunigen. Erstellen wir einen Index für die Spalte email der Tabelle users.

Stellen Sie zunächst erneut eine Verbindung zur SQLite-Datenbank her:

sqlite3 mydb.db

Erstellen Sie nun den Index mit dem folgenden Befehl:

CREATE INDEX idx_users_email ON users (email);

Dieser Befehl erstellt einen Index namens idx_users_email für die Spalte email. SQLite verwendet diesen Index, um Benutzer schnell anhand ihrer E-Mail-Adresse zu finden.

Den Index neu aufbauen

Im Laufe der Zeit können Indizes (Indexes) fragmentiert werden, insbesondere nach vielen Datenänderungen. Das Neuaufbauen des Index kann seine Effizienz verbessern.

Um den gerade erstellten Index neu aufzubauen, verwenden Sie den Befehl REINDEX:

REINDEX idx_users_email;

Dieser Befehl baut den Index idx_users_email neu auf und stellt sicher, dass er für die aktuellen Daten optimiert ist.

Um die SQLite-Shell zu verlassen, führen Sie Folgendes aus:

.exit

Sie befinden sich nun wieder in Ihrem Terminal.

Tabellenstatistiken analysieren

SQLite verwendet Statistiken über Ihre Daten, um Abfragen (Queries) zu optimieren. Es ist eine gute Praxis, diese Statistiken regelmäßig zu aktualisieren.

Stellen Sie zunächst erneut eine Verbindung zur SQLite-Datenbank her:

sqlite3 mydb.db

Um die Tabelle users zu analysieren, führen Sie den folgenden Befehl aus:

ANALYZE users;

Dieser Befehl sammelt Statistiken über die Tabelle users, die der Query Optimizer (Abfrageoptimierer) verwenden kann, um die Abfrageleistung zu verbessern.

Verlassen Sie abschließend die SQLite-Shell:

.exit

Sie befinden sich nun wieder in Ihrem Terminal.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie wichtige Wartungsaufgaben in Ihrer SQLite-Datenbank durchführen. Sie haben den Befehl VACUUM verwendet, um Speicherplatz freizugeben, einen Index (Index) erstellt und neu aufgebaut, um die Abfrageleistung (Query Performance) zu verbessern, und Tabellenstatistiken analysiert, um SQLite bei der Optimierung von Abfragen zu unterstützen. Diese Techniken sind entscheidend, um Ihre Datenbank reibungslos und effizient am Laufen zu halten.