The cache is stored in an application table and applied by a hash value that includes the following factors, the query itself, the database currently being queried, the version of the client protocol, and other information that may affect the return result. When the cache is judged to be hit, MySQL does not parse, "normalize" or parameterize the query statement, but instead directly applies the SQL statement and other raw information sent by the client. Any character differences, such as vacancies, annotations, and so on will result in cache misses, so uniform coding rules are a good habit. did not parse the SQL statement when checking the cache, so MySQL does not know whether the query contains an indeterminate function. Before checking the cache, MySQL does one thing by looking at a case-insensitive check to see if SQL begins with the SEL. But there are some problems to be aware of. First, opening the query cache for read and write operations brings additional consumption: the Read query must first check whether the cache is hit if the read query is cached, and when it does, the query cache will be stored in the query cache. Additional system consumption is also affected by this write operation, because when writing data to a table, MySQL must invalidate all caches of the corresponding table. If the query cache is very large or fragmented, this operation can result in significant system consumption. Nonetheless, query caching can still bring performance gains to the system. However, as mentioned above, these additional costs may also be increasing, plus a lock-exclusive operation on the query cache operation, which may not be a negligible consumption. For InnoDB users, some of the features of a transaction limit the use of query caching. When a statement modifies a table in a transaction, MySQL sets the table's corresponding query cache to expire. In fact, the multi-version feature of InnoDB will show that this modification is masked against other transactions. The related query for this table cannot be cached until this transaction is committed. So all queries on this table-internal or external-can only be cached after the transaction has been committed. Therefore, long-running transactions can greatly reduce the hit rate of the query cache. Reason for cache misses: Query statements cannot be cached, either because the query contains an indeterminate function, or the query result is too large to be cached. MySQL has never processed this query, so the query results were cached before it was cached, but because the memory of the query cache ran out, MySQL needed to disable some cache eviction tables to cause the cache to fail If there was a lot of cache killed, but actually cached, Then the reason must be as follows: The query cache does not complete the waste heat. MySQL has no chance to cache the query results. The query statement has never been executed before. The query statement has never been executed before. There are too many cache invalidation operations. Configuration andMaintain cache: Query_cache_typeoff,on or demand,demand means that only statements that explicitly specify Sql_cache in a query statement are placed into the query cache. The query_cache-size query cache uses the total space, in units of bytes. This value must be an integer multiple of 1024, otherwise MySQL actually assigns a different data round than you specify. query_cache_min_res_unit the smallest unit when allocating memory blocks in the query cache. query_cache_limitmysql The maximum query results that can be cached. Only when the results are all returned does MySQL know if the query results exceed the limit. query_cache_wlock_invalidate If a data table is locked by another link, the result is still returned from the query cache. The default is off. InnoDB and query caching because InnoDB has its own MVCC wit, InnoDB and query cache exchanges are more complex than other stored procedures. About mvcc:http://www.cnblogs.com/chenpingzhao/p/5065316.html General Query cache optimization: Multiple small tables instead of a large table can be used to make the effectiveness of the strategy at a more appropriate granularity. Of course, too. Bulk writes are more efficient than single-heads writes as long as the cache is invalidated. (PS: Do not delay write and bulk write at the same time, otherwise it may cause the server to zombie for a long time because of failure.) Because the cache space is too large, the state-owned enterprise operation may cause the server zombie. Solution: Control the size of the cache space. Query statement caching cannot be controlled at the database or table level, but you can control whether a SELECT statement needs to be cached through Sql_cache and Sql_no_cache. For write-intensive applications, disabling the query cache directly may improve the performance of the system. Closing the query cache removes all related consumption. Because of the competition for mutex semaphores, it is sometimes beneficial to directly close the query cache for read-intensive applications. about cache optimizations, you can do a related test that compares performance differences when opening and closing query caches.
High-performance MySQL reading note 7.12.1
