🧰 Tipp 1★★ FortgeschrittenSoft DeleteViewpartieller Index

Soft Delete: löschen, ohne zu löschen

😖 Problem

Gelöschte Kunden sollen wiederherstellbar bleiben und in Auswertungen der Vergangenheit auftauchen. Aber: Wer sich nach dem Löschen mit derselben E-Mail neu registriert, scheitert am UNIQUE-Index.

💡 Lösung

Statt DELETE ein Zeitstempel geloescht_am. Eine Sicht zeigt nur aktive Zeilen, und ein partieller UNIQUE-Index prüft die Eindeutigkeit nur unter den aktiven. So darf dieselbe Adresse einmal aktiv und beliebig oft gelöscht existieren.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

-- „Löschen“
UPDATE konten SET geloescht_am = '2026-03-01' WHERE konto_id = 1;

-- Neuregistrierung mit derselben Adresse klappt (alte Zeile zählt nicht mehr)
INSERT INTO konten (konto_id, email, name) VALUES (3, 'anna.berger@example.org', 'Anna Berger');

-- Die Anwendung liest nur die Sicht
SELECT * FROM konten_aktiv ORDER BY konto_id;

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

CREATE UNIQUE INDEX ux_konten_email_aktiv
  ON konten (email) WHERE geloescht_am IS NULL;
CREATE VIEW konten_aktiv AS
  SELECT * FROM konten WHERE geloescht_am IS NULL;

Mit Row Level Security ließe sich der Filter sogar erzwingen.

MySQL / MariaDBfehlt – Ersatzweg

nur gezeigt, nicht ausgeführt

-- keine partiellen Indizes: Hilfsspalte, die für Gelöschte NULL ist
ALTER TABLE konten ADD email_aktiv VARCHAR(255)
  AS (IF(geloescht_am IS NULL, email, NULL)) STORED;
CREATE UNIQUE INDEX ux_konten_email_aktiv ON konten (email_aktiv);

Mehrere NULL-Werte sind in einem UNIQUE-Index erlaubt – die gelöschten Zeilen stören also nicht.

SQL Serverab 2008 (gefilterte Indizes)

nur gezeigt, nicht ausgeführt

CREATE UNIQUE INDEX ux_konten_email_aktiv
  ON konten (email) WHERE geloescht_am IS NULL;

Oracle

nur gezeigt, nicht ausgeführt

CREATE UNIQUE INDEX ux_konten_email_aktiv ON konten (
  CASE WHEN geloescht_am IS NULL THEN email END);

SQLiteab 3.8.0

▶ ausgeführt · sql.js

CREATE UNIQUE INDEX ux_konten_email_aktiv
  ON konten (email) WHERE geloescht_am IS NULL;

⚠️ Fallstricke

Jede Abfrage muss den Filter kennen – eine vergessene Stelle zeigt gelöschte Daten. Deshalb die Sicht (oder RLS) als einzige Schnittstelle.
Fremdschlüssel wissen nichts von Soft Delete: Bestellungen können weiter auf „gelöschte“ Kunden zeigen – oft gewollt, aber bewusst entscheiden.
DSGVO: Soft Delete ist kein Löschen im Sinne des Rechts auf Löschung. Personenbezogene Felder nach Frist anonymisieren oder wirklich löschen.

📚 Belege

SQLite: Partielle Indizes – partielle Indizes seit 3.8.0
PostgreSQL: Partielle Indizes – CREATE UNIQUE INDEX … WHERE …
SQL Server: Gefilterte Indizes (2008) – gefilterte Indizes seit 2008
MySQL: CREATE INDEX (funktionale Schlüsselteile) – funktionale Indizes seit 8.0.13, keine WHERE-Klausel (keine partiellen Indizes)

🧰 Tipp 2★★ FortgeschrittenOFFSETKeysetRow Values

Paginierung: OFFSET vs. Keyset (Seek-Methode)

😖 Problem

LIMIT 20 OFFSET 100000 ist bequem – aber die Datenbank muss die 100 000 übersprungenen Zeilen trotzdem lesen und verwerfen. Und kommt zwischen zwei Seiten ein neuer Datensatz dazu, verrutscht alles.

💡 Lösung

Keyset-Paginierung merkt sich den Sortierschlüssel der letzten gezeigten Zeile und fragt „die nächsten 4 nach diesem Schlüssel“. Mit passendem Index springt die Datenbank direkt dorthin – Seite 5000 ist so schnell wie Seite 1. Das Diagramm unten zeigt, wie viele Zeilen je Verfahren gelesen werden.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

-- Seite 2 mit OFFSET (je 4 Bestellungen, sortiert nach Datum)
SELECT bestell_id, datum, betrag FROM bestellungen
 ORDER BY datum, bestell_id
 LIMIT 4 OFFSET 4;

