Sunt un mare fan al bazelor de date. Am vrut chiar să-mi fac propriul SGBD când eram la universitate. Acum lucrez atât cu RDBMS, cât și cu soluții NoSQL și sunt foarte entuziasmat de acest lucru. Știți, nu există un ciocan de aur, fiecare problemă are propria soluție. Alternativ, un subset de soluții.
În seria de articole de pe blog The SQL I Love <3 vă prezint câteva probleme rezolvate cu SQL care mi s-au părut deosebit de interesante. Soluțiile sunt testate folosind un tabel cu peste 100 de milioane de înregistrări. Toate exemplele folosesc MySQL, dar ideile se aplică și la alte magazine de date relaționale, cum ar fi PostgreSQL, Oracle și SQL Server.
Acest capitol se concentrează pe scanarea eficientă a unui tabel mare folosind paginarea cu offset
pe cheia primară. Aceasta este, de asemenea, cunoscută sub numele de paginare cu set de chei.
Background
În acest capitol, folosim ca exemplu următoarea structură a bazei de date. Exemplul canonic despre utilizatori ar trebui să se potrivească oricărui domeniu.
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;
Câteva comentarii despre structură:
-
external_id
coloana stochează referința la același utilizator din alt sistem în format UUID -
name
reprezintăFirstname Lastname
-
metadata
coloana conține blob JSON cu tot felul de date nestructurate
Tabela este relativ mare și conține aproximativ 100 000 000 de înregistrări. Să începem călătoria noastră de învățare.
Scanarea unui tabel mare
Problemă: Trebuie să parcurgeți tabelul, să extrageți fiecare înregistrare, să o transformați în interiorul codului aplicației dvs. și să o inserați în alt loc. Ne concentrăm pe prima etapă în acest post – scanarea tabelului.
Soluție evidentă și greșită
SELECT user_id, external_id, name, metadata, date_createdFROM users;
În cazul meu cu 100 000 000 de înregistrări, interogarea nu se termină niciodată. SGBD-ul o ucide pur și simplu. De ce? Probabil, pentru că a dus la încercarea de a încărca întregul tabel în RAM. Înainte de a returna datele către client. O altă ipoteză – a durat prea mult timp pentru a preîncărca datele înainte de a le trimite și interogarea a fost cronometrată. Oricum, încercarea noastră de a obține toate înregistrările la timp a eșuat. Trebuie să găsim o altă soluție.
Soluția #2
Potem încerca să obținem datele în pagini. Deoarece înregistrările nu sunt garantate a fi ordonate într-o tabelă la nivel fizic sau logic – trebuie să le sortăm pe partea SGBD cu clauza ORDER BY
.
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)
Dulce. A funcționat. Am cerut prima pagină de 10 000 de înregistrări și a durat doar 0.03
sec. pentru a o returna. Cu toate acestea, cum ar funcționa pentru a 5000-a pagină?
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)
Într-adevăr, acest lucru este foarte lent. Să vedem cât timp este necesar pentru a obține datele pentru ultima pagină.
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)
Este o nebunie. Cu toate acestea, poate fi OK pentru soluții care rulează în fundal. Încă o problemă ascunsă a acestei abordări poate fi dezvăluită dacă încercați să ștergeți o înregistrare din tabel în mijlocul scanării acestuia. Să zicem că ați terminat a 10-a pagină (100 000 de înregistrări sunt deja vizitate), urmând să scanați înregistrările cuprinse între 100 001 și 110 000. Dar înregistrările 99 998 și 99 999 sunt șterse înainte de următoarea execuție SELECT
. În acest caz, următoarea interogare returnează rezultatul neașteptat:
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, ...
După cum puteți vedea, interogarea a sărit înregistrările cu id-urile 100 001 și 100 002. Acestea nu vor fi procesate de codul aplicației cu această abordare deoarece, după cele două operații de ștergere, apar în primele 100 000 de înregistrări. Prin urmare, metoda nu este fiabilă dacă setul de date este mutabil.
Soluția #3 – ultima pentru astăzi
Abordarea este foarte asemănătoare cu cea precedentă, deoarece folosește în continuare paginarea, dar acum, în loc să ne bazăm pe numărul de înregistrări scanate, folosim ca offset
user_id
al ultimei înregistrări vizitate.
Algoritm simplificat:
- Obținem
PAGE_SIZE
numărul de înregistrări din tabel. Valoarea inițială a decalajului este 0. - Utilizați valoarea maximă returnată pentru
user_id
în lot ca decalaj pentru pagina următoare. - Obțineți următorul lot din înregistrările care au valoarea
user_id
mai mare decâtoffset
curentă.
Interogarea în acțiune pentru a 5 000-a pagină, fiecare pagină conține date despre 10 000 de utilizatori:
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)
Rețineți că valorile lui user_id
nu sunt secvențiale și pot avea goluri, cum ar fi 25 348 este imediat după 25 345. Soluția funcționează și în cazul în care sunt șterse înregistrări din paginile viitoare – chiar și în acest caz, interogarea nu sare peste înregistrări. Drăguț, nu-i așa?
Explicarea performanțelor
Pentru mai multă învățare, vă recomand să investigați rezultatele lui EXPLAIN EXTENDED
pentru fiecare versiune a interogării pentru a obține următoarele 10 000 de înregistrări după 50 000 000.
Solution | Time | Type | Keys | Rows | Filtered | Extra |
---|---|---|---|---|---|---|
1. Evident | Niciodată | TOATE | NULL | 100M | 100.00 | NULL |
2. Paginare folosind numărul de înregistrări ca offset | 40.81 sec | index | NULL / PRIMARY | 50M | 200.00 | NULL |
3. Paginarea setului de chei utilizând user_id ca offset | 0,03 sec | range | PRIMARY / PRIMARY | 50M | 100.00 | Utilizarea where |
Să ne concentrăm asupra diferenței cheie dintre planurile de execuție pentru a 2-a și a 3-a soluție, deoarece prima nu este practic utilă pentru tabelele mari.
- Join type:
index
vsrange
. Prima soluție înseamnă că întregul arbore de indexare este scanat pentru a găsi înregistrările. Tipulrange
ne spune că indicele este utilizat doar pentru a găsi rândurile corespunzătoare într-un interval specificat. Așadar, tipulrange
este mai rapid decâtindex
. - Chei posibile:
NULL
vsPRIMARY
. Coloana arată cheile care pot fi utilizate de MySQL. BTW, dacă ne uităm în coloana chei, putem vedea că, în cele din urmă, cheiaPRIMARY
este utilizată pentru ambele interogări. - Rânduri:
50 010 000
vs50 000 000
. Valoarea afișează un număr de înregistrări analizate înainte de a returna rezultatul. Pentru cea de-a 2-a interogare, valoarea depinde de cât de adânc este scroll-ul nostru. De exemplu, dacă încercăm să obținem următoarele10 000
înregistrări după pagina 9999, atunci sunt analizate99 990 000
înregistrări. Dimpotrivă, cea de-a 3-a interogare are o valoare constantă; nu contează dacă încărcăm date pentru prima sau ultima pagină. Aceasta este întotdeauna jumătate din dimensiunea tabelului. - Filtrat:
200.00
vs100.00
. Coloana indică procentul estimat al tabelului care trebuie filtrat înainte de procesare. Având valoarea cea mai mare este mai bună. Valoarea100.00
înseamnă că interogarea verifică întregul tabel. Pentru cea de-a doua interogare, valoarea nu este constantă și depinde de numărul paginii: dacă solicităm prima pagină, valoarea coloanei filtrate ar fi1000000.00
. Pentru ultima pagină, aceasta ar fi100.00
. - Extra:
NULL
vsUsing where
. Oferă informații suplimentare despre modul în care MySQL rezolvă interogarea. UtilizareaWHERE
pe cheiaPRIMARY
face ca execuția interogării să fie mai rapidă.
Suspectez că tipul de îmbinare este parametrul din interogare care a avut cea mai mare contribuție la performanță pentru a face cea de-a 3-a interogare mai rapidă. Un alt lucru important este că cea de-a 2-a interogare este extrem de dependentă de numărul de pagini de derulat. O paginare mai profundă este mai lentă în acest caz.
Mai multe îndrumări despre înțelegerea ieșirii pentru comanda EXPLAIN
pot fi găsite în documentația oficială pentru RDBMS-ul dumneavoastră.
Rezumat
Subiectul principal pentru postarea de pe blog a fost legat de scanarea unui tabel mare cu 100 000 000 de înregistrări folosind offset
cu o cheie primară (paginare keyset). În general, au fost analizate și testate 3 abordări diferite pe setul de date corespunzător. Vă recomand doar una dintre ele dacă aveți nevoie să scanați un tabel mare mutabil.
De asemenea, am revizuit utilizarea comenzii EXPLAIN EXTENDED
pentru a analiza planul de execuție al interogărilor MySQL. Sunt sigur că și alte RDBMS au analogi pentru această funcționalitate.
În capitolul următor, vom acorda atenție agregării datelor și optimizării stocării. Rămâneți cu noi!
Care este metoda dvs. de scanare a tabelelor mari?
Vă amintiți vreun alt scop al utilizării paginării keyset ca în soluția #3?
.