Shulou(Shulou.com)06/03 Report--

This article mainly introduces the MySQL Query Cache handout, what it involves is learned from the theoretical knowledge, there are many books and documents for your reference, from the perspective of practical significance, the accumulated practical experience for many years can be shared with you.

I. Preface

In today's systems, caching is an important means to optimize system performance. MySQL Query Cache (MySQL query cache) is turned on by default in MySQL Server, but all kinds of online materials and experienced DBA recommend that MySQL Query Cache be turned off in a production environment. According to reason, MySQL Server is enabled by default to encourage users to use cache, but big players recommend that this feature be disabled, and this feature is turned off by default in the MySQL cloud services provided by various cloud vendors in China. Why? What kind of pit did they encounter in their use? This article will explain MySQL Query Cache in detail from the following aspects.

What is the 1.MySQL query cache?

What are the MySQL caching rules?

How to configure and cache MySQL caching

Advantages and disadvantages of MySQL caching

Should MySQL cache be turned on in production?

2. Brief introduction of MySQL query cache

MySQL query cache is a unique cache area in MySQL, which is used to cache the entire result set information of a particular Query and share it with all clients. In order to improve the response speed of the same Query statement, MySQL Server will calculate the Hash of the query statement and store the hash value corresponding to the result set of the Query query in Query Cache. When MySQL Server opens Query Cache, MySQL Server calculates the hash value of the Query through a specific Hash algorithm for each SELECT statement it receives, and then matches it into the Query Cache through the hashi value.

If there is no match, the hash value is stored in a hash linked list, and the result set of the Query is stored in the cache. Each hash node storing the hashi value linked list stores the address of the corresponding Quey result set in the cache, as well as some table related information involved in the query.

If the same Query is matched by the hash value, the corresponding Query result set in the cache is returned directly to the client.

Currently, MySQL Query Cache only knows cache select statements. Other statements like show and use will not be stored in SQL text as key in every Query Cache of cache MySQL. SQL text will not be processed before Query Cache is applied. In other words, as long as there is even one character difference between two SQL statements (for example, different case, one more space, more comments), the two SQL will use different Cache addresses. For example, the following three SQL will be stored in three different caches, although their results are all the same. Select FROM people where name='surfchen'; select FROM people where / hey~/ name='surfchen'; SELECT * FROM people where name='surfchen'

Third, MySQL caching mechanism

To put it simply, the MySQL caching mechanism is to cache sql text and query results. If you run the same SQL, the CVM will get the results directly from the cache without the need to parse and execute SQL. If the table changes, all cached queries that use the table will no longer be valid and entries related to the values in the query cache will be cleared. The change here refers to any data or structure change in the table, including INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE, as well as queries that map to the changed table using the MERGE table. Obviously, query caching is not suitable for tables that update frequently, while query caching can save a lot of performance for tables that do not change data often and have a large number of the same SQL queries.

The query must be identical (byte by byte) to be considered the same. In addition, the same query string may be thought to be different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.

It should be noted that MySQL Query Cache is case-sensitive, because Query Cache is mapped in HASH structure in memory, and the HASH algorithm is based on the characters that make up the SQL statement, so any change in the SQL statement is re-cache.

3.1 caching rules

When caching is enabled, MySQL Server will automatically return the query statement and result set to memory, and next time check it directly from memory.

The cached results are shared through sessions, so the cached results of one client query and another client can also be used

MySQL Query Cache content is the result set of select, cache uses the full SQL string as key, and is case-sensitive, space-sensitive, and so on. That is, the two SQL must be exactly the same to cause the cache to hit. That is, when checking the query cache, MySQL Server will not do any processing to SQL. It accurately uses the query from the client. As long as the character case or comments are slightly different, the query cache is considered to be a different query.

Prepared statement will never cache the result, even if the parameters are exactly the same. It will be improved after 5.1.

If any uncertain function is included in the where condition, it will never be cache, such as current_date, now, etc.

If a function such as date returns an hour or sky, it's best to figure it out before passing it in.

Select from foo where date1=current_date-- will not be cache

Select from foo where date1='2008-12-30'-by cache, the right thing to do

