A minap szembe jött velem valami, amitől végig szaladt a hideg a hátamon. Ma egy ahhoz nagyon hasonló lekérdezéssel fogunk foglalkozni egy gyors ‘esettanulmány’ keretében. Az eredeti lekérdezést nyilvánvaló okok miatt nem közölhetem, de kreáltam valami hasonlót, amin keresztül remélhetőleg sikerül bemutatnom néhány, a való életben is gyakran előforduló hibát. (Tudjátok milyen nehéz szándékosan hibás lekérdezést gyártani úgy, hogy annak látszólag még értelme is legyen? Nyilván nem sikerült tökéletesen most sem. – A szerző)

Az eredeti lekérdezésben szereplő táblákról annyit, hogy mindkettő valahol az 500.000 rekordos tartományban van, tűrhetően indexeltek. A lekérdezést az egyik felhasználó kreálta, célja, hogy néhány számot szolgáltasson egy havi riporthoz az előző havi forgalommal kapcsolatban. Miért foglalkozom egy lekérdezéssel, ami csak havonta fut? Leginkább azért, mert nagyon egyszerű átírni, hogy naponta vagy akár óránként fusson és akkor már nem mindegy, hogy mennyire hatékony.

Az egészet egy MySQL 5.6 szerveren találtam, mindettől függetlenül a cikkben leírtak nagyjából minden SQL implementációra igazak, ha ez mégsem így lenne, azt az adott résznél jelzem.

Gyenge idegzetűek most csak lassan tekerjék az egér gombját, vagy ha a monitor túl nagy, javaslom eltakarni az alját egy papírlappal.

Ready? Go!

SELECT
  COUNT(*),
  SUM(order_items.amount),
  orders.order_type,
  order_items.product_id,
  CASE
    WHEN order_items.discount_type_id = 1 Then 'coupon'
    WHEN order_items.discount_type_id = 4 Then 'voucher'
    ELSE ''
  END AS discount_type,
  CASE orders.order_type
    WHEN 'storno' THEN -order_items.amount
    ELSE order_items.amount
  END,
  currency_code,
  orders.billing_method
FROM
  order_items
  left JOIN `orders`
    ON `order_items`.`order_id` = `orders`.`order_id`
WHERE
  payment_status != "authorize"
  AND CAST(orders.created_at AS date) >=
      DATE_FORMAT(CURRENT_DATE - interval 1 month, '%Y-%m-01')
  AND CAST(orders.created_at AS date) <=
      DATE_FORMAT(last_day(CURRENT_DATE-interval 1 month), '%Y-%m-%d')
GROUP BY
  currency_code, shipping_method, order_type

Bjútiful.


Anélkül, hogy bármit is tudnánk a táblákról, néhány hibát azonnal ki lehet szúrni:

  • inkonzisztens idézőjel használat,
  • hiányos GROUP BY lista,
  • extra mező a GROUP BY listában
  • valószínűleg hiányos CASE .. WHEN felsorolás,
  • inkonzisztens alias használat,
  • inkonzisztens tábla hivatkozások,
  • művelet a where feltételben egy mezőn

Akkor most nézzük mindezt részletesen…

Inkonzisztens idézőjel használat

