Magento Performance mit SQL analysieren: Top 20 langsamste Datenbankzugriffe

Magento Performance SQL: Warum die Datenbank der Engpass ist

In den meisten Magento-Performance-Projekten liegt der größte Hebel nicht im PHP-Code oder im Caching, sondern in der Datenbank. Ungünstige SQL-Abfragen, fehlende Indizes und typische Magento-spezifische Anti-Pattern wie übermäßige EAV-JOINs oder N+1-Abfragen können eine Seite um den Faktor 10 bis 50 verlangsamen. Das Gute: MySQL und MariaDB liefern mit performance_schema und dem Slow Query Log ausgezeichnete Werkzeuge, um genau diese Probleme zu finden.

Dieser Artikel zeigt, wie man Magento Performance SQL-Analysen systematisch durchführt: vom ersten Screening über die Identifikation der teuersten Abfragen bis hin zur Optimierung konkreter Muster.

1. performance_schema: Die eingebaute Query-Statistik

Das performance_schema ist seit MySQL 5.6 standardmäßig aktiviert und der mächtigste Ausgangspunkt für jede Magento Performance SQL-Analyse. Die View events_statements_summary_by_digest aggregiert alle ausgeführten Queries nach ihrem normalisierten SQL-Fingerprint.

-- Top 20 langsamste Queries nach Gesamtzeit (alle Ausführungen summiert)
SELECT
    ROUND(SUM_TIMER_WAIT / 1e12, 2)           AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 4)           AS avg_time_sec,
    COUNT_STAR                                 AS exec_count,
    SUM_ROWS_EXAMINED                          AS total_rows_examined,
    ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0)   AS avg_rows_examined,
    SUM_ROWS_SENT                              AS total_rows_sent,
    LEFT(DIGEST_TEXT, 120)                     AS query_pattern
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
  AND SCHEMA_NAME = DATABASE()
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- Queries mit schlechtestem Verhältnis rows_examined / rows_sent (Index-Ineffizienz)
SELECT
    ROUND(AVG_TIMER_WAIT / 1e12, 4)            AS avg_time_sec,
    COUNT_STAR                                  AS exec_count,
    ROUND(SUM_ROWS_EXAMINED / SUM_ROWS_SENT, 0) AS examine_to_send_ratio,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT,
    LEFT(DIGEST_TEXT, 120)                      AS query_pattern
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
  AND SCHEMA_NAME = DATABASE()
  AND SUM_ROWS_SENT > 0
  AND COUNT_STAR > 10   -- nur Queries mit signifikanter Ausführungsanzahl
ORDER BY examine_to_send_ratio DESC
LIMIT 20;

Ein examine_to_send_ratio von mehr als 1000 bedeutet: MySQL scannt 1000 Zeilen, um 1 zurückzugeben – ein klares Zeichen für einen fehlenden oder nicht genutzten Index.

2. sys.statement_analysis: Einfacherer Einstieg

Die sys-Bibliothek (verfügbar ab MySQL 5.7.7) bietet eine vereinfachte Sicht auf performance_schema mit menschenlesbaren Zeitangaben. Für den täglichen Gebrauch ist sie die einfachere Alternative.

-- Einfache Performance-Übersicht mit sys.statement_analysis
SELECT
    total_latency,        -- formatierte Gesamtzeit z.B. '12.34 s'
    exec_count,
    avg_latency,          -- formatierte Durchschnittszeit z.B. '23.45 ms'
    rows_examined_avg,
    rows_sent_avg,
    full_scans,           -- Anzahl Full Table Scans
    LEFT(query, 100) AS query_pattern
FROM sys.statement_analysis
WHERE db = DATABASE()
ORDER BY total_latency DESC
LIMIT 20;

-- Queries die Full Table Scans verursachen
SELECT
    exec_count,
    total_latency,
    avg_latency,
    rows_examined_avg,
    full_scans,
    LEFT(query, 120) AS query_pattern
FROM sys.statement_analysis
WHERE db = DATABASE()
  AND full_scans > 0
ORDER BY full_scans DESC
LIMIT 20;

3. Slow Query Log aktivieren und auswerten

Der Slow Query Log ist besonders wertvoll, wenn man konkrete SQL-Abfragen mit ihrem vollständigen Text (nicht nur dem Fingerprint) und dem aufrufenden Host sehen möchte.

-- Slow Query Log zur Laufzeit aktivieren (kein MySQL-Neustart nötig)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;         -- Queries ab 1 Sekunde loggen
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- Auch ohne Index loggen
SET GLOBAL min_examined_row_limit = 1000;          -- Nur bei >1000 gelesenen Zeilen

-- Log-Datei-Pfad prüfen
SHOW VARIABLES LIKE 'slow_query_log_file';

Ein typischer Slow Query Log-Eintrag sieht so aus:

-- Typischer Slow Query Log Eintrag:
-- # Time: 2025-01-26T14:23:01.123456Z
-- # User@Host: magento[magento] @ localhost []
-- # Query_time: 4.532  Lock_time: 0.001  Rows_sent: 42  Rows_examined: 847293
-- # SET timestamp=1706278981;

