Como criar perfis de consultas SQL para melhor desempenho

Na Servebolt, vivemos e respiramosdesempenho .

O desempenho do banco de dados não é exceção.

A execução de uma consulta ineficiente após um visitante do site clicar em um link degradará significativamente a experiência do usuário .Eles precisarão aguardar a duração total da execução lenta da consulta, o que pode levar vários segundos, antes que qualquer outra ação ocorra, como a renderização da página. Esse tempo de espera inclui não apenas o tempo necessário para a execução da consulta, mas também qualquer tempo adicional necessário para pré-processamento e pós-processamento. Como resultado, uma consulta mal projetada pode diminuir significativamente o desempenho geral de um site, resultando em uma experiência frustrante para o usuário.

Time to First Byte (TTFB) é uma maneira de medir quanto tempo leva para o primeiro byte de dados ser recebido depois que um usuário faz uma solicitação a um site.É também uma métrica chave usada pelos motores de busca na avaliação de sites. Quando uma consulta lenta é acionada, isso afetará negativamente o TTFB. Quanto mais tempo a consulta lenta demorar para ser executada, maior será o TTFB, resultando em um desempenho geral do site mais lento e em uma experiência do usuário menos satisfatória.

Neste guia, orientaremos você sobre como criar o perfil de consultas SQL – uma parte crucial da manutenção do desempenho de aplicativos da Web que dependem de respostas de banco de dados. Este é um processo que estabelece as bases para poder começar a trabalhar na otimização dessas consultas para melhorar seu desempenho.

Compreendendo a criação de perfil de consulta SQL

À medida que você desenvolve um aplicativo da Web e ele começa a operar em uma escala maior, as consultas SQL que antes eram executadas sem problemas podem causar problemas de desempenho. De um modo geral, tende a haver um número crescente de consultas em execução em uma quantidade crescente de dados com um número crescente de solicitações por segundo. E quando o desempenho é prejudicado, a experiência que seus usuários têm ao interagir com seu site, software ou serviço também sofre.

A criação de perfil de consulta é uma maneira de analisar consultas de banco de dados, avaliar seu desempenho e identificar possíveis problemas.

Ao analisar e identificar essas consultas problemáticas, você pode fazer melhorias específicas que podem fazer uma diferença mensurável no desempenho de seu banco de dados. Isso, por sua vez, permitirá uma melhor escalabilidade no futuro, bem como a satisfação geral do cliente, pois aplicativos e sites serão mais responsivos.

O MariaDB (e o MySQL) fornecem várias ferramentas e técnicas para criação de perfil de consulta, que abordaremos neste artigo. Uma vez identificadas as consultas lentas , o próximo passo será otimizá-las. Esse processo inclui identificar a causa raiz do problema e fazer alterações na estrutura das consultas para melhorar sua eficiência.

Como criar perfis de consultas SQL (7 métodos)

Vamos começar detalhando as diferentes ferramentas e técnicas disponíveis para identificar consultas lentas e ineficientes para que você saiba onde concentrar os esforços de melhoria:

1 – O comandoEXPLAIN EXTENDED

Uma das ferramentas que podem ser usadas para analisar suas consultas SQL é o comandoEXPLAIN .

Ao executar o comando EXPLAIN em uma consulta, você pode ver como a consulta é executada, incluindo quais índices são usados ​​e o número de linhas examinadas.

EXPLAIN SELECT * FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.name = 'John Smith';

Quando você executa o comando EXPLAINem uma consulta, ele retorna um conjunto de resultados com várias colunas, incluindo:

• id: O identificador exclusivo da consulta no plano de execução
• select_type: o tipo de consulta, como SIMPLE ou SUBQUERY
• table: A tabela que está sendo consultada
• type: o tipo de junção usado, como JOIN ou INDEX
• possible_keys: Os índices que MariaDB ou MySQL poderiam ter usado para processar a consulta
• key: O índice que MariaDB ou MySQL realmente usou para processar a consulta
• key_len: O comprimento da chave que foi usada
• linhas: O número de linhas que MariaDB ou MySQL estima serão examinadas para a consulta

