Atrás quedaron os días nos que non tiñas que preocuparte por optimizar o rendemento da base de datos. O tempo non se para. Todos os novos emprendedores tecnolóxicos queren crear o seguinte Facebook, mentres intentan recoller todos os datos que poidan ter nas súas mans. As empresas necesitan estes datos para formar mellor modelos que lles axuden a gañar cartos. En tales condicións, os programadores necesitan crear API que lles permitan traballar de forma rápida e fiable con grandes cantidades de información.
Se estivo deseñando backends de aplicacións ou bases de datos durante algún tempo, probablemente escribiu código para executar consultas paxinadas. Por exemplo, así:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Como é?
Pero se así fixeches a túa paxinación, sinto dicir que non o fixeches da forma máis eficiente.
Queres obxectarme? . , и Xa están utilizando as técnicas das que quero falar hoxe.
Nomea polo menos un programador de backend que nunca usou OFFSET и LIMIT para realizar consultas paxinadas. En MVP (Produto Mínimo Viable) e en proxectos onde se usan pequenas cantidades de datos, este enfoque é bastante aplicable. "Só funciona", por así dicilo.
Pero se necesitas crear sistemas fiables e eficientes desde cero, debes coidar con antelación a eficacia de consultar as bases de datos utilizadas nestes sistemas.
Hoxe falaremos dos problemas coas implementacións de motores de consulta paxinadas de uso habitual (más malas) e de como conseguir un alto rendemento ao executar tales consultas.
Que hai de malo con OFFSET e LIMIT?
Como xa se dixo, OFFSET и LIMIT Funcionan ben en proxectos que non precisan traballar con grandes cantidades de datos.
O problema xorde cando a base de datos crece ata un tamaño tal que xa non cabe na memoria do servidor. Non obstante, ao traballar con esta base de datos, cómpre utilizar consultas paxinadas.
Para que este problema se manifeste, debe haber unha situación na que o DBMS recorre a unha operación de exploración de táboa completa ineficiente en cada consulta paxinada (mentres poden producirse operacións de inserción e eliminación, e non necesitamos datos obsoletos!).
Que é un "escaneo de táboa completa" (ou "escaneo de táboas secuencial", exploración secuencial)? Trátase dunha operación durante a cal o DBMS le secuencialmente cada fila da táboa, é dicir, os datos contidos nela, e comproba o cumprimento dunha determinada condición. Este tipo de exploración da táboa sábese que é o máis lento. O caso é que cando se executa, realízanse moitas operacións de entrada/saída que implican o subsistema de discos do servidor. A situación empeora pola latencia asociada ao traballo con datos almacenados en discos e polo feito de que a transferencia de datos do disco á memoria é unha operación intensiva en recursos.
Por exemplo, ten rexistros de 100000000 de usuarios e executa unha consulta coa construción OFFSET 50000000. Isto significa que o DBMS terá que cargar todos estes rexistros (e nin sequera os necesitamos!), poñelos na memoria e, despois diso, levar, digamos, 20 resultados informados en LIMIT.
Digamos que pode verse así: "seleccione filas de 50000 a 50020 de 100000". É dicir, o sistema primeiro terá que cargar 50000 filas para completar a consulta. Ves canto traballo innecesario terá que facer?
Se non me cres, bótalle unha ollada ao exemplo que creei usando as funcións .
Exemplo en db-fiddle.com
Alí, á esquerda, no campo Schema SQL, hai un código que insire 100000 filas na base de datos, e á dereita, no campo Query SQL, móstranse dúas consultas. O primeiro, lento, ten o seguinte aspecto:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
E o segundo, que é unha solución eficaz para o mesmo problema, é así:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Para atender estas solicitudes, só tes que premer no botón Run na parte superior da páxina. Feito isto, comparamos información sobre o tempo de execución da consulta. Resulta que executar unha consulta ineficiente leva polo menos 30 veces máis que executar a segunda (este tempo varía dunha execución a outra; por exemplo, o sistema pode informar de que a primeira consulta tardou 37 ms en completarse, pero a execución da segundo - 1 ms).
E se hai máis datos, todo parecerá aínda peor (para estar convencido diso, bótalle un ollo ao meu con 10 millóns de filas).
O que acabamos de comentar debería darche unha idea de como se procesan as consultas de bases de datos.
Teña en conta que canto maior sexa o valor OFFSET - canto máis tempo tardará en completarse a solicitude.
Que debería usar en lugar da combinación de OFFSET e LIMIT?
En lugar dunha combinación OFFSET и LIMIT Paga a pena usar unha estrutura construída segundo o seguinte esquema:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Esta é a execución de consultas con paxinación baseada no cursor.
En lugar de almacenar os actuais localmente OFFSET и LIMIT e transmitilos con cada solicitude, cómpre almacenar a última clave primaria recibida (normalmente é ID) E LIMIT, como resultado, obteranse consultas similares ás anteriores.
Por que? A cuestión é que especificando explícitamente o identificador da última fila lida, indicas ao teu DBMS onde ten que comezar a buscar os datos necesarios. Ademais, a busca, grazas ao uso da clave, realizarase de forma eficiente; o sistema non terá que distraerse con liñas fóra do rango especificado.
Vexamos a seguinte comparación de rendemento de varias consultas. Aquí tes unha consulta ineficaz.
Solicitude lenta
E aquí tes unha versión optimizada desta solicitude.
Solicitude rápida
Ambas consultas devolven exactamente a mesma cantidade de datos. Pero o primeiro tarda 12,80 segundos en completarse e o segundo 0,01 segundos. Sentes a diferenza?
Posibles problemas
Para que o método de consulta proposto funcione de forma eficaz, a táboa debe ter unha columna (ou columnas) que conteñan índices únicos e secuenciais, como un identificador de número enteiro. Nalgúns casos específicos, isto pode determinar o éxito do uso de tales consultas para aumentar a velocidade de traballo coa base de datos.
Por suposto, ao construír consultas, cómpre ter en conta a arquitectura específica das táboas e escoller aqueles mecanismos que funcionarán mellor nas táboas existentes. Por exemplo, se precisa traballar en consultas con grandes volumes de datos relacionados, pode resultarlle interesante artigo.
Se nos atopamos ante o problema de perder unha chave primaria, por exemplo, se temos unha táboa cunha relación de moitos a moitos, entón o enfoque tradicional de usar OFFSET и LIMIT, está garantido para nós. Pero o seu uso pode producir consultas potencialmente lentas. Nestes casos, recomendaría usar unha chave primaria de incremento automático, aínda que só sexa necesaria para xestionar consultas paxinadas.
Se estás interesado neste tema - , и - varios materiais útiles.
Resultados de
A principal conclusión que podemos sacar é que, independentemente do tamaño das bases de datos da que esteamos a falar, sempre é necesario analizar a velocidade de execución das consultas. Hoxe en día, a escalabilidade das solucións é extremadamente importante, e se todo está deseñado correctamente desde o inicio de traballar nun determinado sistema, isto, no futuro, pode salvar ao desenvolvedor de moitos problemas.
Como analizas e optimizas as consultas de bases de datos?
Fonte: www.habr.com