MySQL Knowledge summary (cache)

1. Caching mechanism Concepts

The caching mechanism is simply to cache SQL text and query results, and if you run the same SQL, the server takes the results directly from the cache without having to parse and execute the SQL. If the table changes, all buffered queries that use the table are no longer valid, and the related entries for the query cache value are emptied. Changes refer to any data or structure changes in the table, including insert, UPDATE, DELETE, TRUNCATE, ALTER table, drop table, or drop database, etc. Also included are those queries that map to a changed table using the merge table. Obviously, the query cache is not suitable for frequently updated tables, and for some tables that do not change data frequently and have a large number of identical SQL queries, the query cache can save a lot of performance.
The query must be exactly the same (byte-wise) to be considered the same. In addition, the same query string may be considered different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered to be different queries and are cached separately.

2. Query cache related parameters

MySQL>like'%query_cache%';

3. A simple example:

MySQL> use zzlp;
MySQL> show Tables;

MySQL>Selectcount(*fromuser;

#Qcache_hits表示sql查询在缓存中命中的累计次数, is the cumulative value of
mysql>like'qcache _hits';

#连续查询两次
mysql>  Select Count (*fromuser;
MySQL>  Selectcount(*fromuser;

MySQL>insertintouserSelectfrom-- after inserting data, All SQL caches associated with this table will be emptied of
mysql>Selectcount(* from user;

MySQL>like'qcache_hits';

MySQL>Selectcount(*fromuser;

MySQL>Selectcount(*fromuser;
MySQL>like'qcache_hits';

MySQL Knowledge summary (cache)