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

First, the misunderstanding of query cache

When I first came into contact with QC, I thought it was a good thing to put the results of the query into QC, which greatly improved the speed of the query, but later I learned that the cache corresponding to DML or DDL,QC on the table would be released, which is meaningless for databases that change frequently.

Second, query cache scenarios

Each time the select result is executed, it is placed in the query cache, so that the next time it is executed, there is no need to specifically execute the sql, just access the cache in the query access.

Appropriate scenarios:

1 、 select

2. It is necessary to cache the results only by accessing a large amount of data.

3. Return a small amount of data

A large number of grouping, aggregation, calculation

So that the query cache does not become very large.

For ordinary trading systems, this query does not make much sense, it is some simple sql, and even has side effects.

With a high cost and a small result set, this sql is worth caching.

Third, the configuration of query cache

1. Query_cache_type, which is a static parameter, requires a restart of the database.

0 turns off query caching

Open the query cache and cache the results of all sql statements unless prompted by select sql_no_cache to indicate that the result is not in the cache.

2 turn on the query cache, but only cache the result set corresponding to select sql_cache. More recommended use

2. Query_cache_size adjusts the size of query cache

Fourth, query cache demonstration

When query cache is enabled, the select in MySQL will be placed in the query cache. No matter what operation is put in QC for the first time, the query will be directly queried in QC when it is executed again, so that Qcache_hits will be added by one. However, if you DML or DDL the table, the cache originally placed in QC will be released.

1. Without any action

| | Qcache_hits | 0 | |

| | Qcache_not_cached | 2 | |

| | Qcache_queries_in_cache | 0 | |

2. Execution

Select * from T1 where id=1

| | Qcache_hits | 0 | |

| | Qcache_not_cached | 2 | |

| | Qcache_queries_in_cache | 1 | |

Select * from T1 where id=1

| | Qcache_hits | 1 | |

| | Qcache_not_cached | 2 | |

| | Qcache_queries_in_cache | 1 | |

Insert into T1 values (4 dfd')

| | Qcache_hits | 1 | |

| | Qcache_not_cached | 2 | |

| | Qcache_queries_in_cache | 0 | |

Select * from T1 where id=1

| | Qcache_hits | 1 | |

| | Qcache_not_cached | 2 | |

| | Qcache_queries_in_cache | 1 | |

Select * from T1 where id=1

| | Qcache_hits | 2 | |

| | Qcache_not_cached | 2 | |

| | Qcache_queries_in_cache | 1 | |

Mysql > reset query cache

Query OK, 0 rows affected (0.00 sec)

| | Qcache_hits | 2 | |

| | Qcache_inserts | 2 | |

| | Qcache_queries_in_cache | 0 | |

Query the parameters in the cache

| | Qcache_free_blocks | 1 | # refers to the number of free blocks, which refers to the unused space in the total cache size allocated by the system to the MySQL process |

| Qcache_free_memory | 20953960 | # Free space, which refers to the memory space that has not been allocated by the operating system.

| | Qcache_hits | 12 | # indicates the number of hits |

| | Qcache_inserts | 3 | # number of caches inserted into the cache space |

| | Qcache_lowmem_prunes | 0 | # the number of times the memory is trimmed because the memory is too small |

| | Qcache_not_cached | 0 | # indicates the number of missed hits |

| | Qcache_queries_in_cache | 0 | # number of caches |

| | Qcache_total_blocks | 1 | # Total number of blocks, which refers to the total cache size allocated by the system to the MySQL process |

Flush query_cache # defragmentation

Reset query_cache # clear the 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.