PHP - 在 txt 文件中缓存 MYSQL 查询是一个好习惯吗？
我正在建立一个网上商店&尝试通过最小化 MYSQL 查询来提高性能。
通过 txt 文件缓存 mysql 查询然后获取它而不是查询是一个好的做法吗？这就是我正在做的“
- php 类将 sql 查询作为字符串，
- 则对其进行 md5
- 如果这是第一次运行，
- ，然后在数据库上执行查询，
- 在数组中获取结果，
- 序列化数组并存储它作为 md5_Of_Query.txt
- 返回 unserialize(file_get_contents(md5_of_Query.txt)) 或 $results 实际查询，具体取决于缓存是否存在并且
- 该类也 有效。检查 txt 文件的 filemtime() ，如果它大于一小时，则重新执行查询并刷新缓存，
这比每次执行 sql 查询更有效吗？
I'm building an online shop & trying to improve performance by minimising MYSQL queries.
Is it good practice to cache the mysql queries via a txt file and then fetch that instead of the query? This is what I'm doing"
- A php class takes the sql query as a string
- does an md5 of it
- if this is the first time it's run
- then perform the query on the database
- get the results in an array
- serialize the array and store it as md5_Of_Query.txt
- return either unserialize(file_get_contents(md5_of_Query.txt)) or $results of actual query, depending on whether or not the cache exists and is valid.
- The class also checks the filemtime() of the txt file and if its greater than say, one hour old, then re-perform the query and refresh the cache.
Is this more efficient than doing sql queries every time? Any security issues I'm missing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
如果您进行基准测试，创建唯一散列和执行磁盘 IO 的成本将比简单地从 MySQL 服务器获取的成本更高。
恕我直言，不必费心去达到这个程度。好主意，但 MySQL 已经有了内部缓存和性能调整。
专注于构建您的应用程序，因为“过早的优化是万恶之源”。
If you do a benchmark, the costs of doing creating a unique hash and performing IO to disk will be greater than simply fetching from the MySQL server.
IMHO, don't bother going to the extent. Good thoughts, but MySQL already has internal caching and performance tweak.
Focus on building your application, as "premature optimization is the root of all evil".
如果您刚刚启动应用程序，内存缓存是比使用文本文件更快的方法。
http://memcached.org/
文本文件可以完成这项工作，并且您概述的步骤很有意义，但 memcache 会更快，并且为您处理许多繁重的工作。
If you're just starting the application, memcache is a much faster way to go than using text files.
http://memcached.org/
Text files will do the job, and the steps you've outlined make sense, but memcache will be faster and handle a lot of the heavy lifting for you.
你的方法看起来有点像把问题从一个角落转移到另一个角落。
引入缓存并不能提高Mysql性能。最好看看哪些查询实际上很慢，然后优化查询。
http://dev.mysql.com/doc/refman /5.1/en/slow-query-log.html
Your method looks a bit like shifting the problem from one corner into the other.
Introducing a cache is not improving Mysql performance. Better look which queries are actually slow and then optimize the queries.
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
如果您想实现缓存并着眼于未来的可扩展性，我建议您设置在数据库上运行查询的 RESTful 服务，然后使用 Web 服务器的 HTTP 缓存功能来缓存结果。步骤如下：
您可以在此处阅读有关使用 Apache 缓存 PHP 的更多信息。您现在所做的与此接近，但是您的应用程序将能够通过基于服务的方法更好地扩展。
If you want to achieve caching with an eye towards future scalability, I would recommend setting up RESTful services that run the queries on the database, and then using the HTTP caching features of your web server to cache the results. The steps would look like:
You can read more about caching PHP with Apache here. What you are doing now is close to this, but your application will be able to scale better with the service-based approach.
只是想权衡一下我的两分钱，serialworm 和 thephpdeveloper 所说的共享这样一个事实：内存/RAM 比您提出的任何磁盘 IO 绑定操作要快得多。
把尽可能多的内存扔给mysql，你就不需要处理缓存管理，除非你真的需要升级到集群，而这需要其他考虑。
Memcache 使您可以更好地控制缓存管理，因此您需要进行更多编码。
我将首先构建应用程序，然后对其进行压力测试并优化查询，和/或根据需要添加缓存管理。
Just wanted to weigh in my two cents, what serialworm and thephpdeveloper said share the fact that memory/ram is much faster than any disk IO bound operation you come up with.
Throw as much ram as you can to mysql and you won't need to deal with cache management unless you really need to upgrade to a cluster, and that needs other considerations.
Memcache gives you more control over cache management and consequently you need to do more codding.
I would start by building the app then stress test it and optimize queries, and/or add cache management as needed.
需要注意两件事，基准测试和分析。您可以有意义地比较 atything 的唯一方法是同时使用这些学科的 2 个指标，使用您当前使用的 mysql 配置、php.ini、httpd.conf、.htaccess、mod 重写内容和许多其他内容将进行基准测试和分析执行任务的代理技术。
Two things 2 look at , benchmarking and profiling. About the only way you can compare meaningfully atything is by using the 2 metrics of these diciplines together, using your currently used mysql config, php.ini, httpd.conf, .htaccess, mod rewrite stuff and many other stuff would be benchmarking and profiling the acting technologies doing the task.
这是无意义的，您应该缓存结果。
查询组装时间应该可以忽略不计。 （如果不是，那么您没有按照预期使用 SQL，而是生成流愚蠢的
select，其中智能join就可以解决）从磁盘加载查询显然很容易减慢速度。
（不过，操作系统可能会缓存磁盘 IO，从而很难发现）
无论如何，真正需要花时间的是从数据库中获取结果，然后将它们重新设计到网页槽中一个模板。在你的地方，我会将给定查询的样式结果缓存到磁盘，当被问到时，如果缓存不太旧，我会直接
readfile( ）他们。It's nonsensical, you should cache results.
The query assembly time should be pretty negligible. (If it isn't, you're not using SQL as it's supposed to, generating, instead, streams of stupid
selects where a smartjoinwould have solved)Loading from disk the query obviously is prone to slow things down.
(The OS could be caching disk IO, though, and making it hard to spot)
What really should take time anyway, is getting the results out of the DB and then style them back into a web page trough a template. At your place I would cache to disk the styled results of a given query, and when asked, if the cache is not much old, I would directly
readfile()them.您是否配置了 MySQL 的查询缓存？
Have you configured MySQL's query cache?