High-performance MySQL-query cache introduction

To put it simply, for the same SQL, you can skip the SQL parsing and execution plan generation stages and directly return the cached results. That is, cache the complete SELECT query results.

1. MySQLQuery cache:

The cache is stored in a hash table and referenced by a hash value. The hash value includes: the query itself, the current query database, the client protocol, etc. However, it should be noted that any difference in the SQL statement will cause cache hit failure (spaces, comments, variable names are different)

But the query cache also has serious performance problems:
(1) Must check before reading the query Whether the cache is hit

(2) If the query is not in the cache, it will be put into the cache, occupying the system consumption

(3) When the data table is newly updated with data, the cache will become invalid, causing Huge system loss.

Obviously, cache and cache failure will cause serious system consumption. Querying the cache under high concurrency may cause system performance degradation and zombies. Therefore, it is recommended to use an in-memory database such as memcache to cache data.

To put it simply, for the same SQL, you can skip the SQL parsing and execution plan generation stages and directly return the cached results. That is, the complete SELECT query results are cached.

1. MySQL query cache:

The cache is stored in a hash table and referenced by a hash value. The hash value includes: the query itself, the current query database, the client protocol, etc. However, it should be noted that any difference in the SQL statement will cause cache hit failure (spaces, comments, variable names are different)

But the query cache also has serious performance problems:
(1) Must check before reading the query Whether the cache is hit

(2) If the query is not in the cache, it will be put into the cache, occupying the system consumption

(3) When the data table is newly updated with data, the cache will become invalid, causing Huge system loss.

Obviously, cache and cache failure will cause serious system consumption. Querying the cache under high concurrency may cause system performance degradation and zombies. Therefore, it is recommended to use an in-memory database such as memcache to cache data.

The above is the detailed content of High-performance MySQL-query cache introduction. For more information, please follow other related articles on the PHP Chinese website!