MySQL查询缓存与应用层缓存结果集
我正在运行一个 php/mysql 驱动的网站，访问量很大，我正在考虑在共享内存中缓存结果集的可能性，以减少数据库负载。
然而，现在 MySQL 的查询缓存已启用，而且它似乎做得相当不错，因为如果我禁用查询缓存，CPU 的使用率会立即跳到 100%。
鉴于这种情况，我不知道使用 PHP 在共享内存中本地缓存结果集（甚至生成的 HTML 代码）是否会带来任何明显的性能改进。
有没有人有这方面的经验？
PS：请避免建议像 memcached 这样的重型解决方案。现在我正在寻找不需要太多时间来实施、部署和维护的简单解决方案。
编辑：
我看到我关于 memcached 的评论偏离了实际观点，即考虑到这些查询的结果已经在数据库级别缓存，在应用程序层缓存数据库查询是否会导致明显的性能影响。
I'm running a php/mysql-driven website with a lot of visits and I'm considering the possibility of caching result-sets in shared memory in order to reduce database load.
However, right now MySQL's query cache is enabled and it seems to be doing a pretty good job since if I disable query caching, the use of CPU jumps to 100% immediately.
Given that situation, I dont know if caching result-sets (or even the generated HTML code) locally in shared memory with PHP will result in any noticeable performace improvement.
Does anyone out there have any experience on this matter?
PS: Please avoid suggesting heavy-artillery solutions like memcached. Right now I'm looking for simple solutions that dont require too much time to implement, deploy and maintain.
Edit:
I see my comment about memcached deviated answers from the actual point, which is whether caching DB queries in the application layer would result in a noticeable performace impact considering that the result of those queries are already being cached at the DB level.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我知道您不想听到 memcached，但它是您想要做的事情的最佳解决方案之一。根据您的站点使用情况，性能可能会有巨大的提高。通过简单地在数据库会话处理程序上使用 memcached 的会话处理程序，我能够将负载减少一半，并将请求服务时间减少 30% 以上。
实际上，memcached 是一个简单的解决方案。它已经与 PHP 集成（如果您加载了扩展），并且几乎不需要任何配置（我只需将 memcached 添加为我的 Linux 机器上的一项服务，只需一两个 shell 命令即可完成）。
我建议将会话数据（以及任何适合缓存的数据）存储在内存缓存中。对于动态页面（例如堆栈溢出主页），我建议将输出缓存几秒钟以防止洪水。
I know you didn't want to hear about memcached, but it is one of the best solutions for what you're trying to do. Depending on your site usage, there can be massive improvements in performance. By simply using memcached's session handler over my database session handler, I was able to cut the load in half and cut back on request serving times by over 30%.
Realistically, memcached is a simple solution. It's already integrated with PHP (if you have the extension loaded), and it requires virtually no configuration (I simply had to add memcached as a service on my linux box, which is done in one or two shell commands).
I would suggest storing session data (and anything that lends itself to caching) in memcache. For dynamic pages (such as stack overflow homepage), I would recommend caching output for a couple of seconds to prevent flooding.
一个不错的单盒解决方案是基于文件的缓存，但您必须手动清除它们。除此之外，您可以使用 APC，它非常快并且位于内存中（但仍然需要自己使它们过期）。
不过，一旦扩展到超过一台 Web 服务器，您就需要一个共享缓存，即 memcached。为什么你如此坚决不部署这个？这并不难，而且只会节省您以后的时间。您可以现在开始使用 memcache 并完成它，或者您可以暂时使用上述方法之一，然后最终切换到 memcache，从而导致更多工作。另外，您不必运行 cronjob 或其他一些丑陋的黑客来获得缓存过期功能：它会为您做到这一点。
mysql 查询缓存很好，但也不是没有问题。其中最大的问题之一是每次更改源数据时它都会自动过期，而您可能不希望这样做。
A decent single box solution is file-based caching, but you have to sweep them out manually. Other than that, you could use APC, which is very fast and in-memory (still have to expire them yourself though).
As soon as you scale past one web server, though, you're going to need a shared cache, which is memcached. Why are you so adamant about not deploying this? It's not hard, and it's just going to save you time down the road. You can either start using memcache now and be done with it, or you could use one of the above methods for now and then end up switching to memcache later anyways, resulting in even more work. Plus too, you don't have to deal with running a cronjob or some other ugly hack to get cache expiration features: it does that for you.
The mysql query cache is nice, but it's not without issues. One of the big ones is it expires automatically every time the source data is changed, which you probably don't want.