Optimizar consultas em MySQL com query cache

Hoje vamos falar de uma ferramenta bem bacana do MySQL, o query cache.
o query cache salva resultados de SELECT’s já executados e que seus dados brutos não tenham sido alterados tornando assim o tempo de resposta da query muito mais otimizada, pois ele vai buscar da memória e não do disco.
Ele pode trabalhar de 3 maneiras diferentes, no nosso my.ini dentro do grupo [mysqld]:
query_cache_type = 0
Desligado
query_cache_type = 1
Ligado para todas as query’s
query_cache_type = 2
Ligado sobre demanda

Mas qual a melhor maneira de utilizar o query cache?
Tendo em vista que toda a vez que você altera dados em alguma tabela, ele invalida o cache da mesma, o mais indicado é você fazer um mapeamento da frequencia de atualização das tabelas e utilizar o cache para as que não tenham uma frequencia grande a atualização / inserção, para que assim, o cache lhe dê um ganho de performance, e não fique sobrecarregando o servidor tendo que invalidar muitas querys.
Por exemplo, a tabela de registros de log do sistema, a cada ação do usuário ele irá inserir um novo dado nesta tabela, logo ela não é uma boa candidata a ser cacheada, já a tabela de noticias do site, recebe atualizações 2 vezes ao dia, tá aí, uma boa candidata a ser cacheada,

Vou mostrar na prática que resultados conseguimos alcançar com o query cache:

estou com ele setado para ON DEMAND – query_cache_type = 2
Possuo uma tabela com 100 milhões de registros, para a opção ON DEMAND temos que especificar no select que queremos que ele utilize o cache com SQL_CACHE:

    Rodando a consulta pela primeira vez:

mysql> SELECT SQL_CACHE * FROM noticia WHERE conteudo LIKE "%8555556%";
. . .
20 rows in set (2 min 15.20 sec)

Neste momento, esquentamos o cache com a nossa consulta, ele demorou o tempo padrão, porém colocou-a em cache, veja o resultado da mesma query:
mysql> SELECT SQL_CACHE * FROM noticia WHERE conteudo LIKE "%8555556%";
...
20 rows in set (0.00 sec)

A mesma consulta demorou 0 segundos para trazer os resultados, pois estava guardada em cache.
Mas qual o tamanho do cache de consulta?
Como tudo no MySQL, ele é configurável através da opção query_cache_size, valores em torno de 16M são recomendados, dependendo da capacidade do seu servidor.

Caso você faça a analise e veja que a maioria das suas tabelas devem ser cacheadas, vocÊ pode usar o processo inverso, ou seja, setar a variavel query_cache_type = 1 isso fará com que todas as consultas sejam cacheadas e expecificar nos selects que não devem cachear com SELECT SQL_NO_CACHE * FROM ...
Utilizar qualquer uma das funções abaixo inutiliza o cache na consulta:
BENCHMARK() CONNECTION_ID() CONVERT_TZ()
CURDATE() CURRENT_DATE() CURRENT_TIME()
CURRENT_TIMESTAMP() CURTIME() DATABASE()
ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK()
LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT()
NOW() RAND() RELEASE_LOCK()
SLEEP() SYSDATE() UNIX_TIMESTAMP(
USER() UUID() UUID_SHORT()

Lembrando que para a utilização do query cache, select * from ... é diferente de SELECT * FROM ...
Mais informações podem ser encontradas no site da MySQL

Gostou ? Ajude e Compartilhe!
Esta entrada foi publicada em MySQL, optimização, performance, query cache. Adicione o link permanente aos seus favoritos.
  • Pingback: B11()

  • Denys

    Funcionou! Valeu!

  • Pedro Schneider

    Olá Marcelo!

    Me surgiu uma duvida, as variáveis “query_cache_limit” e “query_cache_size”, são de acordo com as configurações do servidor, quais os cálculos devo fazer para uma melhor usabilidade dessas variáveis?

    • Marcelo Altmann

      Fala Pedrão.
      Tudo certo ?

      Cara, sim, essas configurações são de acordo com cada servidor. Porém hoje em dia é muito comum se assumir que o Query Cache deve ser desligado.
      1) Se tu tem alterações frequentes na tabela, toda alteração vai ter que invalidar todas as queries que estão em cache para esta tabela, visto que os resultados vão mudar.

      2) Se tu tens um volume muito grande de transações acontecendo ao mesmo tempo, vai sofrer com problemas de performance, pois o query cache é um espaço de memória único e vais ter problemas com lock.

      Dá uma lida neste artigo aqui https://www.percona.com/blog/2015/08/07/mysql-query-cache-worst-enemy-best-friend/ e roda alguns testes no teu ambiente.

      Abs.

      • Pedro Schneider

        Obrigado, vou fazer uns testes aqui.