SQLite PRAGMA Optimierung

Einführung

In diesem Lab werden Sie die SQLite PRAGMA-Optimierung (PRAGMA tuning) erkunden, um die Datenbankleistung und -zuverlässigkeit zu verbessern. Sie werden lernen, wie Sie wichtige Aspekte des SQLite-Verhaltens mithilfe von PRAGMA-Anweisungen konfigurieren. Wir werden die Konfiguration des Journalmodus (journal mode), die Aktivierung von Fremdschlüsselprüfungen (foreign key checks), die Durchführung von Integritätsprüfungen (integrity checks) und die Anpassung der Cache-Größe behandeln.

Skills Graph

Erstellen einer Datenbank und Konfigurieren des Journalmodus (Journal Mode)

In diesem Schritt erstellen Sie eine SQLite-Datenbank und konfigurieren ihren Journalmodus. Der Journalmodus steuert, wie SQLite Transaktionen verarbeitet und die Datenintegrität sicherstellt.

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

Erstellen Sie eine SQLite-Datenbank namens test.db und rufen Sie die SQLite-Shell mit dem folgenden Befehl auf:

sqlite3 test.db

Dieser Befehl erstellt die Datenbankdatei test.db (falls sie nicht existiert) und öffnet das SQLite-Befehlszeilentool. Sie sehen eine Eingabeaufforderung wie diese:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Konfigurieren wir nun den Journalmodus. SQLite bietet verschiedene Journalmodi, darunter DELETE, TRUNCATE, PERSIST, MEMORY, WAL und OFF. WAL (Write-Ahead Logging) bietet ein gutes Gleichgewicht zwischen Leistung und Zuverlässigkeit.

Führen Sie den folgenden SQL-Befehl aus, um den Journalmodus auf WAL zu setzen:

PRAGMA journal_mode=WAL;

Dieser Befehl konfiguriert die Datenbank so, dass Write-Ahead Logging verwendet wird. WAL verbessert die Parallelität und Leistung, indem Änderungen in eine separate WAL-Datei geschrieben werden, bevor sie auf die Datenbank angewendet werden.

Um den Journalmodus zu überprüfen, führen Sie Folgendes aus:

PRAGMA journal_mode;

Erwartete Ausgabe:

wal

Dies bestätigt, dass der Journalmodus auf WAL gesetzt ist.

Aktivieren der Fremdschlüsselunterstützung (Foreign Key Support)

In diesem Schritt aktivieren Sie die Fremdschlüsselunterstützung in Ihrer SQLite-Datenbank. Fremdschlüssel erzwingen Beziehungen zwischen Tabellen und tragen zur Aufrechterhaltung der Datenintegrität bei.

Führen Sie innerhalb der SQLite-Shell (falls Sie im vorherigen Schritt beendet haben, stellen Sie mit sqlite3 test.db erneut eine Verbindung her) den folgenden Befehl aus:

PRAGMA foreign_keys = ON;

Dieser Befehl aktiviert die Fremdschlüsselüberprüfung für die aktuelle Datenbankverbindung. Beachten Sie, dass Sie Fremdschlüssel für jede neue Verbindung zur Datenbank aktivieren müssen.

Um zu überprüfen, ob die Fremdschlüsselunterstützung aktiviert ist, führen Sie Folgendes aus:

PRAGMA foreign_keys;

Erwartete Ausgabe:

1

Diese Ausgabe bestätigt, dass die Fremdschlüsselunterstützung aktiviert ist.

Erstellen von Tabellen mit Fremdschlüsselbeziehung (Foreign Key Relationship)

Nachdem die Fremdschlüsselunterstützung aktiviert ist, erstellen wir zwei Tabellen mit einer Fremdschlüsselbeziehung, um ihre Funktionalität zu demonstrieren.

Führen Sie die folgenden SQL-Befehle aus, um eine users-Tabelle und eine orders-Tabelle zu erstellen:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Diese Befehle erstellen zwei Tabellen:

• users: Speichert Benutzerinformationen mit den Spalten id (Primärschlüssel/Primary Key) und name.
• orders: Speichert Bestellinformationen mit den Spalten id (Primärschlüssel), user_id (Fremdschlüssel, der auf users.id verweist) und amount.

