Partitionierung in MySQL: Wann sie hilft und wann nicht

1. Was MySQL Partitionierung wirklich bedeutet

MySQL Partitionierung teilt eine logische Tabelle intern in mehrere physische Segmente auf. Für Abfragen bleibt die Tabelle eine einzige Einheit — der MySQL-Optimizer entscheidet aber selbst, welche Partitionen er überhaupt lesen muss. Das Ergebnis ist potenziell massiv reduzierte I/O-Last, weil irrelevante Datensegmente komplett übersprungen werden.

Das klingt nach einer universellen Lösung für große Tabellen. In der Praxis ist MySQL Partitionierung jedoch ein gezieltes Werkzeug mit klaren Voraussetzungen. Sie hilft zuverlässig, wenn Abfragen den Partitionsschlüssel im WHERE-Filter nutzen und wenn Daten regelmäßig archiviert oder gelöscht werden müssen. Außerhalb dieser Szenarien bringt sie häufig mehr Verwaltungsaufwand als Nutzen.

Für Magento-Shops ist das besonders relevant: Log-Tabellen, sales_order und sales_order_item wachsen mit jedem Bestellmonat. Ohne Archivierungsstrategie werden diese Tabellen irgendwann zum Flaschenhals. Partitionierung nach Datum ist dort ein natürlicher Ansatz — vorausgesetzt, die Abfragen werden entsprechend formuliert und der Partitionsschlüssel taucht direkt im WHERE-Filter auf.

Wichtig ist außerdem der Unterschied zu einem Composite Index: Ein gut gewählter Index reduziert die gelesenen Zeilen innerhalb einer Tabelle. MySQL Partitionierung eliminiert ganze Tabellensegmente aus dem Lesepfad. Beide Techniken ergänzen sich, aber sie lösen unterschiedliche Probleme. Wer nur einen Index braucht, sollte nicht gleich zur Partitionierung greifen.

2. Partitionstypen: RANGE, LIST, HASH und KEY

MySQL unterstützt vier grundlegende Partitionstypen. Die Wahl hängt vom Zugriffspattern und der Natur der Partitionsspalte ab. Jeder Typ hat seinen optimalen Einsatzbereich — und klare Schwächen, wenn er außerhalb dieses Bereichs eingesetzt wird.

RANGE-Partitionierung ist die häufigste Form für zeitbasierte Daten. Jede Partition enthält Zeilen, deren Partitionsschlüssel in einem bestimmten Wertebereich liegt. Das passt ideal zu Log-Tabellen und Bestelltabellen, die nach Monat oder Quartal abgefragt und archiviert werden.

-- RANGE partition by quarter using UNIX_TIMESTAMP
CREATE TABLE order_log (
    id         INT          NOT NULL AUTO_INCREMENT,
    order_id   INT          NOT NULL,
    created_at DATETIME     NOT NULL,
    message    VARCHAR(255),
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p_2024_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION p_2024_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01 00:00:00')),
    PARTITION p_2024_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01 00:00:00')),
    PARTITION p_2024_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01 00:00:00')),
    PARTITION p_future   VALUES LESS THAN MAXVALUE
);

LIST-Partitionierung eignet sich für diskrete Wertmengen. In Magento-Setups mit mehreren Stores lässt sich damit nach store_id partitionieren, sodass Abfragen für einen einzelnen Store nur dessen Partition lesen. Das funktioniert jedoch nur dann performant, wenn Abfragen konsequent store_id im WHERE-Filter nutzen.

-- LIST partition by store_id for multi-store Magento setup
CREATE TABLE catalog_log (
    id       INT NOT NULL AUTO_INCREMENT,
    store_id SMALLINT UNSIGNED NOT NULL,
    event    VARCHAR(100),
    PRIMARY KEY (id, store_id)
)
PARTITION BY LIST (store_id) (
    PARTITION p_store_de  VALUES IN (1, 2),
    PARTITION p_store_at  VALUES IN (3, 4),
    PARTITION p_store_ch  VALUES IN (5, 6),
    PARTITION p_default   VALUES IN (0)
);

HASH-Partitionierung verteilt Zeilen gleichmäßig über eine feste Anzahl von Partitionen. MySQL berechnet intern MOD(Ausdruck, Anzahl). Das eignet sich, wenn es kein natürliches Abfragemuster nach Partitionsschlüssel gibt, aber I/O-Last über mehrere Festplatten verteilt werden soll. Pruning funktioniert hier nur bei exakten Gleichheitsbedingungen.

KEY-Partitionierung funktioniert ähnlich wie HASH, überlässt aber MySQL die Hash-Funktion. KEY kann auf jeder Spalte mit einem Index angewendet werden — auch auf VARCHAR oder DATETIME — was sie flexibler macht als HASH. In der Praxis ist KEY häufig die bessere Wahl, wenn kein Integer-Wert als Partitionsschlüssel vorhanden ist.

