Datetime mező order by lassulás

Datetime mező order by lassulás
2022-02-22T10:15:47+01:00
2022-02-24T09:41:57+01:00
2022-10-15T21:21:00+02:00
Vargab
Sziasztok!

Az a problémám, hogy adott egy query, amiben vannak indexes joinok, többféle szűrések stb és dinamikusan lehet order by állítani hozzá frontendről.

A dátum mezőre (create_time) való order by egy 300k-s innodb táblában közel 10 sec mire választ ad,  ez nélkül pedig szinte azonnal.

A dátum mezők datetime típusúak, indexeltek.

Azt észrevettem, ha nem kérek ki joinolt adatokat, akkor villám gyors.
Tesztelgettem mindenféle select, join tartalommal és azt látom, hogy kb konstans idő alatt fut le, ha kérek ki joinolt adatot, mindegy mennyit.

A rendezést kipróbáltam datetime, timestamp típusú mezőkkel, nagyjából azonos az eredmény.
Az is mindegy, melyik tábla create_time -ra rendezek, kb egyformán lassú.


A mysql TMP-je ramdiskre van irányítva, ettől nem tudok gyorsabbat :) 
Látom, hogy szinte pillanatok alatt létrehozza a munkatáblát a tmp-n és utána gondolom rendez...

Van ötletetek, hogyan lehet ezt élhető sebességre hozni? 300k tábla nem a világ ugye... szóval vajon mi lehet a központi probléma szerintetek?

SELECT `dt`.*, `s`.`sort` as `shop_short_name`, `u`.`username`, `i`.`invoice_number`, `i`.`created`, `i`.`canceled`, `i`.`type` as `invoice_type`, `i`.`invoice_is_paid`, `i`.`external_id`, `i`.`path`, `pa`.`package_number`, `pa`.`id` as `pId`, `pa`.`type` as `package_type`, `pa`.`status` as `package_status`, `pa`.`create_time` as `comission_date`, `po`.`payment_mode`, `pa`.`gathering` as `gathering_status`
FROM `orders` AS `dt`
LEFT JOIN `payment_options` AS `po` ON `dt`.`po_id` = `po`.`id`
LEFT JOIN `invoices` AS `i` ON `dt`.`last_invoice_id`=`i`.`id`
LEFT JOIN `shops` AS `s` FORCE INDEX FOR JOIN (`PRIMARY`) ON `s`.`id`=`dt`.`shop_id`
LEFT JOIN `users` AS `u` FORCE INDEX FOR JOIN (`PRIMARY`) ON `u`.`id`=`dt`.`user_id`
LEFT JOIN `packages` AS `pa` FORCE INDEX FOR JOIN (`PRIMARY`) ON `dt`.`last_package_id`=`pa`.`id`
WHERE `dt`.`status` !=0
AND `dt`.`archiv` =0
GROUP BY `dt`.`id`
ORDER BY `dt`.create_time DESC
LIMIT 10
Mutasd a teljes hozzászólást!
A rengeteg valószínűleg felesleges force index helyett csinálj inkább egy explaint ahogy írták, és nézd meg, hol a baj.

Nem tudom egyébként egy group by eredményét hogy tudná a gép egy másik oszlop szerinti indexszel rendezni.

300k elem rendezése bizony eltarthat egy ideig.

