🌳InnoDB von innen
Tablespaces aus 16-KiB-Seiten, Tabellen als B+-Baum über den Primärschlüssel, ein Cache mit cleverer LRU-Liste und ein Schreibpfad, der auch einen Stromausfall übersteht.
🧱Tablespace → Segment → Extent → Seite → Zeile
Datenverzeichnis /var/lib/mysql
├── ibdata1 System-Tablespace (InnoDB-Data-Dictionary,
│ Doublewrite-Buffer; Change Buffer bis 10.11)
├── undo001 … Undo-Tablespaces (wenn eingerichtet)
├── ib_logfile0 Redo-Log (ringförmig)
└── shop/
├── kunden.frm Tabellendefinition (Serverschicht)
└── kunden.ibd eigener Tablespace (innodb_file_per_table=ON)
kunden.ibd
└── Segmente (Blatt-Segment, Nicht-Blatt-Segment je Index)
└── Extents = 64 aufeinanderfolgende Seiten = 1 MiB
└── Seite = 16 KiB (innodb_page_size, Standard)Aufbau einer Indexseite (16 384 Byte) ┌───────────────────────────────────────┐ │ FIL-Header (38 B): Seitennr., Typ, │ │ Prüfsumme, LSN, Vorgänger/Nachfolger│ ← Blätter doppelt verkettet ├───────────────────────────────────────┤ │ Seiten-Header (56 B): Ebene, Anzahl │ ├───────────────────────────────────────┤ │ infimum │ supremum (Pseudo-Datensätze)│ ├───────────────────────────────────────┤ │ Datensätze (verkettete Liste, nach │ │ Schlüssel sortiert) … wachsen ↓ │ │ freier Platz │ │ Page Directory (Slots) … wächst ↑ │ ├───────────────────────────────────────┤ │ FIL-Trailer (8 B): Prüfsumme + LSN │ └───────────────────────────────────────┘ Jede Zeile trägt versteckt: DB_TRX_ID (6 B), DB_ROLL_PTR (7 B) und – ohne Primärschlüssel – DB_ROW_ID (6 B).
💡 Clustered Index
Die Tabelle ist der B+-Baum über den Primärschlüssel. Ohne PK nimmt InnoDB den ersten UNIQUE-Index mit NOT-NULL-Spalten, sonst eine versteckte 6-Byte-Zeilen-ID.
💡 Sekundärindizes
Enthalten den Indexwert plus den Primärschlüssel – kein Zeiger auf eine Seite. Ein langer PK macht deshalb jeden Sekundärindex größer.
✅ Warum AUTO_INCREMENT?
Aufsteigende Schlüssel werden immer ganz rechts eingefügt: Seiten laufen voll, statt dauernd in der Mitte geteilt zu werden. Zufällige UUIDs als PK verteilen Einfügungen über den ganzen Baum.
🌳B+-Baum zum Anfassen
Eine echte 16-KiB-Seite fasst Hunderte Einträge – daher reichen meist 3–4 Ebenen für Millionen Zeilen. Hier sind die Seiten auf 3–5 Schlüssel verkleinert, damit Splits sichtbar werden. Probier „1…15 aufsteigend“ mit beiden Split-Arten.
Höhe 24 Seiten3 Blätter■ Pfad / Treffer□ kupfer umrandet = gerade geteilt
Was ist passiert?
- Startbaum mit 7 Schlüsseln (maximal 3 Schlüssel pro Seite).
🔗Sekundärindex → Primärschlüssel → Zeile
Ein Sekundärindex liefert nur den Primärschlüssel. Für alle anderen Spalten folgt ein zweiter Abstieg im Clustered Index – außer der Index deckt die Abfrage ab (Covering Index).
SELECT * FROM kunden WHERE name = 'Braun'① Sekundärindex idx_name (name)
Einträge = (name, Primärschlüssel). Blätter enthalten keine Zeilen, sondern den PK als Verweis.
② Clustered Index PRIMARY (id)
Mit jedem gefundenen PK ein zweiter Abstieg – hier liegt die ganze Zeile.
Treffer
id=5, Braun, Bonn · id=21, Braun, Trier
Seitenzugriffe
Sekundärindex: 3 · Clustered Index: 6 (2× Höhe 3)
Ohne Covering braucht jeder Treffer einen zusätzlichen Abstieg im Clustered Index. Bei vielen Treffern ist ein Tabellenscan manchmal billiger.
🧠Buffer Pool: LRU mit Young- und Old-Liste
Der Buffer Pool (innodb_buffer_pool_size, Standard 128 MiB) cacht Seiten im RAM. Neue Seiten kommen in die Mitte, nicht nach vorne: Erst ein weiterer Zugriff nach innodb_old_blocks_time (1000 ms) macht sie „jung“. Die Old-Liste umfasst innodb_old_blocks_pct = 37 % der Liste.
Uhr: 0 ms
◀ Kopf · Young-Liste (neu benutzt)Old-Liste · Ende (wird verdrängt) ▶
frei
frei
frei
frei
frei
frei
frei
frei
frei
frei
Treffer 0Fehlzugriffe 0Trefferquote 0 %Pages made young 0not young 0verdrängt 0
Protokoll (neueste oben)
- Tipp: Heiße Seiten laden, 1 s warten, erneut anklicken (→ made young). Dann einen Tabellenscan starten.
💡 Ansehen im echten Server
SHOW ENGINE INNODB STATUS\G → Abschnitt BUFFER POOL AND MEMORY: „Pages made young … not young …“ und die Trefferquote „Buffer pool hit rate … / 1000“.⚠️ Seit MariaDB 10.5
Es gibt nur noch eine Buffer-Pool-Instanz;
innodb_buffer_pool_instances wurde ignoriert und in 10.6 entfernt.📝Schreibpfad: Undo, Redo (WAL), Doublewrite, Checkpoint
Warum ist ein COMMIT dauerhaft, obwohl die Datenseite noch gar nicht auf der Platte liegt? Steppe durch und löse an jeder Stelle einen Stromausfall aus.
UPDATE konto SET saldo = 50 WHERE id = 1; COMMIT;Schritt 1 / 11 · Tasten ← →
Arbeitsspeicher
👤 Client
wartet auf Antwort
–
🧠 Buffer Pool
Datenseite 3:4
(nicht geladen)
↩️ Undo-Log
alte Versionen
(leer)
📋 Log-Buffer
innodb_log_buffer_size
(leer)
── fsync-Grenze: darunter überlebt einen Stromausfall ──
Platte
📝 ib_logfile0
Redo · geschrieben bis LSN 45.000.000
(nichts Neues)
Checkpoint-LSN 45.000.000
🪞 Doublewrite-Buffer
Sicherheitskopie ganzer Seiten
(leer)
🗄️ konto.ibd
Tablespace, 16-KiB-Seiten
saldo = 100
page_lsn 44.998.120
🔒 keine SperreTransaktion: keineaktuelle LSN 45.000.000
1. Ausgangslage
Auf der Platte (Tablespace konto.ibd) steht saldo = 100. Der Buffer Pool enthält die Seite noch nicht.
LSN-Werte und Satzgrößen sind Beispielwerte; Reihenfolge und Regeln sind die von InnoDB.
💡 innodb_flush_log_at_trx_commit
1 (Standard): bei jedem COMMIT schreiben + fsync – voll dauerhaft. 2: schreiben, fsync etwa einmal pro Sekunde – ein Betriebssystem-Absturz kann ~1 s kosten. 0: auch ein Absturz von mariadbd kann ~1 s kosten.
💡 Redo vs. Undo
Redo wiederholt bestätigte Änderungen nach einem Absturz (Durability). Undo macht unbestätigte rückgängig (Atomicity) und liefert alte Versionen für MVCC.
💡 Doublewrite
Schützt vor „torn pages“: Eine halb geschriebene 16-KiB-Seite kann das Redo-Log nicht reparieren, weil es nur Änderungen enthält.
innodb_doublewrite=ON ist Standard.