Menu fechado

Optimizar consultas em MySQL com query cache

 

EDIT: Cuidado. Query Cache pode causar problemas de performance e escalabilidade em ambientes com muitas sessões simultâneas. Use com cuidado.

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!