Miles de gerentes de oficinas de ventas de todo el país registran decenas de miles de contactos diarios — hechos de comunicación con clientes potenciales o existentes. Y para ello primero hay que encontrar un cliente, y preferiblemente muy rápidamente. Y esto sucede con mayor frecuencia por su nombre.
Por tanto, no es de extrañar que, una vez más analizando consultas "pesadas" en una de las bases de datos más cargadas: la nuestra , encontré "en la parte superior" Solicitud de búsqueda “rápida” por nombre. para tarjetas de organización.
Además, investigaciones posteriores revelaron un ejemplo interesante. Primero optimización y luego degradación del rendimiento. solicitud con su perfeccionamiento secuencial por parte de varios equipos, cada uno de los cuales actuó únicamente con las mejores intenciones.
0: ¿qué quería el usuario?
[KDPV ]
¿Qué suele querer decir un usuario cuando habla de una búsqueda “rápida” por nombre? Casi nunca resulta ser una búsqueda "honesta" de una subcadena como ... LIKE '%роза%' - porque entonces el resultado incluye no sólo 'Розалия' и 'Магазин Роза'pero 'Гроза' e incluso 'Дом Деда Мороза'.
El usuario asume en el día a día que le proporcionarás buscar por comienzo de palabra en el título y hacerlo más relevante que comienza con ingresó. y lo haras casi al instante - para entrada interlineal.
1: limitar la tarea
Y más aún, una persona no entrará específicamente 'роз магаз', por lo que tendrás que buscar cada palabra por prefijo. No, es mucho más fácil para un usuario responder a una pista rápida para la última palabra que "subespecificar" intencionalmente las anteriores; mire cómo maneja esto cualquier motor de búsqueda.
En general, los correctamente formular los requisitos para el problema es más de la mitad de la solución. A veces, análisis cuidadoso de casos de uso. .
¿Qué hace un desarrollador abstracto?
1.0: motor de búsqueda externo
Oh, la búsqueda es difícil, no quiero hacer nada en absoluto, ¡déselo a los devops! Que desplieguen un motor de búsqueda externo a la base de datos: Sphinx, ElasticSearch,...
Una opción funcional, aunque laboriosa en términos de sincronización y velocidad de cambios. Pero en nuestro caso no, ya que la búsqueda se realiza para cada cliente únicamente en el marco de los datos de su cuenta. Y los datos tienen una variabilidad bastante alta, y si el administrador ahora ingresó la tarjeta 'Магазин Роза', luego, después de 5 a 10 segundos, es posible que ya recuerde que olvidó indicar su correo electrónico allí y quiera encontrarlo y corregirlo.
Por lo tanto - vamos buscar “directamente en la base de datos”. Afortunadamente, PostgreSQL nos permite hacer esto, y no solo una opción: las veremos.
1.1: subcadena "honesta"
Nos aferramos a la palabra "subcadena". Pero para la búsqueda de índice por subcadena (¡e incluso por expresiones regulares!) existe una excelente ! Sólo entonces será necesario ordenar correctamente.
Intentemos tomar la siguiente placa para simplificar el modelo:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Subimos allí 7.8 millones de registros de organizaciones reales y los indexamos:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Busquemos los primeros 10 registros para búsqueda interlineal:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Bueno, eso ... 26 ms, 31 MB lea datos y más de 1.7 mil registros filtrados, de los 10 buscados. Los gastos generales son demasiado altos, ¿no hay algo más eficiente?
1.2: ¿buscar por texto? ¡Es FTS!
De hecho, PostgreSQL proporciona una herramienta muy poderosa. (Búsqueda de texto completo), incluida la posibilidad de realizar búsquedas con prefijos. Una excelente opción, ¡ni siquiera necesitas instalar extensiones! Intentemos:
CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));SELECT
*
FROM
firms
WHERE
to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;
Aquí la paralelización de la ejecución de consultas nos ayudó un poco, reduciendo el tiempo a la mitad para 11ms. Y tuvimos que leer 1.5 veces menos, en total 20MB. Pero aquí, cuanto menos, mejor, porque cuanto mayor sea el volumen que leemos, mayores serán las posibilidades de que se pierda el caché, y cada página adicional de datos leída del disco es un potencial "freno" para la solicitud.
1.3: ¿todavía te gusta?
La petición anterior es buena para todos, pero sólo si la haces cien mil veces al día, llegará. 2TB leer datos. En el mejor de los casos, de memoria, pero si no tienes suerte, desde el disco. Así que intentemos hacerlo más pequeño.
Recordemos lo que el usuario quiere ver. primero "que comienzan con...". Así que esto está en su forma más pura. a través de text_pattern_ops! Y solo si "no tenemos suficientes" hasta 10 registros que estamos buscando, entonces tendremos que terminar de leerlos usando la búsqueda FTS:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Excelente rendimiento - total 0.05 ms y un poco más de 100 KB ¡leer! solo nosotros lo olvidamos ordenar por nombrepara que el usuario no se pierda en los resultados:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Oh, algo ya no es tan bonito: parece que hay un índice, pero la clasificación pasa volando... Por supuesto, ya es mucho más eficaz que la opción anterior, pero...
1.4: “terminar con una lima”
Pero hay un índice que le permite buscar por rango y seguir utilizando la clasificación normalmente. árbol regular!
CREATE INDEX ON firms(lower(name));Sólo la solicitud del mismo deberá ser “recogida manualmente”:
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Excelente: la clasificación funciona y el consumo de recursos sigue siendo "microscópico". miles de veces más eficaz que el FTS “puro”! Sólo queda juntarlo en una sola solicitud:
(
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
ORDER BY
lower(name)
LIMIT 10
)
UNION ALL
(
SELECT
*
FROM
firms
WHERE
to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
, lower(name)
LIMIT 10
)
LIMIT 10;
Tenga en cuenta que se ejecuta la segunda subconsulta. sólo si el primero arrojó menos de lo esperado el ultimo LIMIT número de líneas. Me refiero a este método de optimización de consultas. .
Así que sí, ahora tenemos tanto btree como gin sobre la mesa, pero estadísticamente resulta que menos del 10% de las solicitudes llegan a la ejecución del segundo bloque. Es decir, con limitaciones tan típicas conocidas de antemano para la tarea, pudimos reducir el consumo total de recursos del servidor casi mil veces.
1.5*: podemos prescindir de un archivo
Por encima de LIKE Se nos impidió utilizar una clasificación incorrecta. Pero se puede “colocar en el camino correcto” especificando el operador USING:
Por defecto se supone
ASC. Además, puede especificar el nombre de un operador de clasificación específico en una cláusula.USING. El operador de clasificación debe ser miembro de menor o mayor que de alguna familia de operadores de árbol B.ASCgeneralmente equivalenteUSING <иDESCgeneralmente equivalenteUSING >.
En nuestro caso, "menos" es ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: cómo las solicitudes se vuelven amargas
Ahora dejamos nuestra petición a “cocer a fuego lento” durante seis meses o un año, y nos sorprende volver a encontrarla “en la cima” con indicadores del “bombeo” diario total de memoria (buffers de golpe compartido) En 5.5TB - es decir, incluso más de lo que era originalmente.
No, por supuesto, nuestro negocio ha crecido y nuestra carga de trabajo ha aumentado, ¡pero no en la misma cantidad! Esto significa que algo huele mal aquí; averigüémoslo.
2.1: el nacimiento de la paginación
En algún momento, otro equipo de desarrollo quiso hacer posible "saltar" de una búsqueda rápida de subíndices al registro con los mismos resultados, pero ampliados. ¿Qué es un registro sin navegación de páginas? ¡Vamos a arruinarlo!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Ahora era posible mostrar el registro de resultados de búsqueda con carga "página por página" sin ningún estrés para el desarrollador.
Por supuesto, de hecho, para cada página subsiguiente de datos se lee más y más (todo del tiempo anterior, que descartaremos, más la "cola" necesaria), es decir, este es un antipatrón claro. Pero sería más correcto iniciar la búsqueda en la siguiente iteración a partir de la clave almacenada en la interfaz, pero eso ya se hablará en otro momento.
2.2: quiero algo exótico
En algún momento el desarrollador quiso diversificar la muestra resultante con datos de otra tabla, para la cual se envió toda la solicitud anterior al CTE:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;Y aun así no está mal, ya que la subconsulta se evalúa solo para 10 registros devueltos, si no...
2.3: DISTINCT no tiene sentido y es despiadado
En algún lugar del proceso de dicha evolución desde la segunda subconsulta perdió NOT LIKE condición. Está claro que después de esto UNION ALL comenzó a regresar algunas entradas dos veces - se encuentra primero al principio de la línea y luego nuevamente al comienzo de la primera palabra de esta línea. Como límite, todos los registros de la segunda subconsulta podrían coincidir con los registros de la primera.
¿Qué hace un desarrollador en lugar de buscar la causa?... ¡No hay duda!
- duplicar el tamaño muestras originales
- aplicar DISTINTOpara obtener solo instancias únicas de cada línea
WITH q AS (
( ... LIMIT <2 * N> + 10)
UNION ALL
( ... LIMIT <2 * N> + 10)
LIMIT <2 * N> + 10
)
SELECT DISTINCT
*
, (SELECT ...) sub_query
FROM
q
LIMIT 10 OFFSET <N>;Es decir, está claro que el resultado, al final, es exactamente el mismo, pero la posibilidad de "volar" a la segunda subconsulta de CTE se ha vuelto mucho mayor, e incluso sin esto, claramente más legible.
Pero esto no es lo más triste. Dado que el desarrollador pidió seleccionar DISTINCT no para campos específicos, sino para todos los campos a la vez registros, entonces el campo sub_query (el resultado de la subconsulta) se incluyó automáticamente allí. Ahora, para ejecutar DISTINCT, la base de datos ya tenía que ejecutarse no 10 subconsultas, sino todas <2 * N> + 10!
2.4: ¡cooperación ante todo!
Entonces, los desarrolladores siguieron viviendo, no se molestaron, porque el usuario claramente no tuvo la paciencia suficiente para "ajustar" el registro a valores N significativos con una desaceleración crónica en la recepción de cada "página" posterior.
Hasta que los desarrolladores de otro departamento acudieron a ellos y quisieron utilizar un método tan conveniente. para búsqueda iterativa - es decir, tomamos una pieza de alguna muestra, la filtramos por condiciones adicionales, dibujamos el resultado, luego la siguiente pieza (que en nuestro caso se logra aumentando N), y así hasta llenar la pantalla.
En general, en el ejemplar capturado N alcanzó valores de casi 17K, y en solo un día se ejecutaron al menos 4K de dichas solicitudes "a lo largo de la cadena". El último de ellos fue escaneado audazmente por 1 GB de memoria por iteración...
En total
Fuente: habr.com