-- HASH partition for even distribution across 8 partitions
CREATE TABLE session_data (
    session_id CHAR(64)     NOT NULL,
    user_id    INT          NOT NULL,
    data       TEXT,
    PRIMARY KEY (session_id, user_id)
)
PARTITION BY HASH (user_id)
PARTITIONS 8;

3. Partition Pruning: Wie der Optimizer Partitionen überspringt

Partition Pruning ist der entscheidende Mechanismus: MySQL liest nur die Partitionen, die für eine Abfrage relevant sein können. Ohne Pruning wäre Partitionierung für die meisten Abfragen sogar langsamer als eine normale Tabelle — denn der Overhead der Partitionsverwaltung würde ohne Benefit anfallen.

Pruning funktioniert, wenn die WHERE-Bedingung den Partitionsschlüssel direkt enthält. Mit EXPLAIN kann man prüfen, welche Partitionen tatsächlich gelesen werden. In MySQL 8 zeigt EXPLAIN FORMAT=JSON die geprüften Partitionen explizit, ältere Varianten nutzen EXPLAIN PARTITIONS.

-- Check which partitions are accessed (MySQL 8+)
EXPLAIN
SELECT entity_id, increment_id, grand_total
FROM sales_order
WHERE created_at >= '2024-10-01'
  AND created_at <  '2025-01-01';

-- Classic variant: EXPLAIN PARTITIONS (still works in MySQL 8)
EXPLAIN PARTITIONS
SELECT entity_id, increment_id
FROM sales_order
WHERE created_at >= '2024-10-01'
  AND created_at <  '2025-01-01';

-- Check partition statistics in information_schema
SELECT partition_name, table_rows, partition_description
FROM information_schema.partitions
WHERE table_schema = DATABASE()
  AND table_name = 'sales_order'
ORDER BY partition_ordinal_position;

Das Ergebnis zeigt im Feld partitions, welche Partitionen der Optimizer für die Abfrage in Betracht zieht. Idealerweise erscheint dort nur eine oder wenige Partitionen statt aller. Wenn alle Partitionen aufgeführt sind, funktioniert Pruning nicht — häufig weil der WHERE-Filter eine Funktion auf dem Partitionsschlüssel enthält oder der Filter aus einem JOIN abgeleitet wird.

Ein häufiges Missverständnis: Pruning entfällt, wenn JOIN-Bedingungen aus einer anderen, nicht partitionierten Tabelle stammen. Der Optimizer kann in diesem Fall nicht vorab ermitteln, welche Partitionswerte relevant sind, und muss alle Partitionen lesen.

4. Partitionswartung: ADD, DROP und REORGANIZE

Ein großer praktischer Vorteil der RANGE-Partitionierung ist das Archivieren per DROP PARTITION. Im Gegensatz zu einem DELETE über Millionen von Zeilen ist das Löschen einer ganzen Partition eine Metadaten-Operation — sie läuft in Millisekunden und erzeugt kaum Locking. Das ist einer der überzeugendsten Gründe, große historische Tabellen zu partitionieren.

-- Add a new monthly partition before the MAXVALUE catchall
ALTER TABLE sales_order
    REORGANIZE PARTITION p_future INTO (
        PARTITION p_2025_02 VALUES LESS THAN (UNIX_TIMESTAMP('2025-03-01 00:00:00')),
        PARTITION p_future  VALUES LESS THAN MAXVALUE
    );

-- Drop an old partition instantly (metadata operation, no row-level locking)
ALTER TABLE sales_order
    DROP PARTITION p_2023_q1;

-- Move partition data to archive before dropping (requires identical schema)
ALTER TABLE sales_order
    EXCHANGE PARTITION p_2023_q1 WITH TABLE sales_order_archive;

-- Then drop the now-empty partition
ALTER TABLE sales_order
    DROP PARTITION p_2023_q1;

Das Muster für eine automatisierte Verwaltung: Jeden Monat eine neue Partition per REORGANIZE PARTITION aus der MAXVALUE-Partition herauslösen, und alte Partitionen jenseits des Aufbewahrungszeitraums per DROP PARTITION löschen oder per EXCHANGE PARTITION in eine Archivtabelle verschieben. In Kombination mit mysqldump auf Partitionsebene entsteht so ein sehr effizienter Archivierungsworkflow ohne Produktionsauswirkungen.

5. Magento sales_order nach Monat partitionieren

