Ola a todos! Son un programador de backend que escribe microservizos en Java + Spring. Traballo nun dos equipos internos de desenvolvemento de produtos de Tinkoff.
No noso equipo, a miúdo xorde a cuestión de optimizar as consultas nun DBMS. Sempre queres ser un pouco máis rápido, pero non sempre podes saír adiante con índices ben construídos; tes que buscar algunhas solucións. Durante un destes paseos pola web en busca de optimizacións razoables ao traballar con bases de datos, atopei , autor de SQL Performance Explained. Este é ese raro tipo de blog no que podes ler todos os artigos seguidos.
Gustaríame traducirche un pequeno artigo de Marcus. Pódese chamar en certa medida un manifesto que busca chamar a atención sobre o problema vello, pero aínda relevante, da realización da operación de compensación segundo o estándar SQL.
Nalgúns lugares complementarei ao autor con explicacións e comentarios. Referireime a todos os lugares como "aprox". para máis claridade
Pequena introdución
Creo que moitas persoas saben o problemático e lento que é traballar coas seleccións de páxinas a través da compensación. Sabías que se pode substituír con bastante facilidade por un deseño máis eficiente?
Entón, a palabra clave offset indica á base de datos que omita os primeiros n rexistros da solicitude. Non obstante, a base de datos aínda precisa ler estes primeiros n rexistros do disco, na orde indicada (nota: aplique a ordenación se se especifica), e só entón será posible devolver rexistros de n+1 en diante. O máis interesante é que o problema non está na implementación específica no DBMS, senón na definición orixinal segundo o estándar:
...as filas ordénanse primeiro segundo a e despois limitanse eliminando o número de filas especificado na desde o principio...
-SQL:2016, Parte 2, 4.15.3 Táboas derivadas (nota: actualmente o estándar máis utilizado)
O punto clave aquí é que a compensación toma un só parámetro: o número de rexistros que se deben omitir, e iso é todo. Seguindo esta definición, o DBMS só pode recuperar todos os rexistros e despois descartar os innecesarios. Obviamente, esta definición de compensación obríganos a facer un traballo extra. E nin sequera importa se é SQL ou NoSQL.
Só un pouco máis de dor
Os problemas coa compensación non rematan aí, e aquí tes por que. Se, entre a lectura de dúas páxinas de datos do disco, outra operación insire un novo rexistro, que ocorrerá neste caso?
Cando se usa o offset para saltar rexistros de páxinas anteriores, na situación de engadir un novo rexistro entre lecturas de páxinas diferentes, o máis probable é que obteña duplicados (nota: isto é posible cando lemos páxina por páxina usando a orde por construción, entón no medio da nosa saída pode obter unha nova entrada).
A figura representa claramente esta situación. A base le os primeiros 10 rexistros, despois de que se insire un novo rexistro, que compensa todos os rexistros lidos en 1. A continuación, a base toma unha nova páxina dos seguintes 10 rexistros e non comeza a partir do 11, como debería, senón do 10o, duplicando este rexistro. Existen outras anomalías asociadas ao uso desta expresión, pero esta é a máis común.
Como xa descubrimos, estes non son problemas cun DBMS específico ou as súas implementacións. O problema está en definir a paxinación segundo o estándar SQL. Dicímoslle ao DBMS que páxina buscar ou cantos rexistros debe omitir. A base de datos simplemente non é capaz de optimizar tal solicitude, xa que hai moi pouca información para iso.
Tamén convén aclarar que non se trata dun problema cunha palabra clave específica, senón coa semántica da consulta. Hai varias sintaxes máis que son idénticas na súa natureza problemática:
- A palabra clave offset é como se mencionou anteriormente.
- Unha construción de dúas palabras clave limit [offset] (aínda que o límite en si non é tan malo).
- Filtrado por límites inferiores, en función da numeración das filas (por exemplo, row_number(), rownum, etc.).
Todas estas expresións simplemente indican cantas liñas debe saltar, sen información nin contexto adicional.
Máis adiante neste artigo, a palabra clave offset úsase como resumo de todas estas opcións.
Vida sen OFFSET
Agora imaxinemos como sería o noso mundo sen todos estes problemas. Acontece que a vida sen compensación non é tan difícil: cunha selección só pode seleccionar aquelas filas que aínda non vimos (nota: é dicir, as que non estaban na páxina anterior), utilizando unha condición en onde.
Neste caso, partimos do feito de que as seleccións execútanse nun conxunto ordenado (boa orde antiga por). Xa que temos un conxunto ordenado, podemos usar un filtro bastante sinxelo para obter só os datos que hai detrás do último rexistro da páxina anterior:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYEse é todo o principio deste enfoque. Por suposto, as cousas son máis divertidas cando se ordenan por moitas columnas, pero a idea segue sendo a mesma. É importante ter en conta que este deseño é aplicable a moitos -decisións.
Este enfoque chámase método de busca ou paxinación do conxunto de claves. Resolve o problema do resultado flotante (nota: a situación coa escritura entre lecturas de páxina descrita anteriormente) e, por suposto, o que a todos nos gusta, funciona máis rápido e máis estable que o offset clásico. A estabilidade reside no feito de que o tempo de procesamento de solicitudes non aumenta en proporción ao número da táboa solicitada (nota: se quere saber máis sobre o traballo dos diferentes enfoques da paxinación, pode . Tamén podes atopar puntos de referencia comparativos para diferentes métodos alí).
Unha das diapositivas que a paxinación por teclas, por suposto, non é omnipotente, ten as súas limitacións. O máis significativo é que non ten a capacidade de ler páxinas aleatorias (nota: de forma inconsistente). Non obstante, na era do desprazamento sen fin (nota: na parte frontal), este non é un problema. De todos os xeitos, especificar un número de páxina para facer clic é unha mala decisión no deseño da IU (nota: opinión do autor do artigo).
E as ferramentas?
A paxinación nas teclas moitas veces non é adecuada debido á falta de soporte instrumental para este método. A maioría das ferramentas de desenvolvemento, incluíndo varios frameworks, non permiten escoller exactamente como se realizará a paxinación.
A situación vese agravada polo feito de que o método descrito require soporte de extremo a extremo nas tecnoloxías utilizadas, desde o DBMS ata a execución dunha solicitude AJAX no navegador con desprazamento infinito. En lugar de especificar só o número de páxina, agora tes que especificar un conxunto de claves para todas as páxinas á vez.
Non obstante, o número de marcos que admiten a paxinación en claves está crecendo gradualmente. Velaquí o que temos neste momento:
- para Java;
- para Ruby;
- и para Django;
- para Python;
- — API de criterios para implementacións de JPA;
- para Perl;
- , mapeador para Node.js .
(Nota: elimináronse algunhas ligazóns debido a que no momento da tradución algunhas bibliotecas non estaban actualizadas desde 2017-2018. Se estás interesado, podes consultar a fonte orixinal).
É neste momento no que se necesita a túa axuda. Se desenvolves ou admites un marco que faga algún uso da paxinación, pídoche, insto, imploro que proporciones soporte nativo para a paxinación en claves. Se tes preguntas ou necesitas axuda, estarei encantado de axudar (, , ) (nota: pola miña experiencia con Marcus, podo dicir que está moi entusiasmado coa difusión deste tema).
Se utilizas solucións preparadas que cres que merecen ter soporte para a paxinación por teclas, crea unha solicitude ou incluso ofrece unha solución preparada, se é posible. Tamén podes enlazar a este artigo.
Conclusión
A razón pola que un enfoque tan sinxelo e útil como a paxinación por claves non está moi estendido non é porque sexa difícil de implementar tecnicamente ou requira un gran esforzo. A razón principal é que moitos están afeitos a ver e traballar con offset; este enfoque está ditado polo propio estándar.
Como resultado, poucas persoas pensan en cambiar o enfoque da paxinación e, por iso, o apoio instrumental de frameworks e bibliotecas está a desenvolverse mal. Polo tanto, se a idea e o obxectivo da paxinación sen compensación está preto de ti, axuda a difundilo!
Fonte:
Autor: Markus Winand
Fonte: www.habr.com