如何存储SQL查询结果以便快速访问？
SQL初学者在这里。
我有一个查询需要大约 10 秒才能运行，因此应用程序速度变慢。
在 MySQL 或更普遍的 SQL 中，服务器是否可以定期（每 1 到 5 分钟）运行查询并将其存储在某个地方，以便我可以查询这个“缓存”表以便于访问？
非常感谢！
SQL beginner here.
I have a query which takes around 10 seconds to run, so we have a slow down in the application.
Would it be possible in MySQL or more generally in SQL for the server to periodically (every 1 to 5 minutes) run the query and store it somewhere, so that I can query this "cache" table for easy access ?
Many thanks in advance !
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
最有可能的是，根据您的查询是什么，您可以通过在表上创建缺失的索引来加速查询。将 EXPLAIN EXTENDED 放在查询前面即可查看它正在使用哪些索引，并尝试找出应该对哪些列建立索引。应索引的列是 where 语句中的列，以及联接中使用的任何字段。如果您提供查询和表架构，我们也许可以帮助推荐一些索引。
虽然可能有很多方法可以每 5 分钟运行一个进程来填充临时表（cron 作业、计划任务、从应用程序运行线程），但在采取诸如此类的严厉措施之前，最好先用尽索引选项这样，这可能只会使用更多的资源。并且不一定会提高应用程序的性能。
Most likely depending on what your query is, you can speed up the query by creating missing indexes on your table(s). Put EXPLAIN EXTENDED in front of your query to see which indexes it is using, and try to figure out which columns should be indexed. The columns that should be indexed are those in your where statement, as well as any fields used in joins. If you provide the query, and the table schema, we may be able to help recommend some indexes.
While there probably are quite a few ways to run a process every 5 minutes to fill a temp table (cron job, scheduled task, run a thread from your app), you would probably be better off exhausting the indexing option before taking drastic measures such as this, which may just use more resources. and may not necessarily increase the performance of your application.
您的应用程序可能有一个线程唤醒、执行查询、将结果保存到临时表中，而应用程序的其余部分将仅使用临时表。
Your application could have a thread that wakes up, does the query, saves the results into a temp table, and the rest of the application would just use the temp table.
当然，这个解决方案现在可能超出了您的范围。您可以使用 ORM 框架来执行缓存。基本上，您要求的是为您的结果提供缓存解决方案。缓存解决方案可能会根据您使用的供应商和语言而有所不同。
Granted this solution is probably beyond your scope right now. You can use an ORM Framework to perform the caching for you. Basically what you are asking for is for a caching solution for your results. The caching solutions can vary based on the vendor and language you are using.
MySQL 中没有这样的调度规定。
但是，您始终可以运行计划作业（通过 Windows 上的
at/scheduler、cron 或您在 Unix 上使用的任何调度系统）来执行此类“执行并将结果插入临时表”查询/过程。除了您的直接问题之外，还要考虑优化查询 - 如果您还没有这样做，明智地应用索引和/或重新架构表可能会加快速度（但我完全承认一些查询被优化为最好的）
另一个解决方案是流式传输/分块 - 让应用程序按需/需要检索“接下来的 N 行”。
另外，考虑将查询结果缓存在应用程序本身而不是数据库中 - 这样缓存不需要数据库访问，而且速度更快。
There's no such scheduling provision in MySQL.
However you can always run a scheduled job (via
at/scheduler on Windows, cron or whatever your scheduling system you use on Unix) to execute such a "execute and insert results into staging table" query/procedure.Aside from your direct question, also look at optimizing the query - it's possible that judicious application of indexing and/or re-architecting the tables might speed it up if you have not done so yet (but I fully admit the some queries are optimized as best as they can be)
Another solution is streaming/chunking - have the app retrieve "next N rows" on demand/need.
Also, consider caching the query results in teh app itself instead of in DB - that way cache does not require DB access and is even faster.