缓存不经常更改的查询结果的最佳技术 [英] Best technique for caching results from queries that change infrequently

问题描述

我有一个php Web应用程序，其中某些数据每周更改一次，但经常读取.

I have a php web application where certain data changes on a weekly basis but is read very frequently often.

用于检索数据的SQL查询和用于html输出的php代码非常复杂.有多个表联接和许多计算-但是它们导致一个相当基本的html表.将用户分组，每周每个组的表相同，但不同组的表不同.我可能有成千上万个用户的数百个表.

The SQL queries that retrieve the data and the php code for html output are fairly complex. There are multiple table joins, and numerous calculations - but they result in a fairly basic html table. Users are grouped, and the table is the same for each group each week, but different for different groups. I could potentially have hundreds of tables for thousands of users.

出于性能原因，我想缓存此数据.我希望每周运行一个过程来为每个组生成表，而不是每次有人访问页面时都运行这些查询和计算.

For performance reasons, I'd like to cache this data. Rather than running these queries and calculations every time someone hits the page, I want to run a weekly process to generate the table for each group giving me a simple read when required.

我想知道您成功或失败使用了哪些技术来实现这样的目标?

I'd be interested to know what techniques you've used successfully or unsuccessfully to achieve something like this?

我可以看到的选项包括:

Options I can see include:

• 将计算的html结果存储在由用户组标识的MySQL表中
• 将结果数据存储在按用户组标识的MySQL表中(这很困难，因为没有固定数量的数据项)
• 将页面输出缓存在静态文件中

任何其他建议都将受到欢迎！

Any other suggestions would be welcome!

推荐答案

确实有一些选择:

• 每周一次渲染页面，然后静态"提供它们.
• 使用缓存(例如 Squid )将此类响应优先存储在星期.例如，您可以配置缓存策略，以便将转到特定页面(例如very_long.php?...)的请求与网站的其余部分分开缓存.
• 确保打开数据库缓存. MySQL具有自己的缓存，您可以对其进行微调，以免重复的长查询不会被重新计算.

• Prerender the pages on a weekly basis and then serve them "statically".
• Use a cache (e.g. Squid) to cache such responses on a first-chance basis for a week. For example, you can configure the caching policy so requests that go to a particular page (e.g. very_long.php?...) are cached separately from the rest of the website.
• Make sure you turn on DB caching. MySQL has caching of its own and you can fine tune it so that repeated long queries are not recalculated.

这篇关于缓存不经常更改的查询结果的最佳技术的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！