A dupla idézőjel ( " ) az ANSI standard szerint identifier quoter (van ennek szép magyar neve?). A MySQL szerver beállításaitól függően (ANSI_QUOTES) ez vagy igaz, vagy nem. Mivel a ” karakter jelentése nem egyértelmű, MySQL esetében nem javaslom a hasznlatát. Legjob tudomásom szerint az egyes idézőjel ( ' ) minden nagyobb SQL szerver implementációban használható string literal definiálására.

Ugyan nem technikai hibáról van szó, de mivel szerver beállítás függő és van egyszerű alternatívája, a dupla idézőjel használatát nem ajánlom, a két idézőjel típus keverését meg még annyira sem.

Hiányos GROUP BY lista

Ez egy tipikus MySQL specifikus hiba és talán ez okozza a legnagyobb fejfájást azoknak, akik MySQL-ről váltanak más RDBMS-re (esetleg mostanában frissítettek 5.6-ról 5.7-re).

Az SQL92 szabvány kimondja, hogy a mező lista minden elemének vagy szerepelnie kell a GROUP BY felsorolásban, vagy aggregálni kell. A MySQL ugyan megengedi, hogy a SELECT listában olyan mező szerepeljen, ami nincs aggregálva és a GROUP BY felsorolásból is hiányzik, azonban egy nagyon fontos megjegyzéssel (https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html):

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

Ami röviden annyit tesz, hogy ha az ilyen mezők értéke nem meghatározott, azaz a csoportban szereplő értékek bármelyike lehet és még egy ORDER BY oda biggyesztésével sem segíthetünk ezen.

Az SQL99 szabvány ugyan tesz némi engedményt, de csak akkor enged nem aggregált mezőt a SELECT listában, ha az funkcionálisan függ egy a GROUP BY listában szereplő mezőtől.

Fontos, hogy a MySQL5.7.5 verziótól kezdődően az SQL99 szabványt követi (https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html):

SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

Mit is jelent ez? Nézzünk két lekérdezést:

SELECT
  O.order_id,
  O.order_reference_key,
  COUNT(*) AS item_count
FROM
  order_items OI
  INNER JOIN orders O
    ON OI.order_id = O.order_id
GROUP BY
  O.order_id

SQL92 szerint ez a lekérdezés helytelen, hiszen az O.order_reference_key mező nincs aggregálva és nem is használjuk csoportosításra. A helyes lekérdezés így nézne ki:

<code="sql">
SELECT
  O.order_id,
  O.order_reference_key,
  COUNT(*) AS item_count
FROM
  order_items OI
  INNER JOIN orders O
    ON OI.order_id = O.order_id
GROUP BY
  O.order_id,
  O.order_reference_key

MySQL 5.7.5 előtt, gyári beállítások mellett, az első lekérdezés is vígan futtatható volt. Ebben az esetben még az eredmény is helyes lenne, hiszen az O.order_id és az O.order_reference_key 1:1 kapcsolatban állnak és az előbbi mentén csoportosítottunk. Ettől függetlenül ez csak egy MySQL specifikus viselkedés, ennél a lekérdezésnél működik is, azonban nagy pofára esés lehet belőle más lekérdezéseknél, vagy más SQL implementációkban.

MySQL 5.7.5-től kezdve (illetve SQL99 szerint) mindkét verzió helyes, hiszen az order_reference_key értéke függ az order_id értékétől (funkcionális függőségi kitétel teljesül).

Ok, akkor miért is hiba, ha nem sorolunk fel mindent? Nézzük a következő lekérdezést:


SELECT
  O.order_id,
  OI.discount_type_id,
  COUNT(*) AS item_count
FROM
  order_items OI
  INNER JOIN orders O
    ON OI.order_id = O.order_id
GROUP BY
  O.order_id

Az OI.discount_type_id értéke teljesen független az O.order_id értékétől. Ami a nagyobb baj, hogy egy rendelésen belül nem biztos, hogy minden elemhez ugyan az, vagy legalább ugyanolyan kedvezmény kapcsolódik. MySQL 5.7.5 előtt a lekérdezés lefut, ad is valamilyen eredményt, azonban arra nincs semmilyen ráhatásunk, hogy melyik discount_type_id értéket választja a szerver egy adott rendeléshez, ha több is előfordul a tételek között.

MySQL 5.7.5 valami ilyesmi hibát jelez:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
‘test.order_items.discount_type_id’ which is not functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by

Az elterjedt adatbázis szerverek a legjobb tudomásom szerint az SQL92 definíciót implementálták, így megkövetelik vagy az aggregálást, vagy a csoportosítást minden kimenetei mezőre.

Általában véve az ajánlott az, ha tartjuk magunkat az SQL92 definícióhoz. Ez hordozza a legkisebb hibalehetőséget.

Extra mező a GROUP BY listában

Ez alapjában véve nem technikai hiba, azonban javaslom, hogy egy inline megjegyzéssel jelezzétek, hogy miért van ott a mező. Nincs ugyan olyan szabály, hogy a GROUP BY listában szereplő kifejezéseknek bárhol máshol is meg kéne jelennie, azonban könnyen félreérthető eredményhez vezethet a használatuk.

Mi történik, ha a fenti példában a szerepel egy USD-express-purchase, illetve egy USD-pickup-purchase hármas? Az eredményben lesz két USD-purchase rekordunk, azonban semmiféle információnk nem lesz, hogy mi a különbség a kettő között. Ebben az esetben a különbség természetesen a shipping_method.

Hiányos CASE .. WHEN lista

A lekérdezésben szerepel egy CASE-WHEN szerkezet, ami a discount_type_id mező alapján dönti el, hogy a kedvezmény coupon vagy voucher alapú az adott sorhoz. Igen ám, csakhogy a discount_type_id szinte biztos, hogy más értékeket is felvehet. (Arról nem is beszélve, hogy ez az egyik olyan kifejezés, ami nincs aggregálva és a GROUP BY listában sem szrepel.)

Súlyos hiba, hogy mivel a mező/kifejezés nem szerepel a GROUP BY listában, egy csoporton belül a mező értéke bármi lehet.

A GROUP BY dolgot leszámítva technikailag ezzel semmi baj, a lekérdezés futni fog (az adott SQL szerveren). Ami hiányzik innen az a dokumentáció: Miért nincs felsorolva az összes típus (feltételezhetően még legalább kettő van) és miért vonható össze egy üres string nevű csoportba a maradék.

Az igazi meglepetés akkor érhet minket, amikor egy új engedmény típust vezetünk be és a teljes management egy emberként kérdezi, hogy mégis miért nem jelenik meg az a statban.

Mi a megoldás?

  • A discount_type lehetséges értékeit szervezzük ki egy szótár táblába (ez látszólag meg is történt, hisz numerikus értékekkel hivatkozik rájuk a lekérdezés)
  • Ha valóban szükségünk van ilyen bontásra, kapcsoljuk a szótár táblát
  • Csoportosítsunk a mező alapján

Íme a két ER diagram:

Tábla

A fix értékkészletű mezők (discount_type és order_type) a táblán belül vannak definiálva.

normalized_tables

A két ‘fix’ értékkészletű mező lehetséges értékeit kiszerveztük két külön táblába.

A kapcsolódó lekérdezés pedig valami ilyesmi:


SELECT
  O.order_type,
  O.currency_code,
  O.shipping_method,
  DT.discount_type_label,
  COUNT(*) AS item_count,
  SUM(OI.amount) AS total_amount
FROM
  order_items OI
  INNER JOIN orders O
    ON OI.order_id = O.order_id
  INNER JOIN discount_types DT
    ON OI.discount_type_id = DT.discount_type_id
WHERE
  O.payment_status != 'authorize'
  AND O.created_at >=
      LAST_DAY(CURRENT_DATE - INTERVAL 2 MONTH) +
          INTERVAL 1 DAY -- 1st of the prev month
  AND O.created_at <
      LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH) +
          INTERVAL 1 DAY -- 1st of this month