-- Seite 2 mit Keyset: letzte Zeile von Seite 1 war (2026-01-20, 104)
SELECT bestell_id, datum, betrag FROM bestellungen
 WHERE (datum, bestell_id) > ('2026-01-20', 104)
 ORDER BY datum, bestell_id
 LIMIT 4;

SQL-Engine wird geladen …

📉 Wie viele Zeilen werden für Seite 50 gelesen? (Modell: 1.000.000 Zeilen, 20 pro Seite)

LIMIT 20 OFFSET 9801.000 Zeilen gelesen, 980 verworfen

WHERE (datum, id) > (…) LIMIT 203 Indexseiten abwärts + 20 Zeilen

OFFSET:  [████████████████ übersprungen ████████████████][20 gezeigt]  → Aufwand ~ Seite × 20
Keyset:  Index-Baum ─► direkt zur letzten gezeigten Zeile ─►[20 gezeigt]  → Aufwand ~ konstant
         (B-Baum mit ~200 Einträgen je Seite: 3 Ebenen für 1.000.000 Zeilen)

Vereinfachtes Modell zur Veranschaulichung – echte Kosten hängen von Index, Cache und Zeilenbreite ab. Das Verhältnis (linear vs. konstant) gilt aber für alle großen Datenbanken.

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

SELECT * FROM bestellungen
 WHERE (datum, bestell_id) > ($1, $2)
 ORDER BY datum, bestell_id
 LIMIT 20;   -- Index auf (datum, bestell_id)

MySQL / MariaDB

nur gezeigt, nicht ausgeführt

SELECT * FROM bestellungen
 WHERE (datum, bestell_id) > (?, ?)
 ORDER BY datum, bestell_id
 LIMIT 20;

Row-Value-Vergleiche werden unterstützt; ob der Index optimal genutzt wird, mit EXPLAIN prüfen – notfalls die ausgeschriebene Form (siehe SQL Server).

SQL Serverab 2012 (OFFSET/FETCH)

nur gezeigt, nicht ausgeführt

-- keine Row Values: ausgeschrieben
SELECT * FROM bestellungen
 WHERE datum > @datum OR (datum = @datum AND bestell_id > @id)
 ORDER BY datum, bestell_id
 OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

Oracleab 12c (FETCH FIRST)

nur gezeigt, nicht ausgeführt

SELECT * FROM bestellungen
 WHERE datum > :datum OR (datum = :datum AND bestell_id > :id)
 ORDER BY datum, bestell_id
 FETCH FIRST 20 ROWS ONLY;

SQLiteab 3.15.0 (Row Values)

▶ ausgeführt · sql.js

SELECT * FROM bestellungen
 WHERE (datum, bestell_id) > (?, ?)
 ORDER BY datum, bestell_id
 LIMIT 20;

⚠️ Fallstricke

Der Sortierschlüssel muss eindeutig sein – sonst gehen Zeilen mit gleichem Datum an der Seitengrenze verloren. Deshalb (datum, bestell_id).
Keyset kann nicht „direkt zu Seite 37“ springen – ideal für Endlos-Scrollen und „Weiter“-Knöpfe, weniger für nummerierte Seiten.
Absteigend sortiert kehrt sich der Vergleich um: (datum, id) < (…). Gemischte Richtungen (datum DESC, id ASC) lassen sich nicht als ein Row-Value-Vergleich schreiben.

📚 Belege

SQLite: Row Values – (a, b) > (x, y) – Row-Value-Vergleiche (u. a. für Keyset-Paginierung)
SQL Server: ORDER BY – OFFSET/FETCH – OFFSET … FETCH seit 2012 (11.x)
Oracle: Neue Features 12.1 – FETCH FIRST/OFFSET und Identity-Spalten seit 12.1

Grundlagen in der Schwester-App

↗ VisualSql: Indizes & B+-Baum

🧰 Tipp 3★★★ ProfiBatchLIMITSperren

Sicheres Batch-Delete: große Mengen in kleinen Portionen

😖 Problem

Protokolleinträge vor 2026 sollen weg – bei 50 Millionen Zeilen sperrt ein einziges DELETE die Tabelle minutenlang, bläht Log/WAL auf und lässt Replikate hinterherhinken.

💡 Lösung

In Portionen löschen: je Durchlauf höchstens N Zeilen (hier 4), stabil nach Primärschlüssel sortiert, jede Portion in eigener kurzer Transaktion – wiederholen, bis 0 Zeilen betroffen sind. Der Stepper unten führt Batch für Batch in SQLite aus.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

DELETE FROM protokoll
 WHERE id IN (SELECT id FROM protokoll
               WHERE zeit < '2026-01-01'
               ORDER BY id
               LIMIT 4);
