Ùn aduprate micca OFFSET è LIMIT in e dumande paginate

Sò passati i ghjorni chì ùn avete micca da preoccupassi di ottimisà u rendiment di a basa di dati. U tempu ùn si ferma. Ogni novu imprenditore tecnulugicu vole creà u prossimu Facebook, mentre chì prova di cullà tutte e dati chì ponu mette in manu. L'imprese anu bisognu di sti dati per furmà megliu mudelli chì aiutanu à guadagnà soldi. In tali cundizioni, i programatori anu bisognu di creà API chì li permettenu di travaglià rapidamente è in modu affidabile cù quantità enormi d'infurmazioni.

Sè vo avete cuncepimentu di backends di applicazioni o di basa di dati per ogni tempu, avete probabilmente scrittu codice per eseguisce dumande paginate. Per esempiu, cusì:

SELECT * FROM table_name LIMIT 10 OFFSET 40

A manera hè?

Ma s'ellu hè cusì chì avete fattu a vostra paginazione, mi dispiace dì chì ùn avete micca fattu in u modu più efficau.

Vulete ughjettà à mè ? Poi ùn spende время. Slack, Shopify и Mixmax Sò digià aduprà e tecniche chì vogliu parlà oghje.

Nominate almenu un sviluppatore backend chì ùn hà mai usatu OFFSET и LIMIT per fà e dumande paginate. In MVP (Produttu Minimu Viable) è in i prughjetti chì utilizanu picculi quantità di dati, questu approcciu hè abbastanza applicabile. "Funziona solu", per dì cusì.

Ma s'ellu ci vole à creà sistemi affidabili è efficaci da zero, avete da piglià cura in anticipu di l'efficienza di a ricerca di e basa di dati utilizati in tali sistemi.

Oghje parlemu di i prublemi cù l'implementazioni cumunimenti usate (troppu male) di i mutori di ricerca paginati, è cumu per ottene un rendimentu altu quandu eseguisce tali dumande.

Chì ci hè sbagliatu cù OFFSET è LIMIT?

Cum'è digià dettu, OFFSET и LIMIT Eseguinu bè in prughjetti chì ùn anu micca bisognu di travaglià cù grandi quantità di dati.

U prublema nasce quandu a basa di dati cresce à una talla grandezza chì ùn si mette più in a memoria di u servitore. In ogni casu, quandu u travagliu cù sta basa di dati, avete bisognu di utilizà e dumande paginate.

Per chì stu prublema si manifesta, deve esse una situazione in quale u DBMS ricorre à una operazione inefficiente di Scansione di Table Full in ogni dumanda paginata (mentre l'operazione di inserimentu è di eliminazione pò accade, è ùn avemu micca bisognu di dati obsoleti!).

Cosa hè una "scansione di tavula completa" (o "scansione di tavule sequenziale", Scan Sequential)? Questa hè una operazione durante a quale u DBMS leghje in sequenza ogni fila di a tavula, vale à dì, i dati cuntenuti in questu, è verificate per u rispettu di una determinata cundizione. Stu tipu di scansione di tavulinu hè cunnisciutu per esse u più lento. U fattu hè chì quandu hè eseguitu, parechje operazioni di input / output sò realizati chì implicanu u subsistema di discu di u servitore. A situazione hè aggravata da i ritardi assuciati cù u travagliu cù e dati guardati nantu à i discu, è u fattu chì u trasferimentu di dati da u discu à a memoria hè una operazione intensiva di risorse.

Per esempiu, avete record di 100000000 d'utilizatori è eseguite una dumanda cù a custruzzione OFFSET 50000000. Questu significa chì u DBMS averà da carricà tutti issi registri (è ùn ne avemu mancu bisognu!), mette in memoria, è dopu piglià, per dì, 20 risultati rappurtati in LIMIT.

Diciamu chì puderia vede cusì: "select rows from 50000 to 50020 from 100000". Vale à dì, u sistema hà prima bisognu di carricà 50000 XNUMX fila per compie a dumanda. Avete vistu quantu travagliu innecessariu hà da fà ?

Se ùn mi crede micca, fate un ochju à l'esempiu chì aghju creatu cù e funziunalità db-fiddle.com. 

Esempiu à db-fiddle.com

Quì, à manca, in u campu Schema SQL, ci hè un codice chì inserisce 100000 XNUMX fila in a basa di dati, è à a diritta, in u campu Query SQL, duie dumande sò mostrate. U primu, lento, s'assumiglia cusì:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

È u sicondu, chì hè una suluzione efficace à u listessu prublema, hè cusì:

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

Per risponde à queste richieste, basta à cliccà nantu à u buttone Run in cima di a pagina. Dopu fattu questu, paragunemu l'infurmazioni nantu à u tempu di esecuzione di a dumanda. Risulta chì l'esecuzione di una dumanda inefficace dura almenu 30 volte più di l'esecuzione di a seconda (questa volta varieghja da run à run; per esempiu, u sistema pò informà chì a prima dumanda hà pigliatu 37 ms per compie, ma l'esekzione di u seconda - 1 ms).

