A primeira etapa é priorizar as consultas a serem otimizadas:

1. Identifique as perguntas do banco de dados que precisam do melhor tempo de resposta 
2. Identifique perguntas frequentes sobre o banco de dados

Essas duas categorias precisam ser otimizadas durante a criação do esquema do banco de dados.

Uma transação é uma unidade lógica em que todas as instruções contidas seriam confirmadas totalmente ou revertidas. A transação é o recurso que fornece atomicidade, consistência, isolamento e durabilidade (ACID) para MySQL. 

No InnoDB, o mecanismo de armazenamento do MySQL, todas as atividades do usuário ocorrem dentro de uma transação. Por padrão, o modo de confirmação automática é ativado, o que significa que cada instrução SQL forma uma única transação. Para executar uma transação de várias instruções quando a confirmação automática estiver ativada, inicie a transação explicitamente com START_TRANSACTION ou START_TRANSACTION e encerre-a com START_TRANSACTION ou START_TRANSACTION. Se o modo de confirmação automática estiver desativado, a sessão sempre terá uma transação aberta até que um COMMIT ou COMMIT a encerre e inicie uma nova. 

A prática recomendada é realizar transações com o menor tempo possível. Isso ocorre porque as transações longas têm várias desvantagens, conforme descrito mais adiante neste artigo.

1. Contenções prolongadas de bloqueio, que causam consultas mais lentas e possíveis falhas de consulta

• O bloqueio no nível da linha do InnoDB é mantido durante a transação
• É possível que aumentem as esperas de bloqueio, os tempos limite de bloqueio e os impasses, que tornam a consulta mais lenta ou falham completamente.

2. Desempenho do servidor prejudicado devido à grande quantidade de registros undo

• Devido ao controle de simultaneidade de várias versões (MVCC, na sigla em inglês) do InnoDB, as versões antigas de linhas alteradas são armazenadas em registros "undo" para uma leitura e reversão consistentes. Com o nível de isolamento de leitura repetível padrão, os registros "undo" não serão limpos até as transações iniciarem antes da conclusão. Portanto, uma transação de longa duração acumula registros "undo". Isso pode ser observado e monitorado pela lista de histórico na função de linha de comando SHOW ENGINE INNODB STATUS.
• Quando a lista de histórico ultrapassa milhões, o desempenho do servidor é afetado negativamente devido à contenção de mutex nos segmentos de reversão, ao aumento do volume para ler registros "undo" e ao aumento do tempo para transferir a lista vinculada de registros "undo". Isso também cria mais trabalho para limpar as linhas de execução.

3. Aumento do uso de disco

• Aumento em registros "undo" armazenados no disco, seja no tablespace do sistema ou no tablespace undo

4. Tempo de encerramento prolongado

• Durante o encerramento normal, as transações em andamento seriam revertidas. O tempo de reversão costuma ser maior do que o tempo necessário para chegar ao ponto. Portanto, o encerramento do servidor poderia levar muito tempo para a reversão.

5. Tempo de recuperação de falha prolongado

• Durante a recuperação de falhas, o InnoDB repete as transações do último checkpoint e desfaz a reversão das transações não confirmadas. Uma transação longa faria a etapa correspondente demorar mais.

• Consultas SELECT
• Elas não contêm bloqueios de linha
• Podem causar o acúmulo de registros "undo"
• Veja a seção de otimização de consultas abaixo
• Consultas UPDATE/INSERT/DELETE
• A consulta em lote apresenta um desempenho melhor do que muitas alterações de linha única
• Divida e limite o tempo de execução do lote para alguns segundos

• Considere separar consultas SELECT
• Se houver lógica do aplicativo entre as consultas do banco de dados, considere dividir a transação
• Calcule quantos bloqueios de linha seriam mantidos para cada instrução
• Avalie a ordem de execução para minimizar o bloqueio de linhas
• Procure oportunidades de reduzir o tamanho da transação

As consultas podem ser capturadas no lado do aplicativo ou do banco de dados.

É uma prática recomendada de desenvolvimento registrar consultas do banco de dados e o tempo de execução da consulta. A geração de registros do aplicativo facilita a avaliação da eficácia das consultas no contexto empresarial. Por exemplo, os usuários podem registrar o tempo de resposta de cada consulta ou tempo de resposta de determinadas funcionalidades. Essa também é uma maneira fácil de ver o tempo total de execução para transações de várias instruções. 

Além disso, o tempo de resposta da consulta medido com base na geração de registros no aplicativo é uma avaliação completa, incluindo o tempo de rede. Ele complementa o tempo de execução da consulta registrado no banco de dados e facilita identificar se o problema é com a rede ou com o banco de dados.

