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

1. If the cache hits the cache, it returns directly from the cache, which reduces the process of analyzing and executing SQL statements, and improves the query efficiency. 2. Cache will bring additional overhead. If the cost of turning on the cache is greater than the cost of not opening the cache, it is not recommended to turn on the cache 1. The read query will check the query cache before starting. 2. If a read query can be cached and not cached, then MySQL will store its results in the query cache 3 when it is finished, which also affects the write operation, because when the data is written MySQL must invalidate all the caches corresponding to the table, which will lead to a lot of system consumption when the cache memory is large, so the cache memory is not as large as possible. Mysql query cache 1 is used to save the results returned by query statements. When hit, MySQL will immediately return the results, omitting steps such as parsing, optimization and execution. 2. MySQL saves the results in the cache and does hash calculation on select statements. The calculated result is key, and the query result is case-sensitive as value 3 and mysql query cache, so when using SQL, try to use statements that will not be cached in the same style. 1. Uncertain data will not be cached, such as now (), current_time (), etc. 2. If the query SQL contains user-defined functions, storage functions, user variables, temporary tables System Table 3 and SQL in the mysql library contain server variables mysql > SHOW VARIABLES LIKE 'query%' related to field permissions. # check the system variable query_cache_type related to query cache: whether to turn on cache OFF: turn off ON: turn on DEMAND: only queries that explicitly write SQL_CACHE will be written to cache query_cache_size: the total memory space used by cache (in bytes) This value must be an integral multiple of 1024 query_cache_min_res_unit: the minimum allocated block size Too large may lead to memory fragmentation, and too small may lead to frequent requests for memory query_cache_limit: the maximum result that can be cached. If this size is exceeded, the cached data query_cache_wlock_invalidate is cleared. If a data table is locked, whether data is still returned from the cache. OFF: indicates that mysql > SHOW STATUS LIKE'% qcache%' can be returned. # View cache status Qcache_free_blocks # number of free blocks in cache pool Qcache_free_memory # amount of free memory in cache Qcache_hits # cache hits Qcache_inserts # cache writes Qcache_lowmen_prunes # delete cache due to insufficient memory Number of times Qcache_not_cached # queries number of times uncached Qcache_queries_in_cache # number of SQL cached in the current cache Qcache_total_blocks # whether the total memory block of the cache uses cache 1. According to cache hit ratio, cache hit ratio = cache hit times (Qcache_hits) / query times (Com_select) 2. Through cache write rate, write rate = cache write times (Qcache_inserts) / query times (Com_select) 3. Judging by the hit-write rate, the ratio = number of hits (Qcache_hits) / number of writes (Qcache_inserts) 3:1 is considered the query cache valid, and it is best to reach 10:1 mysql > SHOW GLOBAL STATUS LIKE 'com_select'; # number of queries viewed

Analyze and configure query cache

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.