AllYouNeedIsBackend

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:

1. Obținem PAGE_SIZE numărul de înregistrări din tabel. Valoarea inițială a decalajului este 0.
2. Utilizați valoarea maximă returnată pentru user_id în lot ca decalaj pentru pagina următoare.
3. Obțineți următorul lot din înregistrările care au valoarea user_id mai mare decât offset 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.

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 vs range. Prima soluție înseamnă că întregul arbore de indexare este scanat pentru a găsi înregistrările. Tipul range ne spune că indicele este utilizat doar pentru a găsi rândurile corespunzătoare într-un interval specificat. Așadar, tipul range este mai rapid decât index.
• Chei posibile: NULL vs PRIMARY. Coloana arată cheile care pot fi utilizate de MySQL. BTW, dacă ne uităm în coloana chei, putem vedea că, în cele din urmă, cheia PRIMARY este utilizată pentru ambele interogări.
• Rânduri: 50 010 000 vs 50 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ătoarele 10 000 înregistrări după pagina 9999, atunci sunt analizate 99 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 vs 100.00. Coloana indică procentul estimat al tabelului care trebuie filtrat înainte de procesare. Având valoarea cea mai mare este mai bună. Valoarea 100.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 fi 1000000.00. Pentru ultima pagină, aceasta ar fi 100.00.
• Extra: NULL vs Using where. Oferă informații suplimentare despre modul în care MySQL rezolvă interogarea. Utilizarea WHERE pe cheia PRIMARY 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?

.