MySQL e o Query Cache

Uma forma bem simples e rápida de melhorar significantemente a performance de consultas no MySQL é saber utilizar corretamente o cache de resultados de suas consultas.

O query cache basicamente é um local na memória onde o MySQL armazena o texto de sua consulta "SELECT" juntamente com o resultado retornado por esta consulta, teoricamente, sempre que esta consulta for executada novamente não será necessário o mysqld fazer todo o trabalho para obter os dados resultantes sendo que estes já estarão disponíveis na memória.

Vamos aprender a utilizar o query cache, em primeiro lugar precisamos saber se está disponível, para isso vamos entrar o comando:

mysql> SELECT @@have_query_cache;
+--------------------+
| @@have_query_cache |
+--------------------+
| YES |
+--------------------+
1 row in set (0.00 sec)

Bem, isso já nos diz que o query cache está disponível.

Veremos agora como ele está definido:

mysql> show variables like '%query_cache%';
+------------------------------+--------+
| Variable_name | Value |
+------------------------------+--------+
| have_query_cache | YES |
| query_cache_limit | 524288 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 128000 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+--------+
6 rows in set (0.00 sec)

O query_cache_size, nos diz o tamanho em bytes reservado para o query cache, neste caso 128Mb

O query_cache_type irá informar o tipo de cacheamento que estamos utilizando, ele pode ser:

0 ou OFF; Não armazena nem busca resultados do query cache

1 ou ON; Armazena no cache o resultado de todas as consultas válidas para o cacheamento, exceto quando seguidas pela cláusula SQL_NO_CACHE

2 ou DEMAND; Armazena apenas os resultados de consultas válidas e seguidas por SQL_CACHE

O query cache em ação:

Em primeiro lugar se o query_cache_size for 0 então vamos definir seu valor e conseqüentemente será habilitado.

mysql> SET GLOBAL query_cache_size=128000;

O que acabamos de fazer ?
Definimos nosso query cache para ter 128 Mb, isso quer dizer que teremos todo este espaço disponível para armazenar o resultado de nossas consultas. Esse valor escolhi apenas para testes e não significa que seja o valor ideal.

Vamos utilizar uma consulta simples para testar o query cache, estou utilizando um banco de dados específico que em breve estarei disponibilizando aqui no blog, por enquanto veja o resultado:

1- Primeira vez que executo meu SELECT com o query cache habilitado

mysql> SELECT pedido.id, pedido.data, cliente.nome, cliente.sobrenome, cliente.email FROM pedido, cliente WHERE cliente.id=pedido.cliente_id;

............

100 rows in set (0.11 sec)

Suprimi as linhas para economizar o espaço e o SELECT está bem intuitivo e básico para que você entenda como estamos pesquisando.
O resultado foi retornado em 0.11 segundos

2- Vamos executar novamente o mesmo SELECT e observar quanto tempo irá demorar para sair o resultado.

100 rows in set (0.00 sec)

Ótimo. Agora a mesma consulta demorou 0.00 segundos mas o que aconteceu ?

Bem, isso é o nosso query cache trabalhando para armazenar o resultado de nossa consulta, como foi dito antes, na segunda vez que executei o mesmo SELECT o resultado foi retirado diretamente do query cache e não foi necessário nenhuma execução interna para obte-los.

Vamos analisar algumas variáveis referentes ao query cache e aprender mais sobre elas.

Digite o comando:

root@localhost livraria> SHOW STATUS LIKE 'Qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 112400 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 6 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 5 |
+-------------------------+--------+
8 rows in set (0.01 sec)

É importante observar que em Qcache_hits está mostrando quantas vezes utilizamos o conteúdo que está no query cache, e em Qcache_inserts temos a quantidades de resultados que foram inseridos em nosso cache, a cada nova query válida para para preencher o query cache este valor é incrementado.

Ao executar novamente a mesma consulta observamos que nosso Qcache_hits é incrementado.

Quando estamos utilizando o query cache temos que observar também o valor contido em Qcache_lowmem_prunes, esse valor quanto mais próximo de zero mais satisfatório para o bom uso do query cache. Essa variável é responsável por indicar quantas consultas foram "deletadas" do query cache devido a baixa memória definida.

O que não vai para o meu query cache?

1- Prepared Statements
2- Subqueries que se originam de uma OUTER query
3- Queries executadas dentro do corpo de uma função ou triggers
4- Todas as queries que utilize NO_CACHE como por exemplo, SELECT .... FROM .... NO_CACHE;
5- Resultados retornados por funções como SELECT NOW(), BENCHMARK etc.

Outra observação sobre o query cache é que a forma como o MySQL trara o query cache é totalmente case sensitive, ou seja, uma query SELECT * FROM LIVROS, é totalmente diferente de uma query Select * from LiVrOS e assim por diante.

O quanto devo reservar para meu query cache?

Isso irá depender de alguns fatores como: quanto de memória eu tenho no equipamento ? quantas consultas estou utilizando que serão executadas repetidamente ?

No próximo artigo veremos mais detalhes sobre o query cache e como definí-lo corretamente. Até lá.