Ne használja az OFFSET és a LIMIT értéket oldalszámozott lekérdezésekben

Elmúltak azok az idők, amikor nem kellett aggódnia az adatbázis teljesítményének optimalizálása miatt. Az idő nem áll meg. Minden új technológiai vállalkozó szeretné létrehozni a következő Facebookot, miközben megpróbálja összegyűjteni a kezébe kerülő összes adatot. A vállalkozásoknak szükségük van ezekre az adatokra, hogy jobban kiképezzék azokat a modelleket, amelyek segítségével pénzt kereshetnek. Ilyen körülmények között a programozóknak API-kat kell létrehozniuk, amelyek lehetővé teszik számukra, hogy gyorsan és megbízhatóan dolgozzanak hatalmas mennyiségű információval.

Ha már régóta tervez alkalmazás- vagy adatbázis-háttérprogramokat, akkor valószínűleg kódot írt oldalszámozott lekérdezések futtatásához. Például így:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Úgy, ahogy van?

De ha így csináltad az oldalszámozást, akkor sajnálattal kell azt mondanom, hogy nem a leghatékonyabb módon tetted.

Akarsz ellenem tiltakozni? Képes nincs tölt idő. Laza, Shopify и mixmax Már használják azokat a technikákat, amelyekről ma beszélni szeretnék.

Nevezzen meg legalább egy olyan háttérfejlesztőt, aki még soha nem használta OFFSET и LIMIT oldalszámos lekérdezések végrehajtásához. Az MVP-ben (Minimum Viable Product) és azokban a projektekben, ahol kis mennyiségű adatot használnak, ez a megközelítés teljesen alkalmazható. Úgymond „csak működik”.

Ha azonban a semmiből kell megbízható és hatékony rendszereket létrehoznia, akkor előre gondoskodnia kell az ilyen rendszerekben használt adatbázisok lekérdezésének hatékonyságáról.

Ma a lapozásos lekérdezőmotorok gyakran használt (túl rossz) implementációival kapcsolatos problémákról fogunk beszélni, és arról, hogyan érhetünk el nagy teljesítményt az ilyen lekérdezések végrehajtása során.

Mi a baj az OFFSET-tel és a LIMIT-tel?

Mint már említettük, OFFSET и LIMIT Jól teljesítenek olyan projektekben, amelyekben nem kell nagy mennyiségű adattal dolgozni.

A probléma akkor jelentkezik, amikor az adatbázis olyan méretűre nő, hogy már nem fér el a szerver memóriájában. Ha azonban ezzel az adatbázissal dolgozik, oldalszámozott lekérdezéseket kell használnia.

Ahhoz, hogy ez a probléma megnyilvánuljon, léteznie kell olyan helyzetnek, amelyben a DBMS nem hatékony Full Table Scan műveletet hajt végre minden oldalszámozott lekérdezésnél (miközben előfordulhatnak beillesztési és törlési műveletek, és nincs szükségünk elavult adatokra!).

Mi az a „teljes táblázat-ellenőrzés” (vagy „szekvenciális táblázatvizsgálat”, szekvenciális vizsgálat)? Ez egy olyan művelet, amelynek során a DBMS szekvenciálisan beolvassa a tábla minden sorát, vagyis a benne lévő adatokat, és ellenőrzi, hogy megfelelnek-e egy adott feltételnek. Ez a fajta táblázatvizsgálat köztudottan a leglassabb. A tény az, hogy amikor végrehajtják, számos bemeneti/kimeneti műveletet hajtanak végre, amelyek a kiszolgáló lemezalrendszerét érintik. A helyzetet rontja a lemezeken tárolt adatokkal való munkavégzés késése, valamint az a tény, hogy az adatok lemezről a memóriába átvitele erőforrásigényes művelet.

Például 100000000 XNUMX XNUMX felhasználó rekordjai vannak, és lekérdezést futtat a következővel: OFFSET 50000000. Ez azt jelenti, hogy a DBMS-nek be kell töltenie ezeket a rekordokat (és nincs is rájuk szükségünk!), el kell helyeznie a memóriába, és ezután mondjuk 20 eredményt kell vennie LIMIT.

Tegyük fel, hogy ez így nézhet ki: „50000-től 50020-ig válasszon sorokat 100000-ből”. Vagyis a rendszernek először 50000 XNUMX sort kell betöltenie a lekérdezés befejezéséhez. Látod, mennyi felesleges munkát kell majd elvégeznie?

Ha nem hiszi, nézze meg azt a példát, amelyet a funkciók segítségével készítettem db-fiddle.com. 