Result set that is too large will not be cache (

< query_cache_limit) MySQL缓存在分库分表环境下是不起作用的 执行SQL里有触发器,自定义函数时，MySQL缓存也是不起作用的 3.2 缓存失效 在表的结构或数据发生改变时，查询缓存中的数据不再有效。如INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。 一旦表数据进行任何一行的修改，基于该表相关cache立即全部失效。 3.3 手动清理缓存手动清理缓存可以使用下面三个SQL FLUSH QUERY CACHE； #清理查询缓存内存碎片 RESET QUERY CACHE；#从查询缓存中移除所有查询 FLUSH TABLES； #关闭所有打开的表，同时该操作会清空查询缓存中的内容 3.4 缓存机制中的内存管理 MySQL Query Cache 使用内存池技术，自己管理内存释放和分配，而不是通过操作系统。内存池使用的基本单位是变长的block, 用来存储类型、大小、数据等信息；一个result set的cache通过链表把这些block串起来。block最短长度为query_cache_min_res_unit。 当服务器启动的时候，会初始化缓存需要的内存，是一个完整的空闲块。当查询结果需要缓存的时候，先从空闲块中申请一个数据块为参数query_cache_min_res_unit配置的空间，即使缓存数据很小，申请数据块也是这个，因为查询开始返回结果的时候就分配空间，此时无法预知结果多大。 分配内存块需要先锁住空间块，所以操作很慢，MySQL会尽量避免这个操作，选择尽可能小的内存块，如果不够，继续申请，如果存储完时有空余则释放多余的。 但是如果并发的操作，余下的需要回收的空间很小，小于query_cache_min_res_unit，不能再次被使用，就会产生碎片。如图： 四、MySQL缓存发挥作用的情况 1、查询缓存可以降低查询执行的时间，但是却不能减少查询结果传输的网络消耗，如果网络传输消耗是整个查询过程的主要瓶颈，那么查询缓存的作用也很小。 2、对于那些需要消耗大量资源的查询通常都是非常适合缓存的，对于复杂的SELECT语句都可以使用查询缓存，不过需要注意的是，涉及表上的UPDATE、DELETE、INSERT操作相比SELECT来说要非常少才行。 3、查询缓存命中率：Qcache_hits/(Qcahce_hits+Com_select)，查询缓存命中率多大才是好的命中率，需要具体情况具体分析。只要查询缓存带来的效率提升大于查询缓存带来的额外消耗，即使30%的命中率也是值得。另外，缓存了哪些查询也很重要，如果被缓存的查询本身消耗巨大，那么即使缓存命中率低，对系统性能提升仍然是有好处的。 4、任何SELECT语句没有从查询缓存中返回都称为"缓存未命中"，以如下列情况： 查询语句无法被缓存，可能因为查询中包含一个不确定的函数，或者查询结果太大而无法缓存。 MySQL从未处理这个查询，所以结果也从不曾被缓存过。 虽然之前缓存了查询结果，但由于查询缓存的内存用完了，MYSQL需要删除某些缓存，或者由于数据表被修改导致缓存失效。 如果服务器上有大量缓存缓存未命中，但是实际上绝大查询都被缓存了，那么一定是有如下情况发生： 查询缓存还没有完成预热，即MySQL还没有机会将查询结果都缓存起来。 查询语句之前从未执行过。如果应用程序不会重复执行一条查询语句，那么即使完成预热仍然会有很多缓存未命中。 缓存失效操作太多，缓存碎片、内存不足、数据修改都会造成缓存失效。可以通过参数Com_*来查看数据修改的情况（包括Com_update，Com_delete等），还可以通过Qcache_lowmem_prunes来查看有多少次失效是由于内存不足导致的。 5、有一个直观的方法能够反映查询缓存是否对系统有好处，推荐一个指标："命中和写入"的比率，即Qcache_hits和Qcache_inserts的比值。根据经验来看，当这个比值大于3：1时通常查询缓存是有效的，如果能达到10：1最好。 6、通常可以通过观察查询缓存内存的实际使用情况Qcache_free_memory，来确定是否需要缩小或者扩大查询缓存。 五、MySQL缓存管理和配置 5.1 MySQL缓存相关的配置参数 mysql>

Show variables like'% query_cache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_query_cache | YES |-- query whether the cache is available |

| | query_cache_limit | 1048576 |-the maximum value that can be cached for specific query results |

| | query_cache_min_res_unit | 4096 |-- query the size of the minimum block allocated by the cache (in bytes) |

| | query_cache_size | 599040 |-- query cache size |

| | query_cache_type | ON |-whether query cache is supported |

| | query_cache_wlock_invalidate | OFF |-- controls whether to invalidate the Query Cache related to the table when a write lock is added to the table |

+-+ +

6 rows in set (0.02 sec)

Have_query_cache

Whether the MySQL Server supports Query Cache.

Query_cache_limit

The maximum query result that can be cached by MySQL, and will not be cached if the query result is greater than this value. The default value is 1048576 (1MB). If the result of a query exceeds this value, the value of Qcache_not_cached will be increased by 1. If an operation is always exceeded, consider adding SQL_NO_CACHE to SQL to avoid extra consumption.

Query_cache_min_res_unit

