MySQL Server: 6 DICAS INFALÍVEIS para ADMINISTRAR de forma EFICIENTE seu BANCO DE DADOS

fevereiro 6, 2022fevereiro 6, 2022 Eduardo Pereira 0 comentários create index, mysql, mysql create table, mysql database, mysql server, show processlist, show variables

Você já precisou resolver algum problema de banco de dados em produção? Sim ou com certeza? Então aqui vai MySQL Server: 6 DICAS INFALÍVEIS para ADMINISTRAR de forma EFICIENTE seu BANCO DE DADOS!

Você prefere esse conteúdo em vídeo? Então confere aqui:

Mostrar consultas acontecendo no seu MySQL

Bom, para fazer isso, logado no MySQL via terminal você vai digital o comando show process list; e a coluna INFO será onde a consulta será exibida, veja abaixo:

Aqui conseguimos ver que existe um select SLEEP rodando que pode atrapalhar o desempenho do nosso banco de dados.
Se por um acaso esse comando não exibir toda a consulta no campo Info você pode usar o comando show full processlist; para exibir ela por completo.

Matar uma consulta no MySQL

Caso você identifique uma consulta lenta com o comando acima, e você precisar matar essa consulta, você pode usar o comando kill + o valor do campo Id para finalizar a consulta, veja:

A única coisa que eu realmente recomendo é tomar cuidado com o kill em operações com update, delete e inserts pois isso pode gerar vários problemas para você, e se uma operação de update está rodando a 45 minutos, ao dar o kill nesse update, pode ser que demore mais 45 minutos para ele finalizar, e se isso acontecer e DE JEITO NENHUM REINICIE SEU MYSQL, as coisas só vão piorar!

Mostrar variáveis do MySQL

Antes de mais nada AQUI tem um link que leva para a pagina oficial do MySQL explicando o que cada variável dessa faz!

O MySQL possuí em torno de 650 variáveis e para exibir ela você pode usar o comando show variables; caso você queira procurar por uma variável específica você pode usar o show variables like “%log%”; para pesquisar no MySQL todas as variáveis que contém a palavra log ou se você souber exatamente qual é a variável que você busca use show varibales where Variable_name=’general_log’;

Alterar variável no MySQL

Algumas variáveis no MySQL são de escopo dinâmico e podem ser alteradas em tempo de execução do servidor sem a necessidade de reiniciar o banco MySQL.
Nesse exemplo vamos habilitar a variável general_log para o MySQL começar a mostrar todas as consultas que estão acontecendo no servidor.

Para fazer isso use o comando set global general_log=1; MAS ATENÇÃO, não recomendo deixar essa variável habilitada por muito tempo em servidores de produção, isso vai gerar uma quantidade de I/O bem grande no seu servidor! =)

Mostrar consultas lentas no seu servidor MySQL

Muitas vezes você vai estar com algum problema no seu servidor de produção e vai precisar achar quais consultas estão lentas e logar ela em algum lugar.
Como o MySQL tem variável para tudo, vamos habilitar duas, a slow_query_log; que vai mostrar as consultas lentas baseado no tempo do campo slow_launch_time que é em segundos que por default o MySQL usa 2 segundos.

Outra variável que pode ser habilitada e é muito importante é a log_queries_not_using_index que vai mostrar todas as consultas que não estão usando index no seu banco de dados e varrendo tabelas por inteiro!

E todas essas informações vão parar em um arquivo de texto que está definido no campo slow_query_log_file, veja o exemplo abaixo:

Como criar e deletar um index em uma tabela do banco de dados

Bom, para agilizar as consultas do seu servidor, você pode criar index nas tabelas fazendo com que o MySQL não precise “varrer” a tabela completa para conseguir achar o registro e consequentemente retornando os dados bem mais rápido.

Dessa forma, imaginamos que temos um banco de dados chamado parque com uma tabela equipamentos. Essa tabela contém as colunas id, nome, ano, calssificacao_etaria.

Para criar um INDEX nesse banco de dados, precisamos primeiro pensar na consulta. Qual coluna eu vou usar na condição WHERE? Essa campo é alterado constantemente pelo meu sistema ( Ex: dada de ultimo acesso que toda vez que o cliente acessa o sistema é feito um update ) ?

Aqui no caso eu vou usar a coluna nome, e ele não é alterado frequentemente pelo meu sistema, então para criar o INDEX executamos:

create index nome on parque.equipamentos(nome);

Se você executar o comando show index from equipamentos; você consegue conferir se realmente o index foi criado.

Agora, quando uma consulta for executada com a coluna nome na condição where a tebela não vai ser “varrida” por completo, demorando muito menos para retornar os dados dessa consulta.

Existe também uma forma de deletar esse INDEX caso você identifique que ele não está sendo usado, veja:

drop index nome on parque.equipamentos;

Perceba que agora ao executar o comando show index from equipamentos; o index name não vai estar mais lá:

Por fim, acredito que esses comandos/procedimentos acima vão ajudar você a administrar seu banco de dados e resolver problemas do dia a dia.