-- in der Anwendung: wiederholen, solange changes() > 0
SELECT changes() AS geloescht_in_diesem_batch,
       (SELECT COUNT(*) FROM protokoll WHERE zeit < '2026-01-01') AS noch_alt;

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

WITH batch AS (
  SELECT id FROM protokoll
   WHERE zeit < '2026-01-01' ORDER BY id LIMIT 5000
   FOR UPDATE SKIP LOCKED)   -- parallele Löscher kommen sich nicht in die Quere
DELETE FROM protokoll p USING batch b WHERE p.id = b.id;

MySQL / MariaDB

nur gezeigt, nicht ausgeführt

DELETE FROM protokoll
 WHERE zeit < '2026-01-01'
 ORDER BY id
 LIMIT 5000;       -- direkt erlaubt (nur einfache Tabelle)

Wiederholen, solange ROW_COUNT() > 0.

SQL Server

nur gezeigt, nicht ausgeführt

WHILE 1 = 1
BEGIN
  DELETE TOP (5000) FROM protokoll WHERE zeit < '2026-01-01';
  IF @@ROWCOUNT = 0 BREAK;
END;

Kleine Batches vermeiden auch die Sperreskalation auf Tabellenebene.

Oracle

nur gezeigt, nicht ausgeführt

BEGIN
  LOOP
    DELETE FROM protokoll WHERE zeit < DATE '2026-01-01' AND ROWNUM <= 5000;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
  COMMIT;
END;
/

SQLite

▶ ausgeführt · sql.js

DELETE FROM protokoll
 WHERE id IN (SELECT id FROM protokoll
               WHERE zeit < '2026-01-01' ORDER BY id LIMIT 5000);
-- DELETE … LIMIT direkt nur mit SQLITE_ENABLE_UPDATE_DELETE_LIMIT

changes() liefert die Zahl der zuletzt geänderten Zeilen.

⚠️ Fallstricke

Ohne Index auf dem Filter (zeit) sucht jeder Batch erneut per Tabellenscan – dann wird das Batchen langsamer als ein großes DELETE.
Alle Batches in einer Transaktion bringen nichts: Sperren und Log wachsen genauso. Zwischen den Batches committen (und evtl. kurz pausieren).
Für „alles älter als X“ in riesigen Tabellen ist Partitionierung nach Zeit oft die bessere Antwort: Partition abhängen statt Zeilen löschen.

📚 Belege

SQLite: WITH-Klausel (rekursive CTEs) – WITH RECURSIVE

🧰 Tipp 4★★ FortgeschrittenJSON->>json_each

JSON-Spalten abfragen und aufklappen

😖 Problem

Bestelldetails (Zahlungsart, Tags) liegen als JSON in einer Spalte. Gesucht: alle Kartenzahlungen und eine Liste „wie oft kommt welcher Tag vor“.

💡 Lösung

->> holt einen Wert als SQL-Text/Zahl heraus, json_each (SQLite) bzw. jsonb_array_elements_text (PostgreSQL) klappt ein Array in Zeilen auf – danach geht normales GROUP BY.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

-- Werte herausholen und filtern
SELECT auftrag_id, details ->> '$.zahlung' AS zahlung, details ->> '$.betrag' AS betrag
  FROM auftraege
 WHERE details ->> '$.zahlung' = 'karte';

-- Array aufklappen und zählen
SELECT t.value AS tag, COUNT(*) AS anzahl
  FROM auftraege a, json_each(a.details, '$.tags') AS t
 GROUP BY t.value
 ORDER BY anzahl DESC, tag;

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

SELECT details ->> 'zahlung' FROM auftraege
 WHERE details @> '{"zahlung":"karte"}';     -- nutzt GIN-Index
SELECT tag, COUNT(*)
  FROM auftraege, jsonb_array_elements_text(details -> 'tags') AS tag
 GROUP BY tag;

jsonb statt json: binär gespeichert, indizierbar (GIN), Schlüsselreihenfolge nicht erhalten.

MySQL / MariaDBab MySQL 5.7.13 (->>)

nur gezeigt, nicht ausgeführt

SELECT details ->> '$.zahlung' FROM auftraege
 WHERE details ->> '$.zahlung' = 'karte';
SELECT jt.tag, COUNT(*)
  FROM auftraege,
       JSON_TABLE(details, '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')) AS jt
 GROUP BY jt.tag;

SQL Serverab 2016 (JSON_VALUE/OPENJSON)

nur gezeigt, nicht ausgeführt

SELECT JSON_VALUE(details, '$.zahlung') FROM auftraege
 WHERE JSON_VALUE(details, '$.zahlung') = 'karte';
SELECT t.value AS tag, COUNT(*)
  FROM auftraege CROSS APPLY OPENJSON(details, '$.tags') t
 GROUP BY t.value;