Extra: contém informações adicionais sobre a consulta, como se uma verificação completa da tabela foi executada ou se uma tabela temporária foi usada.

Ao analisar a saída do comandoEXPLAIN, você geralmente consegue identificar possíveis gargalos de desempenho, como indexação ruim, tipos de junção abaixo do ideal ou alto número de linhas examinadas.

Por exemplo, se a coluna de tipo mostrar “ALL” em vez de “index”,então a consulta está fazendo uma varredura completa da tabela, o que quase certamente resultará em desempenho lento. Se a coluna chave for NULL, então o MySQL não está usando nenhum índice, o que também será lento. Se a coluna de linhas tiver um valor alto, isso significa que muitas linhas estão sendo examinadas, resultando em degradação adicional no desempenho.

Preferimos usar a variação EXPLAIN EXTENDED para ajudar a fornecer informações adicionais.

Observação: embora esteja obsoleto no MySQL, ainda está disponível no MariaDB.

Ao usar a opção EXTENDED, você poderá ver informações úteis, como o número de linhas examinadas, o número de linhas retornadas, informações sobre o tipo de JOIN usado, a ordem das tabelas verificadas, os índices usados ​​e quanto tempo a consulta demorou para ser executada.

Veja como usar o comando EXPLAIN EXTENDED:

EXPLAIN EXTENDED SELECT * FROM your_table WHERE column_name = 'value';

Neste exemplo, o comando EXPLAIN mostrará uma lista de passos que o banco de dados seguirá para executar a consulta, bem como uma lista dos recursos que utilizará.

Ao usar este comando, você poderá identificar mais facilmente os gargalos na consulta, permitindo que você faça as alterações necessárias para aliviar isso e acelerar o desempenho da consulta.

Por exemplo, usar o comando EXPLAIN EXTENDED pode ajudar a identificar a necessidade de adicionar índices, otimizar as condições de JOIN e limitar o número total de linhas retornadas pela consulta.

Você também deve certificar-se de que desativou o cache de consulta ao realizar esses testes e otimizações para garantir resultados precisos. Para fazer isso, execute este comando primeiro ao conectar seu cliente.

SET SESSION query_cache_type=0;

Depois de fazer essas alterações em sua consulta, teste seu desempenho novamente para identificar o quanto de melhoria foi alcançado (se houver). Lembre-se de que, como em qualquer criação de perfil e otimização de uma consulta, o processo é iterativo – espere usar o comando EXPLAIN EXTENDED, seguido de um teste de desempenho, várias vezes.

2 – O comandoEXPLAIN ANALYZE

Esse comando é usado para analisar o plano de execução de uma consulta e retornar métricas de desempenho, como o tempo real que a consulta levou para ser executada e o número de linhas realmente examinadas. Ao analisar os resultados do comando EXPLAIN ANALYZE, você pode identificar possíveis gargalos na execução da consulta, como falta de índices ou um grande número de linhas que precisam ser examinadas.

3 – O log de consultas lentas

Este é um recurso embutido no MariaDB (e no MySQL) que registra todas as consultas que demoram mais do que um certo tempo para serem executadas. O log de consulta lenta pode ser configurado para registrar consultas que demoram mais do que um limite específico, como um segundo.

No Servebolt, o log de consultas lentas registra todas as consultas que demoram mais de 1 segundo para serem executadas. Isso ocorre porque a maioria das consultas deve ser executada em frações de segundo. No contexto de um aplicativo da Web, como um site executando o WordPress, carregar uma única página requer entre 10 e 100 consultas ao banco de dados, todas as quais devem ser executadas sequencialmente antes que a página possa ser compilada em HTML e retornada ao usuário.

A configuração atual do Servebolt Cloud mantém logs de consulta lentos em um servidor de log global. Se necessário, você pode simplesmente entrar em contato com nossa equipe de suporte e filtraremos o arquivo para os logs relevantes e forneceremos a saída.

Em seus próprios ambientes, você pode habilitar o log de consulta lento adicionando as seguintes linhas ao seu arquivo de configuração MariaDB ou MySQL (my.cnf ou my.ini):

