Enrere han quedat els dies en què no us haureu de preocupar per optimitzar el rendiment de la base de dades. El temps no s'atura. Tots els nous empresaris tecnològics volen crear el següent Facebook, mentre intenta recollir totes les dades que puguin tenir a les seves mans. Les empreses necessiten aquestes dades per formar millor models que els ajudin a guanyar diners. En aquestes condicions, els programadors han de crear API que els permetin treballar de manera ràpida i fiable amb grans quantitats d'informació.
Si heu estat dissenyant backends d'aplicacions o bases de dades durant un temps, probablement heu escrit codi per executar consultes paginades. Per exemple, així:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Com és?
Però si així has fet la teva paginació, em sap greu dir-te que no ho has fet de la manera més eficient.
Voleu oposar-me? . , и Ja estan utilitzant les tècniques de les quals vull parlar avui.
Anomena almenys un desenvolupador de backend que mai no hagi utilitzat OFFSET и LIMIT per realitzar consultes paginades. En MVP (Producte Mínim Viable) i en projectes on s'utilitzen petites quantitats de dades, aquest enfocament és bastant aplicable. "Només funciona", per dir-ho d'alguna manera.
Però si necessiteu crear sistemes fiables i eficients des de zero, hauríeu de tenir cura per endavant de l'eficiència de consultar les bases de dades utilitzades en aquests sistemes.
Avui parlarem dels problemes amb les implementacions d'ús habitual (malament) dels motors de consultes paginades i de com aconseguir un alt rendiment en executar aquestes consultes.
Què passa amb OFFSET i LIMIT?
Com ja s’ha dit, OFFSET и LIMIT Tenen un bon rendiment en projectes que no necessiten treballar amb grans quantitats de dades.
El problema sorgeix quan la base de dades creix fins a tal mida que ja no caben a la memòria del servidor. Tanmateix, quan treballeu amb aquesta base de dades, heu d'utilitzar consultes paginades.
Perquè aquest problema es manifesti, hi ha d'haver una situació en què el SGBD recorre a una operació d'exploració de taula completa ineficient a cada consulta paginada (mentre que es poden produir operacions d'inserció i supressió, i no necessitem dades obsoletes!).
Què és un "escaneig de taula completa" (o "exploració de taula seqüencial", exploració seqüencial)? Es tracta d'una operació durant la qual el SGBD llegeix seqüencialment cada fila de la taula, és a dir, les dades que hi conté, i comprova que compleixen una condició determinada. Se sap que aquest tipus d'exploració de taules és la més lenta. El cas és que quan s'executa, es realitzen moltes operacions d'entrada/sortida que impliquen el subsistema de disc del servidor. La situació empitjora per la latència associada al treball amb dades emmagatzemades en discs i pel fet que la transferència de dades del disc a la memòria és una operació que requereix molts recursos.
Per exemple, teniu registres de 100000000 d'usuaris i executeu una consulta amb la construcció OFFSET 50000000. Això vol dir que el SGBD haurà de carregar tots aquests registres (i ni tan sols els necessitem!), posar-los a la memòria i després agafar, per exemple, 20 resultats reportats a LIMIT.
Suposem que podria semblar així: "seleccioneu files de 50000 a 50020 de 100000". És a dir, primer haurà de carregar 50000 files per completar la consulta. Veus quanta feina innecessària haurà de fer?
Si no em creieu, mireu l'exemple que vaig crear amb les funcions .
Exemple a db-fiddle.com
Allà, a l'esquerra, al camp Schema SQL, hi ha un codi que insereix 100000 files a la base de dades i, a la dreta, al camp Query SQL, es mostren dues consultes. El primer, lent, té aquest aspecte:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
I el segon, que és una solució efectiva al mateix problema, és així:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Per atendre aquestes sol·licituds, només cal que feu clic al botó Run a la part superior de la pàgina. Fet això, comparem informació sobre el temps d'execució de la consulta. Resulta que executar una consulta ineficaç triga almenys 30 vegades més que executar la segona (aquest temps varia d'execució a execució; per exemple, el sistema pot informar que la primera consulta va trigar 37 ms a completar-se, però l'execució de la segon - 1 ms).
I si hi ha més dades, tot semblarà encara pitjor (per estar convençut d'això, mireu el meu amb 10 milions de files).
El que acabem de parlar hauria de donar-vos una visió de com es processen realment les consultes de la base de dades.
Tingueu en compte que com més gran sigui el valor OFFSET — més temps trigarà a completar-se la sol·licitud.
Què he d'utilitzar en lloc de la combinació de OFFSET i LIMIT?
En lloc d'una combinació OFFSET и LIMIT Val la pena utilitzar una estructura construïda segons el següent esquema:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Aquesta és l'execució de consultes amb paginació basada en el cursor.
En lloc d'emmagatzemar els actuals localment OFFSET и LIMIT i transmetre'ls amb cada sol·licitud, cal emmagatzemar la darrera clau primària rebuda (normalment això és ID) I LIMIT, com a resultat, s'obtindran consultes semblants a les anteriors.
Per què? La qüestió és que especificant explícitament l'identificador de l'última fila llegida, dieu al vostre DBMS on ha de començar a cercar les dades necessàries. A més, la cerca, gràcies a l'ús de la clau, es durà a terme de manera eficient; el sistema no s'haurà de distreure per línies fora de l'interval especificat.
Fem una ullada a la següent comparació de rendiment de diverses consultes. Aquí hi ha una consulta ineficaç.
Petició lenta
I aquí teniu una versió optimitzada d'aquesta sol·licitud.
Petició ràpida
Les dues consultes retornen exactament la mateixa quantitat de dades. Però el primer triga 12,80 segons a completar-se i el segon triga 0,01 segons. Sents la diferència?
Possibles problemes
Perquè el mètode de consulta proposat funcioni de manera efectiva, la taula ha de tenir una columna (o columnes) que continguin índexs seqüencials únics, com ara un identificador d'enter. En alguns casos específics, això pot determinar l'èxit d'utilitzar aquestes consultes per augmentar la velocitat de treball amb la base de dades.
Naturalment, a l'hora de construir consultes, cal tenir en compte l'arquitectura específica de les taules i triar aquells mecanismes que funcionin millor a les taules existents. Per exemple, si necessiteu treballar en consultes amb grans volums de dades relacionades, és possible que us resulti interessant article.
Si ens trobem davant del problema de perdre una clau primària, per exemple, si tenim una taula amb una relació de molts a molts, aleshores l'enfocament tradicional d'utilitzar OFFSET и LIMIT, està garantit per a nosaltres. Però el seu ús pot donar lloc a consultes potencialment lentes. En aquests casos, recomanaria utilitzar una clau primària d'increment automàtic, fins i tot si només es necessita per gestionar consultes paginades.
Si esteu interessats en aquest tema - , и - Diversos materials útils.
Resultats de
La principal conclusió que podem extreure és que, independentment de la mida de les bases de dades de què parlem, sempre cal analitzar la velocitat d'execució de la consulta. Avui en dia, l'escalabilitat de les solucions és extremadament important, i si tot està dissenyat correctament des del principi de treballar en un sistema determinat, això, en el futur, pot salvar el desenvolupador de molts problemes.
Com analitzeu i optimitzeu les consultes de bases de dades?
Font: www.habr.com