OPENJSON braucht Kompatibilitätslevel 130 oder höher.

Oracle

nur gezeigt, nicht ausgeführt

SELECT JSON_VALUE(details, '$.zahlung') FROM auftraege
 WHERE JSON_VALUE(details, '$.zahlung') = 'karte';
SELECT jt.tag, COUNT(*)
  FROM auftraege,
       JSON_TABLE(details, '$.tags[*]' COLUMNS (tag VARCHAR2(50) PATH '$')) jt
 GROUP BY jt.tag;

SQLiteab 3.38.0 (->>)

▶ ausgeführt · sql.js

SELECT details ->> '$.zahlung' FROM auftraege;
SELECT t.value, COUNT(*)
  FROM auftraege, json_each(details, '$.tags') t
 GROUP BY t.value;

Vor 3.38.0: json_extract(details, '$.zahlung').

⚠️ Fallstricke

-> liefert JSON, ->> den SQL-Wert. details -> 'zahlung' = 'karte' vergleicht JSON mit Text und findet in PostgreSQL nichts (bzw. Fehler).
Was ständig gefiltert wird, gehört in eine echte Spalte (oder Generated Column mit Index) – JSON ist für wirklich variable Teile.
Zahlen aus JSON kommen je nach Dialekt als Text zurück: vor dem Rechnen casten.

📚 Belege

SQLite: JSON-Funktionen – -> und ->> seit 3.38.0, json_each, json_object
PostgreSQL: JSON-Funktionen und -Operatoren – ->, ->>, jsonb_array_elements_text
MySQL: JSON-Funktionen – ->, ->>, JSON_EXTRACT
SQL Server: JSON_VALUE – JSON_VALUE seit 2016 (13.x)
SQL Server: OPENJSON – OPENJSON seit 2016, Kompatibilitätslevel 130

🧰 Tipp 5★ EinsteigerEXPLAINIndexAusführungsplan

EXPLAIN in 60 Sekunden: SCAN oder SEARCH?

😖 Problem

Eine Abfrage ist langsam. Bevor man rät, fragt man die Datenbank, wie sie die Abfrage ausführen will.

💡 Lösung

EXPLAIN QUERY PLAN (SQLite) bzw. EXPLAIN zeigt den Plan: SCAN bestellungen liest die ganze Tabelle, SEARCH … USING INDEX springt über einen Index direkt zu den passenden Zeilen. Unten derselbe Plan vor und nach CREATE INDEX. Die Grundlagen (B+-Baum, Kosten) erklärt VisualSql ausführlich.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

EXPLAIN QUERY PLAN SELECT * FROM bestellungen WHERE kunde_id = 3;

CREATE INDEX ix_bestellungen_kunde ON bestellungen (kunde_id);

EXPLAIN QUERY PLAN SELECT * FROM bestellungen WHERE kunde_id = 3;

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

EXPLAIN SELECT * FROM bestellungen WHERE kunde_id = 3;
EXPLAIN (ANALYZE, BUFFERS) SELECT …;   -- führt wirklich aus!

Bei winzigen Tabellen ist der Seq Scan billiger – der Planer entscheidet nach Kosten und Statistik.

MySQL / MariaDB

nur gezeigt, nicht ausgeführt

EXPLAIN SELECT * FROM bestellungen WHERE kunde_id = 3;
EXPLAIN ANALYZE SELECT …;   -- MySQL 8.0.18+

SQL Server

nur gezeigt, nicht ausgeführt

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM bestellungen WHERE kunde_id = 3;
-- oder im SSMS: „Tatsächlichen Ausführungsplan einschließen“ (Strg+M)

Oracle

nur gezeigt, nicht ausgeführt

EXPLAIN PLAN FOR SELECT * FROM bestellungen WHERE kunde_id = 3;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQLite

▶ ausgeführt · sql.js

EXPLAIN QUERY PLAN SELECT * FROM bestellungen WHERE kunde_id = 3;
-- EXPLAIN (ohne QUERY PLAN) zeigt den Bytecode der VM

⚠️ Fallstricke

Pläne auf Testdaten mit 12 Zeilen sagen wenig über Produktion mit 12 Millionen – Statistiken aktuell halten (ANALYZE).
EXPLAIN ANALYZE führt die Abfrage aus – bei UPDATE/DELETE in eine Transaktion mit ROLLBACK packen.

📚 Belege

SQLite: EXPLAIN QUERY PLAN – SCAN / SEARCH im Abfrageplan
PostgreSQL: EXPLAIN – EXPLAIN / EXPLAIN ANALYZE

Grundlagen in der Schwester-App

↗ VisualSql: Indizes, B+-Baum und echte EXPLAIN-Pläne