Partitionierung grosser Tabellen: Strategien und Grenzen

1. Was Partitionierung in MySQL leistet

Bei der Partitionierung teilt MySQL eine logisch einzelne Tabelle in mehrere physisch getrennte Speichereinheiten auf, die jeweils eine Teilmenge der Zeilen anhand einer definierten Regel enthalten. Nach aussen bleibt die Tabelle für Anwendungen und Queries eine einzige Entität, intern verwaltet der Server jede Partition jedoch wie eine eigenständige Tabelle mit eigenem Tablespace. Das unterscheidet Partitionierung grundlegend von Sharding, bei dem Daten über mehrere Datenbankinstanzen verteilt werden.

Der zentrale Nutzen liegt in zwei Bereichen: Wartungsoperationen wie das Löschen alter Daten können auf Partitionsebene statt auf Zeilenebene erfolgen, was bei Milliarden Zeilen den Unterschied zwischen Sekunden und Stunden ausmacht. Und Abfragen, die sich auf eine begrenzte Teilmenge der Partitionierungsregel beziehen, können ganze Partitionen überspringen, was als Partition Pruning bezeichnet wird und im vierten Abschnitt genauer erklärt wird.

MySQL unterstützt mehrere Partitionierungstypen: RANGE, LIST, HASH und KEY, sowie Kombinationen davon als Subpartitionierung. Jeder Typ eignet sich für unterschiedliche Datenverteilungen und Zugriffsmuster, und die Wahl des falschen Typs kann dazu führen, dass die Partitionierung keinen messbaren Nutzen bringt oder sogar die Performance verschlechtert, weil der Optimizer über alle Partitionen scannen muss.

2. RANGE-Partitionierung: Zeitbasierte Datenaufteilung

RANGE-Partitionierung ist die häufigste Form in der Praxis und teilt Zeilen anhand von Wertebereichen einer Spalte auf, typischerweise einer Datums- oder ID-Spalte. Ein klassisches Beispiel ist eine Log- oder Bestelltabelle, die nach Jahr oder Monat partitioniert wird. Jede Partition enthält dann genau die Zeilen eines bestimmten Zeitraums, was sowohl das Löschen alter Daten als auch zeitraumbezogene Abfragen erheblich beschleunigt.

Der grosse praktische Vorteil zeigt sich bei der Datenaufbewahrung: Statt DELETE FROM orders WHERE created_at < '2023-01-01' auszuführen, was bei Millionen Zeilen minuten- bis stundenlang läuft und massives Undo-Log-Wachstum verursacht, kann mit ALTER TABLE ... DROP PARTITION eine ganze Partition in Millisekunden entfernt werden. Das ist der wichtigste Grund, warum viele Teams RANGE-Partitionierung für Archivierungs- und Retention-Strategien einsetzen.

-- Range partitioning by year, based on order date
CREATE TABLE order_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATE NOT NULL,
  PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Instant retention cleanup: milliseconds instead of a long DELETE
ALTER TABLE order_history DROP PARTITION p2023;

Ein wichtiges Detail bei RANGE-Partitionierung: Die Partitionierungsspalte muss Teil jedes eindeutigen Schlüssels sein, einschliesslich des Primärschlüssels. Deshalb ist im Beispiel oben created_at Teil des Primärschlüssels, obwohl das fachlich nicht zwingend nötig wäre. Diese Einschränkung führt regelmässig zu Diskussionen im Schema-Design und wird im Abschnitt zu den Grenzen der Partitionierung detaillierter behandelt.

3. LIST- und HASH-Partitionierung im Vergleich

LIST-Partitionierung ähnelt RANGE, arbeitet aber mit expliziten Werten statt Wertebereichen. Das eignet sich für Spalten mit einer begrenzten Anzahl diskreter Kategorien, etwa eine Region- oder Mandanten-Spalte in einem Multi-Tenant-System. Jede Partition wird einer festen Liste von Werten zugeordnet, was eine klare, fachlich nachvollziehbare Datenaufteilung erlaubt, zum Beispiel eine Partition pro Land oder Vertriebsregion.

HASH- und KEY-Partitionierung verfolgen ein anderes Ziel: Statt Daten fachlich sinnvoll zu gruppieren, verteilen sie Zeilen möglichst gleichmässig über eine feste Anzahl Partitionen, basierend auf einem Hash-Wert der Partitionierungsspalte. Das reduziert Hotspots bei sehr schreibintensiven Tabellen und verteilt I/O gleichmässiger, bringt aber keinen Vorteil für zeitraum- oder kategoriebasierte Abfragen, weil der Optimizer den Hash nicht ohne den exakten Schlüsselwert vorhersagen kann.

