Temporäre Tabellen und Memory-Tables richtig einsetzen

1. Zwei verschiedene Konzepte, die oft verwechselt werden

Der Begriff temporäre Tabelle bezeichnet in MySQL-Diskussionen häufig zwei ganz unterschiedliche Mechanismen. Zum einen die explizite CREATE TEMPORARY TABLE, die ein Entwickler bewusst anlegt, um Zwischenergebnisse innerhalb einer Sitzung zu speichern. Zum anderen die internen Temp-Tables, die der Query-Optimizer ohne jede explizite Anweisung selbst erzeugt, sobald eine Abfrage Zwischenergebnisse materialisieren muss.

Diese Unterscheidung ist mehr als Begriffshaarspalterei, denn beide Mechanismen haben unterschiedliche Lebenszyklen, unterschiedliche Konfigurationshebel und unterschiedliche typische Fallstricke. Wer beide in einen Topf wirft, übersieht leicht, dass eine langsame Abfrage möglicherweise gar keine explizite temporäre Tabelle verwendet, sondern intern eine erzeugt, die nie im eigenen Code sichtbar wird.

2. Explizite CREATE TEMPORARY TABLE: Sitzungsbindung und Anwendungsfälle

Eine mit CREATE TEMPORARY TABLE angelegte Tabelle ist ausschließlich innerhalb der Verbindung sichtbar, in der sie erzeugt wurde, existiert unabhängig von anderen gleichnamigen persistenten Tabellen und wird beim Schließen der Verbindung automatisch gelöscht. Das macht sie ideal für mehrstufige Zwischenergebnisse in komplexen Reports oder für Staging-Daten während eines Import- oder Batch-Vorgangs, ohne Konflikte mit parallel laufenden Sitzungen anderer Nutzer befürchten zu müssen.

Ein wichtiger Fallstrick bei Connection-Pooling-Setups, wie sie in vielen PHP-Anwendungen inklusive Magento üblich sind, ist die Annahme, eine temporäre Tabelle bleibe über mehrere Anfragen hinweg erhalten. Da Connection-Pools Verbindungen rotieren und wiederverwenden können, ist eine temporäre Tabelle, die in einem Request angelegt wurde, im nächsten Request über eine andere physische Verbindung schlicht nicht mehr vorhanden.

-- Zwischenergebnis für einen mehrstufigen Report
CREATE TEMPORARY TABLE tmp_order_totals AS
SELECT customer_id, SUM(grand_total) AS total_spent
FROM sales_order
WHERE created_at >= CURDATE() - INTERVAL 90 DAY
GROUP BY customer_id;

CREATE INDEX idx_customer ON tmp_order_totals (customer_id);

SELECT c.email, t.total_spent
FROM tmp_order_totals t
JOIN customer_entity c ON c.entity_id = t.customer_id
ORDER BY t.total_spent DESC
LIMIT 50;

3. Interne Optimizer-Temp-Tables: wann MySQL sie automatisch erzeugt

Der Optimizer legt intern eine temporäre Tabelle an, sobald eine Abfrage ein Zwischenergebnis materialisieren muss, das nicht direkt aus einem Index gelesen werden kann. Typische Auslöser sind GROUP BY oder ORDER BY ohne passenden Index, DISTINCT-Abfragen, UNION-Konstrukte, bei denen doppelte Zeilen entfernt werden müssen, sowie abgeleitete Tabellen und Unterabfragen in der FROM-Klausel, die der Optimizer nicht in die äußere Abfrage einbinden kann.

Diese internen Temp-Tables sind für den Entwickler unsichtbar, es sei denn, man untersucht den Ausführungsplan explizit über EXPLAIN, wo sie sich als Using temporary zeigen. Genau weil sie unsichtbar sind, werden sie oft übersehen, wenn eine Abfrage langsamer ist als aufgrund der reinen Zeilenzahl erwartet.

4. In-Memory vs. On-Disk: die TempTable-Engine und ihre Grenzen

Seit MySQL 8.0.16 nutzt der Optimizer standardmäßig die TempTable-Speicher-Engine für interne temporäre Tabellen, die im Gegensatz zur älteren MEMORY-Engine auch variabel lange Spalten wie VARCHAR effizient im Arbeitsspeicher ablegen kann, statt sie auf feste Maximalbreite aufzufüllen. Solange die Zieltabelle innerhalb der über tmp_table_size und temptable_max_ram konfigurierten Grenzen bleibt, verbleibt sie vollständig im Arbeitsspeicher.

Wird diese Grenze überschritten, weicht die TempTable-Engine auf speicherabgebildete Dateien auf der Platte aus, bevor sie in älteren Versionen komplett auf MyISAM oder InnoDB als Fallback umschaltete. Dieser Übergang von Arbeitsspeicher auf Platte geschieht transparent, kostet aber spürbar Performance, weil Plattenzugriffe deutlich langsamer sind als Arbeitsspeicherzugriffe, gerade bei Abfragen mit vielen Gruppierungs- oder Sortierdurchgängen.

5. Die MEMORY-Storage-Engine für explizite Lookup-Tabellen

Neben den internen Konstrukten des Optimizers gibt es die explizit nutzbare ENGINE=MEMORY, mit der Entwickler eigene, dauerhaft im Arbeitsspeicher gehaltene Tabellen anlegen können. Der Standardindextyp ist ein Hash-Index, der für Gleichheitsvergleiche extrem schnell ist, für Bereichsabfragen wie BETWEEN jedoch ungeeignet bleibt, sofern man nicht ausdrücklich einen BTREE-Index anfordert.

