存储很少更改但经常访问的数据，哪个是解决方案？
我的 Oracle 数据库包含一些简单的、很少更改的数据（更改大约需要几个月甚至几年的时间）。但它的访问频率很高（每天数百次）。我认为在数据库中持续访问它是昂贵的。
编辑：我正在考虑另一种存储这些数据的方法，而不是存储在数据库中，例如，将其存储在我的应用程序的缓存中，但我对数据一致性感到非常困惑。 我怎样才能有效地做到这一点？
编辑：我原来的问题太笼统了。我想解释得更清楚：
我有一个表，其中包含：
MinValue MaxValue PackageID
1 4 1
5 10 2
11 50 3
当客户端向我们的服务发送请求时，它会发送金额，然后我们的服务必须确定该请求属于哪个包。这取决于金额，并且可能会因业务需要而改变（正如我之前提到的，它很少改变）。
我使用此查询来执行此操作：
select packageid from vmeet_temp where amount between minvalue and maxvalue
是的，它确实有效。但由于我是一个经验不足的程序员，我怀疑是否有更有效的方法来存档它。
所以我的问题是：根据我们的需要，我们是否应该将这些信息存储在数据库中？如果没有，该采取哪种解决方案？
My Oracle database contains some simple, rarely-changed data (it takes about months or even years for it to change). But it's frequently accessed (hundred times/day). I think to continuously access it in database is expensive.
EDIT: I'm thinking about an alternative way to store this data, not in database, for example, store it in my app's cache, but I'm really confused about data consistency.
How can I do this efficiently?
EDIT: my original question is quite too general. I want to explain it clearer:
I have a table that contains:
MinValue MaxValue PackageID
1 4 1
5 10 2
11 50 3
When the client send an request to our service, it will send the amount, then our service has to determine which package this request belong to. This depends on the amount, and may be changed due to business needs (as I mentioned before, it's very rarely changed).
I use this query to do this:
select packageid from vmeet_temp where amount between minvalue and maxvalue
Yes, it does work. But since I'm an inexperienced programmer, I doubt that if there's more efficient way to archive this.
So my question is: for our need, should we store this information in database, or not? If not, which solution to go?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
将其存储在表中，并让 DBMS 处理它。它具有缓存功能，擅长确保经常使用的数据存储在内存中，因此不需要转到磁盘。如果您确实愿意，可以将数据加载到您的应用程序中 - 您会冒数据过时的（小）风险。但一般来说，让 DBMS 来处理它，它就会为您做好准备。
如果表只有 10 行，并且行的大小适中（每行最多几百字节），那么即使您创建一个索引，DBMS 也可能不会费心使用索引 - 它知道这样会更简单并且更方便。直接使用表格效率更高。即使是头脑简单的优化器也能做到这一点。显然，如果你强迫它使用带有错误提示的索引，那么你就会付出性能损失。如果留给它自己的设备，优化器不太可能使用索引。
Store it in a table, and let the DBMS worry about it. It has caching and is good at making sure that frequently used data is stored in memory so it does not need to go to disk for it. If you really want to, you can load the data into your application - you run the (small) risk that the data will be stale. But generally, let the DBMS worry about it and it will get it right for you.
If the table is only 10 rows, and the rows are modest in size (up to a few hundred bytes each), then the DBMS will probably not bother with using an index even if you create one - it knows that it will be simpler and more efficient to use the table directly. Even simple-minded optimizers manage that. Obviously, if you bludgeon it into using the index with misplaced hints, then you get to pay the performance penalty. If left to its own devices, it is unlikely the optimizer would use the index.
首先，@Jonathan Leffler 是正确的，数据库非常擅长缓存有限数据的简单查询。因此，如果数据库服务器没有表现出压力迹象，例如 CPU 利用率较高或磁盘 I/O 情况可能没问题。如果是的话，Oracle 有管理视图来告诉您查询是什么消耗最多的处理能力。
关于缓存，如果您只有一台服务器并且基础表是通过同一应用程序更新的，则可以使用直写策略。如果是多服务器设置，您将需要消息传递来刷新每台服务器上的缓存。如果数据不是通过应用程序更新的，则需要使用后写策略来检查或了解数据何时更改并刷新缓存。
如果性能是您真正关心的问题，我建议您重点关注负载测试，而不是使用 JMeter 等工具来识别瓶颈。强调每次迭代更多线程记录性能指标（通常是页面响应时间）并在 Excel 中将其绘制出来。如果您看到一条指数线，您就发现了瓶颈。退后一些并开始在代码中添加计数器以查看速度减慢的位置。您可能会发现它与您所询问的数据无关。
To start, @Jonathan Leffler is correct, databases are really good at caching simple queries over limited data. So if the database server is not exhibiting signs of stress, such as high CPU utilization or disk I/O things are probably fine. Then if it is, Oracle has management views to tell you what queries are consuming the most processing power.
Regarding caching, if you only have one server and the underlying table is updated through the same application you can use a write through strategy. If a multi-server setup, you'll need messaging to refresh the cache on each server. If the data is not updated through the application, a write behind strategy is necessary to check or know when data changes and refresh the cache.
If performance is your real concern, I would suggest focusing on load testing instead to identify bottlenecks using a tool such as JMeter. Stress with iteratively more threads recording performance metric(s) each time - typically page response time - and chart it out in Excel. If you see a exponential line you've identified a bottleneck. Back off some and start adding counters in code to see where the slow down is. You may find it has nothing to do with the data you're asking about.
我建议你看看11g中的客户端结果缓存。
此处
"此功能允许客户端在内存中缓存 SQL 查询结果集。这
客户端结果缓存对 ODP.NET 应用程序完全透明，并且其
结果集数据的缓存自动与任何会话或数据库保持一致
服务器端的更改会改变结果。
多次调用同一查询的 .NET 应用程序性能得到改善
因为查询结果是在本地检索的。本地客户端处理速度比
进行数据库往返以重新执行查询并获取结果。如果申请
经常运行相同的查询，他们将经历显着的
当结果缓存在客户端时，性能得到改进，以及
减少数据库服务器负载。”
I suggest you look at the Client Result Cache in 11g.
An example is here
"This feature enables client-side caching of SQL query result sets in memory. The
client result cache is completely transparent to ODP.NET applications, and its
cache of result set data is automatically kept consistent with any session or database
server side changes that would alter the results.
.NET applications calling the same query multiple times see improved performance
since query results are retrieved locally. Local client processing is faster than
making database round trips to re-execute a query and fetch results. If applications
are frequently running the same queries, they will experience a significant
performance improvement when their results are cached on the client, as well as a
reduction in database server load."
即使数据库数据很少，缓存也是一个好主意。它适用于这种类型的场景——很少更新数据。它确实可以加快速度，因为在很多情况下您甚至不需要访问数据库。
这是一篇描述 .NET 中的缓存的文章
http://msdn.microsoft.com /en-us/library/aa478965.aspx
如果您的应用程序速度很慢，您需要使用分析器进入那里并找出瓶颈所在...
Even with little db data, caching is a good idea. It is meant for this type of scenario -- rarely updated data. It can really speed things up, because you don't even need to go to the DB in a lot of cases.
Here is an article that describes caching in .NET
http://msdn.microsoft.com/en-us/library/aa478965.aspx
If your app is slow, you need to get in there with a profiler and figure out where the bottleneck is...
相对于典型系统将执行的其他数据访问，任何命中小表的查询都不太可能昂贵。
通过缓存优化此查询可能需要付出相当大的努力（如其他答案所示）。
作为一个没有经验的程序员，您可能需要熟悉以下引用：
Any query hitting a small table is unlikely to be expensive relative the the other data access a typical system will perform.
Optimising this query via cacheing is likely to require considerable effort (as the other answers show).
As an inexperienced programmer you might want to familiarse your self with the following quote: