MySQL Query cache sample code _ MySQL

The following articles mainly describe the actual application code of MySQL cache query and the description of MySQL cache query size, fragmentation, Cache clearing, and monitoring of MySQL cache query performance, the following is a description of the specific content. I hope you will query MySQL in your future study.

The following articles mainly describe the actual application code of MySQL cache query and the description of MySQL cache query size, fragmentation, Cache clearing, and monitoring of MySQL cache query performance, the following is a description of the specific content. I hope it will be helpful for your future study.

 
 

  
  
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.