GROUP BY
  O.order_type,
  O.currency_code,
  O.shipping_method,
  DT.discount_type_label

 

Inkonzisztens alias használat

A lekérdezés SELECT listájában egyes kifejezések alias nélkül, mások aliassal szerepelnek. Ez egészen addig nem baj, amíg csak magunknak valami kliensben noszogatjuk a lekérdezést, azonban amint egy alkalmazással kell megetetnünk érdekes problémákba futhatunk bele, arról nem is beszélve, hogy a CASE orders.order_type WHEN 'storno' ... mezőnév nem igazán szerencsés.

Ezen nem sokat kell csámcsogni, minden kimeneti mezőnek adjunk egy nevet, főleg ha a lekérdezésünket vagy annak nyers eredményét más is láthatja.

Inkonzisztens tábla hivatkozások

Mint azt láthatjuk, a lekérdezésben szereplő mezőnevek van amikor táblanévvel, van amikor táblanév nélkül szerepelnek.

Ha egynél több tábla szerepel egy lekérdezésben, a tábláknak mindig adjunk aliast és a mezőkre mindig az aliassal együtt hivatkozzunk. Ha meg nem szeretünk gépelni, használjunk olyan IDE-t ami segít.

Az ok rendkívül egyszerű: Ha valaki létrehoz egy új mezőt és annak neve megegyezik egy már létező (és hivatkozott) mező nevével valamely táblában (amit a lekérdezésben használunk), a mező hirtelen nem lesz egyértelműen azonosítható, így a lekérdezésünk többé nem futtatható.

Tegyük fel, hogy a management kitalálja, hogy a shipping_method termékenként választható legyen. A fejlesztés során létre is hozzuk a szükséges mezőt az order_items táblában. Természetesen a shipping_method nevet adjuk az új mezőnek. Amint lefuttatjuk az ALTER-t az éles rendszeren, a fenti lekérdezést el is törtük.

Művelet egy mezőn a WHERE feltételben

Tisztázzuk az elején: ez néha elkerülhetetlen.

Mi a különbség az eredeti verzió

WHERE
  payment_status != "authorize"
  AND CAST(orders.created_at AS date) >=
      DATE_FORMAT(CURRENT_DATE - interval 1 month, '%Y-%m-01')
  AND CAST(orders.created_at AS date) <=
      DATE_FORMAT(LAST_DAY(CURRENT_DATE-interval 1 month), '%Y-%m-%d')

és mondjuk a következő között?

WHERE
  payment_status != 'authorize'
  AND orders.created_at >=
      LAST_DAY(CURRENT_DATE - INTERAVL 2 MONTH) + INTERVAL 1 DAY
  AND orders.created_at <
      LAST_DAY(CURRENT_DATE - interval 1 month) + interval 1 day