-- List partitioning by tenant region
CREATE TABLE tenant_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  region_code VARCHAR(2) NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  occurred_at DATETIME NOT NULL,
  PRIMARY KEY (id, region_code)
) ENGINE=InnoDB
PARTITION BY LIST COLUMNS (region_code) (
  PARTITION p_eu VALUES IN ('DE', 'AT', 'CH', 'FR'),
  PARTITION p_na VALUES IN ('US', 'CA'),
  PARTITION p_other VALUES IN ('GB', 'AU', 'JP')
);

-- Hash partitioning to spread write load evenly
CREATE TABLE session_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  session_id BIGINT UNSIGNED NOT NULL,
  logged_at DATETIME NOT NULL,
  PRIMARY KEY (id, session_id)
) ENGINE=InnoDB
PARTITION BY HASH (session_id)
PARTITIONS 8;

4. Partition Pruning: wie der Optimizer profitiert

Partition Pruning ist der Mechanismus, mit dem der MySQL-Optimizer beim Ausführungsplan erkennt, dass bestimmte Partitionen für eine Abfrage irrelevant sind, und sie komplett überspringt. Das funktioniert nur, wenn die WHERE-Bedingung direkt auf die Partitionierungsspalte Bezug nimmt, etwa WHERE created_at >= '2026-01-01' bei einer nach Jahr partitionierten Tabelle. Der Optimizer prüft dann, welche Partitionen diesen Wertebereich überhaupt enthalten können, und scannt ausschliesslich diese.

Mit EXPLAIN PARTITIONS lässt sich direkt beobachten, ob Pruning tatsächlich greift. Fehlt die Partitionierungsspalte in der WHERE-Bedingung, oder wird sie in eine Funktion eingebettet, die der Optimizer nicht auflösen kann, etwa WHERE DATE_FORMAT(created_at, '%Y') = '2026' statt WHERE created_at >= '2026-01-01', greift Pruning nicht und die Abfrage muss alle Partitionen durchsuchen, was den vermeintlichen Performance-Vorteil komplett zunichtemacht.

-- EXPLAIN shows which partitions are actually scanned
EXPLAIN PARTITIONS
SELECT * FROM order_history
WHERE created_at >= '2026-01-01' AND created_at < '2026-07-01';
-- partitions: p2026 (only one partition touched, pruning works)

-- Anti-pattern: function on the partition column disables pruning
EXPLAIN PARTITIONS
SELECT * FROM order_history
WHERE YEAR(created_at) = 2026;
-- partitions: p2023,p2024,p2025,p2026,p_future (all scanned, pruning fails)

5. Wann Partitionierung wirklich hilft

Partitionierung zahlt sich klar aus, wenn zwei Bedingungen gleichzeitig erfüllt sind: Die Tabelle ist sehr gross, typischerweise im zweistelligen Gigabyte- oder Terabyte-Bereich, und es gibt eine klare, in Abfragen konsequent genutzte Partitionierungsspalte, meist ein Datum. Retention-Löschungen über DROP PARTITION statt DELETE, wie im RANGE-Abschnitt gezeigt, sind der mit Abstand häufigste erfolgreiche Anwendungsfall in der Praxis.

Auch bei Reporting- oder Analyse-Workloads, die konsequent nach Zeitraum filtern, etwa Monatsberichte oder Jahresvergleiche, bringt Partitionierung spürbare Vorteile durch Pruning. Ein weiterer Anwendungsfall ist die Verteilung von I/O bei sehr schreibintensiven Tabellen über HASH-Partitionierung, wenn ein einzelner Tablespace zum Flaschenhals wird, was allerdings seltener der eigentliche Engpass ist, als viele Teams zunächst annehmen.

6. Wann Partitionierung nicht hilft oder schadet

Ein weit verbreitetes Missverständnis ist, dass Partitionierung automatisch Abfragen beschleunigt, die keinen Bezug zur Partitionierungsspalte haben. Wird häufig nach anderen Spalten gefiltert oder sortiert, bringt Partitionierung keinen Vorteil und kann durch den zusätzlichen Verwaltungsaufwand sogar leicht langsamer werden als eine einzige, gut indizierte Tabelle. Ein sekundärer Index über alle Partitionen hinweg ist zudem kein globaler Index, sondern wird pro Partition separat gepflegt, was bei sehr vielen kleinen Partitionen den Overhead erhöht.

Auch bei kleinen bis mittelgrossen Tabellen, die problemlos in den Buffer Pool passen, bringt Partitionierung meist keinen messbaren Vorteil, weil der eigentliche Engpass ohnehin nicht Festplatten-I/O ist. In solchen Fällen ist ein guter, selektiver Index fast immer die einfachere und wartungsärmere Lösung. Partitionierung ist ein Werkzeug für sehr grosse Tabellen mit klaren Zugriffsmustern, kein generelles Performance-Upgrade.