The size (in bytes) of the smallest block allocated by the query cache. The default value is 4096 (4KB). When the query is in progress, MySQL saves the query results in qurey cache, but if the result to be saved is larger than the value of query_cache_min_res_unit, mysql will retrieve the results and save the results at the same time, so sometimes instead of saving all the results all at once, it allocates a piece of memory space of query_cache_min_res_unit size to save the result set at a time. After use, then allocate one such block, and if it is not enough, then allocate another block, and so on, that is, it is possible for mysql to allocate memory multiple times in one query. Properly adjusting query_cache_min_res_unit can optimize memory. If your query results are all some small result, the default query_cache_min_res_unit may cause a lot of memory fragmentation. If your query results are all some larger resule, you can increase the query_cache_min_res_unit appropriately.

Query_cache_size

The amount of memory allocated to cache query results in bytes and the value must be an integral multiple of 1024. The default value is 0, which disables query caching. Note that if this value is set, this amount of memory will be allocated even if query_cache_type is set to 0.

Query_cache_type

Sets the query cache type, default to ON. Setting the global value sets the type of all subsequent client connections. Clients can set the session value to affect their own use of the query cache. The following table shows the possible values:

Query_cache_wlock_invalidate

If a table is locked, it is also recommended whether to return the data in the cache or not. In general, when the client performs WRITE locking on the MyISAM table, if the query result is in the query cache, other clients are not locked, and the table can be queried. Setting this variable to 1 allows WRITE locking on the table, making all queries against the table in the query cache illegal. In this way, when the lock takes effect, you can force other clients trying to access the table to wait.

5.2 enable and close caching

Turn on caching

Mysql > set global query_cache_size = 600000;-- set cache memory size

Mysql > set global query_cache_type = ON;-- enable query caching

Turn off caching

Mysql > set global query_cache_size = 0;-- set the cache memory size to 0, that is, no cache memory is allocated for initialization

Mysql > set global query_cache_type = OFF;-- turn off query caching

SUPER permission is required for set global

VI. The influence of MySQL Query Cache on performance

6.1 additional overhead for MySQL Query Cache

As shown in the figure above: opening Query Cache in MySQL Server results in additional consumption for both reading and writing to the database:

1) before the read query starts, you must check whether the cache is hit.

2) if the read query can be cached, the query results and query statements will be written to the cache after the query operation is executed.

3) when writing data to a table, all caches of the table must be set to invalidate. If the cache space is very large, the consumption will be very large, which may make the system freeze for a period of time. Because this operation is protected by global locking operations.

4) for the InnoDB table, cache invalidation is set when modifying a table, but the multi-version feature temporarily shields the modification from other transactions. Before the transaction is committed, all queries cannot use the cache until the transaction is committed, so long-term transactions will greatly reduce the hit of the query cache.

6.2 MySQL Query Cache fragment optimization

As shown in the image above, there is no way to avoid fragmentation completely, but choosing the right query_cache_min_res_unit can help you reduce the waste of memory space caused by fragmentation. If this value is too small, less space will be wasted, but it will result in frequent memory block requests; if set too large, there will be a lot of fragmentation. Adjusting the appropriate value is actually balancing memory waste and CPU consumption. The average cache size of a single query can be calculated by dividing the actual memory consumption (query_cache_size-Qcache_free_memory) by Qcache_queries_in_cahce. You can observe the fragments through Qcahce_free_blocks.

Defragmenting through FLUSH_QUERY_CAHCE, this command reorders all query caches and focuses all free space on an area of the query cache.

6.3 MySQL cache status view

Mysql > SHOW STATUS LIKE 'Qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| Qcache_free_blocks | 1 |-the idle block in the query cache. If the value is large, it means that there may be more memory fragments in Query Cache. FLUSH QUERY CACHE defragments the cache to get a large block of free memory.

| | Qcache_free_memory | 382704 |-the size of the remaining cache |

| | Qcache_hits | 198 |-number of cache hits |

| Qcache_inserts | 131The number of times the cache was inserted, that is, the number of times the query missed.

| Qcache_lowmem_prunes | 0 |-the number of cache entries deleted due to low memory. If this number is increasing, it is generally due to insufficient free memory in Query Cache (judged by Qcache_free_memory) or serious memory fragmentation (judged by Qcache_free_blocks).

| | Qcache_not_cached | number of entries that are not cached. There are three situations that will cause the query result not to be cached: first, the query result will not be cached due to the setting of query_cache_type; second, the query is not a SELECT statement; and third, the query statement is constantly changing due to the use of functions such as now (). |

| | Qcache_queries_in_cache | 128 |-how many query statements are in the cache |

| | Qcache_total_blocks | 281 |-Total number of blocks |

+-+ +

8 rows in set (0.00 sec)

6.4 Query Cache fragmentation rate Query Cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%

If the Query Cache fragmentation rate exceeds 20%, you can use FLUSH QUERY CACHE to defragment memory; if your query is a small amount of data, you can try to reduce query_cache_min_res_unit.

6.5 Query Cache utilization Query Cache utilization = (query_cache_size-Qcache_free_memory) / query_cache_size * 100%

