V tomto zápisku se budu věnovat optimalizaci SQL dotazu, zejména v souvislosti s filtrováním dat na základě délky vstupního data ve formátu YYYY-mm popřípadě YYYY-mm-dd. Původní dotaz zahrnoval několik podmínek založených na LENGTH, které se snažily porovnat různé formáty data (rok, měsíc-rok, den-měsíc-rok). Rozhodl jsem se refaktorovat tyto podmínky pomocí CASE, což zlepšuje čitelnost a potenciálně i výkon dotazu. Funkce která ošetřuje vstupní data řeším pomocí sanitace do které posílá upravený řetězec statická třída DateHelper::getDateInfo();
Původní dotaz:
SELECT wr.id,
wr.creator,
wr.costumer_id,
wc.name,
DATE_FORMAT(wr.manualDate, '%d-%m-%Y') AS formattedManualDate,
wr.visible,
wr.enable,
wr.eu_pallet,
wr.small_pallet,
wr.big_pallet,
wr.box,
wr.small_box,
wr.big_box,
wr.container_twenty,
wr.container_forty,
(
wr.eu_pallet * wv.eu_pallet +
wr.small_pallet * wv.small_pallet +
wr.big_pallet * wv.big_pallet +
wr.box * wv.box +
wr.small_box * wv.small_box +
wr.big_box * wv.big_box +
wr.container_twenty * wv.container_twenty +
wr.container_forty * wv.container_forty
) AS sum_square
FROM warehouse_items_records wr
LEFT JOIN warehouse_items_square as wv ON wr.costumer_id = wv.costumer_id
LEFT JOIN warehouse_costumers as wc ON wr.costumer_id = wc.id
WHERE wr.costumer_id = :costumer_id
AND wr.visible = 1
AND (
(LENGTH(:manualDate) = 4 AND YEAR(wr.manualDate) = :manualDate) OR
(LENGTH(:manualDate) = 7 AND DATE_FORMAT(wr.manualDate, '%Y-%m') = :manualDate) OR
(LENGTH(:manualDate) = 10 AND DATE(wr.manualDate) = :manualDate)
)
ORDER BY wr.manualDate ASC;Optimalizace pomocí CASE a LENGTH
Proč používat CASE a LENGTH místo LIKE:
- Bezpečnost a výkon: Použití LIKE s
%může způsobit neefektivní full table scan, což zpomaluje dotaz, zejména na velkých tabulkách. Zároveň je citlivé na SQL injection, pokud není správně ošetřeno. - Jasná logika: Pomocí
CASEmůžeme lépe strukturovat podmínky a logiku dotazu, což zvyšuje čitelnost a udržovatelnost kódu. - Přesnost: Podmínky s
CASEaLENGTHjsou přesnější při porovnávání různých formátů dat, což eliminuje potenciální problémy s filtrováním vstupních hodnot.
Refaktorovaný dotaz:
SELECT
wr.id,
wr.creator,
wr.costumer_id,
wc.name,
DATE_FORMAT(wr.manualDate, '%d-%m-%Y') AS formattedManualDate,
wr.visible,
wr.enable,
wr.eu_pallet,
wr.small_pallet,
wr.big_pallet,
wr.box,
wr.small_box,
wr.big_box,
wr.container_twenty,
wr.container_forty,
(
wr.eu_pallet * wv.eu_pallet +
wr.small_pallet * wv.small_pallet +
wr.big_pallet * wv.big_pallet +
wr.box * wv.box +
wr.small_box * wv.small_box +
wr.big_box * wv.big_box +
wr.container_twenty * wv.container_twenty +
wr.container_forty * wv.container_forty
) AS sum_square
FROM warehouse_items_records wr
LEFT JOIN warehouse_items_square wv ON wr.costumer_id = wv.costumer_id
LEFT JOIN warehouse_costumers wc ON wr.costumer_id = wc.id
WHERE wr.costumer_id = :costumer_id
AND wr.visible = 1
AND (
CASE
WHEN LENGTH(:manualDate) = 4 THEN YEAR(wr.manualDate) = :manualDate
WHEN LENGTH(:manualDate) = 7 THEN DATE_FORMAT(wr.manualDate, '%Y-%m') = :manualDate
WHEN LENGTH(:manualDate) = 10 THEN DATE(wr.manualDate) = :manualDate
END
)
ORDER BY wr.manualDate ASC;Vysvětlení refaktoru:
- Podmínka CASE: Přidání podmínky
CASEzjednodušuje a zpřehledňuje logiku filtrování dat na základě délky:manualDate. - Zvýšení výkonu: Odstranění použití LIKE s
%a nahrazení explicitními podmínkami přináší lepší výkon dotazu. - Čitelnost a údržba: Jasně definovaná logika filtrování zjednodušuje pochopení kódu a jeho budoucí údržbu.
Tato optimalizace zvyšuje efektivitu a bezpečnost dotazu a je ukázkovým příkladem, jak malé změny mohou mít velký dopad na kvalitu kódu.
Napsat komentář