Magento speichert Bestellungen in sales_order und den dazugehörigen Zeilenpositionen in sales_order_item. In einem mittelgroßen Shop wächst sales_order schnell auf mehrere Millionen Datensätze. Abfragen im Admin-Backend, in Reports und von Drittintegrationen laufen regelmäßig über created_at-Filter — ein perfekter Kandidat für RANGE-Partitionierung nach Datum.

-- Example: Partitioned sales_order structure (simplified)
-- IMPORTANT: In production, use pt-online-schema-change to avoid full table lock
CREATE TABLE sales_order_partitioned (
    entity_id       INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    increment_id    VARCHAR(50)      NOT NULL,
    store_id        SMALLINT UNSIGNED,
    status          VARCHAR(32),
    grand_total     DECIMAL(20,4),
    created_at      DATETIME         NOT NULL,
    updated_at      DATETIME,
    customer_id     INT UNSIGNED,
    -- Partition key MUST be in every unique index
    PRIMARY KEY (entity_id, created_at),
    UNIQUE KEY idx_increment_id (increment_id, created_at),
    KEY idx_status (status),
    KEY idx_customer_id (customer_id)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p_2024_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION p_2024_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01 00:00:00')),
    PARTITION p_2024_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01 00:00:00')),
    PARTITION p_2024_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01 00:00:00')),
    PARTITION p_2025_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2025-04-01 00:00:00')),
    PARTITION p_future  VALUES LESS THAN MAXVALUE
);

Eine kritische Einschränkung fällt hier sofort auf: Jeder eindeutige Index muss den Partitionsschlüssel enthalten. Das bedeutet, der PRIMARY KEY und alle UNIQUE-Constraints müssen created_at einschließen. Das ist ein fundamentaler Unterschied zur normalen sales_order-Tabelle und kann Einfluss auf Magento-Modelle haben, die nach entity_id ohne created_at suchen.

Für eine bestehende Produktionstabelle empfiehlt sich die Migration über pt-online-schema-change aus dem Percona Toolkit, um Downtime zu vermeiden. Ein direktes ALTER TABLE auf einer Tabelle mit Millionen von Zeilen sperrt die Tabelle für die gesamte Laufzeit des Schema-Änderungsvorgangs.

6. Einschränkungen und wann man nicht partitionieren sollte

MySQL Partitionierung hat mehrere harte Limits, die man kennen muss, bevor man ein Schema entwirft. Wer diese Grenzen ignoriert, baut technische Schulden auf, die später schwierig zu beseitigen sind.

Foreign Keys: Auf eine partitionierte Tabelle können keine Foreign Keys referenzieren. Magento nutzt zwar im Standard kaum FK-Constraints auf Datenbankebene, aber selbst erstellte Beziehungen in Custom-Modulen müssen entfernt werden, bevor eine Tabelle partitioniert werden kann. Das schränkt Referenzintegrität auf Datenbankebene erheblich ein.

Partitionsschlüssel in jedem Unique Index: Jeder UNIQUE-Index und der PRIMARY KEY müssen den Partitionsschlüssel enthalten. Das schränkt das Schemadesign erheblich ein und kann bestehende Abfragemuster aufbrechen, die nach einem einfachen Primärschlüssel filtern.

Maximum 8192 Partitionen: MySQL erlaubt maximal 8192 Partitionen pro Tabelle. Bei monatlicher Partitionierung sind das theoretisch über 680 Jahre — kein praktisches Problem. Bei tagesgenauen Partitionen ist das Limit jedoch innerhalb von etwa 22 Jahren erreichbar.

-- Pruning WORKS: direct filter on partition key
SELECT entity_id FROM sales_order
WHERE created_at >= '2024-10-01'
  AND created_at <  '2025-01-01';

-- Pruning does NOT work: filter derived from a JOIN
-- MySQL cannot determine relevant partitions at parse time
SELECT o.entity_id
FROM sales_order o
JOIN report_filters rf ON rf.start_date = o.created_at
WHERE rf.report_id = 42;

-- Pruning does NOT work: function wrapping the partition key
-- DATE() prevents index use and partition pruning
SELECT entity_id FROM sales_order
WHERE DATE(created_at) = '2024-10-15';

-- Correct: use a range instead
SELECT entity_id FROM sales_order
WHERE created_at >= '2024-10-15 00:00:00'
  AND created_at <  '2024-10-16 00:00:00';

Die Faustregel lautet: MySQL Partitionierung lohnt sich bei sehr großen Tabellen mit klar zeitbasiertem Zugriffspattern und regelmäßigem Archivierungsbedarf. Für Performance-Probleme bei kleinen bis mittelgroßen Tabellen ist ein gut gewählter Composite Index fast immer die bessere und wartungsärmere Lösung. Partitionierung ist kein Ersatz für gutes Indexdesign.

8. Zusammenfassung

9. FAQ: MySQL Partitionierung