È s'ellu ci hè più dati, allura tuttu sarà ancu peggiu (per esse cunvinta di questu, fate un ochju à u mo esempiu cù 10 milioni di file).

Ciò chì avemu appena discututu duverebbe dà un pocu insight in quantu e dumande di basa di dati sò in realtà trattate.

Per piacè nutate chì u più altu u valore OFFSET - u più longu sarà a dumanda à compie.

Chì duverebbe aduprà invece di a cumminazzioni di OFFSET è LIMIT?

Invece di una cumminazione OFFSET и LIMIT Hè vale a pena aduprà una struttura custruita secondu u schema seguente:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Questu hè l'esekzione di a dumanda cù a paginazione basata nantu à u cursore.

Invece di almacenà i currenti in u locu OFFSET и LIMIT è trasmette cù ogni dumanda, avete bisognu di almacenà l'ultima chjave primaria ricevuta (di solitu questu hè ID) è LIMIT, in u risultatu, e dumande simili à quì sopra saranu ottenute.

Perchè? U puntu hè chì spicificendu esplicitamente l'identificatore di l'ultima fila lettu, dite à u vostru DBMS induve deve principià a ricerca di e dati necessarii. Inoltre, a ricerca, grazia à l'usu di a chjave, serà realizatu in modu efficiente, u sistema ùn deve esse distractatu da e linee fora di a gamma specifica.

Fighjemu un ochju à u seguente paragone di prestazioni di diverse dumande. Eccu una dumanda inefficace.

Richiesta lenta

È quì hè una versione ottimizzata di sta dumanda.

Richiesta rapida

E duie dumande tornanu esattamente a stessa quantità di dati. Ma u primu pigghia 12,80 seconde per compie, è u sicondu pigghia 0,01 seconde. Sentu a diffarenza ?

I prublemi

Per u metudu di dumanda pruposta per travaglià in modu efficace, a tavula deve avè una colonna (o culonni) chì cuntene indici unichi, sequenziali, cum'è un identificatore integer. In certi casi specifichi, questu pò determinà u successu di utilizà tali dumande per aumentà a velocità di travaglià cù a basa di dati.

Naturalmente, quandu custruite e dumande, avete bisognu di piglià in contu l'architettura specifica di e tavule è sceglite quelli meccanismi chì funzionanu megliu nantu à e tavule esistenti. Per esempiu, sè avete bisognu di travaglià in dumande cù grandi volumi di dati rilativi, pudete truvà interessanti questu articulu.

Sè avemu affruntatu cù u prublema di manca una chjave primaria, per esempiu, s'ellu avemu un tavulinu cù una rilazioni assai à parechji, allura l'approcciu tradiziunale di usu OFFSET и LIMIT, hè garantitu per cunvene à noi. Ma u so usu pò risultà in dumande potenzialmente lente. In casi cum'è questu, ricumanderaghju di utilizà una chjave primaria auto-incrementante, ancu s'ellu avete bisognu solu per trattà e dumande paginate.

Sè site interessatu in questu tema - eccu, eccu и eccu - parechji materiali utili.

Risultati

A cunclusione principale chì pudemu tirà hè chì, ùn importa di quale dimensione di basa di dati parlemu, hè sempre necessariu analizà a rapidità di l'esekzione di a dumanda. Oghje, a scalabilità di suluzioni hè assai impurtante, è se tuttu hè cuncepitu currettamente da u principiu di travaglià in un certu sistema, questu, in u futuru, pò salvà u sviluppatore da parechji prublemi.

Cumu analizà è ottimisate e dumande di basa di dati?

Source: www.habr.com