如何让 SQLite 缓存 select 命令的结果
我正在运行一个带有后端 SQLite 数据库的 Web 应用程序，该数据库仅执行读取操作。用户连接到数据库，通过选择命令搜索条目，然后在浏览器中查看结果。但是，选择非常耗时，因为它涉及数百万表行的字符模式匹配。 （结果表的大小相当小）。
不同的用户通常会进行完全相同的搜索，因此，如果我可以缓存第一次选择的结果，则下一个搜索数据库的用户（同时或更可能是几天后）可以获得快速返回结果。
我怎样才能在 SQLite 中做到这一点？有我需要使用的编译指示吗？我听说 SQLite 有自动缓存功能，但这似乎没有帮助。请注意，我正在使用托管服务，因此无论如何我都无法重建 SQLite。
任何帮助将不胜感激。
I am running a web application with a backend SQLite database that solely performs read operations. Users connect to the database, search for entries via a select command, and view the results in a browser. But, the select is quite time-consuming because it involves character pattern matching across several million table rows. (The size of the results table is quite small).
Different users will generally do the exact same search, so if I can cache the results of the select the first time, the next user to search to database (concurrently or more likely a few days later) can get back the results quickly.
How can I do this in SQLite? Is there a pragma I need to use? I hear that SQLite has an automatic caching feature, but this does not seem to help. Note that I'm using a hosting service, so I cannot rebuild SQLite in anyway.
Any help would be much appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
用户是否使用不同的 SQLite DB 连接？尝试使用 PRAGMA 命令来增加缓存大小。
来自文档： http://www.sqlite.org/pragma.html#pragma_cache_size
Are the users using different connections to the SQLite DB? Try using the PRAGMA command to increase cache size.
From documentation: http://www.sqlite.org/pragma.html#pragma_cache_size
我会使用外部缓存解决方案，例如
memcached
或
APC/Zend Cache（在 PHP 上）
然后您可以更好地控制缓存（存储什么、生命周期、完全清除缓存...）
I would use an external caching solution like
memcached
or
APC/Zend Cache (on PHP)
Then you have much more control over your cache (what to store, lifetimes, clearing the cache completely...)
发布上述问题后，我发现了一个似乎有效的简单解决方案，不需要对我使用的 CPanel 托管服务进行任何更改。
我没有缓存 SQL 结果，而是简单地缓存 PHP 脚本生成的整个网页。然后，进行完全相同搜索的用户将获得缓存页面，完全绕过数据库。
我在这里得到了基本的想法。
优点是涉及多个不同数据库调用的相当复杂的结果集都可以缓存在非常小的空间中。然后，使用 PHP 在一定时间后删除缓存的网页是一项简单的任务。
我希望这可以帮助其他解决类似问题的人。
After posting the above question, I found one simple solution that seems effective that doesn't require any changes to the CPanel hosting service that I use.
Instead of caching the SQL results, I simple cache the entire webpage generated by the PHP script. Users making the exact same search are then given the cached page, bypassing the database completely.
I got the basic idea here.
The advantage is that a quite complex results set involving several different database calls can all be cached in a very minimal space. Then, using PHP it's a trivial task to delete the cached webpages after a certain time.
I hope this helps others working on similar problems.
我有一个非常类似的问题，我尝试通过散列查询并将散列/结果存储在缓存表中来优化。
但也有一些陷阱 - 例如，命令：
应该给出完全相同的答案，但是对这两个字符串进行 MD5 处理会给出不同的结果。同样适用：
如果您可以标准化查询进入数据库的方式（对 SELECTables 进行排序、WHERE 过滤器、分组等 - 全部在我们的 PHP/JavaScript 中），那么您可以可靠地快速散列查询，执行它们，然后存储结果在你的数据库中。如果您不打算像OP那样返回整个页面，我建议将其存储为JSON，因为这样就不必稍后转换它，并且JSON在发送给用户之前几乎总是被gzip压缩（如果您的AJAXing到用户屏幕） 。
为了真正加快速度，您的 CACHE 表应该让哈希列包含 UNIQUE 类型，以便自动建立索引。
如果您使用的是 Node.js，那么 id 更进一步，您可以在启动时从磁盘读取此 CACHE 表，将所有哈希值存储在一组对象中，然后当您的服务器收到查询时，只需执行 allHashes.has(hashedSortedQuery ）。由于该操作发生在内存中，因此它本质上是瞬时的。如果为 true，则从 CACHE 中选择结果，其中 Hash='hashedSortedQuery'，否则（实际查询）。
最后一点 - 根据我做同样事情的经验，更新我的 SQLite 版本对速度有巨大的改进，我只是提到它，因为通常在共享托管服务器上，并不总是安装最新版本。这是我今天在我的服务器上安装的全局 SQLite 版本与从源代码编译的最新 SQLite 版本之间进行的比较：http:// Pastebin.com/hpWu3UCk
I have a very similar issue which I attempted to optimise by hashing queries, and storing the hash/result in a CACHE table.
But there are gotchas - for example, the commands:
Should give exactly the same answer, but MD5'ing those two string will give different results. Same goes for:
If you can standardise how the queries come into the database (sorting SELECTables, WHERE filters, grouping, etc - all in our PHP/JavaScript), then you can reliably quick-hash the queries, do them, then store the result in your database. I would recommend storing it as JSON if your not going to return whole pages like the OP, because that saves having to convert it later, and JSON is pretty much always gzipped before sending it to the user (if your AJAXing to the users screen).
To really speed this up, your CACHE table should have the Hash column include the UNIQUE type so it is automatically indexed.
If you are using Node.js, then id go one step further and say you can read this CACHE table from disk on startup, store all the hashes in a set object, then when your server gets a query just do allHashes.has(hashedSortedQuery). Since that operation happens in memory, it is essentially instantaneous. If true, SELECT result from CACHE where Hash='hashedSortedQuery', else, (actual query).
One final note - from my experience doing the same thing, updating my SQLite version had a HUGE improvement on speed, and i only mention it because often on shared hosting servers, the latest version isn't always installed. Heres a comparison i did today between the global SQLite version installed on my server, and the latest SQLite version compiled from source: http://pastebin.com/hpWu3UCk