-- Die Query selbst (vereinfachtes Beispiel):
SELECT e.entity_id, varchar_table.value AS name,
       int_table.value AS status
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar varchar_table
    ON varchar_table.row_id = e.row_id
    AND varchar_table.attribute_id = 73
    AND varchar_table.store_id = 0
LEFT JOIN catalog_product_entity_int int_table
    ON int_table.row_id = e.row_id
    AND int_table.attribute_id = 96
    AND int_table.store_id = 0
WHERE e.entity_id IN (SELECT product_id FROM catalog_category_product WHERE category_id = 5)
ORDER BY varchar_table.value ASC;

Wichtige Metriken im Log: Query_time (Gesamtzeit), Lock_time (Wartezeit auf Lock), Rows_sent (zurückgegebene Zeilen) und Rows_examined (gelesene Zeilen). Ein hohes Verhältnis Rows_examined zu Rows_sent ist das wichtigste Warnsignal.

4. EXPLAIN auf Magento EAV-Abfragen

EXPLAIN zeigt den Ausführungsplan einer Abfrage und ist das primäre Werkzeug zur Index-Analyse. Für Magento EAV-Abfragen gibt es typische Muster, die man erkennen muss.

-- EXPLAIN für eine typische EAV-Produktabfrage
EXPLAIN SELECT
    cpe.entity_id,
    cpe.sku,
    v_name.value    AS product_name,
    d_price.value   AS price
FROM catalog_product_entity cpe
LEFT JOIN catalog_product_entity_varchar v_name
    ON v_name.row_id = cpe.row_id
    AND v_name.attribute_id = 73
    AND v_name.store_id = 0
LEFT JOIN catalog_product_entity_decimal d_price
    ON d_price.row_id = cpe.row_id
    AND d_price.attribute_id = 75
    AND d_price.store_id = 0
WHERE cpe.entity_id IN (SELECT product_id FROM catalog_category_product WHERE category_id = 12)
ORDER BY v_name.value ASC
LIMIT 20 OFFSET 0;

-- EXPLAIN FORMAT=JSON für detailliertere Analyse
EXPLAIN FORMAT=JSON SELECT
    cpe.entity_id,
    v_name.value AS product_name
FROM catalog_product_entity cpe
JOIN catalog_product_entity_varchar v_name
    ON v_name.row_id = cpe.row_id
    AND v_name.attribute_id = 73
WHERE cpe.entity_id BETWEEN 1 AND 1000;

-- Was in EXPLAIN zu achten ist:
-- type = ALL → Full Table Scan (schlecht, außer bei kleinen Tabellen)
-- type = ref/range/eq_ref → Index wird genutzt (gut)
-- key = NULL → kein Index verwendet
-- Extra = "Using filesort" → externe Sortierung ohne Index (teuer)
-- Extra = "Using temporary" → temporäre Tabelle (sehr teuer)
-- rows = sehr hoch → MySQL schätzt viele Zeilen zu lesen

5. Anti-Pattern: EAV-JOINs ohne Flat Catalog

Das häufigste Performance-Problem in Magento ist die direkte Abfrage über EAV-Tabellen ohne Flat Catalog. Jedes Attribut benötigt einen zusätzlichen JOIN, und bei einer Kategorieseite mit 50 Produkten und 20 Attributen entstehen schnell 1000 JOINs.

-- Flat Catalog Status prüfen
SELECT path, value
FROM core_config_data
WHERE path IN (
    'catalog/frontend/flat_catalog_product',
    'catalog/frontend/flat_catalog_category'
)
ORDER BY path;
-- value = '1' = aktiviert, '0' = deaktiviert

-- Flat Catalog Tabellen prüfen (ob sie existieren und gefüllt sind)
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME LIKE 'catalog_product_flat_%'
ORDER BY TABLE_NAME;

Wenn der Flat Catalog deaktiviert ist, werden Kategorieseiten aus rohen EAV-Tabellen aufgebaut. Mit aktiviertem Flat Catalog nutzt Magento eine vorberechnete flache Tabelle mit allen Attributen als direkte Spalten – das kann die Query-Zeit um den Faktor 10–50 reduzieren.

6. Anti-Pattern: N+1-Abfragen

N+1-Abfragen entstehen, wenn PHP-Code für jedes Element einer Liste eine separate Datenbankabfrage ausführt, statt alle Daten in einer Abfrage zu laden. In Magento tritt dies häufig bei schlecht implementierten Collections auf.

-- N+1-Muster im performance_schema erkennen:
-- Zeichen: sehr viele Ausführungen derselben Query mit kleiner Ergebnismenge