log_slow_queries = /path/to/slow.log

long_query_time = 1

4 – Plano de Explicação Visual

Um plano de explicação visual fornece uma representação gráfica da saída do comando EXPLAIN, tornando mais fácil entender a execução de uma consulta e detectar quaisquer problemas de desempenho.

Nota: Os planos de explicação visual são úteis quando você está no processo de desenvolvimento de aplicativos da web.

Em vez de saída de texto simples, ele exibe a execução da consulta em uma estrutura de árvore , com cada nó representando uma tabela, índice ou operação, e as conexões entre eles descrevem a ordem das operações.

Diferentes ferramentas, como o MySQL Workbench e o EXPLAIN Analyzer, podem gerar planos explicativos visuais e oferecer uma interface interativa para navegar no plano de execução e examinar cada operação em detalhes.

Por exemplo, no MySQL Workbench, gerar um plano de explicação visual é tão simples quanto executar a consulta e clicar no botão “Plano de explicação ” na guia de resultados.Isso apresenta uma representação gráfica do plano de execução da consulta, juntamente com informações detalhadas sobre cada operação. Isso permite que você identifique quaisquer problemas de desempenho e otimize a consulta conforme necessário.

5 – O Tuner MySQL

O MySQL Tuner é um script que verifica o desempenho e a configuração de um servidor de banco de dados e fornece recomendações para melhorias. Ele fornece um resumo do status atual do servidor, incluindo informações como o número total de consultas, o número de consultas lentas e o uso atual do buffer pool.

Ele também pode ser usado para verificar várias outras configurações, como a versão do banco de dados, o mecanismo de armazenamento em uso e a configuração do cache de consulta, além de fornecer recomendações para otimizar essas configurações com base na carga de trabalho atual.

Uma das principais diferenças com outras ferramentas é que é uma ferramenta de linha de comando que pode ser executada no próprio servidor ou remotamente, facilitando a automatização do processo de monitoramento e otimização do desempenho do banco de dados.

Nota: Se o seu aplicativo da web (e banco de dados) já estiver hospedado na Nuvem Servebolt – isso é algo em que nossa equipe é especializada e é capaz de fazer melhor do que qualquer recomendação que uma ferramenta seria capaz de fornecer.

6 – Perfis de Consulta

Existem criadores de perfil de consulta de terceiros que podem ser usados ​​para criar perfis de consultas SQL, como MariaDB Enterprise Query Analyzer , Dataedo e Percona Toolkit . Os criadores de perfil de consulta de terceiros podem fornecer recursos e funcionalidades adicionais em comparação com as ferramentas integradas disponíveis no MariaDB (ou MySQL).

Nota: Os Query Profilers são úteis quando você está no processo de desenvolvimento de aplicativos da web.

Por exemplo, eles podem oferecer informações mais detalhadas sobre o desempenho da consulta, como tempos de execução e tempos de espera de bloqueio, e podem fornecer visualização dos dados de maneiras que não são possíveis com as ferramentas integradas.

Se as ferramentas integradas forem suficientes para suas necessidades, não há necessidade de usar criadores de perfil de consulta de terceiros. No entanto, se você precisar de informações mais detalhadas ou recursos avançados, pode valer a pena considerar um criador de perfil de terceiros.

7 – Criação de perfil com ferramentas de monitoramento

Há também várias ferramentas de monitoramento, como Prometheus, Grafana e Nagios, que podem ser usadas para criar perfis de consultas e monitorar o desempenho de seus bancos de dados.

O Prometheus é um sistema de monitoramento eficiente que pode coletar, armazenar e consultar dados de métricas, permitindo que você obtenha insights valiosos em tempo real.Ele se integra ao MariaDB (e ao MySQL) para armazenar as métricas coletadas e vem com o Grafana para uma visualização eficaz.

O Grafana é uma poderosa ferramenta de análise de código aberto que pode ser usada para monitorar e visualizar dados coletados do Prometheus.A configuração de painéis e alertas personalizados permite que você fique de olho no desempenho do seu banco de dados em tempo real.