Typische Einsatzfälle sind kleine, sehr häufig abgefragte Referenztabellen, etwa Wechselkurs-Caches, Länder- oder Statuscode-Mappings, bei denen die Abfragehäufigkeit den Aufwand rechtfertigt, sie dauerhaft im Arbeitsspeicher vorzuhalten, statt bei jedem Zugriff über den regulären Buffer Pool zu gehen.

-- Kleine Lookup-Tabelle dauerhaft im Arbeitsspeicher halten
CREATE TABLE currency_rate_cache (
    currency_code CHAR(3) NOT NULL PRIMARY KEY,
    rate_to_base  DECIMAL(12,6) NOT NULL,
    updated_at    DATETIME NOT NULL
) ENGINE=MEMORY;

SELECT rate_to_base FROM currency_rate_cache WHERE currency_code = 'USD';

6. Fallstricke: fehlende Persistenz und Einschränkungen bei Spaltentypen

Der wichtigste Fallstrick der MEMORY-Engine ist die fehlende Persistenz: Bei einem Neustart des Servers oder einem Absturz ist der gesamte Inhalt einer MEMORY-Tabelle unwiderruflich verloren, lediglich die leere Tabellenstruktur bleibt erhalten. Für alles, was nicht aus einer anderen Quelle jederzeit neu befüllt werden kann, ist MEMORY deshalb schlicht ungeeignet.

Hinzu kommt, dass klassische MEMORY-Tabellen keine BLOB- oder TEXT-Spalten unterstützen und aufgrund des Fixed-Row-Formats bei variabel langen Werten unnötig Arbeitsspeicher verschwenden, weil jede Zeile auf die maximale definierte Spaltenbreite aufgefüllt wird. Die per-Tabelle-Grenze über max_heap_table_size sorgt zusätzlich dafür, dass eine zu groß gewachsene MEMORY-Tabelle mit einem Fehler abgewiesen wird, statt automatisch auf die Platte auszuweichen.

7. Größenlimits und Monitoring: versteckte Disk-Spills erkennen

Ob Abfragen unbemerkt auf plattenbasierte interne Temp-Tables ausweichen, lässt sich über die Status-Variablen Created_tmp_tables und Created_tmp_disk_tables beobachten. Steigt Letztere im Verhältnis zur Gesamtzahl deutlich an, deutet das auf Abfragen hin, deren Zwischenergebnisse regelmäßig die konfigurierten Speichergrenzen sprengen oder Spaltentypen enthalten, die eine In-Memory-Verarbeitung verhindern.

Diese Kennzahlen sollten regelmäßig beobachtet werden, idealerweise als Zeitreihe im Monitoring, statt nur punktuell nach einem Performance-Problem nachgeschaut zu werden, denn ein schleichender Anstieg über Wochen deutet oft auf wachsende Datenmengen hin, für die bestehende Indizes oder Konfigurationswerte nicht mehr ausreichen.

-- Anteil plattenbasierter Temp-Tables pruefen
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- Aktuelle Konfigurationsgrenzen für interne Temp-Tables
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'temptable_max_ram';
SHOW VARIABLES LIKE 'max_heap_table_size';

8. Praxisbeispiel Magento: Report-Queries, Importe und Session-Handling

In Magento-Umgebungen erzeugen vor allem Report-Abfragen mit GROUP BY über große Bestell- oder Bestandstabellen häufig interne Temp-Tables, insbesondere wenn kein passender Index die Gruppierungsspalten abdeckt. Eigene Import- oder Batch-Skripte profitieren dagegen häufig von expliziten TEMPORARY TABLEs, um Rohdaten vor der eigentlichen Verarbeitung zwischenzuspeichern, zu bereinigen und erst danach per INSERT ... SELECT in die Zieltabellen zu übernehmen.

Datenbankgestützte Sessions, wie sie technisch möglich, aber für Magento nicht empfohlen sind, sollten grundsätzlich vermieden werden, da Session-Handling über Redis in Hyvä-Setups sowohl die MEMORY-Engine-Fallstricke als auch unnötige Schreiblast auf der Datenbank umgeht.

9. Best Practices: wann explizit sinnvoll, wie man Optimizer-Temp-Tables reduziert

Explizite TEMPORARY TABLEs lohnen sich vor allem dann, wenn ein komplexes Zwischenergebnis mehrfach innerhalb derselben Sitzung weiterverwendet wird, denn dann spart man sich wiederholte, teure Neuberechnungen. Für einmalige Zwischenschritte innerhalb einer einzelnen Abfrage genügt dagegen meist eine gut geschriebene Unterabfrage oder ein Common Table Expression, ohne den zusätzlichen Verwaltungsaufwand einer eigenen temporären Tabelle.

Um interne Optimizer-Temp-Tables zu vermeiden, helfen passende zusammengesetzte Indizes, die GROUP-BY- und ORDER-BY-Spalten bereits in sortierter Reihenfolge liefern, sowie ein bewusster Verzicht auf unnötige DISTINCT-Klauseln, wenn Eindeutigkeit bereits durch die Abfragelogik garantiert ist. Wer tmp_table_size und max_heap_table_size erhöht, sollte bedenken, dass diese Grenzen pro Verbindung und teils pro Abfrage gelten und bei vielen gleichzeitigen Verbindungen schnell zu erheblichem Gesamtspeicherverbrauch führen können.

10. Zusammenfassung

11. FAQ: Temporäre Tabellen in MySQL: Das Wichtigste auf einen Blick