Hilfe, wo sind meine Rabatte?
OXID Enterprise Edition bietet vielfältige Möglichkeiten, Rabatte einzusetzen
Rabatte können für jeden Subshop separat konfiguriert werden und sie können an Subshops vererbt werden. Rabatte können auf eine Auswahl von Ländern eingeschränkt werden, sie können auch auf einen Teil des Sortiments eingeschränkt werden (wenn das nicht der Fall ist, gelten sie für den gesamten Warenkorb). Und schließlich können Rabatte in Bezug auf Benutzer bzw. Benutzergruppen eingeschränkt werden.
OXID Professional Services wurde vom Partner W&Co um Unterstützung gebeten, weil in einem ihrer Projekte die Rabattberechnung nicht oder nur erratisch funktionionierte.
Das Problem mit der Optimierung derived_merge
Es hat sich herausgestellt, dass beim Einsatz einer OXID Enterprise Edition zusammen mit MySQL 5.7 die Rabatte nicht funktionieren, wenn die Konfigurationsmöglichkeiten für Rabatte ausgereizt werden. Der Shop berechnet einen Rabatt einfach nicht, obwohl er laut Konfiguration berechnet werden sollte.
Das Problem ist hier die Optimierung derived_merge, die bei MySQL 5.7 zu einer fehlerhaften Ausführung bestimmter Abfragen führen kann. Dies zu ermitteln, hat uns beträchtlich Nerven und Mühe gekostet.
Wir wandten uns unsererseits an den Hosting-Partner qwertiko und gaben ihnen den zusätzlichen Hinweis, dass der Fehler nicht auftritt, wenn wir alle Optimizer-Switches auf 'off' stellten mittels
set global optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off';
Dies empfanden wir als Holzhammer-Methode mit unübersehbaren Auswirkungen auf die Datenbank-Performance.
Sehr bald lieferte qwertiko uns jedoch eine wesentlich weniger invasive Lösung. Es reicht nämlich, nur den Optimizer-Switch derived_merge auf 'off' zu stellen. Diese Optimierung kann laut Dokumentation (Optimizing Derived Tables and View References with Merging or Materialization) auch auf Abfragen mit Views angewendet werden.
Ein Artikel bei Percona "Why Optimization derived_merge can Break your Queries" beschreibt, dass diese Optimierung dazu führen kann, dass die durch den Optimizer umgeschriebene Abfrage nicht mehr die gleichen Ergebnisse produziert.
Eine bleibende Lehre ist ein weiteres Werkzeug für unseren Werkzeugkasten, das wir diesem Percona-Artikel entnehmen konnten. Es gibt nämlich eine einfache Möglichkeit, in das Wirken des Optimizers Einblick zu nehmen und diese Möglichkeit wollen wir in diesem Artikel besprechen.
Der Job des Optimizers ist es, eine Datenbankabfrage so umzuschreiben, dass sie performanter ausgeführt werden kann. Doch was macht der Optimizer?
Das EXPLAIN-Statement
Das EXPLAIN-Statement gibt Einblick, wie eine Abfrage ausgeführt wird und lässt zu, die Performance einer Abfrage zu beurteilen. Wir wollen das mit einer vereinfachten Version der problematischen Abfrage zeigen (vereinfacht in der Hinsicht, dass lediglich die Gruppenzuordnung der Rabatte vorkommt).
Die Abfrage sieht so aus:
SELECT
*
FROM
oxv_oxdiscount_3_de
WHERE
(oxv_oxdiscount_3_de.oxactive = 1
OR (oxv_oxdiscount_3_de.oxactivefrom < '2021-07-28 17:14:00'
AND oxv_oxdiscount_3_de.oxactiveto > '2021-07-28 17:14:00'))
AND (SELECT
IF(EXISTS( SELECT
1
FROM
oxobject2discount,
oxv_oxgroups_de
WHERE
oxv_oxgroups_de.oxid = oxobject2discount.oxobjectid
AND oxobject2discount.OXDISCOUNTID = oxv_oxdiscount_3_de.OXID
AND oxobject2discount.oxtype = 'oxgroups'
LIMIT 1),
EXISTS( SELECT
oxobject2discount.oxid
FROM
oxobject2discount
WHERE
oxobject2discount.OXDISCOUNTID = oxv_oxdiscount_3_de.OXID
AND oxobject2discount.oxtype = 'oxgroups'
AND oxobject2discount.OXOBJECTID IN ('oxidnewcustomer' , 'b846c39ca20caf6be1e84b5db78df31a')),
1)
)
ORDER BY oxv_oxdiscount_3_de.oxsort;
EXPLAIN mit derived_merge=on
Ein EXPLAIN mit derived_merge=on liefert
Wir wollen uns nun nicht den 5 Ergebniszeilen zuwenden, sondern einen Blick auf die Zusammenfassung werfen. Dort steht, dass EXPLAIN 5 Ergebniszeilen und 4 Warnungen liefert. Diese Warnungen sind der Schlüssel zu einem tieferen Verständnis des Optimizers.
Mittels SHOW WARNINGS zeigt MySQL nämlich das Ergebnis der Arbeit des Optimizers:
Abfrage nach Formatierung
Schön formatiert (und die Select-Felder durch * ersetzt) sieht die Abfrage so aus:
SELECT
*
FROM
`oxid`.`oxdiscount`
JOIN
`oxid`.`oxdiscount2shop` `t2s`
WHERE
((`oxid`.`oxdiscount`.`OXMAPID` = `oxid`.`t2s`.`OXMAPOBJECTID`)
AND (`oxid`.`t2s`.`OXSHOPID` = 3)
AND ((`oxid`.`oxdiscount`.`OXACTIVE` = 1)
OR ((`oxid`.`oxdiscount`.`OXACTIVEFROM` < '2021-07-28 17:14:00')
AND (`oxid`.`oxdiscount`.`OXACTIVETO` > '2021-07-28 17:14:00')))
AND IF(EXISTS( SELECT
1
FROM
`oxid`.`oxobject2discount`
JOIN
`oxid`.`oxgroups`
WHERE
((`oxid`.`oxobject2discount`.`OXTYPE` = 'oxgroups')
AND (`oxid`.`oxgroups`.`OXID` = `oxid`.`oxobject2discount`.`OXOBJECTID`)
AND (`oxid`.`oxobject2discount`.`OXDISCOUNTID` = `oxid`.`oxdiscount`.`OXID`))),
EXISTS( SELECT
`oxid`.`oxobject2discount`.`OXID`
FROM
`oxid`.`oxobject2discount`
WHERE
((`oxid`.`oxobject2discount`.`OXTYPE` = 'oxgroups')
AND (`oxid`.`oxobject2discount`.`OXDISCOUNTID` = `oxid`.`oxdiscount`.`OXID`)
AND (`oxid`.`oxobject2discount`.`OXOBJECTID` IN ('oxidnewcustomer' , 'b846c39ca20caf6be1e84b5db78df31a')))),
1))
ORDER BY `oxid`.`oxdiscount`.`OXSORT`
EXPLAIN mit derived_merge=off
Wenn wir derived_merge auf 'off' stellen, liefert EXPLAIN Folgendes:
Abfrage nach erneuter Formatierung
Wir erhalten mittels SHOW WARNINGS (wiederum ohne Select-Felder und formatiert):
SELECT
*
FROM
`oxid`.`oxv_oxdiscount_3_de`
WHERE
(((`oxv_oxdiscount_3_de`.`OXACTIVE` = 1)
OR ((`oxv_oxdiscount_3_de`.`OXACTIVEFROM` < '2021-07-28 17:14:00')
AND (`oxv_oxdiscount_3_de`.`OXACTIVETO` > '2021-07-28 17:14:00')))
AND IF(EXISTS( SELECT
1
FROM
`oxid`.`oxobject2discount`
JOIN
`oxid`.`oxv_oxgroups_de`
WHERE
((`oxid`.`oxobject2discount`.`OXTYPE` = 'oxgroups')
AND (`oxid`.`oxobject2discount`.`OXOBJECTID` = `oxv_oxgroups_de`.`OXID`)
AND (`oxid`.`oxobject2discount`.`OXDISCOUNTID` = `oxv_oxdiscount_3_de`.`OXID`))),
EXISTS( SELECT
`oxid`.`oxobject2discount`.`OXID`
FROM
`oxid`.`oxobject2discount`
WHERE
((`oxid`.`oxobject2discount`.`OXTYPE` = 'oxgroups')
AND (`oxid`.`oxobject2discount`.`OXDISCOUNTID` = `oxv_oxdiscount_3_de`.`OXID`)
AND (`oxid`.`oxobject2discount`.`OXOBJECTID` IN ('oxidnewcustomer' , 'b846c39ca20caf6be1e84b5db78df31a')))),
1))
ORDER BY `oxv_oxdiscount_3_de`.`OXSORT`
Die so umgeschriebene Abfrage liefert nun unabhängig von der Einstellung von derived_merge immer das gewünschte Ergebnis. Abgesehen von der Auflösung der View (falls derived_merge=on) fällt auf, dass jeweils aus 'SELECT (IF (EXISTS' ein 'IF (EXISTS (SELECT' erzeugt wird.
Zusammenfassung und Handlungsempfehlung
Nach diesem Einblick in die Arbeit des Optimizers wollen wir es nicht unterlassen, zu betrachten, wieviel besser die mittels derived_merge=on produzierte Abfrage arbeiten würde. Sie würde lediglich 124 statt 24200 Zeilen untersuchen! Das ist immerhin ein Faktor 200. Das hilft natürlich nicht, wenn das Ergebnis nicht stimmt. Aber es zeigt dennoch eindrucksvoll, was der Optimizer leisten kann. Von daher kann der Verzicht auf bestimmte Optimierungen nur als Krücke betrachtet werden, die im Fall eines Shops mit nicht allzu viel Aufrufen durchaus akzeptabel sein kann. Im Fall von deutlicher Last auf dem Shop ist aber auf jeden Fall eine Version des Datenbankservers zu empfehlen, mit der der Bug behoben ist.