Die Klausel FOREIGN KEY (user_id) REFERENCES users(id) stellt eine Beziehung zwischen der orders-Tabelle und der users-Tabelle her. Sie stellt sicher, dass die user_id in der orders-Tabelle in der id-Spalte der users-Tabelle vorhanden sein muss.

Testen der Fremdschlüsselbeschränkung (Foreign Key Constraint)

In diesem Schritt testen Sie die Fremdschlüsselbeschränkung, um zu sehen, wie sie verhindert, dass ungültige Daten in die Datenbank eingefügt werden.

Fügen Sie zunächst einen Benutzer in die users-Tabelle ein:

INSERT INTO users (id, name) VALUES (1, 'Alice');

Dieser Befehl fügt einen neuen Benutzer mit der id 1 und dem name 'Alice' in die users-Tabelle ein.

Versuchen Sie nun, eine Bestellung in die orders-Tabelle mit einer user_id einzufügen, die in der users-Tabelle nicht vorhanden ist:

INSERT INTO orders (user_id, amount) VALUES (999, 100.0);

Da die Fremdschlüsselüberprüfungen aktiviert sind, schlägt dieser Befehl mit einer Fehlermeldung fehl:

Error: FOREIGN KEY constraint failed

Dies zeigt, dass die Fremdschlüsselbeschränkung korrekt funktioniert und verhindert, dass Sie eine Bestellung für einen nicht existierenden Benutzer erstellen.

Fügen Sie als Nächstes eine gültige Bestellung mit der vorhandenen user_id ein:

INSERT INTO orders (user_id, amount) VALUES (1, 100.0);

Dieser Befehl ist erfolgreich, da die user_id 1 in der users-Tabelle vorhanden ist.

Durchführung einer Integritätsprüfung (Integrity Check)

In diesem Schritt führen Sie eine Integritätsprüfung Ihrer SQLite-Datenbank durch, um sicherzustellen, dass keine Inkonsistenzen oder Fehler vorhanden sind.

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

PRAGMA integrity_check;

Dieser Befehl überprüft die gesamte Datenbank auf Inkonsistenzen oder Fehler. Wenn die Datenbank in Ordnung ist, lautet die Ausgabe ok.

Erwartete Ausgabe:

ok

Wenn die Datenbank beschädigt ist, enthält die Ausgabe Fehlermeldungen, die Art und Ort der Beschädigung angeben.

Anpassen der Cache-Größe und Beenden

In diesem letzten Schritt passen Sie die von SQLite verwendete Cache-Größe an und beenden dann die SQLite-Shell.

Die Cache-Größe bestimmt, wie viel Speicher SQLite zum Speichern kürzlich abgerufener Daten verwendet. Das Erhöhen der Cache-Größe kann die Leistung verbessern, insbesondere bei leseintensiven Arbeitslasten (read-heavy workloads).

Führen Sie den folgenden Befehl aus, um die Cache-Größe auf 4 MB (4096 KB) festzulegen:

PRAGMA cache_size = 4096;

Dieser Befehl legt die Cache-Größe für die aktuelle Datenbankverbindung auf 4 MB fest.

Um die Cache-Größe zu überprüfen, führen Sie Folgendes aus:

PRAGMA cache_size;

Erwartete Ausgabe:

4096

Beenden Sie abschließend die SQLite-Shell:

.exit

Dieser Befehl schließt die Datenbankverbindung und kehrt zur Befehlszeile zurück.

Zusammenfassung

In diesem Lab haben Sie die Optimierung von SQLite PRAGMA untersucht. Sie haben gelernt, wie Sie den Journalmodus (journal mode) für verbesserte Leistung und Zuverlässigkeit konfigurieren, die Unterstützung für Fremdschlüssel (foreign key) aktivieren, um die Datenintegrität zu erzwingen, Tabellen mit Fremdschlüsselbeziehungen erstellen, Fremdschlüsselbeschränkungen testen, Integritätsprüfungen (integrity checks) durchführen, um Datenbankbeschädigungen zu identifizieren, und die Cache-Größe anpassen, um die Speichernutzung zu optimieren. Diese Fähigkeiten bieten eine solide Grundlage für die effektive Arbeit mit SQLite-Datenbanken.