If the Query Cache utilization is below 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the Query Cache utilization is above 80%, and if the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there may be too many memory fragments.

6.6 Query Cache hit rate

Query Cache hit ratio of cacheable queries = Qcache_hits / (Qcache_hits + Qcache_inserts) 100%

Query Cache hit ratio covering all queries = Qcache_hits / (Qcache_hits + Com_select) 100%

If the hit ratio is in the range of 50-70%, the cache efficiency of Query Cache is high. If the hit rate is significantly less than 50%, it is recommended to disable (set query_cache_type to 0 (OFF)) or use Query Cache on demand (set query_cache_type to 2 (DEMAND)). The saved memory can be used as a buffer pool for InnoDB.

6.7How can I tell if Query Cache has insufficient free memory or too much memory fragmentation? If the Qcache_lowmem_ prunes value is large, it means that the memory size setting for Query Cache is too small and needs to be increased.

If the Qcache_free_ blocks value is large, it means that there are more memory fragments, so you need to use the FLUSH QUERY CACHE statement to clean up the memory fragments.

6.8 what size should the system variable query_cache_min_res_unit be set to? The formula for query_cache_min_res_unit is as follows:

Query_cache_min_res_unit = (query_cache_size-Qcache_free_memory) / Qcache_queries_in_cache

It is generally not recommended to set the size of Query Cache (that is, the query_cache_size system variable) to exceed 256MB.

VII. Advantages and disadvantages of MySQL Query Cache

7.1. The query of the advantage Query Cache occurs after the MySQL receives the query request from the client, after the query permission verification, and before the query SQL parsing. In other words, when MySQL receives the query SQL from the client, it only needs to verify the corresponding permissions, and it will find the results through Query Cache, and it does not even need to analyze and optimize the execution plan of the Optimizer module, let alone any interaction with the storage engine. Because Query Cache is memory-based and returns the corresponding query results directly from memory, a large number of disk I _ Unio and CPU calculations are reduced, resulting in very high efficiency.

7.2. Disadvantages the advantages of Query Cache are obvious, but some of its disadvantages should not be ignored:

Resource consumption caused by hash calculation and hash lookup of query statements. If query_cache_type is set to 1 (that is, ON), MySQL performs an hash calculation on each query of type SELECT received, and then finds out if the cached result of the query exists. Although the efficiency of hash computation and lookup is high enough, the cost of one query statement can be ignored, but when it comes to high concurrency and there are thousands of query statements, the cost of hash computation and lookup must be paid attention to.

Failure of Query Cache. If the table changes frequently, it will result in a very high failure rate of Query Cache. A change in a table refers not only to a change in the data in the table, but also to any change in the table structure or index.

Query statements are different, but queries with the same query results will be cached, which will result in excessive consumption of memory resources. Query Cache will think of query statements as different queries with different character case, spaces, or comments (because their hash values will be different).

Improper setting of relevant system variables will result in a large number of memory fragments, which will cause Query Cache to clean memory frequently.

8. How to set up MySQL Query Cache in production

Query Cache in MySQL is a caching mechanism suitable for fewer cases. As shown in the figure above, if the cache hit rate is very high, tests have shown that efficiency can be improved by 238% in extreme cases. But what is the actual situation? Query Cache has the following rule that if the data table is changed, all Cache associated with the data table will be invalid and deleted. Here "datasheet changes" include: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE and so on. For example, if the data table posts is accessed frequently, it means that a lot of its data will be cached by QC, but every update of the posts data table, whether it affects the cache data or not, will clear all the cache associated with the posts table. If your datasheet is updated frequently, Query Cache will become a burden on the system. Some experiments have shown that when bad, QC will reduce the processing capacity of the system by 13%.

If your application makes few updates to the database, then QC will play a significant role. More typical such as blog system, generally blog update is relatively slow, the data table is relatively stable, at this time the role of QC will be more obvious.

But a BBS system that updates frequently. Here are the status parameters of an actual forum database: QCache_hit 5280438QCache_insert 8008948Qcache_not_cache 95372Com select 8104159 can see that the database has written about 800W caches to Query Cache, but only about 500W hits have actually been made. In other words, the usage of each cache is about 0.66 times. It is difficult to say whether the role of the cache is greater than the overhead of the Query Cache system. But one thing is certain, the role of Query Cache caching is very small, if the application layer can implement caching, the effect of Query Cache can be ignored.

Therefore, if you have a frequently updated system and want to get a higher tps, it is recommended to turn off Query Cache in the first place.

9, the alternative to query caching MySQL query caching work principle is: the fastest way to execute the query is not to execute, but the query still needs to be sent to the server, the server also needs to do a little work, if some queries do not need to communicate with the server what will happen, then the client cache can largely share the pressure on the MySQL server.

After reading the introduction of the above MySQL Query Cache handout, I hope it can bring some help to you in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.