Kapitel 11: Transaktionen und Indizes ⭐⭐

11.1 Transaktionen (Transactions) - Datensicherheit gewährleisten

Transaktion (Transaction) stellt sicher, dass eine Gruppe von SQL-Befehlen entweder alle erfolgreich ausgeführt werden oder gar nicht (im Fehlerfall wird alles rückgängig gemacht).

Beispiel: Banküberweisung
1. Konto A: -100€
2. Konto B: +100€

Problem: Wenn Schritt 1 gelingt, aber Schritt 2 fehlschlägt → Geld verloren!
Lösung: Transaktion verwenden → Beide Schritte werden zusammen ausgeführt (oder gar nicht)

ACID-Eigenschaften (wichtig für Interviews!)

Eigenschaft	Bedeutung	Beispiel
Atomicity (Atomarität)	Alle Befehle oder keiner	Überweisung: Belastung + Gutschrift
Consistency (Konsistenz)	Datenbank bleibt konsistent	Summe aller Kontostände bleibt gleich
Isolation (Isolation)	Transaktionen beeinflussen sich nicht	Zwei gleichzeitige Überweisungen
Durability (Dauerhaftigkeit)	Ergebnis ist dauerhaft gespeichert	Auch nach Stromausfall verfügbar

Transaktion-Steuerung

-- 1. Transaktion starten
START TRANSACTION;  -- oder BEGIN;

-- 2. SQL-Befehle ausführen
UPDATE konten SET betrag = betrag - 100 WHERE konto_id = 1;
UPDATE konten SET betrag = betrag + 100 WHERE konto_id = 2;

-- 3. Wenn alles erfolgreich: COMMIT (Speichern)
COMMIT;

-- 3. Alternative: Wenn Fehler: ROLLBACK (Rückgängig machen)
-- ROLLBACK;

Praxisbeispiel: Banküberweisung

-- Tabellen erstellen
CREATE DATABASE IF NOT EXISTS bank_db;
USE bank_db;

CREATE TABLE konten (
    konto_id INT PRIMARY KEY AUTO_INCREMENT,
    inhaber VARCHAR(100),
    betrag DECIMAL(10,2)
);

INSERT INTO konten (inhaber, betrag) VALUES 
    ('Max Mustermann', 1000.00),
    ('Lisa Schmidt', 500.00);

Überweisung durchführen:

-- Transaktion starten
START TRANSACTION;

-- Schritt 1: Max's Konto belasten
UPDATE konten SET betrag = betrag - 100 WHERE konto_id = 1;

-- Schritt 2: Lisa's Konto gutschreiben
UPDATE konten SET betrag = betrag + 100 WHERE konto_id = 2;

-- Überprüfen (noch nicht dauerhaft gespeichert!)
SELECT * FROM konten;

/*
+-----------+------------------+---------+
| konto_id  | inhaber         | betrag  |
+-----------+------------------+---------+
|         1 | Max Mustermann  |  900.00 |
|         2 | Lisa Schmidt    |  600.00 |
+-----------+------------------+---------+
*/

-- Alles erfolgreich → Speichern
COMMIT;

Fehlersimulation (ROLLBACK):

-- Transaktion starten
START TRANSACTION;

-- Schritt 1: Max's Konto belasten
UPDATE konten SET betrag = betrag - 50 WHERE konto_id = 1;

-- Oops! Fehler erkannt → Alles rückgängig machen
ROLLBACK;  -- Macht die Belastung rückgängig!

-- Überprüfen (Betrag ist wieder 1000)
SELECT * FROM konten;

11.2 Indizes (Indexes) - Abfragegeschwindigkeit erhöhen ⭐⭐

Index (Index) ist eine Datenstruktur, die den Datenbankengine hilft, Daten schneller zu finden.

Vergleich:
- Ohne Index: Wie ein Buch ohne Inhaltsverzeichnis (jede Seite durchsuchen)
- Mit Index:  Wie ein Buch mit Inhaltsverzeichnis (direkt zur Seite springen)

Wann werden Indizes verwendet?

• ✅ Bei großen Tabellen (Tausende von Datensätzen)
• ✅ Bei Feldern, die häufig in WHERE, ORDER BY, JOIN verwendet werden
• ❌ Nicht bei kleinen Tabellen (Index ist dann langsamer!)
• ❌ Nicht bei Feldern, die häufig geändert werden (Index muss mitgeändert werden)

1. Primärschlüssel-Index (Automatisch)

-- Der Primärschlüssel hat automatisch einen Index!
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,  -- Hat automatisch einen Index
    username VARCHAR(50)
);