Query Insights do MySQL do Cloud SQL

A ferramenta Query Insights do Cloud SQL permite captura, monitoramento e diagnóstico de consultas.

O Query Insights facilita a localização das principais consultas com base no tempo e na frequência de execução.

A ferramenta tem opções de filtragem como período, banco de dados, conta de usuário e endereço do cliente. Ela tem gráficos para mostrar o uso da CPU e detalhamento sobre E/S e esperas de bloqueio. A tabela "Principais consultas e tags" lista as principais consultas por tempo de execução, com as consultas sendo normalizadas. Além do tempo de execução, ela inclui estatísticas sobre "linhas médias verificadas" e "linhas médias retornadas", que fornecem insights para consultar a eficiência.

Consulte a documentação para ver tudo o que ela oferece e como ativá-la.

Usar performance_schema

No Cloud SQL para MySQL, o recurso performance_schema é ativado por padrão no MySQL 8.0.26 e versões mais recentes com mais de 15 GB de memória. Para ativá-lo ou desativá-lo, é necessário reiniciar a instância. 

Quando performance_schema=ON, os instrumentos de instrução de consulta são ativados por padrão. A tabela sys.statement_analysis oferece estatísticas agregadas para consultas normalizadas. Ela responde a perguntas como: 

• Quais consultas são realizadas na verificação completa da tabela?
• full_scan/exec_count: para descobrir se as consultas frequentemente realizam uma verificação completa da tabela, que geralmente é ineficiente. 
• Quais consultas estão lentas?
• avg_latency: tempo médio de execução da consulta
• Quais consultas são ineficientes? 
• rows_examined_avg/rows_examined_avg: para consultas de leitura. A proporção ideal é 1. Quanto maior a proporção, mais ineficiente é a consulta. 
• rows_examined_avg/rows_examined_avg: para consultas de gravação. A proporção ideal é 1. Quanto maior a proporção, mais ineficiente é a consulta. 
• Quais consultas usam tabelas temporárias e precisam ser convertidas em tabelas temporárias em disco? 
• tmp_disk_tables/tmp_disk_tables: para ver se tmp_disk_tables/tmp_disk_tables é suficiente
• Quais consultas usam filesort? 
• rows_sorted/rows_sorted, rows_sorted/rows_sorted: para identificar consultas com grandes quantidades de classificação e podem usar um rows_sorted maior

Se você usa MySQL Workbench, ele tem relatórios de esquema de desempenho com base na visualização do sistema. O relatório tem uma seção sobre "Instruções SQL de alto custo", que fornece insights sobre o desempenho da consulta.

Usar registro lento + ferramentas

O registro lento captura todas as consultas em execução por mais tempo do que o long_query_time. Ele também registra o tempo de execução da consulta, o tempo de bloqueio, as linhas de dados examinadas e as linhas de dados enviadas. As outras estatísticas de execução fazem com que ele seja um candidato preferencial para analisar as consultas do banco de dados do que usar o registro geral.

É uma boa prática ativar o registro lento. Normalmente, o long_query_time precisa ser mantido em um limite razoável para capturar consultas que você pretende analisar e otimizar. 

log_output=FILE

slow_query_log=ON

long_query_time=2

Periodicamente, é recomendável definir long_query_time=0 para capturar todas as consultas por um breve período e ter uma visão geral do volume e do desempenho da consulta. 

Existem ferramentas, como mysqldumpslow e pt-query-digest, que extraem assinaturas de consulta e geram um relatório para mostrar estatísticas de consulta.   

Há outras ferramentas de monitoramento de terceiros que geram relatórios sobre estatísticas de consulta, como o Monitoramento e Gerenciamento Percona, o SolarWinds Database Performance Monitor (antigo VividCortex) e muito mais.

Depois de capturar as consultas nas transações, a próxima etapa é otimizá-las.

O comando EXPLAIN fornece o plano de execução da consulta e, a partir da versão 8.0.18, o comando EXPLAIN ANALYZE executa uma instrução e produz uma saída EXPLAIN com o tempo da execução. 

O Query Insights do MySQL fornece acesso prático ao plano EXPLAIN.

O que se deve procurar na saída? 

