Sziasztok! Háttérfejlesztő vagyok, és mikroszolgáltatásokat írok Java + Spring nyelven. A Tinkoff egyik belső termékfejlesztési csoportjában dolgozom.
Csapatunkban gyakran felmerül a lekérdezések DBMS-ben történő optimalizálásának kérdése. Mindig szeretne egy kicsit gyorsabb lenni, de nem mindig boldogulhat az átgondolt indexekkel – keresnie kell néhány megoldást. Az egyik ilyen bolyongás során az interneten, és ésszerű optimalizálásokat keresve az adatbázisokkal való munka során, rájöttem , az SQL Performance Explained című könyv szerzője. Ez az a ritka típusú blog, amelyben az összes cikket sorban olvashatja.
Szeretnék lefordítani neked Marcus egy rövid cikkét. Bizonyos mértékig kiáltványnak is nevezhető, amely az offset művelet SQL szabvány szerinti végrehajtásának régi, de még mindig aktuális problémájára igyekszik felhívni a figyelmet.
Helyenként magyarázatokkal, megjegyzésekkel egészítem ki a szerzőt. Minden ilyen helyre „kb. a nagyobb érthetőség érdekében
Kis bemutatkozás
Szerintem sokan tudják, milyen problémás és lassú az eltoláson keresztüli oldalkiválasztással dolgozni. Tudta, hogy meglehetősen egyszerűen lecserélhető egy hatékonyabb kialakításra?
Tehát az offset kulcsszó azt mondja az adatbázisnak, hogy hagyja ki a kérés első n rekordját. Az adatbázisnak azonban továbbra is ki kell olvasnia ezeket az első n rekordot a lemezről, a megadott sorrendben (megjegyzés: rendezést kell alkalmazni, ha ez meg van adva), és csak ezután lehet n+1-től kezdve rekordokat visszaadni. A legérdekesebb az, hogy a probléma nem a DBMS-ben lévő konkrét megvalósításban van, hanem a szabvány szerinti eredeti definícióban:
…a sorok először a szerint vannak rendezve majd korlátozza a megadott sorok számának eldobásával elölről…
-SQL:2016, 2. rész, 4.15.3 Származtatott táblák (megjegyzés: jelenleg a leggyakrabban használt szabvány)
A lényeg itt az, hogy az offset egyetlen paramétert vesz igénybe – az átugorandó rekordok számát, és ennyi. Ezt a definíciót követve a DBMS csak az összes rekordot tudja lekérni, majd eldobni a feleslegeseket. Nyilvánvaló, hogy az eltolásnak ez a meghatározása többletmunkára kényszerít bennünket. És még az sem mindegy, hogy SQL vagy NoSQL.
Csak még egy kis fájdalom
Az offsettel kapcsolatos problémák ezzel még nem értek véget, és itt van az ok. Ha két oldalnyi adat lemezről történő kiolvasása között egy másik művelet új rekordot szúr be, mi történik ebben az esetben?
Ha az eltolást az előző oldalak rekordjainak átugrására használjuk, abban az esetben, ha a különböző oldalak olvasásai között új rekordot adunk hozzá, nagy valószínűséggel duplikátumokat kapunk (megjegyzés: ez akkor lehetséges, ha oldalanként olvasunk a konstrukció szerinti sorrendben, majd kimenetünk közepén új bejegyzést kaphat).
Az ábra világosan ábrázolja ezt a helyzetet. A bázis beolvassa az első 10 rekordot, majd beszúr egy új rekordot, amely az összes olvasott rekordot 1-gyel eltolja. Ezután a bázis új oldalt vesz a következő 10 rekordból, és nem a 11.-től kezdi, ahogy kellene, hanem a 10., megismételve ezt a rekordot. Más anomáliák is kapcsolódnak a kifejezés használatához, de ez a leggyakoribb.
Amint azt már megtudtuk, ezek nem egy adott DBMS vagy azok megvalósításának problémái. A probléma az oldalszámozás SQL szabvány szerinti meghatározásában van. Megmondjuk a DBMS-nek, hogy melyik oldalt kell lekérni, vagy hány rekordot hagyjon ki. Az adatbázis egyszerűen nem képes optimalizálni egy ilyen kérést, mivel ehhez túl kevés információ áll rendelkezésre.
Azt is érdemes tisztázni, hogy ez nem egy adott kulcsszóval van probléma, hanem inkább a lekérdezés szemantikájával. Számos szintaxis létezik, amelyek problematikus természetükben megegyeznek:
- Az offset kulcsszó a korábban említett.
- Két kulcsszóból álló konstrukció limit [offset] (bár maga a limit nem olyan rossz).
- Szűrés alsó határok alapján, sorszámozás alapján (például sor_szám(), rownum, stb.).
Mindezek a kifejezések egyszerűen megmondják, hány sort kell kihagyni, további információ vagy kontextus nélkül.
A cikk későbbi részében az offset kulcsszót az összes beállítás összegzéseként használjuk.
Élet OFFSET nélkül
Most képzeljük el, milyen lenne a világunk mindezen problémák nélkül. Kiderült, hogy az eltolás nélküli élet nem is olyan nehéz: a kijelöléssel csak azokat a sorokat jelölhetjük ki, amelyeket még nem láttunk (megjegyzés: vagyis azokat, amelyek nem voltak az előző oldalon), a where feltétellel.
Ebben az esetben abból indulunk ki, hogy a kiválasztások egy rendezett halmazon (régi jó sorrendben) hajtódnak végre. Mivel rendezett halmazunk van, egy meglehetősen egyszerű szűrővel csak azokat az adatokat kapjuk meg, amelyek az előző oldal utolsó rekordja mögött vannak:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYEz ennek a megközelítésnek az egész elve. Természetesen a dolgok szórakoztatóbbá válnak, ha sok oszlop szerint rendezünk, de az ötlet továbbra is ugyanaz. Fontos megjegyezni, hogy ez a kialakítás sokak számára alkalmazható - döntések.
Ezt a megközelítést keresési módszernek vagy kulcskészlet lapozásnak nevezik. Megoldja a lebegő eredmény problémát (megj.: a korábban leírt oldalolvasások közötti írás helyzete) és persze amit mindannyian szeretünk, gyorsabban és stabilabban működik, mint a klasszikus offset. A stabilitás abban rejlik, hogy a kérések feldolgozási ideje nem növekszik arányosan a kért tábla számával (megjegyzés: ha többet szeretne megtudni a lapozás különböző megközelítési módjairól, akkor . Ott is találhat összehasonlító benchmarkokat a különböző módszerekhez).
Az egyik csúszda hogy a billentyűk szerinti lapozás természetesen nem mindenható – megvannak a maga korlátai. A legjelentősebb az, hogy nem tud véletlenszerű oldalakat olvasni (megjegyzés: következetlenül). Azonban a végtelen görgetés korszakában (megjegyzés: az előlapon) ez nem olyan probléma. Az oldalszám megadása a kattintáshoz egyébként is rossz döntés a felhasználói felület kialakításában (megj.: a cikk szerzőjének véleménye).
Mi a helyzet az eszközökkel?
A billentyűkön történő lapozás gyakran nem megfelelő, mivel ehhez a módszerhez nincs hangszeres támogatás. A legtöbb fejlesztőeszköz, beleértve a különféle keretrendszereket is, nem teszi lehetővé a lapozás végrehajtásának pontos megválasztását.
A helyzetet súlyosbítja, hogy a leírt módszer végpontok közötti támogatást igényel az alkalmazott technológiákban - a DBMS-től az AJAX kérés böngészőben történő végrehajtásáig, végtelen görgetéssel. Ahelyett, hogy csak az oldalszámot adná meg, most meg kell adnia egy kulcskészletet az összes oldalhoz egyszerre.
A kulcsokon történő lapozást támogató keretrendszerek száma azonban fokozatosan növekszik. Íme, ami jelenleg elérhető:
- Java számára;
- Ruby számára;
- и Django számára;
- Python számára;
- — kritérium API a JPA megvalósításokhoz;
- Perl esetében;
- , Mapper a Node.js számára .
(Megjegyzés: néhány hivatkozást eltávolítottunk, mert a fordítás időpontjában egyes könyvtárak 2017-2018 óta nem frissültek. Ha érdekel, megtekintheti az eredeti forrást.)
Ebben a pillanatban van szükség a segítségedre. Ha olyan keretrendszert fejleszt vagy támogat, amely bármilyen módon használja az oldalszámozást, akkor azt kérem, sürgetem, kérem, hogy biztosítson natív támogatást a kulcsokon történő lapozáshoz. Ha kérdése van, vagy segítségre van szüksége, szívesen segítek (, , ) (megjegyzés: Marcusszal kapcsolatos tapasztalataim alapján elmondhatom, hogy nagyon lelkesen terjeszti ezt a témát).
Ha olyan kész megoldásokat használ, amelyekről úgy gondolja, hogy érdemes támogatni a kulcsok szerinti lapozást, készítsen kérést, vagy akár kínáljon kész megoldást, ha lehetséges. Ezt a cikket is linkelheti.
Következtetés
Az ok, amiért egy ilyen egyszerű és hasznos megközelítés, mint a billentyűk szerinti lapozás nem elterjedt, nem az, hogy technikailag nehéz vagy nagy erőfeszítést igényel. Ennek fő oka az, hogy sokan megszokták, hogy offszetet lássanak és dolgozzanak – ezt a megközelítést maga a szabvány diktálja.
Emiatt kevesen gondolnak az oldalszámozás szemléletének megváltoztatására, és emiatt a keretrendszerek és a könyvtárak instrumentális támogatása gyengén fejlődik. Ezért ha közel áll Önhöz az ofszetmentes lapozás gondolata és célja, segítsen ennek terjesztésében!
Forrás:
Szerző: Markus Winand
Forrás: will.com