请选择 进入手机版 | 继续访问电脑版

[MYSQL] 为什么Mysql8.0 删除了查询缓存 为什么不推荐使用缓存

框架与中间件 框架与中间件 1028 人阅读 | 0 人回复




MySQL 8.0: Retiring Support for the Query Cache


MySQL 8.0:停用对查询缓存的支持

发表于 五月 30, 2017 by 马特·洛德 类别: 新闻公告





MySQL 查询缓存是查询结果缓存。它将以 SEL 开头的传入查询与哈希表进行比较,如果存在匹配项,则返回先前执行查询的结果。有一些限制:

  • 查询必须逐字节匹配(查询缓存避免解析)
  • 使用非确定性功能将导致查询未被缓存(包括临时表、用户变量、RAND()、NOW() 和 UDF)。
  • 查询缓存旨在不提供过时的结果。对基础表的任何修改都会导致这些表的所有缓存失效。
  • 对于缓存是否可以用于InnoDB有一些限制(尊重MVCC;当您打开事务时,“缓存”可能无法代表预期视图中的数据。





  • 在MySQL服务器中,我们现在能够重写查询以插入提示(或其他修改以提高性能)
  • 我们有第三方工具,如ProxySQL,它可以充当中间人查询缓存。ProxySQL 还支持缓存的 TTL,这在我之前提供的示例中运行良好(为下拉列表构建值列表)。


自 MySQL 5.6 (2013) 以来,查询缓存已默认禁用,因为已知它不能随多核计算机上的高吞吐量工作负载进行扩展。Rene昨天在他的帖子中证实了这一点,但Stewart SmithDomas Mituzas(更新:和Kristian Koehntopp)之前也提到了这一点。

假设可以提高可伸缩性,查询缓存的限制因素是,因为只有命中缓存的查询才会看到改进;它不太可能提高性能的可预测性。 对于面向用户的系统,减少性能的可变性 通常比提高峰值吞吐量 更重要:

幻灯片 2/47 来自自上而下的方法,以实现数据库系统中的性能可预测性。幻灯片 3/47 来自自上而下的方法,以实现数据库系统中的性能可预测性。## 决定删除对查询缓存的支持

我们同意密歇根大学安娜堡分校的Jiamin Huang,Barzan Mozafari,Grant Schoenebeck,Thomas F. Wenisch进行的研究。我们考虑了我们可以对查询缓存进行哪些改进,以及我们可以进行的优化,从而改进所有工作负载


我们也同意 Rene 的结论,即当缓存靠近客户端时,缓存提供了最大的好处:

“客户端 + 2x ProxySQL”结果显示将缓存移动到客户端时性能提高了 5.2 倍。## 现有用户的升级路径

注意到当前的限制,查询缓存将在MySQL 5.7的生命周期内继续受支持。MySQL 8.0将不支持查询缓存,鼓励升级用户使用服务器端查询重写或ProxySQL作为中间人 缓存。


感谢您使用 MySQL!


Posted on May 30, 2017 by Matt Lord Category: News Announcements

As Rene wrote on the ProxySQL blog yesterday:

Although MySQL Query Cache was meant to improve performance, it has serious scalability issues and it can easily become a severe bottleneck.

This is indeed something we have observed in the MySQL team for a while. Before we get to the subject of today’s post, let me start with an introduction.

Introduction to Query Cache

The MySQL query cache is a query results cache. It compares incoming queries that start with SEL to a hash table, and if there is a match returns the results from the previous execution of the query. There are some restrictions:

  • The query must match byte-for-byte (the query cache avoids parsing)
  • Use of non-deterministic features will result in the query not being cached (including temporary tables, user variables, RAND(), NOW() and UDFs.)
  • The query cache was designed to not serve stale results. Any modification to the underlying table(s) results in all cache being invalidated for those tables.
  • There are some restrictions on if the cache can be used for InnoDB (to respect MVCC; as you have a transaction open, the ‘cache’ might not represent the data in your expected view.)

The Best Case Scenario

As I wrote on my personal blog some years ago:

The ideal scenario for the query cache tends to be largely read-only, where there are a number of very expensive queries which examine millions of rows only to return a few. A hypothetical example might be a complex query to build a list of values for a drop-down list that always appears on a webpage form. In a situation like this, the query cache can mask performance problems caused by missing indexes, which makes it helpful for novice users.

This comment still yields true today, but I think it is important to also point out that DBA tools for poor-application-intervention have also improved:

  • In the MySQL server, we now have the ability to rewrite queries to insert hints (or other modifications to improve performance)
  • We have third-party tools like ProxySQL, which can act as a man-in-the-middle query cache. ProxySQL also supports a TTL for cache, which works fine in the example I provided earlier (build a list of values for a drop-down list).

Limitations of the Query Cache

The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines. Rene confirmed this in his post yesterday, but it has also previously been mentioned by Stewart Smith, Domas Mituzas (update: and Kristian Koehntopp).

Assuming that scalability could be improved, the limiting factor of the query cache is that since only queries that hit the cache will see improvement; it is unlikely to improve predictability of performance. For user facing systems, reducing the variability of performance is often more important than improving peak throughput :


Slide 2/47 from A Top-Down Approach to Achieving Performance Predictability in Database Systems.


Slide 3/47 from A Top-Down Approach to Achieving Performance Predictability in Database Systems.## Decision to Remove Support for the Query Cache

We concur with the research performed by Jiamin Huang, Barzan Mozafari, Grant Schoenebeck, Thomas F. Wenisch at the University of Michigan, Ann Arbor. We considered what improvements we could make to query cache versus optimizations that we could make which provide improvements to all workloads .

While these choices themselves are orthogonal, engineering resources are finite. That is to say that we are shifting strategy to invest in improvements that are more generally applicable to all workloads.

We also agree with Rene’s conclusion, that caching provides the greatest benefit when it is moved closer to the client:


The “Client + 2x ProxySQL” results showing a 5.2x performance improvement when moving the cache to the client.

Upgrade Path for Existing Users

With the current limitations noted, the query cache will continue to be supported for the life time of MySQL 5.7. MySQL 8.0 will not support query cache, and users upgrading will be encouraged to use either Server-side Query Rewrite or ProxySQL as a man-in-the-middle cache.

We expect this change to only affect a small number of users, but if this concerns you, please reach out and get in touch!

Thank you for using MySQL!




  • 目前专注于分享Java领域干货,公众号同步更新。原创以及收集整理,把最好的留下。
[CXX1300] CMake '3.18.1' was not
[md][CXX1300] CMake '3.18.1' was not found in SDK, PATH, or
[md]获取正版 OFFICE你的许可证并非正版,你可能是盗版软件的受