Milhares de gerentes de escritórios de vendas em todo o país registram dezenas de milhares de contatos diariamente — factos de comunicação com clientes potenciais ou existentes. E para isso é preciso primeiro encontrar um cliente, de preferência muito rápido. E isso acontece com mais frequência pelo nome.
Portanto, não é de surpreender que, analisando mais uma vez consultas “pesadas” em um dos bancos de dados mais carregados - o nosso , encontrei "no topo" solicitar uma pesquisa “rápida” por nome para cartões de organização.
Além disso, uma investigação mais aprofundada revelou um exemplo interessante primeira otimização e depois degradação do desempenho solicitação com seu refinamento sequencial por diversas equipes, cada uma das quais agiu unicamente com as melhores intenções.
0: o que o usuário queria?
[KDPV ]
O que um usuário geralmente quer dizer quando fala sobre uma pesquisa “rápida” por nome? Quase nunca acaba sendo uma busca “honesta” por uma substring como ... LIKE '%роза%' - porque então o resultado inclui não só 'Розалия' и 'Магазин Роза'Mas 'Гроза' e ainda 'Дом Деда Мороза'.
O usuário assume no dia a dia que você fornecerá a ele pesquisar pelo início da palavra no título e tornar mais relevante que começa com entrou. E você vai fazer isso quase instantaneamente - para entrada interlinear.
1: limite a tarefa
E mais ainda, uma pessoa não entrará especificamente 'роз магаз', para que você tenha que pesquisar cada palavra por prefixo. Não, é muito mais fácil para um usuário responder a uma dica rápida para a última palavra do que “subespecificar” propositalmente as anteriores – veja como qualquer mecanismo de pesquisa lida com isso.
geralmente, corretamente formular os requisitos para o problema é mais da metade da solução. Às vezes, análise cuidadosa de casos de uso .
O que um desenvolvedor abstrato faz?
1.0: mecanismo de pesquisa externo
Ah, pesquisar é difícil, não quero fazer nada - vamos entregar para devops! Deixe-os implantar um mecanismo de busca externo ao banco de dados: Sphinx, ElasticSearch,...
Uma opção de trabalho, embora trabalhosa em termos de sincronização e velocidade de mudanças. Mas não no nosso caso, uma vez que a pesquisa é efectuada para cada cliente apenas no âmbito dos dados da sua conta. E os dados têm uma variabilidade bastante alta - e se o gerente já inseriu o cartão 'Магазин Роза', depois de 5 a 10 segundos ele já pode se lembrar que esqueceu de indicar seu e-mail lá e deseja encontrá-lo e corrigi-lo.
Portanto - vamos pesquise “diretamente no banco de dados”. Felizmente, o PostgreSQL nos permite fazer isso, e não apenas uma opção - vamos dar uma olhada nelas.
1.1: substring "honesta"
Nós nos apegamos à palavra “substring”. Mas para pesquisa de índice por substring (e até por expressões regulares!) existe uma excelente ! Só então será necessário ordenar corretamente.
Vamos tentar pegar a seguinte placa para simplificar o modelo:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Carregamos lá 7.8 milhões de registros de organizações reais e os indexamos:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Vamos procurar os primeiros 10 registros para pesquisa interlinear:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Bem, isso ... 26 ms, 31 MB leia dados e mais de 1.7 mil registros filtrados - para 10 pesquisados. Os custos indiretos são muito altos, não há algo mais eficiente?
1.2: pesquisa por texto? É FTS!
Na verdade, o PostgreSQL fornece uma ferramenta muito poderosa (Pesquisa de texto completo), incluindo a capacidade de pesquisa de prefixo. Uma excelente opção, você nem precisa instalar extensões! Vamos tentar:
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;
Aqui a paralelização da execução das consultas nos ajudou um pouco, reduzindo o tempo pela metade para 11ms. E tivemos que ler 1.5 vezes menos - no total 20MB. Mas aqui, quanto menos, melhor, porque quanto maior o volume que lemos, maiores são as chances de ocorrer uma perda de cache, e cada página extra de dados lida do disco é um potencial “freio” para a solicitação.
1.3: ainda GOSTA?
O pedido anterior é bom para todos, mas só se você puxar cem mil vezes por dia ele virá 2TB ler dados. Na melhor das hipóteses, da memória, mas se não tiver sorte, então do disco. Então, vamos tentar torná-lo menor.
Vamos lembrar o que o usuário deseja ver primeiro “que começam com...”. Então isso está em sua forma mais pura via text_pattern_ops! E só se “não tivermos o suficiente” até 10 registros que procuramos, teremos que terminar de lê-los usando a pesquisa FTS:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Excelente desempenho - total 0.05 ms e pouco mais de 100 KB ler! Só nós esquecemos classificar por nomepara que o usuário não se perca nos resultados:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Ah, algo não é mais tão bonito - parece que existe um índice, mas a classificação passa voando... É claro que já é muitas vezes mais eficaz que a opção anterior, mas...
1.4: “terminar com um arquivo”
Mas existe um índice que permite pesquisar por intervalo e ainda usar a classificação normalmente - árvore normal!
CREATE INDEX ON firms(lower(name));Apenas o pedido deverá ser “recolhido manualmente”:
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Excelente - a classificação funciona e o consumo de recursos permanece “microscópico”, milhares de vezes mais eficaz que o STF “puro”! Resta apenas reuni-lo em uma única solicitação:
(
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;
Observe que a segunda subconsulta é executada somente se o primeiro retornou menos que o esperado ultimo LIMIT número de linhas. Estou falando sobre este método de otimização de consulta .
Então sim, agora temos btree e gin na mesa, mas estatisticamente acontece que menos de 10% das solicitações chegam à execução do segundo bloco. Ou seja, com essas limitações típicas conhecidas antecipadamente para a tarefa, conseguimos reduzir em quase mil vezes o consumo total de recursos do servidor!
1.5*: podemos passar sem um arquivo
Acima LIKE Fomos impedidos de usar classificação incorreta. Mas pode ser “colocado no caminho certo” especificando o operador USING:
Por padrão é assumido
ASC. Além disso, você pode especificar o nome de um operador de classificação específico em uma cláusulaUSING. O operador de classificação deve ser membro de menor ou maior que de alguma família de operadores de árvore B.ASCgeralmente equivalenteUSING <иDESCgeralmente equivalenteUSING >.
No nosso caso, “menos” é ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: como os pedidos azedam
Agora deixamos nosso pedido “fervendo” por seis meses ou um ano, e ficamos surpresos ao encontrá-lo novamente “no topo” com indicadores do “bombeamento” diário total de memória (buffers compartilhados hit) em 5.5TB - isto é, ainda mais do que era originalmente.
Não, claro, o nosso negócio cresceu e a nossa carga de trabalho aumentou, mas não na mesma proporção! Isso significa que algo está suspeito aqui - vamos descobrir.
2.1: o nascimento da paginação
Em algum momento, outra equipe de desenvolvimento quis tornar possível “pular” de uma pesquisa rápida de subscritos para o registro com os mesmos resultados, mas ampliados. O que é um registro sem navegação de página? Vamos estragar tudo!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Agora foi possível mostrar o registro dos resultados da pesquisa com carregamento “página por página” sem nenhum estresse para o desenvolvedor.
Claro, na verdade, para cada página subsequente de dados, mais e mais são lidos (tudo do tempo anterior, que descartaremos, mais a “cauda” necessária) - ou seja, este é um antipadrão claro. Mas seria mais correto iniciar a busca na próxima iteração a partir da chave armazenada na interface, mas sobre isso em outra hora.
2.2: Quero algo exótico
Em algum momento o desenvolvedor quis diversificar a amostra resultante com dados de outra tabela, para a qual toda a solicitação anterior foi enviada ao CTE:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;E mesmo assim não é ruim, já que a subconsulta é avaliada apenas para 10 registros retornados, se não...
2.3: DISTINCT é sem sentido e impiedoso
Em algum lugar no processo de tal evolução a partir da 2ª subconsulta perdido NOT LIKE condição. É claro que depois disso UNION ALL começou a retornar algumas entradas duas vezes - encontrado primeiro no início da linha e depois novamente - no início da primeira palavra desta linha. No limite, todos os registros da 2ª subconsulta poderiam corresponder aos registros da primeira.
O que um desenvolvedor faz em vez de procurar a causa?.. Sem dúvida!
- dobrar o tamanho amostras originais
- aplicar DISTINTOpara obter apenas instâncias únicas de cada linha
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>;Ou seja, fica claro que o resultado, no final das contas, é exatamente o mesmo, mas a chance de “voar” para a 2ª subconsulta CTE ficou muito maior, e mesmo sem isso, claramente mais legível.
Mas isso não é a coisa mais triste. Como o desenvolvedor pediu para selecionar DISTINCT não para campos específicos, mas para todos os campos de uma vez registros, então o campo sub_query — o resultado da subconsulta — foi automaticamente incluído lá. Agora, para executar DISTINCT, o banco de dados já precisava ser executado não 10 subconsultas, mas todas <2 * N> + 10!
2.4: cooperação acima de tudo!
Assim, os desenvolvedores sobreviveram - eles não se preocuparam, porque o usuário claramente não teve paciência suficiente para “ajustar” o registro para valores N significativos com uma lentidão crônica no recebimento de cada “página” subsequente.
Até que desenvolvedores de outro departamento os procurassem e quisessem usar um método tão conveniente para pesquisa iterativa - ou seja, pegamos um pedaço de alguma amostra, filtramos por condições adicionais, desenhamos o resultado, depois o próximo pedaço (que no nosso caso é conseguido aumentando N), e assim sucessivamente até preenchermos a tela.
Em geral, no espécime capturado N atingiu valores de quase 17K, e em apenas um dia pelo menos 4K dessas solicitações foram executadas “ao longo da cadeia”. O último deles foi corajosamente escaneado por 1 GB de memória por iteração...
No total
Fonte: habr.com