Share MySQL cache query and clear command example code

This article mainly introduces the detailed explanation of the query and clear commands of MySQL cache. For some tables that do not change data frequently and have a large number of the same SQL queries, the query cache will be more useful. Friends in need can refer to it

Mysql Query Cache
The role of the query cache is that when the query receives a query that is the same as before, the server will retrieve the results from the query cache instead of analyzing and executing the last query again. query. This greatly improves performance and saves time.
1. Configure query cache
Modify the configuration file, modify query_cache_size and query_cache_type under [mysqld] (if not, add it). Among them, query_cache_size represents the size of the cache, and query_cache_type has 3 values, indicating the type of select result set to be cached. The values ​​of query_cache_type are as follows:
0 or off closes the cache
1 or on turns on the cache, but does not save it for use The select statement of sql_no_cache, if you do not cache select sql_no_cache name from wei where id=2
2 or demand to enable conditional caching, only cache the select statement with sql_cache, cache select sql_cache name from wei where id=4
Example The configuration is as follows. After the configuration is completed, restart the Mysql server.

##

query_cache_size=10M 
query_cache_type=1

You can use the following command to check whether it is enabled, where have_query_cache indicates whether it is enabled, query_cache_limit specifies the buffer size that can be used by a single query, the default is 1M; query_cache_min_res_unit The minimum cache block size allocated for the system, the default is 4KB. A large setting value is good for big data queries, but if your queries are all small data queries, it will easily cause memory fragmentation and waste; query_cache_size and query_cache_type are our configurations above. ;query_cache_wlock_invalidate indicates that when other clients are writing to the MyISAM table, if the query is in the query cache, whether to return the cache result or wait until the write operation is completed and then read the table to obtain the result.

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       | 10485760 | 
| query_cache_type       | ON    | 
| query_cache_wlock_invalidate | OFF   | 
+------------------------------+----------+ 
6 rows in set (0.00 sec)

2. Test

We execute it once first, select count(*) from wei; and then execute it again. It can be seen that the second use The time is much lower than the first execution, because the select result is read from the cache the second time.

mysql> select count(*) from wei ; 
+----------+ 
| count(*) | 
+----------+ 
| 4194304 | 
+----------+ 
1 row in set (3.92 sec) 
mysql> select count(*) from wei ; 
+----------+ 
| count(*) | 
+----------+ 
| 4194304 | 
+----------+ 
1 row in set (0.00 sec)

We can check the current cache situation through the following command

mysql> show status like 'qcache%'; 
+-------------------------+----------+ 
| Variable_name      | Value  | 
+-------------------------+----------+ 
| Qcache_free_blocks   | 1    | 
| Qcache_free_memory   | 10475424 | 
| Qcache_hits       | 1    | 
| Qcache_inserts     | 1    | 
| Qcache_lowmem_prunes  | 0    | 
| Qcache_not_cached    | 0    | 
| Qcache_queries_in_cache | 1    | 
| Qcache_total_blocks   | 4    | 
+-------------------------+----------+ 
8 rows in set (0.00 sec)

Each of them The meaning of the parameters is as follows:

• Qcache_free_blocks: The number of adjacent memory blocks in the cache. A large number indicates there may be fragments. FLUSH QUERY CACHE will defragment the cache to obtain a free block.

• Qcache_free_memory: Free memory in the cache.

• Qcache_hits: Increased each time a query hits in the cache

• Qcache_inserts: Increased each time a query is inserted. The number of hits divided by the number of insertions is the miss ratio.

• Qcache_lowmem_prunes: The number of times the cache ran out of memory and had to be purged to make room for more queries. This number is best viewed over a long period of time; if this number is growing, it may indicate severe fragmentation or low memory. (The free_blocks and free_memory above can tell you which situation it is)

• Qcache_not_cached: The number of queries that are not suitable for caching, usually because these queries are not SELECT statements or use now( ) and other functions.

• Qcache_queries_in_cache: The number of currently cached queries (and responses).

• Qcache_total_blocks: Number of blocks in the cache.

Clear cache

mysql's FLUSH syntax (clear cache)

FLUSH flush_option [,flush_option]

If you want to clear some of the internal cache used by MySQL, you should use the FLUSH command. In order to execute FLUSH, you must have reload permission.

flush_option can be any of the following:

• HOSTS This is the most used and often encountered. Mainly used to clear the host cache table. If some of your hosts change IP numbers, or if you get the error message Host... isblocked, you should clear the host table. When more than max_connect_errors errors occur continuously for a given host when connecting to the MySQL server, MySQL will block further connection requests from that host for security reasons. Clearing the host table allows the host to try connecting again.

• LOGS Close the current binary log file and create a new file. The name of the new binary log file is added to the number of the current binary file by 1.

• PRIVILEGES This is also often used. Whenever re-authorization is performed, in order to make the new permissions take effect immediately just in case, it is usually executed. The purpose is to obtain the authorization table from the database. Reload the permissions into the cache.

• TABLES Close all open tables, and this operation will clear the contents of the query cache.

• FLUSH TABLES WITH READ LOCK Closes all open tables and adds a read lock to all tables in the database until unlock tables is explicitly executed. This operation is often used for data backup.

• STATUS Reset most status variables to 0.

• ##MASTER Delete all binary log files in the binary log index file, reset the index file of the binary log file to empty, and create a new binary log file. However, this is no longer recommended. Use, changed to reset master. As you can imagine, I was very naive in the past. What could have been done with one simple command actually required several commands. The previous method was to first find out the name of the current binary log file and then use the purge operation.

• QUERY CACHE Reorganizes the query cache, eliminates fragments, and improves performance, but does not affect the existing data in the query cache. This is the same as Flush table and Reset Query Cache (will Will clear the contents of the query cache) are different.

• SLAVE It is similar to resetting replication. It makes the slave database forget the replication location of the master database, and also deletes the downloaded relay log. Like the Master, it is no longer recommended. Changed to Reset Slave. This is also very useful.

Generally speaking, Flush operations will be recorded in the binary log file, but FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK will not be recorded. Therefore, if the above operations are recorded In the binary log file, it will affect the slave database. Note: The Reset operation actually plays the role of an enhanced version of the Flush operation.

The above is the detailed content of Share MySQL cache query and clear command example code. For more information, please follow other related articles on the PHP Chinese website!