AllYouNeedIsBackend

Nagy rajongója vagyok az adatbázisoknak. Egyetemista koromban még saját DBMS-t is akartam készíteni. Most RDBMS és NoSQL megoldásokkal is dolgozom, és nagyon lelkes vagyok ezzel kapcsolatban. Tudod, nincs aranykalapács, minden problémának megvan a saját megoldása. Vagy a megoldások egy részhalmaza.

A The SQL I Love <3 blogbejegyzés-sorozatban végigvezetlek néhány SQL-lel megoldott problémán, amelyeket különösen érdekesnek találtam. A megoldásokat egy több mint 100 millió rekordot tartalmazó táblával teszteltem. Minden példa a MySQL-t használja, de az ötletek más relációs adattárolókra, például a PostgreSQL-re, az Oracle-re és az SQL Serverre is alkalmazhatók.

Ez a fejezet egy nagyméretű táblázat hatékony beolvasására összpontosít, amely az offset elsődleges kulcsra vonatkozó oldalszámozást használja. Ezt kulcscsoportos paginálásnak is nevezik.

Háttér

A fejezetben a következő adatbázis-struktúrát használjuk példaként. A felhasználókról szóló kanonikus példának bármelyik tartományba illeszkednie kell.

CREATE TABLE `users` ( `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `external_id` varchar(32) NOT NULL, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `metadata` text COLLATE utf8_unicode_ci, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), UNIQUE KEY `uf_uniq_external_id` (`external_id`), UNIQUE KEY `uf_uniq_name` (`name`), KEY `date_created` (`date_created`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Néhány megjegyzés a struktúrával kapcsolatban:

• external_id oszlop UUID formátumban tárolja az ugyanazon felhasználóra való hivatkozást más rendszerben
• name képviseli Firstname Lastname
• metadata oszlop JSON blobot tartalmaz mindenféle strukturálatlan adatot

A táblázat viszonylag nagy, körülbelül 100 000 000 rekordot tartalmaz. Kezdjük el a tanulási utunkat.

Nagy tábla átvizsgálása

Probléma: Végig kell járni a táblán, kivenni minden egyes rekordot, átalakítani az alkalmazás kódján belül, és beilleszteni egy másik helyre. A bejegyzésben az első fázisra – a táblázat átvizsgálására – koncentrálunk.

Nyilvánvaló és rossz megoldás

SELECT user_id, external_id, name, metadata, date_createdFROM users;

A 100 000 000 rekorddal rendelkező esetemben a lekérdezés soha nem fejeződik be. A DBMS egyszerűen megöli. Miért? Valószínűleg azért, mert az egész tábla RAM-ba való betöltési kísérletéhez vezetett. Mielőtt visszaadná az adatokat az ügyfélnek. Egy másik feltételezés – túl sok időt vett igénybe az adatok előzetes betöltése a küldés előtt, és a lekérdezés időzített. Mindenesetre az összes rekord időben történő lekérdezésére tett kísérletünk kudarcot vallott. Valami más megoldást kell találnunk.

Megoldás #2

Megpróbálhatjuk az adatokat oldalakonként megszerezni. Mivel a rekordok nem garantáltan rendezettek egy táblában fizikai vagy logikai szinten – a DBMS oldalán kell rendeznünk őket a ORDER BY záradékkal.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 0, 10 000;10 000 rows in set (0.03 sec)

Sweet. Működött. Lekértük a 10 000 rekordot tartalmazó első oldalt, és csak 0.03 másodpercig tartott a visszaadása. Azonban hogyan működne az 5000. oldalnál?

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size10 000 rows in set (40.81 sec)

Ez valóban nagyon lassú. Nézzük meg, mennyi időre van szükség a legutolsó oldal adatainak lekérdezéséhez.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 99 990 000, 10 000; --- 9999th page * 10 000 page size10 000 rows in set (1 min 20.61 sec)

Ez őrület. A háttérben futó megoldásoknál azonban rendben lehet. Még egy rejtett problémára derülhet fény a megközelítéssel kapcsolatban, ha megpróbálsz törölni egy rekordot a táblázatból a beolvasás közepén. Tegyük fel, hogy befejezted a 10. oldalt (100 000 rekordot már megnéztél), és a 100 001 és 110 000 közötti rekordokat fogod beolvasni. De a 99 998 és 99 999 rekordok törlődnek a következő SELECT végrehajtás előtt. Ebben az esetben a következő lekérdezés váratlan eredményt ad vissza:

 SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 100 000, 10 000; N, id, ... 1, 100 003, ... 2, 100 004, ...

Amint látható, a lekérdezés kihagyta a 100 001 és 100 002 azonosítóval rendelkező rekordokat. Ezeket a megközelítéssel az alkalmazás kódja nem fogja feldolgozni, mert a két törlési művelet után az első 100 000 rekord között szerepelnek. Ezért a módszer megbízhatatlan, ha az adathalmaz változékony.

3. megoldás – a mai utolsó

A megközelítés nagyon hasonlít az előzőhöz, mert továbbra is lapozást használ, de most ahelyett, hogy a beolvasott rekordok számára támaszkodnánk, a legutolsó meglátogatott rekord user_id-ját használjuk offset-nek.

Egyszerűsített algoritmus:

1. A táblázatból PAGE_SIZE rekordok számát kapjuk. A kezdő eltolás értéke 0.
2. A következő oldal eltolásaként a tételben a user_id maximálisan visszaadott értékét használjuk.
3. A következő tételt azokból a rekordokból kapjuk, amelyek user_id értéke nagyobb, mint az aktuális offset.

A lekérdezés az 5 000. oldalra, minden oldal 10 000 felhasználó adatait tartalmazza:

SELECT user_id, external_id, name, metadata, date_createdFROM usersWHERE user_id > 51 234 123 --- value of user_id for 50 000 000th recordORDER BY user_id ASCLIMIT 10 000;10 000 rows in set (0.03 sec)

Megjegyezzük, hogy a user_id értékei nem szekvenciálisak, és lehetnek hiányosságok, például a 25 348 közvetlenül a 25 345 után van. A megoldás akkor is működik, ha a későbbi oldalakon lévő rekordok törlésre kerülnek – a lekérdezés még ebben az esetben sem hagyja ki a rekordokat. Édes, ugye?

A teljesítmény magyarázata

A továbbtanuláshoz javaslom a EXPLAIN EXTENDED eredményeinek vizsgálatát a lekérdezés minden egyes változatánál, hogy 50 000 000 után a következő 10 000 rekordot kapjuk.

Fókuszáljunk a 2. és 3. megoldás végrehajtási tervei közötti legfontosabb különbségre, mivel az 1. megoldás nagy táblák esetén gyakorlatilag nem hasznos.

• Join type: index vs range. Az első azt jelenti, hogy a teljes indexfát beolvassuk a rekordok megtalálásához. A range típus azt mondja, hogy az indexet csak egy megadott tartományon belüli egyező sorok keresésére használják. Tehát a range típus gyorsabb, mint a index.
• Lehetséges kulcsok: NULL vs. PRIMARY. Az oszlop a MySQL által használható kulcsokat mutatja. BTW, ha megnézzük a kulcsok oszlopot, láthatjuk, hogy végül a PRIMARY kulcsot mindkét lekérdezéshez használjuk.
• sorok: 50 010 000 vs 50 000 000. Az érték az eredmény visszaadása előtt elemzett rekordok számát jeleníti meg. A 2. lekérdezésnél az érték attól függ, hogy milyen mély a görgetésünk. Például, ha a 9999. oldal után a következő 10 000 rekordot próbáljuk megszerezni, akkor 99 990 000 rekordot vizsgálunk meg. Ezzel szemben a 3. lekérdezésnek állandó értéke van; nem számít, hogy a legeslegutolsó oldal 1. oldalának adatait töltjük be. Ez mindig a táblázat méretének fele.
• Szűrt: 200.00 vs 100.00. Az oszlop azt jelzi, hogy becslések szerint a táblázat hány százalékát kell szűrni a feldolgozás előtt. A magasabb érték jobb. A 100.00 érték azt jelenti, hogy a lekérdezés az egész táblázatot átnézi. A 2. lekérdezés esetében az érték nem állandó, és az oldalszámtól függ: ha az 1. oldalt kérdezzük, a szűrt oszlop értéke 1000000.00 lesz. A legutolsó oldal esetében 100.00.
• Extra: NULL vs. Using where. További információkat ad arról, hogy a MySQL hogyan oldja fel a lekérdezést. A WHERE használata a PRIMARY kulcson gyorsabbá teszi a lekérdezés végrehajtását.

Gyanítom, hogy a join típusa a lekérdezés azon paramétere, amely a legnagyobb mértékben hozzájárult a teljesítményhez, hogy a 3. lekérdezés gyorsabb legyen. Egy másik fontos dolog, hogy a 2. lekérdezés rendkívül függ a lapozandó oldalak számától. A mélyebb lapozás ebben az esetben lassabb.

A EXPLAIN parancs kimenetének megértésével kapcsolatos további útmutatás az RDBMS hivatalos dokumentációjában található.

Összefoglaló

A blogbejegyzés fő témája egy nagy, 100 000 000 rekordot tartalmazó táblázat beolvasásához kapcsolódott a offset elsődleges kulccsal (keyset pagination). Összességében 3 különböző megközelítést tekintettünk át és teszteltünk a megfelelő adathalmazon. Csak az egyiket ajánlom, ha egy változékony nagy táblát kell beolvasni.

Az EXPLAIN EXTENDED parancs használatát is felülvizsgáltuk a MySQL-lekérdezések végrehajtási tervének elemzésére. Biztos vagyok benne, hogy más RDBMS-ek is rendelkeznek analóg funkcióval.

A következő fejezetben az adataggregációra és a tárolás optimalizálására fordítunk figyelmet. Maradjanak velünk!

Mi a módszere a nagy táblák átvizsgálására?

Emlékszik még valamilyen más célra a kulcskészlet oldalszámozásának használatára, mint a 3. megoldásnál?