Példa a db-fiddle.com webhelyen

Ott, balra, a mezőn Schema SQL, van egy kód, amely 100000 XNUMX sort szúr be az adatbázisba, jobb oldalon pedig a mezőbe Query SQL, két lekérdezés jelenik meg. Az első, lassú így néz ki:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

A második pedig, amely ugyanennek a problémának a hatékony megoldása, a következő:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Ezen kérések teljesítéséhez kattintson a gombra Run az oldal tetején. Ezt követően összehasonlítjuk a lekérdezés végrehajtási idejére vonatkozó információkat. Kiderült, hogy egy hatástalan lekérdezés végrehajtása legalább 30-szor tovább tart, mint a másodiké (ez az idő futásonként változik; például a rendszer jelentheti, hogy az első lekérdezés végrehajtása 37 ms-ig tartott, de a második - 1 ms).

És ha több adat van, akkor minden még rosszabbul fog kinézni (hogy meggyőződj erről, nézd meg a példa 10 millió sorral).

Az imént tárgyaltak némi betekintést nyújtanak az adatbázis-lekérdezések tényleges feldolgozásába.

Kérjük, vegye figyelembe, hogy minél magasabb az érték OFFSET — annál tovább tart a kérés teljesítése.

Mit használjak az OFFSET és LIMIT kombinációja helyett?

Kombináció helyett OFFSET и LIMIT Érdemes a következő séma szerint épített szerkezetet használni:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Ez a lekérdezés végrehajtása kurzor alapú lapozással.

Ahelyett, hogy a jelenlegieket helyben tárolná OFFSET и LIMIT és minden kéréssel továbbítja őket, tárolnia kell az utoljára kapott elsődleges kulcsot (általában ez ID) És LIMIT, ennek eredményeként a fentiekhez hasonló lekérdezéseket kapunk.

Miért? A lényeg az, hogy az utolsó beolvasott sor azonosítójának explicit megadásával megmondja a DBMS-nek, hogy hol kell elkezdenie keresni a szükséges adatokat. Ráadásul a keresés a kulcs használatának köszönhetően hatékonyan fog lezajlani, a rendszert nem kell elvonni a megadott tartományon kívül eső vonalakkal.

Vessünk egy pillantást a különböző lekérdezések alábbi teljesítmény-összehasonlítására. Íme egy eredménytelen lekérdezés.

Lassú kérés

És itt van a kérés optimalizált változata.

Gyors kérés

Mindkét lekérdezés pontosan ugyanannyi adatot ad vissza. De az első 12,80 másodpercet vesz igénybe, a második pedig 0,01 másodpercet vesz igénybe. Érzi a különbséget?

Lehetséges problémák

Ahhoz, hogy a javasolt lekérdezési módszer hatékonyan működjön, a táblának rendelkeznie kell egy oszloppal (vagy oszlopokkal), amelyek egyedi, szekvenciális indexeket, például egész számot tartalmaznak. Bizonyos esetekben ez meghatározhatja az ilyen lekérdezések használatának sikerességét az adatbázissal való munka sebességének növelése érdekében.

Természetesen a lekérdezések összeállításakor figyelembe kell venni a táblák sajátos architektúráját, és ki kell választani azokat a mechanizmusokat, amelyek a legjobban működnek a meglévő táblákon. Ha például nagy mennyiségű kapcsolódó adatot tartalmazó lekérdezéseken kell dolgoznia, akkor ez érdekes lehet ezt cikk.

Ha azzal a problémával szembesülünk, hogy hiányzik egy elsődleges kulcs, például ha van egy táblánk sok-több kapcsolattal, akkor a hagyományos megközelítés OFFSET и LIMIT, garantáltan megfelel nekünk. Használata azonban potenciálisan lassú lekérdezéseket eredményezhet. Ilyen esetekben javaslom az automatikusan növekvő elsődleges kulcs használatát, még akkor is, ha ez csak a lapozásos lekérdezések kezeléséhez szükséges.

Ha érdekel ez a téma - itt, itt и itt - számos hasznos anyag.

Eredményei

A fő következtetés, amit levonhatunk, hogy bármilyen méretű adatbázisról beszélünk, mindig szükséges elemezni a lekérdezés végrehajtási sebességét. Napjainkban rendkívül fontos a megoldások méretezhetősége, és ha minden helyesen van megtervezve egy bizonyos rendszeren való munka kezdetétől, ez a jövőben sok problémától kímélheti meg a fejlesztőt.

Hogyan elemzi és optimalizálja az adatbázis-lekérdezéseket?

Forrás: will.com