MySQL – Melhorando o desempenho com tabelas MEMORY

21/04/2008  1 comentário

Quem já teve que trabalhar com grandes volumes de dados no MySQL, já se perguntou como melhorar o desempenho de consultas longas e repetivas. E sabe como é difícil.

Este post nasceu de várias perguntas que me fiz enquanto procurava o gargalo em um sistema de verificação de inconsistências que gastava 5 horas analisando as mesmas tabelas. Fiz algumas contas e constatei que gastava menos de 0,1 segundos em cada QUERY (nada mal). Mas algo tinha que ser feito!

Imaginar como melhorar o desempenho de um JOIN de campos CHAR entre três tabelas, uma com 200k linhas, outra com 5k linhas e outra com 50 linhas não é fácil. A primeira saída é otimizar o banco, com a criação de índices e adequação dos campos ao conteúdo (INT, CHAR, VARCHAR, …). Mas mesmo assim não estava funcionando no meu caso. Nada que uma visita na documentação do MySQL não resolva os problemas.

Na documentação descobri que exatamente para estes casos o MySQL possui um tipo especial de tabela, o MEMORY. Ela possui algumas limitações, porém é imcomparável quando o assunto é velocidade.

Algumas limitações:

  • Não suporta campos do tipo BLOB e TEXT (mais uma desculpa para otimizar o banco);
  • Os registros ocupam um tamanho fixo na memória. Assim, um campo INT vai sempre ocupar 4 bytes de memória, mesmo que você o use como um TINYINT;
  • Você precisa de muita memória RAM disponível no servidor (devido ao caso acima);

Como você pode precisar de uma quantidade de memoria RAM absurda, o MySQL limita a quantidade de memória até o valor definido na variável max_heap_table_size. O valor default é 16Mb, mas o céu é o limite.

Apesar de possível, não é recomendada a utilização de uma tabela MEMORY para armazenar dados pois as informações serão perdidas em caso de falta de energia. Considere sua utilização apenas em casos onde:

  • você tenha muitas pesquisas em um curto espaço de tempo;
  • Os dados sejam estáticos, sem variações (nada de UPDATES)

É uma boa pedida para uma STORE PROCEDURE que replica algumas informalções de uma tabela normal (MYSAM, por exemplo) para uma MEMORY, melhorando o desempenho daquela QUERY complexa.

Ainda neste semana, vou apresentar um tutorial de como criar uma tabela MEMORY com o PHP, replicando as informações de outra tabela. Não percam!!!