O Nagios ajuda você a ficar de olho na integridade do seu banco de dados o tempo todo.Ele pode ser configurado para monitorar os principais recursos, como CPU, RAM e espaço em disco, além de acompanhar outros serviços e dispositivos de rede. Como é altamente configurável, é uma ótima ferramenta para monitoramento proativo de consultas de banco de dados.

Com a ajuda dessas ferramentas de monitoramento de servidor, você pode rastrear problemas de desempenho e agir rapidamente, garantindo que seu servidor de banco de dados funcione sem problemas.

Técnicas comuns de otimização de consultas

Existem várias técnicas comuns de otimização de consulta que podem ser usadas para melhorar o desempenho de consultas SQL:

1 – Indexação

Os índices são uma forma de agilizar as consultas – principalmente aquelas que utilizam filtros(WHERE).O uso de índices resulta em estruturas de dados em seu mecanismo de banco de dados (MariaDB ou MySQL) fora de tabelas específicas e aponta para os dados que você está tentando consultar. Não entraremos em muitos detalhes neste post, pois o uso de índices para melhorar as consultas ao banco de dados merece um artigo próprio – algo que planejamos cobrir no futuro.

Por exemplo, considere uma tabela grande chamada “pedidos” que contém milhões de linhas de dados, incluindo informações como ID do pedido, ID do cliente e data do pedido. Se uma consulta fosse executada para recuperar todos os pedidos feitos por um cliente específico sem um índice na coluna de ID do cliente, o MariaDB teria que varrer toda a tabela para localizar os dados relevantes. Isso pode levar tempo e recursos significativos, especialmente para tabelas grandes.

De um modo geral, sempre que você tiver certeza de que executará uma consulta específica repetidamente e lerá questões de desempenho, criar um índice (ou mais de um) pode ser a abordagem certa para acelerar essa consulta.

No contexto do WordPress, isso é muito comum. Muitos plugins são construídos por desenvolvedores que (por conveniência) usam tabelas genéricas e compartilhadas sem usar índices. Como resultado, é também uma área onde muitas vezes há ganhos de desempenho muito significativos.

Para visualizar quaisquer índices existentes em uma tabela específica,

Você pode visualizar qualquer índice que exista em uma tabela específica usando SHOW INDEX FROM – como no exemplo abaixo para a tabela wp_postmeta:

MariaDB [db_name] > SHOW INDEX FROM wp_postmeta;

Em um cenário, criamos recentemente dois índices para uma tabela wp_postmeta:sb_postid_metakey e sb_postid_metakey_metaval.

Esses índices foram adicionados com base na observação das principais consultas lentas e na descoberta de que todas eram relativamente semelhantes pela característica de serem instruções SELECT que filtram usando WHERE, além de muitas condições de comparação (AND/OR). Ao ver isso, revisei os índices atuais da tabela usada e executeiEXPLAIN EXTENDED na consulta para validar ainda mais minha abordagem.

A consulta estava funcionando principalmente e usando a tabela wp_postmeta usandoJOIN.Com base na ordem em que isso estava acontecendo, adicionar esses índices permitiria ao MariaDB (ou MySQL) obter sua resposta dos índices em vez de varrer a tabela inteira com todas as suas linhas.

CREATE INDEX sb_postid_metakey ON wp_postmeta (post_id, meta_key);

CREATE INDEX sb_postid_metakey_metaval ON wp_postmeta (post_id, meta_key, meta_value);

Esta é uma combinação de “descobrir as coisas” usando as ferramentas que você tem à sua disposição (conforme descrito acima), bem como o conhecimento dos tipos de dados e conteúdos do banco de dados. Isso nem sempre funciona; mesmo quando isso acontece, nem sempre resulta em uma melhoria de desempenho de 500%. Ter um índice enorme pode acabar sendo mais lento do que varrer todas as linhas, então as consultas devem ser testadas antes e depois de aplicar os índices para ter certeza.

Observação: ao tentar testar as velocidades do índice, você deve desativar o cache de consulta para a sessão, usando:

