Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how MySQL query cache, I believe most people still do not know how, so share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

6.9 MySQL query cache

As of MySQL 4.0.1, MySQL SERVER has one important feature: Query Cache. When in use, the query cache stores the text of a select query and the corresponding results that are transmitted to the client. If the same query is received later, the server retrieves the results from the query cache rather than parsing and executing the same query again.

Note: Query cache never returns stale data. When the data is modified, any relevant terms in the query cache are flushed.

Query caching can be useful when certain tables don't change very often and you perform a lot of the same queries on them. This is typical of many web servers that use a lot of dynamic information.

Here's a performance statistic for query caching. (These results were generated by executing MySQL Benchmark Suite on a Linux Alpha 2 x 500 MHz, 2GB RAM and 64MB query cache):

If all the queries you execute are simple (e.g., selecting rows from a table); but still different, so the query cannot be buffered, the overhead is 13% when the query cache is active. This can be seen as the worst case scenario. In practice, however, queries are much more complex than our simple example, so the overhead is usually significantly lower.

After searching for one row in a table with only one row, the search is 238% faster. This may be considered to be close to the minimum speedup expected for a buffered query.

If you want to disable query caching, set query_cache_size=0. Query caching is disabled and there will be no significant overhead. (Query caching can be excluded from the outer code with the help of the configuration option--without-query-cache)

6.9.1 How Query Cache Works

Queries are compared before being analyzed, so

SELECT * FROM tbl_name

and

Select * from tbl_name

For queries cached are treated as distinct queries, queries need to be strictly consistent (byte-by-byte) in order to be considered identical. In addition, a query is considered different if one client uses a new connection protocol format or another character set different from the other client.

Queries that use different databases, different protocol versions, or different default strings will be considered different queries and buffered separately.

Cache not correct SELECT CALC_ROWS... and SELECT FOUND_ROWS()... Type queries work because the number of rows found is also stored in the buffer.

If query results are returned from the query cache, then the state variable Com_select is not incremented, but Qcache_hits is. See Section 6.9.4 Query Cache Status and Maintenance.

If a table changes (INSERT, UPDATE, Delete, TRUNCATE, ALTER or DROP TABLE)| DATABASE), then all buffered queries used by this table (possibly via an MRG_MyISAM table!) Will be invalidated and removed from the buffer.

Changes made to Innodb table transactions will invalidate the data when a COMMIT is completed.

A query cannot be buffered if it includes the following functions: Functions Functions User-Defined Functions CONNECTION_ID FOUND_ROWS GET_LOCK RELEASE_LOCK LOAD_FILE MASTER_POS_WAIT NOW SYSDATE CURRENT_TIMESTAMP CURDATE CURRENT_DATE CURTIME DATABASE ENCRYSTPT LAST_INSERT_ID RAND unix_TIMESTAMP USER BENCHMARK

If a query contains user variables, references the MySQL system database, or one of the following formats, SELECT... IN SHARE MODE, SELECT ... INTO OUTFILE ..., SELECT ... INTO DUMPFILE ... or SELECT * FROM AUTOINCREMENT_FIELD IS NULL (retrieves the last insert ID-ODBC statement), the query may not be cached either.

FOUND ROWS(), however, will return the correct value even if the previous query was read from cache.

In case a query does not use any tables, or uses temporary tables, or the user has a column permission on any related tables, the query will not be cached.

Before a query is read from the query cache, MySQL checks that the user has SELECT permissions on all relevant databases and tables. If this is not the case, cached results will not be used.

6.9.2 Query Cache Settings

Query cache adds several MySQL system variables for mysqld, which can be set in the configuration file or on the command line when starting mysqld.

query_cache_limit does not cache results greater than this value. (default is 1M)

query_cache_min_res_unit This variable has been introduced since 4.1. The results of the query (data that has been transmitted to the client) are stored in the query cache during result retrieval. Thus, the data is not processed in chunks. The query cache allocates blocks to process this data as needed, so when a block is filled, a new block is allocated. Even though memory allocation operations are expensive, the query cache allocates blocks with the smallest size query_cache_min_res_unit. When a query completes execution, the final result block is trimmed to the actual data size so that unused memory is freed.

The default value for query_cache_min_res_unit is 4 KB, which is sufficient for most data cases.

If you have many queries that return a small result, the default block size may cause memory fragmentation (shown as a large number of free blocks (Qcache_free_blocks), which causes the query cache to have to remove queries from the cache due to lack of memory (Qcache_lowmem_prunes)). In this case, you should reduce query_cache_min_res_unit.

If your primary query returns a large result set (see Qcache_total_blocks and Qcache_queries_in_cache), you can increase performance by adding query_cache_min_res_unit. However, be careful not to make it too large.

query_cache_size The amount of memory (specified in bytes) allocated to store old query results. If set to 0, query buffering is disabled (default is 0 ).

query_cache_type This can be set to (number only) Option Meaning 0 (OFF, do not cache or retrieve results) 1 (ON, cache all results except SELECT SQL_NO_CACHE... Query) 2 (DEMAND, cache SELECT SQL_CACHE only... query)

Within a thread (connection), the behavior of the query cache can be changed. The syntax is as follows:

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

0 or OFF Do not cache or retrieve results 1 or ON Cache all results except SELECT SQL_NO_CACHE... QUERY 2 or DEMAND Cache SELECT SQL_CACHE only... query

Query Cache Options in SELECT

There are two possible query cache-related parameters that can be specified in a SELECT query:

SQL_CACHE allows the query to be cached if QUERY_CACHE_TYPE is DEMAND. If QUERY_CACHE_TYPE is ON, this is the default. If QUERY_CACHE_TYPE is OFF, it does nothing SQL_NO_CACHE causes the query not to be cached and does not allow the query to be stored in the cache

6.9.4 Query Cache Status and Maintenance

Using the FLUSH QUERY CACHE command, you can defragment the query cache to make better use of its memory. This command does not remove any queries from the cache. FLUSH TABLES flushes the query cache.

RESET QUERY CACHE removes all query results from the query cache.

You can check whether the query cache in your MySQL is imported:

mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.00 sec)

In SHOW STATUS, you can monitor query cache performance:

Variable Meaning Qcache_queries_in_cache Number of queries registered in cache Qcache_inserts Number of queries added to cache Qcache_hits Number of cache samples Qcache_lowmem_prunes Number of queries removed from cache due to lack of memory Qcache_not_cached Number of queries not cached (Cannot be cached, or due to QUERY_CACHE_TYPE) Qcache_free_memory Total free memory in query cache Qcache_free_blocks Number of free memory blocks in query cache Qcache_total_blocks Total number of blocks in query cache

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

Query caches use variable-length blocks, so Qcache_total_blocks and Qcache_free_blocks may show fragmentation of query caches. After FLUSH QUERY CACHE, only one single (large) free block remains.

Note: Each query requires a minimum of two blocks (one to store query text and one or more to store query results). Similarly, each table used by a query requires one block, but if two or more queries use the same table, only one block needs to be allocated.

You can adjust the query cache size using the state variable Qcache_lowmem_prunes. It counts queries removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) policy to determine which queries to remove from the cache.

The above is "MySQL how to query cache" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.