MySQL's query cache

MySQL query caching mechanism principle Querycache,querycache is based on SQL statements to cache. If a SQL query starts with SELECT, the MySQL server tries to use QC for it. Each cache is saved as a key with SQL text. SQL text will not be processed until Querycache is applied. In other words, two SQL statements, as long as the difference is even one character (such as the case is not the same as a space, and so on), then the two SQL will use a different querycache.

However, SQL text is likely to be handled by the client. For example, on the official command line client, before sending SQL to the server, the following processing is done:
Filter all comments
Remove SQL text before and after the space, tab and other characters. Note that the text is front and back. The middle will not be removed.

In the following three SQL, because of the Select Case relationship, the last and two other storage locations that are definitely used in the QC section. And the first and second, the difference is that the latter has a note, in different clients, there will be the same result. So, to be on the safe side, please try not to use dynamic annotations. In PHP's MySQL extensions, SQL annotations are not removed. That is, three of SQL is stored in three different caches, although their results are the same.
SELECT * from people where name= ' surfchen ';
SELECT * from people where/*hey~*/name= ' surfchen ';
SELECT * from people where name= ' surfchen ';

Currently only SELECT statements will be cache, other similar show,use statements will not be cache.

Because the QC is so front-end, so simple a caching system, so if a table is updated, then the table related to the SQL of all the QC will be invalidated. Suppose a union query involves table A and table B, and if either table A or one of the table B is updated (update or delete), the QC of the query will be invalidated.

In other words, if a table is frequently updated, consider whether or not you should be QC the relevant SQL. A frequently updated table, if applied Querycache, can add to the burden of the MySQL database rather than reduce the MySQL burden. My general practice is to turn on Querycache by default, and disable cache for SQL statements that involve frequently updated tables, plus sql_no_cache keywords. This avoids unnecessary memory operations as much as possible and keeps the memory as continuous as possible.

You should also not use caching for SQL statements that are very decentralized queries. For example, use MySQL to query user and password statements--"Select pass from user where name= ' surfchen '". Such statements, in a system, are likely to be used only when a user logs on. The queries used for each user's login are different SQL texts, and Querycache is almost useless here, because cached data is almost never used, and they only take up space in memory.
Storage block
In this section, "Storage Block" and "blocks" are the same meaning

Querycache caching A query result, it is generally not a one-time allocation to allocate enough memory to cache the results. But in the process of obtaining the results of the query, block storage. When a block of storage is filled, a new block of storage is created and allocated memory (allocate). The memory allocation size of a single storage block is controlled by the Query_cache_min_res_unit parameter, and the default is 4KB. The last storage block, if not fully utilized, the unused memory will be released. If the result of the cache is large, this may result in allocating memory operations too frequently and system systems to degrade, and if the cached results are small, it can cause excessive memory fragmentation that, if too small, is likely to be no longer allocated.