SET SESSION query_cache_type=0;

Nesse caso, antes de usar os índices, a consulta levava 10,437 segundos para ser executada. E depois de criar os dois índices, a mesma consulta levou [# de segundos].

2 – Redução do Acesso a Dados

Reduzir o acesso aos dados , ou seja, minimizar o número de linhas e colunas que devem ser acessadas para executar uma consulta.Isso pode ser obtido filtrando os dados recuperados pela consulta, usando índices e particionando tabelas grandes. Embora não seja algo que a maioria das pessoas precise (ou seja capaz) de fazer, é um ponto essencial a ter em mente ao projetar consultas de banco de dados desde o início.

Por exemplo, se uma consulta de banco de dados está procurando dados sobre um usuário para fins de login, a consulta deve ser LIMIT 1, pois claramente nunca deve haver mais de um dado de usuário necessário.

Observação: isso está mais relacionado ao design do banco de dados do que à otimização.Embora importante para manter o desempenho, esse esforço é mais relevante para desenvolvedores de plug-ins (no contexto do WordPress) do que para a maioria dos usuários finais.

Lembre-se de que, antes de testar as velocidades após fazer qualquer alteração no acesso aos dados, você deve garantir que desativou o cache de consulta executando o seguinte comando:

SET SESSION query_cache_type=0;

3 – Usando o Particionamento de Dados

Ao particionar os dados em partes menores, os bancos de dados se tornam mais eficientes e consomem menos tempo para gerenciar. Essa estratégia pode ajudar a reduzir o tempo gasto em processos de manutenção, como backups e atualizações, além de limitar a quantidade de dados que precisam ser gerenciados. No geral, ajuda a melhorar o desempenho e otimizar o uso de recursos.

Para particionar dados em um banco de dados, você pode seguir estas etapas:

1. Ao selecionar uma tabela a ser particionada, certifique-se de escolher uma que contenha uma grande quantidade de dados e se beneficie da divisão. Isso ajudará a otimizar seu sistema e melhorar o desempenho da consulta.
2. Selecionar o método de particionamento correto para seu banco de dados é crucial. Você pode escolher entre intervalo, lista, hash ou particionamento de chave – dependendo da estrutura de seus dados e das consultas que planeja executar. Certifique-se de escolher aquele que melhor se adapta às suas necessidades para desempenho e resultados otimizados.
   
   1. O particionamento de intervalo é a escolha ideal quando você tem dados que podem ser divididos em determinados intervalos.Por exemplo, se você tiver uma tabela com dados de vários anos, poderá criar uma partição de intervalo para organizá-la melhor. Pode ser baseado na data ou no valor numérico da coluna em questão.
   2. O particionamento de lista é uma técnica eficiente para lidar com dados que podem ser facilmente segregados em vários grupos de acordo com um parâmetro específico.Por exemplo, você tem uma tabela com as informações dos funcionários categorizadas por Departamento; isso requer o uso de particionamento de lista.
   3. O particionamento de hash é uma estratégia eficaz para organizar dados em clusters de tamanho igual com base no valor de hash de uma coluna específica.Isso permite uma distribuição uniforme dos dados em várias partições, tornando-o uma ótima opção para distribuir os dados com eficiência.
   4. O particionamento de chaves é semelhante ao particionamento de hash, mas a principal diferença é que ele usa um valor de coluna específico como base para dividir os dados em diferentes grupos.Isso o torna uma escolha ideal para conjuntos de dados que podem ser divididos em grupos separados com base em um identificador exclusivo ou chave natural.
3. Ao criar uma tabela particionada, você pode efetivamente dividir a tabela original em tabelas menores. Isso é obtido adicionando uma cláusula de particionamento na instrução CREATE TABLE, onde você especifica o método e as condições desejados para segmentação. Isso pode ajudar a melhorar o desempenho da consulta – e também tornar o gerenciamento de dados mais eficiente.
4. Você pode copiar dados rapidamente da tabela original para a recém-particionada usando a instrução INSERT INTO… SELECT. Isso preencherá facilmente sua tabela particionada com todas as informações relevantes.
5. Os aplicativos agora devem ser reconfigurados para aproveitar a tabela particionada. Isso substituirá a tabela original e tornará seus aplicativos mais eficientes.
6. Antes de executar qualquer teste para avaliar a melhoria de desempenho potencial, será essencial desativar o cache de consulta primeiro executando o comando: SET SESSION query_cache_type=0;
7. Para garantir que sua tabela particionada esteja funcionando sem problemas, é importante ficar de olho em seu desempenho. Se você notar algum problema, ajustar as condições de particionamento ou mudar para outro método pode ajudar. Monitorar regularmente suas partições ajudará você a maximizar seu potencial.

Observação importante sobre atualizações de script e tabelas particionadas

Embora o particionamento de bancos de dados possa fazer uma diferença positiva na eficiência, é importante ter em mente os possíveis problemas causados ​​pela execução de scripts de atualização para alterar o esquema do banco de dados. É essencial que as tabelas particionadas sejam levadas em consideração ao fazer o script dessas atualizações. Se as tabelas particionadas não forem consideradas nos scripts de atualização, pode haver problemas potenciais que quase certamente resultarão em um site com defeito.

Por exemplo, se um script for criado para adicionar uma nova coluna a uma tabela particionada, ele poderá alterar apenas uma partição, criando inconsistências e problemas nos dados. Da mesma forma, se um script de atualização for criado para adicionar um índice a uma tabela particionada, ele só poderá gerar o índice em uma partição, resultando em desempenho mais lento e resultados inconsistentes.

Para evitar tais problemas, os scripts de atualização devem ser projetados para considerar as tabelas particionadas. Isso pode envolver a execução do script em cada partição individualmente ou a revisão dos scripts para trabalhar com tabelas particionadas. Também é importante realizar testes completos para garantir que o processo de atualização não gere nenhum problema inesperado ou perda de dados.

4 – Redes

Para clientes do Servebolt, o Redis é um complemento (pago) que pode ajudar na otimização de consultas.

Redis (às vezes conhecido como Remote Dictionary Server) é uma solução de código aberto que armazena dados na memória e pode ser usada para armazenamento em cache, um banco de dados ou até mesmo como um intermediário de mensagens. Pode ser integrado a um banco de dados para melhorar o desempenho, atuando como um intermediário eficiente entre o aplicativo e o banco de dados.

Ele trabalha para melhorar o desempenho e os tempos de resposta dos aplicativos, reduzindo a carga no banco de dados. Isso é feito armazenando dados usados ​​com frequência no Redis em vez do banco de dados para cada solicitação, economizando assim um tempo considerável.

Ao configurar corretamente o plug-in, o Redis pode ser usado com um banco de dados para otimizar a execução da consulta. Quando os dados necessários não estiverem presentes no Redis, o aplicativo os recuperará do banco de dados e os armazenará no Redis para uso futuro. Isso torna a recuperação de dados muito mais rápida e eficiente.

Ao usar essa abordagem, o aplicativo pode se beneficiar do acesso rápido à memória do Redis e também armazenar e acessar dados do banco de dados conforme necessário.

Lembre-se de que, se estiver implementando o Redis pela primeira vez, será necessário desativar o cache de consulta antes de executar qualquer teste de desempenho. Para fazer isso, use o comando:

SET SESSION query_cache_type=0;

Conclusão

O ecossistema MariaDB e MySQL possui uma ampla gama de ferramentas e métodos para facilitar a descoberta de gargalos nas execuções de consultas ao banco de dados, permitindo melhorar o desempenho de suas aplicações web.

É provável que ocorram lentidões ao longo da vida útil da execução de qualquer aplicativo. Tentar evitá-los é ótimo, mas você precisa saber onde procurar quando começar a diagnosticar problemas de desempenho. Dependendo do tamanho e da natureza dos bancos de dados que você executa, esse é um processo iterativo que requer monitoramento contínuo, solução de problemas e melhoria contínua para manter o desempenho de seus bancos de dados em alto padrão.