Aprenda 7 cuidados para turbinar a performance do MySQL

Acredite: mesmo profissionais experimentados costumam deixar passar itens básicos de performance. Por isso, siga conosco a lista abaixo, que reúne: 

7 cuidados para turbinar a performance do MySQL

Os exemplos estão em linguagem PHP. Veja:

#1 Fuja do asterisco

Seja criterioso ao construir sua instrução SELECT, pois quanto mais dados são lidos das tabelas, mais tempo levará sua execução. Especialmente quando o servidor de banco de dados estiver separado do servidor de aplicação, porque os dados vão ter que passar pela rede entre os dois.

Crie o hábito saudável de sempre especificar as colunas de que vai precisar ao montar o seu SELECT.

// mau hábito

$comando = mysql_query(“SELECT * FROM usuarios WHERE id_usuario = 33”);

$dados = mysql_fetch_assoc($comando);

echo “Olá, {$d[‘nome’]}”;

// bem melhor:)

$comando = mysql_query(“SELECT nome FROM usuarios WHERE id_usuario = 33”);

$dados = mysql_fetch_assoc($r);

echo “Olá, {$d[‘nome’]}”;

// quanto maior o resultset, mais significante a diferença

#2 Adicione índices a colunas muito pesquisadas

O uso de índices não se limita às chaves primárias e chaves exclusivas (unique). Se uma coluna da sua tabela vai ser usada frequentemente em pesquisas, na maioria das vezes você deveria adicionar um índice a ela. A diferença no tempo de resposta é grande.

Esta regra se aplica também a pesquisas por conteúdo parcial da coluna. Por exemplo, quando você fornece o começo da string a ser pesquisada: “…sobrenome LIKE ‘so%'”. Ela não se aplica quando você busca uma palavra inteira dentro de um texto maior, por exemplo “WHERE descricao LIKE ‘%multibanda%'”. Neste caso você não verá melhoras com o uso de um índice normal, seria melhor usar os recursos de fulltext search.

#3 E por falar em índices, tenha estas três regras de indexação em mente

Você deve ter notado que no item anterior usamos a expressão “na maioria das vezes” na hora de recomendar a inclusão de um índice. Isto porque esta inclusão deve sempre levar em conta o custo computacional adicional gerado.

Lembre-se sempre de que quando uma tabela indexada receber um novo registro, não só a tabela mas também um ou mais índices deverão ser atualizados.

Índices bem planejados cumprem três missões importantíssimas dentro do servidor de banco:

• Eles permitem que o servidor encontre grupos de linhas em sequência ao invés de linhas separadas. Muitos pensam que a finalidade de um índice é permitir que linhas individuais sejam localizadas, mas este tipo de busca leva a buscas aleatórias no disco, que são mais lentas. Do ponto de vista da máquina, é muito mais produtivo buscar grupos de linhas, mesmo que não vá usar todas de uma vez, do que buscar as linhas uma por uma.
  
• Eles podem evitar que o servidor precise fazer uma ordenação, fazendo com que as linhas sejam lidas na ordem pretendida. A operação de ordenação é custosa; ler as linhas preordenadas é muito mais rápido.
  
• Eles possibilitam que o servidor satisfaça completamente uma pesquisa usando apenas o índice, evitando a necessidade de buscar os dados na tabela. Isto é mais conhecido como cobertura pelo índice (index covering) ou query somente pelo índice (index-only query).

#4 Tire proveito do cache de queries (Query Cache)

A maioria dos servidores MySQL ativa o cache de queries. Este é um dos métodos mais eficazes de otimização de performance, e funciona de forma transparente para o usuário/desenvolvedor. Quando uma mesma query é solicitada várias vezes, o resultado passa a ser buscado no cache, o que é muito rápido.

Mas como isto é feito de forma transparente pelo servidor, temos a tendência de nos esquecer disto. Mas podemos tomar alguns cuidados para tirar proveito deste recurso.

// desse jeito o cache NÃO vai funcionar

$resultado = mysql_query(“SELECT nome FROM usuarios WHERE data_inclusao >= CURDATE()”);

// assim funciona!

$hoje = date(“Y-m-d”);

$resultado = mysql_query(“SELECT nome FROM usuarios WHERE data_inclusao >= ‘$hoje'”);

No primeiro exemplo, o cachê de queries não vai funcionar por causa do uso da função CURDATE(). O mesmo se aplica para todas as funções não determinísticas como, por exemplo, NOW() e RAND(). Como o valor retornado pela função varia, o MySQL decide não ativar o cache para este tipo de query.

Para evitar isso, tudo o que precisamos fazer é adicionar uma linha extra de código, e o valor será determinístico, habilitando o uso do cache.

#5 Dê preferência a colunas menores. São mais rápidas

Quando se trata de bancos de dados, os recursos de disco talvez sejam o gargalo mais relevante. Manter os dados enxutos e mais compactos na maioria das vezes faz bem à performance, reduzindo a quantidade de dados buscados no disco. A documentação do MySQL tem uma descrição extensa dos Requisitos de Armazenamento para todos os tipos de dados.

Se uma tabela foi projetada para conter poucas linhas — como tabelas de parâmetros — não há razão para que a chave primária seja do tipo INT, ao invés de MEDIUMINT ou SMALLINT, ou até em alguns casos, TINYINT. Se você não vai precisar de data e hora numa coluna, use DATE ao invés de DATETIME. Mas não vá exagerar e esbarrar em limitações num ponto adiantado do ciclo de vida da aplicação: use o bom senso.

#6 Ao fazer junções, use índices em colunas do mesmo tipo de dados

Se o seu aplicativo usa muitas queries com JOIN, você deve certificar-se que as colunas usadas na junção são indexadas em ambas as tabelas. Isto afeta como o mecanismo interno no MySQL otimiza a operação.

Além disso, as colunas usadas no JOIN devem ter o mesmo tipo de dados. Por exemplo, se o seu JOIN igualar uma coluna do tipo INT de uma tabela a uma coluna do tipo DECIMAL da outra tabela, pelo menos um dos índices não poderá ser usado pelo MySQL. Até mesmo a codificação de caracteres deve ser a mesma nas colunas do tipo string.

// buscando filiais no meu Estado

$r = mysql_query(“SELECT nome_empresa FROM usuarios

    LEFT JOIN empresas ON (usuarios.estado = empresas.estado)

    WHERE usuario.id = $user_id”);

// as colunas ‘estado’ devem estar indexadas em ambas tabelas

// e ambas devem ter o mesmo tipo de dados e codificação de caracteres

// ou o MySQL pode precisar fazer um scan na tabela inteira

#7 Prefira o tipo ENUM ao VARCHAR

As colunas do tipo ENUM são muito rápidas e compactas. Internamente elas são guardadas no formato TINYINT e, ainda assim, podem conter e exibir valores de string. Isto faz delas as candidatas perfeitas para determinados campos.

Se você for usar um campo que vai variar dentro de uma lista pequena de valores, use ENUM ao invés de VARCHAR. Por exemplo, um sistema que tenha o campo “status_pedido”, e vai poder conter valores limitados a “recebido”, “em processamento”, “pago”, “enviado, “cancelado”, etc.

E então? Quantos você já conhecia? Deixe seu comentário!