In-depth parsing of MySQL query Cache Mechanism

The MySQL query cache mechanism is one of the important mechanisms in the MySQL database. The following provides an in-depth analysis of the MySQL query cache mechanism for your reference.

The MySQL cache mechanism simply caches the SQL text and query results. If the same SQL statement is run, the server directly obtains the results from the cache without parsing and executing the SQL statement. If the table is changed, all buffered queries using this table will no longer be valid, and the items related to the query cache value will be cleared. Changes refer to any data or structure changes in the TABLE, including INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database, this also includes queries for tables mapped to changed tables that use MERGE tables. Obviously, the query cache is not suitable for frequently updated tables. For tables that do not often change data and have a large number of identical SQL queries, the query cache will save a lot of performance.

The query must be exactly the same (in bytes) to be considered the same. In addition, the same query string may be considered different for other reasons. Queries using different databases, different protocol versions, or different default character sets are considered to be different queries and cache them separately.

The following SQL query cache is considered different:

 
 

  
  
SELECT * FROM tbl_name  
  
  
Select * from tbl_name  
 
 

Query cache Parameters

 
 

  
  
Mysql> show variables like '% query_cache % ';
  
  
+ ------------------------------ + --------- +
  
  
| Variable_name | Value |
  
  
+ ------------------------------ + --------- +
  
  
| Have_query_cache | YES | -- query whether the cache is available
  
  
| Query_cache_limit | 1048576 | -- maximum value of the specific query result that can be cached
  
  
| Query_cache_min_res_unit | 4096 |
  
  
| Query_cache_size | 599040 | -- query the cache size.
  
  
| Query_cache_type | ON | -- blocks or supports query Cache
  
  
| Query_cache_wlock_invalidate | OFF |
  
  
+ ------------------------------ + --------- +
 
 

The following is an example of a simple MySQL query cache mechanism:

 
 

  
  
[Mysql @ csdba1850 ~] $ Mysql-u root-p
  
  
Enter password:
  
  
Welcome to the MySQL monitor. Commands end with; or \ g.
  
  
Your MySQL connection id is 3
  
  
Server version: 5.0.45-community MySQL Community Edition (GPL)
  
  
 
  
  
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
  
  
 
  
  
Mysql> set global query_cache_size = 600000; -- sets the cache memory.
  
  
Query OK, 0 rows affected (0.00 sec)
  
  
 
  
  
Mysql> set session query_cache_type = ON; -- enable query Cache
  
  
Query OK, 0 rows affected (0.00 sec)
  
  
 
  
  
Mysql> use test
  
  
Reading table information for completion of table and column names
  
  
You can turn off this feature to get a quicker startup with-
  
  
 
  
  
Database changed
  
  
Mysql> show tables;
  
  
+ ---------------- +
  
  
| Tables_in_test |
  
  
+ ---------------- +
  
  
| Animals |
  
  
| Person |
  
  
+ ---------------- +
  
  
5 rows in set (0.00 sec)
  
  
 
  
  
Mysql> select count (*) from animals;
  
  
+ ---------- +
  
  
| Count (*) |
  
  
+ ---------- +
  
  
| 6 |
  
  
+ ---------- +
  
  
1 row in set (0.00 sec)
  
  
 
  
  
-- Qcache_hits indicates the cumulative number of times that the SQL query hits in the cache, which is the cumulative value.
  
  
Mysql> show status like 'qcache _ hits ';
  
  
+ --------------- + ------- +
  
  
| Variable_name | Value |
  
  
+ --------------- + ------- +
  
  
| Qcache_hits | 0 | -- 0 times
  
  
+ --------------- + ------- +
  
  
8 rows in set (0.00 sec)
  
  
 
  
  
Mysql> select count (*) from animals;
  
  
+ ---------- +
  
  
| Count (*) |
  
  
+ ---------- +
  
  
| 6 |
  
  
+ ---------- +
  
  
1 row in set (0.00 sec)
  
  
 
  
  
Mysql> show status like 'qcache % ';
  
  
+ --------------- + ------- +
  
  
| Variable_name | Value |
  
  
+ --------------- + ------- +
  
  
| Qcache_hits | 1 | -- indicates that the SQL statement is directly obtained in the cache and does not need to be parsed.
  
  
+ --------------- + ------- +
  
  
8 rows in set (0.00 sec)
  
  
 
  
  
Mysql> select count (*) from animals;
  
  
+ ---------- +
  
  
| Count (*) |
  
  
+ ---------- +
  
  
| 6 |
  
  
+ ---------- +
  
  
1 row in set (0.00 sec)
  
  
 
  
  
Mysql> select count (*) from animals;
  
  
+ ---------- +
  
  
| Count (*) |
  
  
+ ---------- +
  
  
| 6 |
  
  
+ ---------- +
  
  
1 row in set (0.00 sec)
  
  
 
  
  
Mysql> show status like 'qcache _ hits ';
  
  
+ --------------- + ------- +
  
  
| Variable_name | Value |
  
  
+ --------------- + ------- +
  
  
| Qcache_hits | 3 | -- the preceding SQL statement is obtained directly from the cache.
  
  
+ --------------- + ------- +
  
  
1 row in set (0.00 sec)
  
  
 
  
  
Mysql> insert into animals select 9, 'testsds '; -- after data is inserted, all SQL caches related to the table will be cleared.
  
  
Query OK, 1 row affected (0.00 sec)
  
  
Records: 1 Duplicates: 0 Warnings: 0
  
  
 
  
  
Mysql> select count (*) from animals;
  
  
+ ---------- +
  
  
| Count (*) |
  
  
+ ---------- +
  
  
| 7 |
  
  
+ ---------- +
  
  
1 row in set (0.00 sec)
  
  
 
  
  
Mysql> show status like 'qcache _ hits ';
  
  
+ --------------- + ------- +
  
  
| Variable_name | Value |
  
  
+ --------------- + ------- +
  
  
| Qcache_hits | 3 | -- or equal to 3, indicating that the previous SQL statement was not directly obtained from the cache.
  
  
+ --------------- + ------- +
  
  
1 row in set (0.00 sec)
  
  
 
  
  
Mysql> select count (*) from animals;
  
  
+ ---------- +
  
  
| Count (*) |
  
  
+ ---------- +
  
  
| 7 |
  
  
+ ---------- +
  
  
1 row in set (0.00 sec)
  
  
 
  
  
Mysql> show status like 'qcache _ hits ';
  
  
+ --------------- + ------- +
  
  
| Variable_name | Value |
  
  
+ --------------- + ------- +
  
  
| Qcache_hits | 4 |
  
  
+ --------------- + ------- +
  
  
1 row in set (0.00 sec)
 
 

Implementation of MySQL non-repeated Query

Five common MySQL command lines

Two methods to fix mysql tables

How to insert an array into a mysql table in php

Usage of MySQL conditional query statements