The actual application code of MySQL to query Cache

The following articles mainly describe the actual operation steps for MySQL to query cache and the description of more than 20 application codes of MySQL to query cache, the following describes how to view the cache size and monitor its performance.

 
 

  
  
MySQL> select @@query_cache_type;  
  
  
+--------------------+  
  
  
| @@query_cache_type |  
  
  
+--------------------+  
  
  
| ON |   
  
  
+--------------------+  
  
  
MySQL> set query_cache_type=off;  
  
  
MySQL> set query_cache_type=on;  
  
  
MySQL>   
  
  
MySQL> select sql_cache id, title, body from article;  
  
  
MySQL> select sql_no_cache id, title, body from article;  
  
  
MySQL> show variables like 'have_query_cache';  
  
  
+------------------+-------+  
  
  
| Variable_name | Value |  
  
  
+------------------+-------+  
  
  
| have_query_cache | YES |   
  
  
+------------------+-------+  
  
  
1 row in set (0.00 sec)  
 
 

View the cache size of a MySQL Query

 
 

  
  
MySQL> select @@global.query_cache_size;  
  
  
+---------------------------+  
  
  
| @@global.query_cache_size |  
  
  
+---------------------------+  
  
  
| 16777216 |   
  
  
+---------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select @@query_cache_size;  
  
  
+--------------------+  
  
  
| @@query_cache_size |  
  
  
+--------------------+  
  
  
| 16777216 |   
  
  
+--------------------+  
  
  
1 row in set (0.00 sec)   
 
 

View the maximum number of cached results. If the number of result sets is greater than this value, the results are not cached.

 
 

  
  
MySQL> select @@global.query_cache_limit;  
  
  
+----------------------------+  
  
  
| @@global.query_cache_limit |  
  
  
+----------------------------+  
  
  
| 1048576 |   
  
  
+----------------------------+  
  
  
1 row in set (0.00 sec) 
 
 

Fragment

 
 

  
  
MySQL> flush query cache  
  
  
-> ;  
  
  
Query OK, 0 rows affected (0.00 sec)  
 
 

Clear Cache

 
 

  
  
MySQL> reset query cache  
  
  
-> ;  
  
  
Query OK, 0 rows affected (0.00 sec) 
 
 

Monitor MySQL query cache performance:

 
 

  
  
MySQL> flush tables;  
  
  
Query OK, 0 rows affected (0.04 sec)  
  
  
MySQL> show status like 'qcache%';  
  
  
+-------------------------+----------+  
  
  
| Variable_name | Value |  
  
  
+-------------------------+----------+  
  
  
| Qcache_free_blocks | 1 |   
  
  
| Qcache_free_memory | 16768408 |   
  
  
| Qcache_hits | 6 |   
  
  
| Qcache_inserts | 36 |   
  
  
| Qcache_lowmem_prunes | 0 |   
  
  
| Qcache_not_cached | 86 |   
  
  
| Qcache_queries_in_cache | 0 |   
  
  
| Qcache_total_blocks | 1 |   
  
  
+-------------------------+----------+  
  
  
8 rows in set (0.06 sec) 
 
 

Check the number of items in the current cache:

 
 

  
  
MySQL> show status like 'qcache_q%';  
  
  
+-------------------------+-------+  
  
  
| Variable_name | Value |  
  
  
+-------------------------+-------+  
  
  
| Qcache_queries_in_cache | 0 |   
  
  
+-------------------------+-------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> select sql_cache id, title, body from article;  
  
  
MySQL> show status like 'qcache_q%';  
  
  
+-------------------------+-------+  
  
  
| Variable_name | Value |  
  
  
+-------------------------+-------+  
  
  
| Qcache_queries_in_cache | 1 |   
  
  
+-------------------------+-------+  
  
  
1 row in set (0.00 sec)  
  
  
MySQL> show status like 'qcache_f%';  
  
  
+--------------------+----------+  
  
  
| Variable_name | Value |  
  
  
+--------------------+----------+  
  
  
| Qcache_free_blocks | 1 |   
  
  
| Qcache_free_memory | 16766728 |   
  
  
+--------------------+----------+  
  
  
2 rows in set (0.00 sec)  
 
 

The above content is an introduction to MySQL query cache. I hope you will get some benefits.