Dicas para otimizar seu MySQL

Suas consultas estáo demorando muito tempo para serem processadas? Está gerando filas de conexões? Todas as operações de banco de dados estão sofrendo muita lentidão? Se respondeu sim para qualquer uma das perguntas, então está na hora de otimizar seu Banco de Dados.  A equipe da SempiHost compartilha nesse artigo algumas dicas para otimização de seu Banco de Dados. Utilizando poucas funções MySQL, o resultado no desempenho de seu sistema pode ser surpreendente.

Dos três modos de otimização (configuração de hardware do servidor, configuração de rede do servidor e otimização de código), apresentamos aqui métodos para otimização por código MySQL.

MYSQLREPORT

Como primeiro passo, você pode usar o comando MYSQLREPORT para gerar relatórios sobre bancos de dados que estão atingindo o limite de sua capacidade de uso  e estabelecer um patamar para operações e usos adicionais. Talvez também você se veja obrigado a modificar arquivo de configuração (my.cnf) para registrar o log de consultas lentas. Identifique as consultas que estão processando muito devagar e as possíveis causas.

Para acessar a documentação completa do MYSQLREPORT, recomendamos esse link: man.he.net/man1/mysqlreport.

EXPLAIN

Aplique o comando EXPLAIN antes do script SELECT para o MySQL retornar um registro contendo a análise do script para aquela tabela consultada. Esse método retorna algumas informações, como ID, SELECT_TYPE, TABLE,TYPE, KEY_LEN, REF, sendo que as informações que você vai querer para avaliar o desempenho do código são:

• POSSIBLE_KEYS: mostra os índices disponíveis para serem utilizados naquela consulta;
• KEY: mostra o índice escolhido pelo MySQL para realizar a consulta;
• ROW: número estimado de linhas percorridas para encontrar o resultado do SELECT;
• EXTRA: sugestões de otimização em índices da consultas, como usar DISTINCT, NOT EXIST, USING INDEX, entre outras.

ANALYZE

Use o comando ANALYZE para gerar uma distribuição de chaves, para a tabela a ser utilizada pelo otimizador de consultas do MySQL, para decidir quais índices serão melhores se utilizados em uma consulta.

OPTIMIZE 

Se há grandes quantidades de inserções/exclusões em uma tabela, principalmente se a tabela conter muitos campos do tipo varchar, text e blob, o disco será comprometido à várias fragmentações. O comando OPTIMIZEdeve ser usado com freqüência para esse tipo de situação. O comando ira otimizar a movimentação da cabeça de leitura e gravação do disco durante a recuperação de dados, implementando a desfragmentação do disco causada por campos de comprimento variável.

LOAD DATA INFILE

Para a importação de dados para uma tabela, o comando LOAD DATA INFILE, no lugar do comando SELECT, irá apresentar um desempenho até 20 vezes melhor

TRUNCATE

O comando DELETE deleta linha por linha, enquanto TRUNCATE deleta todas as linhas de uma só vez. Portanto, se você não estiver interessado no número de linhas suprimidas de uma tabela, como resultado do comandoDELETE, utilize o comando TRUNCATE com a seguinte sintaxe:

Permições no MySQL

Além de incrementar na segurança da conexão, criar usuários com permissões específicas reduzem o overheaddas verificações das permissões em todos os comandos realizados no MySQL, melhorando assim no desempenho do sistema. Como exemplo, um leitor do blog apenas tem interesse na leitura de postagem, leitura e inserção de comentários. Criar um usuário de nome “leitor”, com acesso apenas a leitura da tabelas ‘postagens’, e acesso a leitura e escrita à tabela ‘comentarios’, será de fato uma boa prática. A segurança será incrementada, já que aquele usuário não terá permissão de acessar, modificar ou deletar outros registros que não são de seu respeito, como também o desempenho, já que o overhead do usuário constará suas propriedades de acessos de antemão.

BENCHMARK

Por fim, para saber quanto tempo uma determinada função ou expressão MySQL está demorando, use a função MySQL embutida:

Lembrando que, para passar consultas na função BENCHMARK, obrigatoriamente a query deve retornar 1 linha com 1 coluna.