2. Normalen Index erstellen (CREATE INDEX)

-- Index auf einem Feld erstellen
CREATE INDEX idx_username ON users(username);

-- Index auf mehreren Feldern erstellen (zusammengesetzter Index)
CREATE INDEX idx_name_age ON users(nachname, alter);

3. Eindeutigen Index erstellen (UNIQUE INDEX)

-- Eindeutiger Index (wie UNIQUE Constraint, aber schneller bei Abfragen)
CREATE UNIQUE INDEX idx_email ON users(email);

4. Indizes anzeigen

-- Alle Indizes einer Tabelle anzeigen
SHOW INDEX FROM users;

5. Index löschen

-- Index löschen
DROP INDEX idx_username ON users;

11.3 Indizes - Häufige Typen

Indextyp	Beschreibung	Beispiel
PRIMARY KEY	Eindeutig, darf nicht NULL sein	PRIMARY KEY (id)
UNIQUE	Eindeutig, darf NULL sein	UNIQUE INDEX idx_email
INDEX / KEY	Normaler Index (nicht eindeutig)	INDEX idx_username
FULLTEXT	Für Volltextsuche (Texte)	FULLTEXT INDEX idx_inhalt

11.4 Praxisbeispiel: Index erstellen und testen

Schritt 1: Testtabelle mit vielen Daten erstellen

-- Datenbank erstellen
CREATE DATABASE IF NOT EXISTS performance_db;
USE performance_db;

-- Tabelle erstellen
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    alter INT,
    stadt VARCHAR(50)
);

-- Viele Testdaten einfügen (1000 Datensätze)
-- (In der Praxis würde man ein Skript verwenden)
INSERT INTO users (username, email, alter, stadt)
VALUES 
    ('user1', 'user1@email.de', 25, 'Berlin'),
    ('user2', 'user2@email.de', 30, 'Hamburg'),
    -- ... (Wiederholen für 1000 Benutzer)
    ('user1000', 'user1000@email.de', 40, 'München');

Schritt 2: Abfrage ohne Index (langsam)

-- Abfrage ohne Index (vollständige Tabellensuche)
SELECT * FROM users WHERE email = 'user500@email.de';
-- Dauert lange bei großen Tabellen!

Schritt 3: Index erstellen

-- Index auf "email" erstellen
CREATE INDEX idx_email ON users(email);

Schritt 4: Abfrage mit Index (schnell)

-- Abfrage mit Index (Index wird verwendet)
SELECT * FROM users WHERE email = 'user500@email.de';
-- Viel schneller!

Schritt 5: Index verifizieren

-- Indizes anzeigen
SHOW INDEX FROM users;

11.5 Indizes - Verwendungsrichtlinien

✅ Index erstellen wenn:

1. Feld wird häufig in WHERE verwendet
2. Feld wird häufig in JOIN verwendet
3. Feld wird häufig in ORDER BY / GROUP BY verwendet
4. Tabelle hat viele Datensätze (ab ca. 1000)

❌ Index vermeiden wenn:

1. Tabelle hat wenige Datensätze (unter 1000)
2. Feld wird häufig geändert (UPDATE, INSERT, DELETE wird langsamer!)
3. Feld hat viele NULL-Werte
4. Zu viele Indizes auf einer Tabelle (max. 5-7 Indizes empfohlen)

Zusammenfassung

In diesem Kapitel hast du gelernt:

• ✅ Was Transaktionen sind (ACID-Eigenschaften)
• ✅ Transaktionen zu steuern (START TRANSACTION, COMMIT, ROLLBACK)
• ✅ Praxisbeispiel: Banküberweisung
• ✅ Was Indizes sind (Vergleich mit Buch-Inhaltsverzeichnis)
• ✅ Indizes zu erstellen (CREATE INDEX)
• ✅ Indizes zu löschen (DROP INDEX)
• ✅ Wann Indizes verwendet werden sollten (und wann nicht)

Übungen

1. Theorie: Was bedeutet ACID (in eigenen Worten)?
2. Praxis: Simuliere eine Banküberweisung mit START TRANSACTION und COMMIT
3. Praxis: Simuliere einen Fehler mit ROLLBACK
4. Praxis: Erstelle einen Index auf das Feld email in der users-Tabelle
5. Praxis: Lösche den Index wieder

Nächstes Kapitel

Im nächsten Kapitel lernen wir MySQL häufig verwendete Funktionen - String-Funktionen, Datumsfunktionen, mathematische Funktionen!