Csak annyi, hogy ha van index a created_at mezőn, akkor azt tudja használni a szerver a második esetben.

Általánosságban elmondható, hogy a jelenlegi RDBMS-ek képtelenek indexet használni, ha a mező egy kifejezésben szerepel. Ez igaz akkor is, ha a művelet adattípus vagy collation konvezió, akkor is ha dátum művelet, de akkor is, ha az egy egyszerű szorzás vagy összeadás.

Talán ez az egyik leggyakrabban elkövetett hiba és az egyik legkönyebben orvosolható is egyben. A különböző fórumokon és QA oldalakon rengeteg ‘miért lassú a lekérdezésem’ kérdéssel lehet találkozni, nagyon sok azért lassú, mert nem használ indexet. Képtelen rá.

Bónusz #1

A lekérdezés tartalmaz két CASE .. WHEN struktúrát, egyik sincs aggregálva. Nem tudom, hogy az eredeti szerző szándéka mi volt velük, de feltételezem, hogy mindkettő csak bent maradt miközben a lekérdezést írta annak szerzője, a végeredmény szempontjából valószínűleg irrelevánsak, hiszen teljesen nonszensz eredményt adnak ebben a kontextusban.

Ha a SELECT *-ot tipikus hibának minősítettem, ez annak a minősített esete. Nem csak az alkalmazás kap értelmezhetetlen és teljesen felesleges adatot, de még az SQL szerverrel is futtat néhány felesleges kört.

Konklúzió: takaríts magad után.

Bónusz #2

Szerencsére egyes hibákat a legtöbb optimizer ki tud javítani. Ilyen például a ‘használjunk mindenhol LEFT JOIN-t aztán majd  szűrjük amit kell’.

A fenti lekérdezésben az orders táblát LEFT JOIN-al kapcsoltuk az order_items táblához, majd a WHERE feltételek között azt mondtuk, hogy csak azok a rendelések érdekelnek, amiknek a fizetés státusza nem ‘authorized‘, illetve dátum alapján is van egy feltételünk, ezzel gyakorlatilag minden NULL értéket is kiszűrtünk, azokat is, amiket a LEFT JOIN miatt hozna létre az SQL szerver, azaz a LEFT JOIN-ból INNER JOIN-t fabrikáltunk.

Arról most ne is beszéljünk, hogy a LEFT JOIN mikor adhat olyan eredményt, ahol az order_items rekordnak nincs meg az orders rekordja.

Konklúzió: Ugyan nem hiba, szemantikailag nem állja meg a helyét, illetve az optimizerrel is felesleges köröket futtatunk. Használjuk a megfelelő JOIN típust.

Bónusz #3

Írjunk egy SQL Coding Standards dokumentumot és használjuk.

Érdekes módon, nagyon sok fejlesztőnél láttam, hogy az alkalmazás kódjához van egy gyönyörű coding standard, sőt annak használatát valamilyen automatizált eszköz ki is kényszeríti, azonban az SQL-hez nincs ilyen. Legyen legalább egy szabálygyűjtemény, ami taglalja, hogyan indenteljünk, mik az alapvető elnevezési szabályok, hogyan aliasoljunk, stb. Rengeteget segít, ha mindenki ugyanúgy formázza a lekérdezéseket, ha az elnevezési szabályok egyértelműek és mindenki használja őket.

Végezetül

Ha nekem kéne megírni a fenti lekérdezést, a végeredmény valami ilyesmi lenne:

SELECT
  O.order_type,
  O.currency_code,
  O.shipping_method,
  COUNT(*) AS item_count,
  SUM(OI.amount) AS total_amount
FROM
  order_items OI
  INNER JOIN orders O
    ON OI.order_id = O.order_id
WHERE
  O.payment_status != 'authorize'
  AND O.created_at >=
      LAST_DAY(CURRENT_DATE - INTERVAL 2 MONTH) +
          INTERVAL 1 DAY -- 1st of the prev month
  AND O.created_at <
      LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH) +
          INTERVAL 1 DAY -- 1st of this month
GROUP BY
  O.order_type,
  O.currency_code,
  O.shipping_method

A fentiek közül te átlagosan hány hibát követsz el egy lekérdezésben?

Disclaimer

Nyilvánvaló, hogy a példa lekérdezés, valamint az általa sugallt adatstruktúra nem tökéletes, ezért elnézést kérek, azonban úgy vélem, hogy egy ilyen 2 táblás lekérdezést mindenkinek egyszerűbb átlátni, mint egy olyat, ami egy agyon normalizált, “tökéletes” adatszerkezetre épül és úgy 10 táblát használ.