7. Grenzen: Fremdschlüssel und Unique Keys

Die wichtigste praktische Einschränkung: InnoDB unterstützt keine Fremdschlüssel auf oder von partitionierten Tabellen. Weder darf eine partitionierte Tabelle einen Fremdschlüssel auf eine andere Tabelle referenzieren, noch darf eine andere Tabelle einen Fremdschlüssel auf eine partitionierte Tabelle setzen. In Systemen mit konsequenter referenzieller Integrität über Fremdschlüssel bedeutet das entweder den Verzicht auf Partitionierung für diese Tabelle oder die Verlagerung der Integritätsprüfung in die Anwendungslogik.

Die zweite zentrale Einschränkung betrifft eindeutige Schlüssel: Jeder Unique Key und der Primärschlüssel müssen die komplette Partitionierungsspalte enthalten. Das erzwingt bei vielen Schemas eine Anpassung des Primärschlüssels, wie im RANGE-Beispiel gezeigt, wo created_at zusätzlich zur eigentlichen ID Teil des Primärschlüssels werden musste. Für Tabellen mit mehreren, fachlich unabhängigen Unique Keys kann diese Regel Partitionierung praktisch unmöglich machen, ohne das Datenmodell grundlegend zu verändern.

8. Wartung: Partitionen anlegen, austauschen und löschen

Der operative Alltag mit partitionierten Tabellen erfordert eine Strategie, wie neue Partitionen rechtzeitig angelegt werden, bevor Daten in die Auffang-Partition MAXVALUE fallen. Ein häufiges Muster ist ein automatisierter, monatlicher oder jährlicher Job, der per ALTER TABLE ... REORGANIZE PARTITION die MAXVALUE-Partition auftrennt und eine neue, konkrete Partition für den kommenden Zeitraum schafft, bevor sie tatsächlich benötigt wird.

ALTER TABLE ... EXCHANGE PARTITION ist ein weiteres nützliches Werkzeug: Es tauscht den Inhalt einer Partition mit einer eigenständigen, nicht partitionierten Tabelle gleicher Struktur, praktisch instantan, weil nur Metadaten geändert werden. Das eignet sich hervorragend, um grosse Datenmengen aus einer partitionierten Tabelle zu archivieren, ohne einen langen, sperrenden Kopiervorgang auszuführen.

-- Split the catch-all MAXVALUE partition ahead of time
ALTER TABLE order_history REORGANIZE PARTITION p_future INTO (
  PARTITION p2027 VALUES LESS THAN (2028),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Archive a partition instantly by swapping it with a standalone table
CREATE TABLE order_history_archive_2023 LIKE order_history;
ALTER TABLE order_history_archive_2023 REMOVE PARTITIONING;

ALTER TABLE order_history
  EXCHANGE PARTITION p2023 WITH TABLE order_history_archive_2023;

9. Partitionierungsstrategien im direkten Vergleich

Die folgende Übersicht fasst zusammen, welcher Partitionierungstyp für welchen Anwendungsfall geeignet ist und worauf jeweils zu achten ist.

Die Tabelle macht deutlich: RANGE-Partitionierung ist der klare Standard für zeitbasierte Retention, LIST für kategorische Trennung, HASH ausschliesslich für I/O-Verteilung ohne fachlichen Filternutzen. Für alle anderen Fälle bleibt eine gut indizierte, nicht partitionierte Tabelle meist die robustere und wartungsärmere Wahl.

10. Zusammenfassung

Partitionierung grosser Tabellen in MySQL ist ein präzises Werkzeug für zwei Kernprobleme: schnelles Löschen alter Daten über DROP PARTITION und Partition Pruning bei konsequent zeitraumbezogenen Abfragen. RANGE-Partitionierung nach Datum ist dabei mit Abstand der häufigste und zuverlässigste Anwendungsfall. LIST eignet sich für klare Kategorien, HASH ausschliesslich zur I/O-Verteilung ohne Query-Vorteil.

Die Grenzen sind ebenso wichtig wie der Nutzen: keine Fremdschlüssel auf oder von partitionierten Tabellen, und jeder Unique Key muss die Partitionierungsspalte enthalten. Wer diese Einschränkungen vor dem Schema-Design kennt, vermeidet teure Umbauten später. Für Tabellen ohne klare, konsequent genutzte Partitionierungsspalte bleibt ein guter Index fast immer die bessere Wahl als Partitionierung.

11. FAQ: Partitionierung grosser Tabellen