Miért kell a group by?
Mutasd a teljes hozzászólást!

  • Én ugyan csak műkedvelő vagyok a témában, de a query plant nézted már? Valószínűleg támpontot adna, ha összehasonlítanád a gyors lekérdezés tervét a lassú lekérdezésével.
    Mutasd a teljes hozzászólást!
  • Miért nem próbálsz meg egyszerűen id-ra rendezni? Nem ugyanaz lesz az eredménye, mintha create_time-ra rendeznél?
    Mutasd a teljes hozzászólást!
  • Lehet kényelmetlen de a timestamp gyors.
    Az átalakítás (esetleg plusz oszlop) ami kényelmetlen.
    Mutasd a teljes hozzászólást!
  • Szia! 
    Itt több adatban order, invoice, package is van dátum mező amikre változatosan kell tudni order-by rendezni és ez nem feltétlenül egyezik az adat felvételkori ID-vel, mert az adatok importból jönnek be, nem feltétlenül időrendben.
    Mutasd a teljes hozzászólást!
  • kb. 1 sec különbözetet tapasztaltam a datetime/timestamp rendezése közt.
    A fő baj az 1-2 sec / 10-13 sec aránnyal van ID/datetime rendezés kközött.
    Mutasd a teljes hozzászólást!
  • A Csabóka által javasolt módszert ajánlanám én is:
    az EXPLAIN paranccsal meg tudod nézni, hogy egy adott select mit csinál, mikor, milyen indexet használ, ha használ. Van egy olyan érzésem, hogy amikor lassú, akkor nem index alapján dolgozik.

    Régi emlékek alapján RDBMS tmp-t nem jó ötlet ramdiskre tenni (ha csak emiatt van fenntartva), mert csak elveszed a memóriát a szerveredtől olyan célra, amire eleve csak akkor használja, ha kifogyott a memóriából.
    Mutasd a teljes hozzászólást!
  • SELECT `p`.* FROM (SELECT `dt`.*, `s`.`sort` as `shop_short_name`, `u`.`username`, `i`.`invoice_number`, `i`.`created`, `i`.`canceled`, `i`.`type` as `invoice_type`, `i`.`invoice_is_paid`, `i`.`external_id`, `i`.`path`, `pa`.`package_number`, `pa`.`id` as `pId`, `pa`.`type` as `package_type`, `pa`.`status` as `package_status`, `pa`.`create_time` as `comission_date`, `po`.`payment_mode`, `pa`.`gathering` as `gathering_status` FROM `orders` AS `dt` LEFT JOIN `payment_options` AS `po` ON `dt`.`po_id` = `po`.`id` LEFT JOIN `invoices` AS `i` ON `dt`.`last_invoice_id`=`i`.`id` LEFT JOIN `shops` AS `s` FORCE INDEX FOR JOIN (`PRIMARY`) ON `s`.`id`=`dt`.`shop_id` LEFT JOIN `users` AS `u` FORCE INDEX FOR JOIN (`PRIMARY`) ON `u`.`id`=`dt`.`user_id` LEFT JOIN `packages` AS `pa` FORCE INDEX FOR JOIN (`PRIMARY`) ON `dt`.`last_package_id`=`pa`.`id` WHERE `dt`.`status` !=0 AND `dt`.`archiv` =0 GROUP BY `dt`.`id` LIMIT 10 ) `p` ORDER BY `dt`.create_time DESC
    Mutasd a teljes hozzászólást!
  • Azért van ramdisk-en, mert nagy táblák vannak, sokszor kell tmp tábla készítése és ez normál esetben ugye diskre írna, ezt küszöböli ki a ramdisk, 2 nagyságrenddel gyorsabb így a tmp. RAM van bőven erre a célra is.
    Mutasd a teljes hozzászólást!
  • Na ezzel az a baj, hogy normál orderezésben leválogat 10 tételt, majd azt rendezi csak utána sorba, így nem kapjuk vissza a tábla elejét/végét hanem folyton csak az id alapján első 10-et, de azt legalább változtatható sorrendben.
    Mutasd a teljes hozzászólást!
  • Ez a sor tuti jó?

    LEFT JOIN `packages` AS `pa` FORCE INDEX FOR JOIN (`PRIMARY`) ON `dt`.`last_package_id`=`pa`.`id`
    Mutasd a teljes hozzászólást!
  • Off, nem valószínű, hogy köze lesz a memóriának a fentiekhez, de nem lenne jobb az ideiglenes táblákat CREATE TEMPORARY TABLE ... STORAGE MEMORY; segítségével gyártani?

    Saját, igaz, régi tapasztalataim alapján sok RDBMS jobban szereti, ha belül csinálod amit lehet.
    Mutasd a teljes hozzászólást!
  • Szia!

    Ha id alapján kell group by csoportosítást végezni, akkor azt a joint célszerű pontosítani egy subquery-vel, amelyik megsokszorozza az adatokat. Ha ebben a subqueryben van limit 1, akkor a join csak egy sort kapcsol a táblához. És ezzel megoldható az is, hogy a joinolt adat csak a szükséges oszlopokat tartalmazza. Persze ez az adatok jellegétől is függ, és értem hogy nem ez az alapvető probléma. De a sebességen talán segít, és ha a végén nem kell a group by, akkor az order by hatékonyabb lehet.

    Esetleg próbáld meg azt, hogy az order by mezőt is beteszed a csoportosításba második feltételként:

    GROUP BY `dt`.`id`, `dt`.create_time
    ORDER BY `dt`.create_time DESC 

    De ez csak tipp. Valahol mintha olvastam volna, hogy a mysql nem használja az order by esetén az indexet, ha az order by és a group by különböző kifejezéseket tartalmaz. Egy próbát megér.
    Mutasd a teljes hozzászólást!
  • A rengeteg valószínűleg felesleges force index helyett csinálj inkább egy explaint ahogy írták, és nézd meg, hol a baj.

    Nem tudom egyébként egy group by eredményét hogy tudná a gép egy másik oszlop szerinti indexszel rendezni.

    300k elem rendezése bizony eltarthat egy ideig.

    Miért kell a group by?
    Mutasd a teljes hozzászólást!
  • GROUP BY `dt`.`id` helyett használj DISTINCT

    SELECT DISTINCT `dt`.*, `s`.`sort` as `shop_short_name`, `u`.`username`, `i`.`invoice_number`, `i`.`created`, `i`.`canceled`, `i`.`type` as `invoice_type`, `i`.`invoice_is_paid`, `i`.`external_id`, `i`.`path`, `pa`.`package_number`, `pa`.`id` as `pId`, `pa`.`type` as `package_type`, `pa`.`status` as `package_status`, `pa`.`create_time` as `comission_date`, `po`.`payment_mode`, `pa`.`gathering` as `gathering_status` FROM `orders` AS `dt` LEFT JOIN `payment_options` AS `po` ON `dt`.`po_id` = `po`.`id` LEFT JOIN `invoices` AS `i` ON `dt`.`last_invoice_id`=`i`.`id` LEFT JOIN `shops` AS `s` FORCE INDEX FOR JOIN (`PRIMARY`) ON `s`.`id`=`dt`.`shop_id` LEFT JOIN `users` AS `u` FORCE INDEX FOR JOIN (`PRIMARY`) ON `u`.`id`=`dt`.`user_id` LEFT JOIN `packages` AS `pa` FORCE INDEX FOR JOIN (`PRIMARY`) ON `dt`.`last_package_id`=`pa`.`id` WHERE `dt`.`status` !=0 AND `dt`.`archiv` =0 ORDER BY `dt`.create_time DESC LIMIT 10
    Mutasd a teljes hozzászólást!
  • Hali!

    GROUP BY `dt`.`id` helyett használj DISTINCT

    A GROUP BY és a DISTINCT csak abban az esetben lehetnek „csereszabatosak” így, ha csak egyetlen oszlopot szeretnél lekérdezni (akkor is csak ha ugyanaz a csoportosítás feltétele, mint a kiválasztott oszlop). Itt pedig nem az az eset van, ezért nem jó ez a tanács. Amúgy sem célszerű keverni, egyikkel helyettesíteni a másikat, mivel nem ugyanaz a céljuk.

    Mutasd a teljes hozzászólást!
  • Mivel GROUP-olja a az orders adatbázis táblát (dt-t) és nem kezd semmit a group többi sorával ezért láttam érdemesnek a DISTINCT-et.
    Mutasd a teljes hozzászólást!
  • Hali!

    Mivel GROUP-olja a az orders adatbázis táblát (dt-t)… 

    Nem az `orders` táblát csoportosítja, hanem az eredmény-halmazt, az `orders` táblából származó `id` mező alapján.

    … és nem kezd semmit a group többi sorával…

    Ezt nem tudom értelmezni.

    … ezért láttam érdemesnek a DISTINCT-et.

    Csak nem ugyanazt az eredményt adhatja a DISTINCT, mint a GROUP BY `dt`.`id` használata. DISTINCT esetén a lekérdezés eredményének összes oszlopát vizsgálja, és ha két eredmény-sor bármelyik oszlopában nem azonos érték van, akkor azokat berakja az eredmény-halmazba. Tehát, ha pl. két sorban a `dt`.`id` ugyanaz (mert pl. a JOIN-olt táblák között van olyan, amiből több sort kapcsol), de bármelyik másik oszlop nem, akkor mindkét sor belekerül. Míg GROUP BY `dt`.`id` esetén tutira olyan eredmény-halmaz lesz, amiben egy `dt`.`id` csak egyszer szerepel. Más kérdés – viszont a DISTINCT vs GROUP BY viszonylatban lényegtelen –, hogy aggregátor használata nélküli GROUP BY-nak mi értelme van (van-e egyáltalán értelme), hiszen nincs ráhatásod, hogy a csoportosításba milyen oszlop-értékeket vesz be.

    Mutasd a teljes hozzászólást!
  • Szia!

    1) Gyanús, hogy a "group by" felesleges a select-be. Egy megrendeléshez normális eseten max. egy-egy rekordnak kellene a kapcsolt táblákból (már ha jól értem az adatszerkezetet). Mivel a group by sokat tud lassítani, célszerű megszabadulni tőle.

    2) Ha mégis duplikálódik valamelyik tábla miatt, akkor csak azt a táblát kellene group by-jal egységesíteni, és úgy joinolni

    3) Ekkora adatmennyiségnél érdemes fixen szűrni pl. időszakra, mondjuk egy évre vagy hónapra. A teljesítmény szempontjából rengeteget segít (ha van erre index - legyen), és a mindennapi használat során jellemzően nem éveket átfogó lekérdezéseket futtatunk.

    4) A MySQL tmp-jét nem ram-ra kell irányítani, hanem a megfelelő memória buffereket kell úgy növelni, hogy jellemzően ne akarjon temp fájlt gyártani. Lásd: MySQL, ORDER BY Optimization, "Influencing ORDER BY Optimization" szakasz.

    4.1) Érdemes valami mysql tunert hasznáni, ami segít jól paraméterezni a szervert, pl.: MySQLTuner-perl
    Mutasd a teljes hozzászólást!
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd