Hola a tots! Sóc un desenvolupador de backend que escriu microserveis a Java + Spring. Treballo en un dels equips interns de desenvolupament de productes de Tinkoff.
Al nostre equip, sovint sorgeix la qüestió d'optimitzar les consultes en un SGBD. Sempre voleu ser una mica més ràpid, però no sempre us podeu sortir amb índexs ben construïts; heu de buscar algunes solucions. Durant una d'aquestes passejades per la web a la recerca d'optimitzacions raonables a l'hora de treballar amb bases de dades, vaig trobar , autor de SQL Performance Explained. Aquest és aquest tipus de bloc rar en què podeu llegir tots els articles seguits.
M'agradaria traduir-vos un breu article de Marcus. Es pot anomenar fins a cert punt un manifest que pretén cridar l'atenció sobre l'antic, però encara rellevant problema del rendiment de l'operació offset segons l'estàndard SQL.
En alguns llocs complementaré l'autor amb explicacions i comentaris. Em referiré a tots aquests llocs com "aprox". per a més claredat
Petita introducció
Crec que molta gent sap com de problemàtic i lent és treballar amb seleccions de pàgines mitjançant offset. Sabíeu que es pot substituir fàcilment per un disseny més eficient?
Per tant, la paraula clau offset indica a la base de dades que omet els primers n registres de la sol·licitud. Tanmateix, la base de dades encara necessita llegir aquests primers n registres del disc, en l'ordre donat (nota: apliqueu l'ordenació si s'especifica), i només llavors serà possible retornar registres a partir de n+1. El més interessant és que el problema no està en la implementació específica al SGBD, sinó en la definició original segons l'estàndard:
… les files s'ordenen primer segons la i després es limiten deixant anar el nombre de files especificat a la des del principi...
-SQL:2016, Part 2, 4.15.3 Taules derivades (nota: actualment l'estàndard més utilitzat)
El punt clau aquí és que el desplaçament pren un únic paràmetre: el nombre de registres a saltar, i ja està. Seguint aquesta definició, el SGBD només pot recuperar tots els registres i després descartar els innecessaris. Evidentment, aquesta definició de compensació ens obliga a fer un treball addicional. I ni tan sols importa si és SQL o NoSQL.
Només una mica més de dolor
Els problemes amb l'offset no acaben aquí, i aquí teniu el perquè. Si, entre la lectura de dues pàgines de dades del disc, una altra operació insereix un nou registre, què passarà en aquest cas?
Quan l'offset s'utilitza per saltar registres de pàgines anteriors, en la situació d'afegir un nou registre entre lectures de diferents pàgines, el més probable és que obtingueu duplicats (nota: això és possible quan llegim pàgina per pàgina utilitzant l'ordre per construcció, llavors al mig de la nostra sortida pot obtenir una nova entrada).
La figura mostra clarament aquesta situació. La base llegeix els primers 10 registres, després del qual s'insereix un registre nou, que compensa tots els registres llegits en 1. Aleshores, la base agafa una pàgina nova dels 10 registres següents i comença no a partir de l'11, com hauria de ser, sinó des del 10è, duplicant aquest registre. Hi ha altres anomalies associades a l'ús d'aquesta expressió, però aquesta és la més freqüent.
Com ja hem esbrinat, no es tracta de problemes d'un SGBD específic ni de les seves implementacions. El problema és definir la paginació segons l'estàndard SQL. Diguem al SGBD quina pàgina ha d'obtenir o quants registres ha de saltar. La base de dades simplement no és capaç d'optimitzar aquesta sol·licitud, ja que hi ha massa poca informació per a això.
També val la pena aclarir que això no és un problema amb una paraula clau concreta, sinó més aviat amb la semàntica de la consulta. Hi ha diverses sintaxis més que són idèntiques en la seva naturalesa problemàtica:
- La paraula clau de compensació és com s'ha esmentat anteriorment.
- Una construcció de dues paraules clau limit [offset] (encara que el límit en si no és tan dolent).
- Filtrat per límits inferiors, basat en la numeració de files (per exemple, row_number(), rownum, etc.).
Totes aquestes expressions simplement us indiquen quantes línies heu de saltar, sense informació addicional ni context.
Més endavant en aquest article, la paraula clau offset s'utilitza com a resum de totes aquestes opcions.
Vida sense OFFSET
Ara imaginem com seria el nostre món sense tots aquests problemes. Resulta que la vida sense compensació no és tan difícil: amb una selecció, podeu seleccionar només aquelles files que encara no hem vist (nota: és a dir, les que no estaven a la pàgina anterior), utilitzant una condició en on.
En aquest cas, partim del fet que les seleccions s'executen en un conjunt ordenat (bon antic ordre per). Com que tenim un conjunt ordenat, podem utilitzar un filtre bastant senzill per obtenir només les dades que hi ha darrere de l'últim registre de la pàgina anterior:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYAquest és tot el principi d'aquest enfocament. Per descomptat, les coses es fan més divertides en ordenar per moltes columnes, però la idea segueix sent la mateixa. És important tenir en compte que aquest disseny és aplicable a molts - decisions.
Aquest enfocament s'anomena mètode de cerca o paginació del conjunt de claus. Soluciona el problema del resultat flotant (nota: la situació de l'escriptura entre les lectures de pàgines, descrita anteriorment) i, per descomptat, el que a tots ens agrada, funciona més ràpid i més estable que el clàssic offset. L'estabilitat rau en el fet que el temps de processament de la sol·licitud no augmenta en proporció al nombre de la taula sol·licitada (nota: si voleu obtenir més informació sobre el treball dels diferents enfocaments de la paginació, podeu . També hi podeu trobar punts de referència comparatius per a diferents mètodes).
Una de les diapositives que la paginació per claus, és clar, no és omnipotent: té les seves limitacions. El més significatiu és que no té la capacitat de llegir pàgines aleatòries (nota: de manera inconsistent). Tanmateix, a l'era del desplaçament sense fi (nota: a la part frontal), això no és un problema. Especificar un número de pàgina per fer clic és una mala decisió en el disseny de la interfície d'usuari de totes maneres (nota: opinió de l'autor de l'article).
Què passa amb les eines?
La paginació a les tecles sovint no és adequada a causa de la manca de suport instrumental per a aquest mètode. La majoria de les eines de desenvolupament, inclosos diversos marcs, no us permeten triar exactament com es realitzarà la paginació.
La situació s'agreuja pel fet que el mètode descrit requereix suport d'extrem a extrem en les tecnologies utilitzades, des del SGBD fins a l'execució d'una sol·licitud AJAX al navegador amb un desplaçament sense fi. En lloc d'especificar només el número de pàgina, ara heu d'especificar un conjunt de claus per a totes les pàgines alhora.
No obstant això, el nombre de frameworks que admeten la paginació a les claus està creixent gradualment. Aquí teniu el que tenim actualment:
- per a Java;
- per Rubí;
- и per a Django;
- per a Python;
- — API de criteris per a les implementacions de JPA;
- per a Perl;
- , mapeador per a Node.js .
(Nota: es van eliminar alguns enllaços perquè en el moment de la traducció algunes biblioteques no s'havien actualitzat des del 2017-2018. Si esteu interessats, podeu consultar la font original.)
És en aquest moment que es necessita la teva ajuda. Si desenvolupeu o doneu suport a un marc que fa ús de la paginació, us demano, us demano, us imploro que proporcioneu suport natiu per a la paginació a les claus. Si tens preguntes o necessites ajuda, estaré encantat d'ajudar-te (, , ) (nota: des de la meva experiència amb Marcus, puc dir que està molt entusiasmat a l'hora de difondre aquest tema).
Si utilitzeu solucions ja fetes que creieu que mereixen tenir suport per a la paginació per claus, creeu una sol·licitud o fins i tot ofereix una solució ja feta, si és possible. També podeu enllaçar a aquest article.
Conclusió
La raó per la qual un enfocament tan senzill i útil com la paginació per claus no està generalitzat no és perquè sigui difícil d'implementar tècnicament o requereixi un gran esforç. La raó principal és que molts estan acostumats a veure i treballar amb offset; aquest enfocament està dictat per la pròpia norma.
Com a resultat, poca gent pensa en canviar l'enfocament de la paginació i, per això, el suport instrumental dels frameworks i biblioteques s'està desenvolupant malament. Per tant, si la idea i l'objectiu de la paginació sense offset us són a prop, ajudeu a difondre-la!
Font:
Autor: Markus Winand
Font: www.habr.com