In addition to the query results that require storage blocks, each SQL text also requires a block of storage, and the table involved requires a storage block (the table's storage block is shared by all threads, and each table requires only one storage block). Total storage block = Query Result quantity *2+ The number of database tables involved. That is, when the first cache is generated, it requires at least three blocks of storage: The table Information store block, the SQL text storage block, and the query result storage block. The second query, if it uses the same table, requires at least two blocks of storage: The SQL text block, the query result storage block.

By observing Qcache_queries_in_cache and qcache_total_blocks, you can know the average storage block that each cache result occupies. Their proportions, if they are close to 1:2, indicate that the current query_cache_min_res_unit parameter is large enough. If there are many more qcache_total_blocks than Qcache_queries_in_cache, you need to increase the query_cache_min_res_unit size.

Qcache_queries_in_cache*query_cache_min_res_unit (The block of SQL text and table information occupies a small memory that can be ignored) if it is much larger than the query_cache_size-qcache_ Free_memory, you can try to reduce the value of query_cache_min_res_unit.
Adjust size
If the qcache_lowmem_prunes grows fast, it means that many caches are freed because of insufficient memory, rather than because the related tables are updated. Try to increase query_cache_size and try to make qcache_lowmem_prunes zero.
Startup parameters
Show variables like ' query_cache% ' can see this information.
Query_cache_limit: If a single query result is greater than this value, then no cache
Query_cache_size: The memory assigned to the QC. If set to 0, the equivalent is to disable QC. Note that the QC must use about 40KB to store its structure, and if the setting is less than 40KB, it is equivalent to disabling the QC. The minimum unit for QC storage is 1024 byte, so if you set a value that is not a multiple of 1024, this value is rounded to the nearest value equal to the current value of 1024.
query_cache_type:0 Complete prohibition of QC, not controlled by SQL statements (it may also be noted that even if this is disabled, the memory size set by one of the above parameters will be allocated), 1 enable QC, can be disabled in SQL statements, and 2 can be enabled using Sql_cache in SQL statements.
Query_cache_min_res_unit: Allocate the memory size to the QC results each time
State
Show status like ' qcache% ' can see this information.
Qcache_free_blocks: When a table is updated, the cache blocks associated with it will be free. However, the block may still exist in the queue unless it is at the end of the queue. These blocks will be counted to this value. You can use the Flush QUERY cache statement to empty the free blocks.
Qcache_free_memory: Available memory, if very small, consider adding query_cache_size
Qcache_hits: Number of cache hits since the MySQL process started
Qcache_inserts: Number of QC additions since the MySQL process started
Qcache_lowmem_prunes: Number of strips of QC being deleted due to too little memory. Increase query_cache_size and keep this value as much as 0 growth.
Qcache_not_cached: The number of read-only queries without cache (including SELECT,SHOW,USE,DESC, etc.) since the MySQL process started
Qcache_queries_in_cache: Current cache number of SQL
Qcache_total_blocks: Number of blocks in QC. A query may be stored by multiple blocks, and the last of these blocks, unused memory will be released. For example, a QC results to 6KB memory, if the Query_cache_min_res_unit is 4KB, then will eventually generate 3 blocks, the first block to store the SQL statement text, this will not be counted into the query+cache_size, The second block is 4KB, the third block is 2KB (allocate4kb first, then the extra 2KB is released). Each table uses a block to store table information when the first SQL query associated with it is cache. In other words, block will be used in three places: table information, SQL text, query results.

Another article:

If the MySQL Server load is high and processing is very busy, you can start query Cache to speed up response time, and the startup method can add the following items to MY.CNF (Linux) or My.ini (Windows): (Redhat below:/etc /my.cnf;debian and Ubuntu are in/etc/mysql/my.cnf)

Query_cache_size = 268435456
Query_cache_type = 1
Query_cache_limit = 1048576

The above statement in the settings query_cache_size is allocated 256M memory to query cache;query_cache_type=1, is to do all the query Cache;query_cache_limit Is the memory that specifies the 1MB of the individual query statement.

This data can make the appropriate changes according to their own needs, after the setup is complete, save the document, restart MySQL.

Query_cache_type 0 Delegates do not use buffering, 1 delegates use buffers, and 2 delegates are used as needed.

Setting 1 means that buffering is always valid, and if you do not need a buffer, you need to use the following statement:

SELECT Sql_no_cache * from my_table WHERE ...

If set to 2, you need to turn on the buffer, you can use the following statement:

SELECT Sql_cache * from my_table WHERE ...

Use Show status to view the buffering situation:

Mysql> Show status like ' qca% ';
+-------------------------+----------+
| variable_name | Value |
+-------------------------+----------+
| Qcache_queries_in_cache | 8 |
| Qcache_inserts | 545875 |
| Qcache_hits | 83951 |
| Qcache_lowmem_prunes | 0 |
| qcache_not_cached | 2343256 |
| Qcache_free_memory | 33508248 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 18 |
+-------------------------+----------+
8 rows in Set (0.00 sec)

If you need to calculate the hit ratio, you need to know how many SELECT statements the server executes:

Mysql> Show status like ' com_sel% ';
+---------------+---------+
| variable_name | Value |
+---------------+---------+
| Com_select | 2889628 |
+---------------+---------+
1 row in Set (0.01 sec)

In this case, MySQL hits 83,951 of the 2,889,628 queries, and only 545,875 of the INSERT statements. Therefore, there is a big gap between the two and the total query for 2.8 million, so we know that the buffer type used in this example is 2.

In the case of type 1, the qcache_hits value would be much larger than com_select.

Several commands:
Mysql> Show status like ' qcache% '; #查看mysql查询缓存的运行时状态
mysql> reset query Cache; #重置mysql查询缓存
Mysql> Flush Query Cache #清理查询缓存碎片