Identificar problemas e Melhorar a Performance no MySQL

Caso o seu ambiente apresente algum tipo de lentidão ou travamento, é interessante verificar se existe algum bloqueio no banco de dados com longa duração. Uma forma de você tentar verificar isso seria executando o comando abaixo:

sql

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;

Caso ainda não traga as informações desejadas, você pode consultar os processos em execução com mais detalhes através da consulta abaixo:

sql

SHOW PROCESSLIST;

Ou:

sql

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;

• 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.

Uma vez identificada uma sessão mais demorada e com impacto nas demais sessões, é possível checar se o plano de execução está adequado. Nesse retorno, é possível verificar se a consulta está fazendo uso de índice e se esse filtro apresenta boa cobertura.

sql

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

É 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.

1. Indexação

Criar índices:

sql

CREATE INDEX index_name ON table_name(column_name);

Verifique o uso do índice:

sql

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

2. Otimização de consulta

Evite SELECT:

sql

SELECT column1, column2 FROM table_name;

• Use JOINs com eficiência: certifique-se de que os índices adequados estejam em vigor para as colunas usadas em JOINs.

Limitar resultados:

sql

SELECT column1 FROM table_name LIMIT 100;

3. Análise e perfil

Use o comando EXPLAIN:

sql

EXPLAIN SELECT column1 FROM table_name WHERE column2 = 'value';

Use o comando ANALYZE:

sql

ANALYZE TABLE table_name;