MySQL: Otimize suas consultas com Query Cache

O QUERY CACHE armazena o texto de uma declaração SELECT junto ao seu resultado, no cliente. Quando uma declaração idêntica é recebida, mais tarde, o servidor apresenta o resultado, já pronto, em vez de fazer o trabalho de pesquisa novamente.
O query cache é compartilhado entre sessões e, portanto, pode ser aproveitado por diversos outros clientes.

Quando o Query Cache pode ser útil?

Imagine um ambiente em que haja múltiplas tabelas, que não sofram muitas mudanças, para as quais o servidor receba várias consultas idênticas. Em uma situação como esta, é mais rápido fornecer a resposta pronta do que pesquisar a mesma coisa várias vezes.
O query cache não retorna dados velhos ou vencidos. Quando as tabelas são alteradas, qualquer entrada relevante no query cache é descartada.
Até as versões atuais do MySQL, há certas limitações:

• O query cache não funciona em ambiente onde haja múltiplos servidores mysqld atualizando tabelas MyISAM.
• O query cache não suporta tabelas particionadas e é automaticamente desabilitado nestes casos. Você não pode habilitá-lo nestes casos.

O query cache não é “ciência exata” e sua eficiência pode variar em função da carga de trabalho a que o servidor está submetido.

Como funciona o Query Cache?

Antes de serem executadas, os textos das consultas são comparados aos que já se encontram armazenados no query cache, que é sensível à caixa. Portanto:

SELECT * FROM nome_da_tabela

select * from nome_da_tabela

são duas queries diferentes.
As consultas têm que ser exatamente as mesmas (em cada byte) para serem reconhecidas como idênticas.
Há outros casos em que queries, mesmo idênticas, serão consideradas diferentes:

• quando usarem bancos de dados diferentes;
• ao usarem versões divergentes de protocolo;
• ao ter diferentes default character set.

O cache vai armazenar as queries, inseridas nos casos acima, separadamente.
O cache não vai aceitar queries dentro das seguintes condições:

• se refere a uma função definida por usuário — User Defined Function ou UDF;
• em que sejam executadas de dentro de uma stored function, uma trigger ou um evento;
• se refere a variáveis definidas por usuário ou programas locais;
• se refere às tabelas dentro dos bancos de dados MySQL, INFORMATION_SCHEMA ou performance_schema;
• não usa tabelas;
• usa tabelas temporárias;
• gera avisos;
• o usuário não tem privilégios suficientes para sua completa execução;

Agora, que já terminei de explicar como o query cache não funciona, vou tentar explicar aonde ele vai funcionar – o que é bem mais simples.
Antes de entregar o resultado de uma query, o MySQL verifica se o usuário tem privilégios de SELECT referentes aos bancos de dados e tabelas envolvidos. Se não, o resultado armazenado no cache não poderá ser usado.
O query cache também funciona entre transações, quando você estiver usando tabelas InnoDB.
A partir da versão 5.7 do MySQL, o resultado de uma consulta SELECT em uma VIEW será armazenada no cache.

Como configurar

É possível configurar o valor do query cache

• no terminal do seu sistema, com o comando mysqld;
• no arquivo de configuração my.cnf (permanentemente);
• no cliente do MySQL.

Neste texto, vou mostrar como configurar o query cache direto no cliente MySQL. Abra um terminal e autentique-se no servidor:

mysql -u root -p

Dentro do MySQL, verifique se o query cache está disponível:

SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

Para a gente pode brincar, aqui, o valor da variável have_query_cache precisará ser igual a YES, mesmo que o query cache esteja desativado.
Dentro do MySQL ajuste o tamanho do query cache através da definição da variável de sistema query_cache_size:

SET GLOBAL query_cache_size = 40000

Para desabilitar a função, ajuste o valor de query_cache_size para 0. Seja cuidadoso com o tamanho total da variável. As threads precisam trancar o cache durante sua atualização — caches muito grandes ficam mais tempo trancados.

Textos relacionados:

• O comando SELECT — conheça várias dicas de uso do comando, no MySQL.
• Data types — os tipos de dados possíveis para criar campos em uma tabela.
• Storage engines — conheça os mecanismos de armazenamento do MySQL.

Que tamanho usar pro query cache

O manual do MySQL adverte que o tamanho mínimo possível para query_cache_size é 40Kb, para que ele possa alocar suas estruturas.
Já o tamanho adequado depende da arquitetura do seu sistema. Como já foi dito, valores muito altos podem causar mais prejuízos do que benefícios — uma vez que aumentam o tempo em que o cache fica indisponível a cada atualização.
Valores muito pequenos podem causar a exibição de uma mensagem de aviso e o desativamento do query cache. Veja:

SET GLOBAL query_cache_size = 40000;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

SHOW WARNINGS\G

*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect query_cache_size value: '40000'
*************************** 2. row ***************************
  Level: Warning
   Code: 1282
Message: Query cache failed to set size 39936;
new query cache size is 0
2 rows in set (0.01 sec)

Na última linha da mensagem, o sistema avisa que alterou o valor da variável query_cache_size para 0.
Neste caso, você deve aumentar gradualmente o valor da variável e testar o seu sistema por alguns dias.
O manual da versão 5.7 do MySQL recomenda um valor mínimo de 1000000, para que o query cache consiga guardar um número razoável de resultados de consultas.