MySQL Basics (v) Query caching

The cache has the disadvantage of     1, if hit cache, directly from the cache back, reduce the process of parsing and executing SQL statements, improve query efficiency     2, caching brings additional overhead, If the cost of opening the cache is greater than the cost of not opening the cache, it is not recommended to turn on caching      caching overhead     1, the read query checks the query cache before it starts      2, if a read query can be cached and not cached, then when the execution is complete, MySQL will put its results into the query cache     3, the write operation is also affected, because when the data is written, MySQL must set all cache for the corresponding table to fail,     This will cause a large system consumption when the cache memory is large, so the cache memory is not as large as possible      MySQL Query cache     1, used to save the results returned by the query statement, when hit, MySQL will immediately return the results, eliminating the parsing, optimization and execution steps    2, MySQL save results in the cache, the SELECT statement hash calculation, the results of the calculation as key, query results as value  3, MySQL query cache is case-sensitive, so use SQL as far as possible to use the same style         will not be cached by statements   1, indeterminate data, not cached, such as now (), current_time (), etc.   2, If the query SQL contains user-defined functions, stored functions, user variables, temporal tables, MySQL library system table   3, SQL contains field permissions      Cache-related server variables mysql>  SHOW VARIABLES LIKE  ' query% '; #查看和查询缓存相关的系统变量     query_cache_type:   Whether to open cache off:  close     ON:  Open     demand:  only queries that explicitly write Sql_cache will write to the cache     query_cache_size:  The total amount of memory space used by the cache, in bytes, this value must be 1024 integer times     query_cache_min_res_unit:  allocated minimum memory block size, too large may lead to memory fragmentation, Too small can cause frequent requests for memory     query_cache_limit:  can cache the maximum results, and if this size is exceeded, the data already cached is cleared      query_cache_wlock_invalidate:  If a data table is locked and still returns data from the cache, OFF: Indicates that the mysql> show status can be returned  LIKE  '%qcache% '; #查看缓存状态 the number of free blocks in the     qcache_free_blocks# cache pool      qcache_free_memory# Cache number of idle memory     qcache_hits# cache hits     qcache_ inserts# Cache writes     qcache_lowmen_prunes# Delete cache count due to insufficient memory     qcache_not_ cached  #查询未被缓存次数     Qcache_queries_in_cache  #当前缓存中缓存的SQL数量      Qcache_total_blocks  #缓存的总内存块是否使用缓存     1.  determine,  cache hit ratio by cache hit rate  =   Cache Hit count   (qcache_hits)  / Number of queries   (com_select)     2. ,  write rate by cache write rate  =  cache write times   (qcache_ Inserts)  /  queries   (com_select)     3.  pass   hit-write rate   judging,  ratios  =  hit Count   (qcache_hits)  /  write Count   (qcache_inserts) 3:1 is a valid query cache, preferably up to 10:1     mysql> SHOW GLOBAL STATUS LIKE  ' Com_select '; #查看查询次数

Parsing and configuring the query cache

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/97/FA/wKiom1k2DLLTDkHyAAGCbN1a-_4178.jpg "title=" Cache configuration considerations. jpg "alt=" wkiom1k2dlltdkhyaagcbn1a-_4178.jpg "/>

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1932624

MySQL Basics (v) Query caching