SELECT
    COUNT_STAR                               AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1e12, 4)         AS avg_time_sec,
    ROUND(SUM_TIMER_WAIT / 1e12, 2)         AS total_time_sec,
    SUM_ROWS_SENT,
    ROUND(SUM_ROWS_SENT / COUNT_STAR, 1)    AS avg_rows_per_exec,
    LEFT(DIGEST_TEXT, 120)                  AS query_pattern
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
  AND COUNT_STAR > 100
  AND SUM_ROWS_SENT / COUNT_STAR < 5  -- wenig Zeilen pro Ausführung
ORDER BY COUNT_STAR DESC
LIMIT 20;

Typische N+1-Fälle in Magento: Produktbilder in einer Schleife laden, Attributoptionswerte einzeln auflösen, Preisinformationen für Listenansicht per Entity-ID abfragen. Die Lösung ist immer ein Collection-Load mit addAttributeToSelect für alle benötigten Attribute oder ein Single-Query-JOIN.

7. Anti-Pattern: Fehlender Index auf Filter-Spalte

Fehlende Indizes auf häufig verwendeten Filter-Spalten sind besonders in custom Modulen ein häufiges Problem. Magento nutzt den Query Optimizer, aber ohne passenden Index muss MySQL Full Table Scans durchführen.

-- Indizes einer Tabelle anzeigen
SHOW INDEX FROM sales_order;

-- Oder über information_schema:
SELECT
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns,
    INDEX_TYPE,
    NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'sales_order'
GROUP BY INDEX_NAME
ORDER BY INDEX_NAME;

-- Beispiel: Index auf custom_attribute Spalte hinzufügen (ohne Downtime bei MariaDB)
ALTER TABLE custom_table
ADD INDEX idx_custom_status_store (status, store_id);

-- Für große Tabellen (Millionen Zeilen): pt-online-schema-change nutzen
-- pt-online-schema-change --alter "ADD INDEX idx_status (status)" D=magento,t=large_table

-- Index-Nutzung einer Query prüfen
EXPLAIN SELECT * FROM sales_order
WHERE status = 'pending'
  AND store_id = 1
  AND created_at >= '2025-01-01';

8. Anti-Pattern: Nicht-sargable WHERE-Bedingungen

Nicht-sargable Bedingungen sind solche, bei denen eine Funktion auf die Spalte angewendet wird, sodass der Index nicht genutzt werden kann. Dies ist in Magento besonders bei Datums-Filtern und String-Vergleichen ein häufiges Problem.

-- SCHLECHT: Funktion auf Spalte verhindert Index-Nutzung
-- MySQL muss alle Zeilen lesen und die Funktion auf jede anwenden
SELECT * FROM sales_order
WHERE YEAR(created_at) = 2025
  AND MONTH(created_at) = 1;

-- GUT: Range-Condition nutzt den Index
SELECT * FROM sales_order
WHERE created_at >= '2025-01-01 00:00:00'
  AND created_at <  '2025-02-01 00:00:00';

-- SCHLECHT: LIKE mit führendem Wildcard
SELECT * FROM customer_entity
WHERE email LIKE '%gmail.com';
-- Kein Index nutzbar!

-- GUT: LIKE ohne führenden Wildcard
SELECT * FROM customer_entity
WHERE email LIKE 'max.mustermann%';
-- Kann B-Tree Index nutzen

-- SCHLECHT: Impliziter Typ-Cast verhindert Index
SELECT * FROM catalog_product_entity_varchar
WHERE value = 42;   -- Spalte ist VARCHAR, 42 ist INT → Cast, kein Index

-- GUT: Korrekter Typ
SELECT * FROM catalog_product_entity_varchar
WHERE value = '42';

9. Query-Fingerprint: Welches Modul erzeugt die Abfrage?

Hat man eine langsame Query identifiziert, muss man herausfinden, welcher Magento-Code sie erzeugt. Der Weg führt vom normalisierten Query-Muster aus performance_schema in die Codebase.

-- Konkreten Query-Text aus performance_schema lesen
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%catalog_product_entity_varchar%'
  AND DIGEST_TEXT LIKE '%attribute_id%'
  AND SCHEMA_NAME = DATABASE()
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

Den Query-Text aus performance_schema nutzt man als Suchbegriff in der Codebase. Charakteristische Tabellenamen und WHERE-Spalten grenzen die Suche ein:

-- Hilfreiche Diagnose-Abfrage: Welche Tabellen werden am häufigsten abgefragt?
SELECT
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(DIGEST_TEXT, 'FROM ', -1), ' ', 1
    ) AS main_table,
    COUNT(*) AS distinct_query_patterns,
    SUM(COUNT_STAR) AS total_executions
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
  AND DIGEST_TEXT LIKE 'SELECT%'
GROUP BY main_table
ORDER BY total_executions DESC
LIMIT 20;

10. Zusammenfassung

Magento Performance SQL-Analysen folgen einem klaren Prozess: Zuerst die teuersten Queries mit performance_schema oder dem Slow Query Log identifizieren, dann mit EXPLAIN den Ausführungsplan verstehen und schließlich das Anti-Pattern gezielt beheben – ob Flat Catalog, Index oder N+1-Fix.

11. FAQ: Magento Performance SQL