About MySQL Query caching

The purpose of the query cache is that when a query receives the same query as before, the server will retrieve the results from the query cache instead of parsing and executing the last query again. This greatly improves performance and saves time.

Check if the cache is on: select @ @query_cache_type;
Disable Query caching: set session Query_cache_type=off; The settings here are only for the current setting, which is temporary

If the set session Query_cache_type=off is executed; The Times error prompts restart with query_cache_type=1 when editing the configuration file my.cnf add

[Mysqld]
query_cache_size=256m
Query_cache_type=1

Restart Service mysqld Restart

/********************************************

Query_cache_type has 3 values that represent the cache of that type of select result set, Query_cache_type each value as follows:
0 or off to close the cache
1 or on opens the cache, but does not save the SELECT statement using Sql_no_cache, such as the Select Sql_no_cache name from Wei where id=2 is not cached
2 or demand open the conditional cache, cache only SELECT statements with Sql_cache, cache select Sql_cache name from Wei where id=4

/********************************************

Mysql> Show variables like '%query_cache% ';
+------------------------------+-----------+
| variable_name | Value |
+------------------------------+-----------+
| Have_query_cache | YES |
| Query_cache_limit | 1048576 |
| Query_cache_min_res_unit | 4096 |
| Query_cache_size | 268435456 |
| Query_cache_type | On |
| Query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+

Where Have_query_cache is on, query_cache_limit specifies the size of the buffer that a single query can use, by default the minimum cache block size allocated to the system is 1m;query_cache_min_res_unit, which defaults to 4KB. Setting a large value is good for big data queries, but if your queries are small data queries, it is easy to create memory fragmentation and waste; Query_cache_size and Query_cache_type are our configuration above; Query_cache_wlock_ Invalidate indicates whether the query is returned to the cache result or wait for the write operation to complete before the table gets the result when another client is writing to the MyISAM table.

/********************************************

Mysql> Show status like ' qcache% ';
+-------------------------+-----------+
| variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 268417440 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| qcache_not_cached | 2 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+

the meanings of each parameter are as follows:  
qcache_free_blocks: The number of contiguous memory blocks in the cache. A large number indicates that there may be fragmentation. FLUSH QUERY Cache organizes the fragments in the cache to get a free block.  
qcache_free_memory: Free memory in the cache.  
qcache_hits: Increases   Each time a query is hit in the cache;
qcache_inserts: Increases each time a query is inserted. The number of hits divided by the number of inserts is not the ratio.  
Qcache_lowmem_prunes: The number of times that the cache is out of memory and must be cleaned up to provide space for more queries. This number is best seen over a long period of time, and if the number is growing, it can mean that fragmentation is very serious, or that there is little memory. (The Free_blocks and free_memory above can tell you which is the case)  
qcache_not_cached: The number of queries that are not appropriate for caching, usually because these queries are not The SELECT statement or functions such as now () are used.  
Qcache_queries_in_cache: The number of queries (and responses) currently cached.  
qcache_total_blocks: The number of blocks in the cache.