• O campo "rows" mostra a quantidade de linhas a serem lidas 
• A E/S é a parte mais demorada. Se uma consulta precisar ler uma grande quantidade de dados, ela provavelmente será lenta. Para ter uma ideia disso, multiplique as "linhas" entre as tabelas mescladas. No exemplo acima, é 858 * 23523. Ler 23.523 linhas na t2 de cada uma das 858 linhas na t1 soa abaixo do ideal. Por isso, a otimização seria reduzir a quantidade de acesso aos dados da t2 para cada iteração.
• O campo "type" descreve o tipo de mesclagem da tabela 
• O tipo "índice" significa que o índice é verificado. Se o índice atender a todos os dados exigidos da tabela, o campo Extra vai mostrar "Usa índice". 
• O tipo "intervalo" significa que não apenas um índice é usado, mas também que uma condição de intervalo é fornecida para limitar a verificação de dados. 
• Para tabelas subsequentes na ordem de mesclagem, o tipo "eq_ref" significa que uma linha é lida nessa tabela para cada combinação de linhas das tabelas anteriores, o que é mais eficiente. 
• O tipo "ref" significa que a correspondência de índice é 1:m em vez de 1:1. Mais de uma linha será lida nesta tabela para cada combinação de linhas das tabelas anteriores. 
• O tipo a ser evitado é "ALL". Isso significa que uma verificação de tabela completa é feita para cada combinação de linhas das tabelas anteriores. 
• O campo "key" mostra o índice que está sendo usado. 
• A seleção do índice a ser usado se baseia na cardinalidade de índice, que pode estar desatualizada. Portanto, é importante verificar se o índice mais seletivo é usado.  
• O campo "key_len" indica o comprimento da chave em bytes. 
• Com um índice de várias colunas, o key_len sugere a parte do índice que é usada. Por exemplo, se um índice tiver (col1, col2, col3), e a condição de consulta for “col1 = n and col2 like ‘%string%’”, somente col1 será usado para filtragem de índice. Se a consulta puder ser alterada para “col1 = n and col2 like ‘string%’”, ambos (col1, col2) serão usados para filtragem de índice. Essa pequena mudança pode fazer uma diferença drástica no desempenho da consulta. 
• O campo "Extra" contém mais informações sobre o plano da consulta. 
• “Using temporary” significa que uma tabela temporária interna foi criada, o que pode gerar uma tabela temporária no disco
• "Using filesort" significa que a classificação não pôde aproveitar nenhum índice e requer um buffer de classificação e, possivelmente, arquivos de disco temporários. 
• "Uses index" significa que todos os dados exigidos pela tabela estão contidos no índice; não é necessário ler linhas de dados

As variáveis de status da sessão podem ser usadas para acessar os detalhes da execução da consulta. 

Primeiro, limpe as variáveis de sessão, execute a consulta e examine os contadores. Por exemplo, o status Handler_* mostra o padrão de acesso a dados e o valor da linha. Created_* mostraria se uma tabela temporária e/ou uma tabela temporária no disco foi criada. O Sort_* mostraria o número de passes de mesclagem de classificação e a quantidade de linhas classificadas. Mais variáveis de sessão são explicadas na documentação.

A instrução SHOW PROFILE informa o tempo de execução de consulta por estágio de execução, o que também pode ser útil.

Depois que o plano de execução da consulta é compreendido, há várias maneiras de influenciá-lo e otimizá-lo. 

• Adicione ou atualize a definição do índice 
• Para ter uma filtragem melhor, menos acesso aos dados
• Para classificar, evite filesort
• Atualize as estatísticas do índice se estiverem desativadas
• ANALYZE TABLE <tbl>;
• Em seguida, verifique novamente a saída do plano EXPLAIN
• Use as dicas de índice
• Para sugerir ou forçar um determinado índice a ser usado para filtragem, mescle ou ordene por/grupo por 
• Use STRAIGHT_JOIN para definir a ordem de mesclagem de tabelas. 
• Use as dicas do otimizador

Para otimizar a configuração do servidor para determinadas consultas, é altamente recomendável usar as variáveis no nível da sessão em vez de alterar o valor global que afeta todas as sessões. 

Os valores da sessão usados com frequência são:

Resumindo, para o ajuste de consultas, falamos sobre três aspectos: 

• Tome decisões conscientes ao criar consultas. Essas decisões são o fator determinante para o desempenho da consulta, a capacidade geral do servidor e o desempenho do servidor. 
• Acompanhe os dados de execução da consulta no lado do aplicativo e do banco de dados. A geração de registros do lado do aplicativo é importante. Ela pode ser configurada com base nos interesses empresariais e refletir as operações comerciais. 
• Por fim, há várias ferramentas que ajudam a entender o plano de execução de consultas, os custos associados a diferentes etapas e maneiras de otimizar as consultas.