Identificar problemas e Melhorar a Performance no MySQL

Caso o seu ambiente apresente lentidão nas respostas ou travamento, você pode realizar uma análise de vários pontos do seu banco de dados afim de tentar identificar a causa raiz.

Lista de processos

Você pode consultar os processos em execução com mais detalhes através do comando SHOW exemplificado abaixo:

SHOW PROCESSLIST;

Esse comando oferece uma visão geral de todas as conexões (threads) ativas no servidor MySQL naquele exato momento.

Ou ainda uma outra consulta mais detalhada na tabela information_schema.processlist, como mostramos abaixo:

SELECT
    pl.id,
    pl.user,
    pl.host,
    pl.db,
    pl.command,
    pl.time,
    pl.state,
    SUBSTRING(pl.info, 1, 60) AS info,
    it.trx_rows_modified
FROM information_schema.processlist pl
LEFT JOIN information_schema.innodb_trx it
ON pl.id = it.trx_mysql_thread_id;

Explicação de cada campo:

• pl.id: ID da sessão.
• pl.user: usuário que iniciou a conexão.
• pl.host: host a partir do qual a execução está ocorrendo.
• pl.db: nome do banco de dados em uso pela conexão.
• pl.command: tipo de comando sendo executado (e.g., Query, Sleep).
• pl.time: tempo de execução do comando atual, em segundos.
• pl.state: estado atual da conexão.
• SUBSTRING(pl.info, 1, 60) AS info: primeiros 60 caracteres da coluna info, que contém a informação sobre o comando em execução.
• it.trx_rows_modified: número de linhas modificadas ou inseridas pela transação.

Transações bloqueadas (lock)

É interessante verificar se existe algum bloqueio no banco de dados com longa duração. Uma forma de você tentar identificar a causa é executando este comando SELECT abaixo na tabela sys.innodb_lock_waits (que é uma view sobre sys.schema para facilitar esse tipo de análise):

A tabela sys.innodb_lock_waits apresenta uma linha para cada transação que está atualmente aguardando por um lock no InnoDB. Por padrão, os resultados são ordenados pela idade da espera, mostrando os bloqueios mais antigos primeiro.

SELECT
    waiting_pid, -- id da query bloqueada
    waiting_query, -- comando da query bloqueada
    blocking_pid, -- id da query bloqueadora
    blocking_query -- comando da query bloqueadora
FROM sys.innodb_lock_waits;

Significado das colunas:

• waiting_pid e waiting_query: Mostram qual sessão e qual comando SQL estão "presos".
• blocking_pid e blocking_query: Revelam qual sessão e qual comando estão causando o bloqueio.

Plano de Execução de uma Consulta (EXPLAIN)

Se você identificou uma consulta específica que está demorando mais tempo para retornar (lock) e possivelmente impacta nas outras sessões do servidor, é possível ainda analisar o plano de execução das suas consultas com o comando EXPLAIN abaixo. Assim você pode verificar quais consulta podem estar demorando mais e qual o motivo.

O comando EXPLAIN é a ferramenta fundamental e de primeiro uso para entender como o MySQL planeja executar uma consulta. Ele não executa a query. Em vez disso, ele consulta o otimizador do banco de dados e retorna o plano de execução da consulta.

No exemplo estamos utilizando uma simples consulta, que deve ser substituída pela sua consulta em análise.

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

No retorno desse comando retorno, é possível verificar se a consulta está fazendo uso de índices, por exemplo, e se esse filtro (cláusula WHERE) apresenta boa cobertura.

Este plano é a "receita" que o MySQL acredita ser a forma mais eficiente de buscar os dados, com base nas estatísticas que ele possui sobre as tabelas (distribuição de dados, cardinalidade dos índices, etc.).

É importante sempre considerar a criação de índices e referenciá-los na consulta para uma melhor execução. Mais informações podem ser encontradas na documentação oficial do MySQL sobre otimização de índices.

Indexação

Para melhorar a performance de suas consultas é fundamental que você faça uso de índices, como citado anteriormente.

E para criar um índice basta executar o comando abaixo, substituindo o nome da sua tabela e coluna que precisam de índice.

CREATE INDEX index_name ON table_name(column_name);

E para verifique se o índice está funcionando como esperado, rode novamente o comando EXPLAIN para analisar o desempenho da consulta após a criação do índice:

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

Otimização de consulta

Com algumas boas práticas SQL é possível evitar problemas de performance em consultas. Vamos ver algumas.

Comando SELECT sem WHERE

Evite comandos SELECT muito amplos e com potencial de devolver um número muito grande de linhas, como este:

SELECT column1, column2 FROM table_name;

O comando acima não contém cláusula WHERE, ou seja, não está filtrando a consulta. Sempre procure consultar o mínimo número de linhas possível.

Você pode também limitar os resultados utilizando o comando LIMIT, que é muito útil em trabalho exploratórios.

SELECT column1 FROM table_name LIMIT 100;

ANALYZE TABLE

Falamos acima sobre o comando EXPLAIN, que ajuda você a entender o plano de execução de uma query, mas você também pode fazer uso do comando ANALYZE se desejar obter dados estatísticos que são usados pelo otimizador do MySQL.

Usando o comando ANALYZE:

ANALYZE TABLE table_name;

As estatísticas que o ANALYZE TABLE coleta ajudam o otimizador a responder perguntas cruciais como:

• Qual índice usar? Se sua consulta filtra por cidade e data_cadastro, o otimizador usa as estatísticas para estimar qual desses filtros irá reduzir mais o número de linhas a serem lidas, escolhendo assim o índice mais seletivo.
• Vale a pena usar um índice? Se você tem uma consulta como SELECT * FROM usuarios WHERE ativo = 1; e as estatísticas mostram que 99% dos usuários estão ativos, o otimizador pode decidir que é mais rápido ler a tabela inteira (Full Table Scan) do que usar o índice e depois voltar na tabela para buscar cada linha.
• Qual a melhor ordem para juntar tabelas (JOIN)? O otimizador tentará começar pela tabela que, após a filtragem inicial, retorna o menor número de linhas, tornando as junções subsequentes mais rápidas.

Em resumo, ANALYZE TABLE "educa" o otimizador para que ele possa criar planos de execução (EXPLAIN) mais eficientes e realistas. Você pode usar esse comando para entender melhor o otimizador e então construir um schema mais eficiente para suas consultas.