About MySQL query Cache Mechanism

The MySQL query mechanism is an important part of the MySQL database. The following describes the MySQL query cache mechanism in detail and hopes to help you.

The cache mechanism simply caches the SQL text and query results. If the same SQL statement is run, the server directly retrieves 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 a 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 |-blocked or supported query Cache
| Query_cache_wlock_invalidate | OFF |
+ ---------- + --- +
 

Use MySQL temporary tables to accelerate queries

How does MySQL modify indexes in a table structure?

Modify Table Structure in MySQL -- add and delete Fields

10 common MySQL command lines

Seven common MySQL command lines