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

程序员潇然

原文地址:

https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/

原文标题:

MySQL 8.0: Retiring Support for the Query Cache

机翻

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

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

正如Rene昨天在ProxySQL博客上写的那样：

尽管MySQL查询缓存旨在提高性能，但它存在严重的可扩展性问题，并且很容易成为严重的瓶颈。

这确实是我们在MySQL团队中观察到的一段时间。在我们进入今天帖子的主题之前，让我先介绍一下。

查询缓存简介

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

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

最佳案例方案

正如我几年前在我的个人博客上写的那样：

查询缓存的理想方案往往是只读的，其中有许多非常昂贵的查询，这些查询检查数百万行只是为了返回几行。一个假设的示例可能是一个复杂的查询，用于为始终显示在网页窗体上的下拉列表生成值列表。在这种情况下，查询缓存可以屏蔽由缺少索引引起的性能问题，这对新手用户很有帮助。

这个评论今天仍然适用，但我认为重要的是要指出，用于不良应用程序干预的DBA工具也得到了改进：

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

查询缓存的限制

自 MySQL 5.6 （2013） 以来，查询缓存已默认禁用，因为已知它不能随多核计算机上的高吞吐量工作负载进行扩展。Rene昨天在他的帖子中证实了这一点，但Stewart Smith，Domas 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!