The reasons why MySQL query cache effect is not obvious include: 1. Only effective for exactly the same SQL, and different spaces or case are considered as new queries; 2. Each time the table has a write operation, the relevant cache will be cleared, and the hit rate is low in frequent read and write scenarios; 3. The cache efficiency depends on the usage mode, which is only suitable for scenarios where there are fewer data changes and many repeated queries. Alternative solutions include: 1. Application-layer cache (such as Redis), which controls fine granularity but requires management of life cycle; 2. Proxy-layer cache (such as ProxySQL), which supports flexible and regular configuration; 3. Optimize SQL and indexes to fundamentally improve performance. You can judge the cache efficiency by viewing the Qcache status indicators. If the number of hits is much lower than the number of inserts, you should consider disabling it.

MySQL's query caching was once a tool used by many people to improve database performance, but its limitations are often troublesome. If you find that the query cache effect is not obvious, or even slowing down the system, it may not be a problem with your operation, but it is determined by its own mechanism.

Let’s take a look at its limitations from several common perspectives and what alternatives we can use.

Query cache is not a "universal accelerator"

Many people think that as long as the query cache is turned on, all SELECT statements can be faster. In fact, it only works for the exact same SQL statement . Even if there is only one space or case difference, it will be regarded as a different query and cannot hit the cache.

Moreover, every time a table has a write (INSERT, UPDATE, DELETE), MySQL will clear all cached results related to the table. This means that in scenarios where read and write frequently, query cache will become a burden - because the data that has just been cached will be quickly cleared, which will also increase maintenance overhead.

For example:

• Table users are updated every minute
• Multiple SELECT queries frequently access this table
• After each update, the previous cache is cleared
• The result is that the cache hit rate is low, which wastes resources

Query cache efficiency depends on usage mode

In certain specific application scenarios, query cache is still useful, such as:

• The data rarely changes, but is queried a lot (such as static configuration information)
• Very few write operations, read operations dominate
• SQL statements are highly consistent and do not change frequently

However, in most modern web applications, data changes frequently and SQL statements are generated dynamically, and query caches are almost useless at this time.

You can check whether it is worth continuing by looking at the status variables of MySQL:

 SHOW STATUS LIKE 'Qcache%';

Focus mainly on these indicators:

• Qcache_hits : cache hits
• Qcache_inserts : cache inserts
• Qcache_lowmem_prunes : The number of caches that were cleared due to insufficient memory

If the number of hits is much lower than the number of inserts, it means that the cache efficiency is very low.

Alternatives are more flexible and efficient than query cache

Since the built-in query cache has so many restrictions, is there a better way? Of course there are:

Using application layer cache

For example, Redis or Memcached does not depend on whether SQL is consistent, but caches data based on business logic. You can cache some hot data on the application server to reduce the number of direct access to the database.

advantage:

• Fineering granularity control
• You can set the expiration time or actively clean it up
• Not affected by SQL variants

shortcoming:

• Need to manage the cache life cycle yourself
• Increase system complexity

Use proxy layer caches (such as ProxySQL)

ProxySQL is a powerful MySQL middleware that supports query caching and can flexibly control which SQL to cache and how long it takes to cache according to rules.

Compared with MySQL's own query cache, it:

• Support regular matching cache rules
• More flexible caching strategy
• Little performance impact

Reasonably design indexing and optimize SQL

Many times we want to rely on caching to solve performance problems, but the root cause is that SQL is not well written or indexed is unreasonable. Taking some time to optimize the query structure and add appropriate indexes is often more effective than enabling query cache.

Basically that's it. The query cache has been completely removed from MySQL 8.0, which shows that the official also believes that it is not a long-term solution. Although it is still valuable in specific scenarios, more often we need to change our thinking to solve the problem.

The above is the detailed content of Understanding MySQL Query Cache Limitations and Alternatives. For more information, please follow other related articles on the PHP Chinese website!