安卓：保持在内存和数据库中的数据在同一时间的最佳实践 [英] Android: Best practice for keeping data in Memory and Database at same time

问题描述

我们正在设计一个Android应用程序，有大量的数据（客户，产品，订单......），我们不希望每一个我们需要一些创纪录的时间来查询sqlite的。我们想避免数据库查询，因为大多数，因为我们可以，所以我们决定要保持一定的数据传真到内存中。

We're designing an Android app that has a lot of data ("customers", "products", "orders"...), and we don't want to query sqlite every time we need some record. We wanna avoid to query database as most as we can, so we decided to keep certain data allways in memory.

我们的初步想法是创建2个简单的类：

Our initial idea is to create 2 simple classes:

1. MemoryRecord：将包含基本对象的数组（串/ INT /双/日期/等...），这是从表记录的数据，并且所有的方法来获取这些类数据/从该数组。

1. "MemoryRecord": a class that will contain basically an array of objects (string/int/double/datetime/etc...), that are the data from a table record, and all methods to get those data in/out from this array.

MemoryTable：将包含基本上是一个地图[键，MemoryRecord]和所有方法来操作这个地图和插入/更新/从数据库中删除记录到/类

"MemoryTable": a class that will contain basically a Map of [Key,MemoryRecord] and all methods to manipulate this Map and insert/update/delete record into/from database.

这些类将被导出到各种表格，我们在数据库中。当然，有上述未列出的其他有用的方法，但它们都没有在这一点很重要。

Those classes will be derived to every kind of table we have in database. Of course that there are other usefull methods not listed above, but they are not important at this point.

因此​​，从应用程序的时候，我们将利用这些类加载从SQLite数据库的表来存储，而且每次我们需要改变一些数据的时候，我们将改变在内存中，并张贴到数据库权后。

So, when starting app, we will load those tables from SQLite database to memory using those classes, and every time we need to change some data, we will change in memory and post it into database right after.

不过，我们需要一些帮助/意见你。您能否提供一些更简单或有效实施这样的事情？或者一些现有的类已经为我们做？

But, we want some help/advice from you. Can you suggest something more simple or efficient to implement such thing? Or maybe some existing classes that already do it for us?

在此先感谢。

更新：

我明白你们是想告诉我，我向你表示感谢。

I understand what you guys are trying to show me, and I thank you for that.

但是，让我们说我们有2000条记录的表，我将列出这些记录。对于每一个，我要查询其他30桌（他们中的一些有1000条记录，其他10条记录），以添加额外的信息列表中，而这，而它的飞行（如你所知，我们必须非常快在这个momment）。

But, let's say we have a table with 2000 records, and I will to list those records. For each one, I have to query other 30 tables (some of them with 1000 records, others with 10 records) to add additional info in the list, and this while it's "flying" (and as you know , we must be very fast at this momment).

现在你会说：只是建立你与所有那些主查询连接，并把所有你需要在一个步骤的SQLite可以非常快，如果你的数据库是精心设计的，等等。 。

Now you'll gonna say: "just build your main query with all those 'joins', and bring all you need in one step. SQLite can be very fast, if your database is well designed, etc...".

确定，但此查询将变得非常复杂和不确定，甚至SQLite的是非常快的，这将是太慢（2 4秒，因为我证实，这不是一个可接受的时间对我们来说）。

OK, but this query will become very complicated and sure, even SQLite be very fast, it will be "too" slow (2 a 4 seconds, as I confirmed, and this isn't a acceptable time for us).

另一个complicator是，这取决于用户的交互，我们需要重新查询的所有记录，因为所涉及的表是不一样的，我们必须重新加入与另一组表。

Another complicator is that, depending of user interaction, we need to "re-query" all records, because the tables involved are not the same, and we have to "re-join" with another set of tables.

那么，另一种是只将主记录（这永远不会改变，不管是什么用户执行或希望），没有加入（这是非常快的！），我们需要一些数据，每次查询其他表。请注意，在桌子上有10只的记录，我们将获取相同的记录很多，很多次。在这种情况下，它是一个时间赤身，因为不管快sqlite的是，它将永诺更昂贵查询/鼠标/读取的/ etc ...不只是抢的记录来自于一种内存缓存的。我想明确表示，我们不打算将所有数据保存在内存传真，只是一些表我们查询非常offten。

So, an alternative is bring only the main records (this will never change, no matter what user does or wants) with no join (this is very fast!) and query the others tables every time we want some data. Note that on the table with 10 records only, we will fetch the same records many and many times. In this case, it is a wast of time, because no matter fast sqlite be, it will allways be more expensive to query/cursor/fetch/etc... than just grab the record from a kind of "memory cache". I want to make clear that we don't plan to keep all data in memory allways, just some tables we query very offten.

和我们来到了原来的问题：什么是最好的办法缓存的记录。我真的很喜欢把重点放在讨论，而不是为什么你需要缓存的数据？

And we came to the original question: what is the best way to "cache" those records. I really like to focus the discussion on that and not "why do you need to cache data?"

再次感谢。

推荐答案

在应用程序绝大多数的平台上（联系人，电子邮件时，Gmail，日历等）不这样做。其中的一些具有极其复杂的数据库模式，可能会有大量的数据，并不需要这样做。你所提出做将会导致的巨大的疼你，没有明确的收益。

The vast majority of the apps on the platform (contacts, Email, Gmail, calendar, etc.) do not do this. Some of these have extremely complicated database schemas with potentially a large amount of data and do not need to do this. What you are proposing to do is going to cause huge pain for you, with no clear gain.

您应该首先专注于设计数据库和架构能够做到有效的查询。主要有两个原因，我能想到的数据库访问是缓慢的：

You should first focus on designing your database and schema to be able to do efficient queries. There are two main reasons I can think of for database access to be slow:

• 您已经非常复杂的数据模式。
• 您有一个非常大的数据量。

如果你将有大量的数据，你不能让这一切在内存中，无论如何，所以这是一个死胡同。如果你有复杂的结构，应在这两种情况下受益以优化它们以提高性能。在这两种情况下，你的数据库架构将是关键，良好的性能。

If you are going to have a lot of data, you can't afford to keep it all in memory anyway, so this is a dead end. If you have complicated structures, you would benefit in either case with optimizing them to improve performance. In both cases, your database schema is going to be key to good performance.

其实优化模式可以是一个有点黑色艺术（和我在这方面的专家），但有些东西看出来的是正确的，你将查询行创建索引，设计连接，以便他们将采取有效路径等我相信有很多人谁可以帮助你与这个区域。

Actually optimizing the schema can be a bit a of a black art (and I am no expert on it), but some things to look out for are correctly creating indices on rows you will query, designing joins so they will take efficient paths, etc. I am sure there are lots of people who can help you with this area.

您也可以尝试寻找一些平台的数据库的来源，以了解如何设计良好的表现一些想法。例如联系人数据库（特别是2.0开始）是极其复杂的，有很多的优化，提供比较大的数据和可扩展的数据集，有很多不同类型的查询性能好。

You could also try looking at the source of some of the platform's databases to get some ideas of how to design for good performance. For example the Contacts database (especially starting with 2.0) is extremely complicated and has a lot of optimizations to provide good performance on relatively large data and extensible data sets with lots of different kinds of queries.

更新：

下面是如何重要的数据库优化是一个很好的例证。在Android的媒体提供商的数据库，该平台的新版本改变了架构显著增加一些新的功能。升级code修改现有的媒体数据库到新的模式可能需要8分钟或更长时间来执行。

Here's a good illustration of how important database optimization is. In Android's media provider database, a newer version of the platform changed the schema significantly to add some new features. The upgrade code to modify an existing media database to the new schema could take 8 minutes or more to execute.

这是工程师做了优化的降低一个真正的测试数据库的升级时间从8分钟8秒。一个60X的性能提升。

An engineer made an optimization that reduced the upgrade time of a real test database from 8 minutes to 8 seconds. A 60x performance improvement.

什么是这个优化？

这是创建一个临时索引，在升级点，在升级操作中使用的一个重要的列。 （然后将其删除完成时。）因此，这60倍的性能改进而来的，尽管它也包括在升级过程中使用的一列建索引所需的时间。

It was to create a temporary index, at the point of upgrade, on an important column used in the upgrade operations. (And then delete it when done.) So this 60x performance improvement comes even though it also includes the time needed to build an index on one of the columns used during upgrading.

SQLite是那些东西在那里，如果你知道你在做什么它可以是非常有效的。如果你不小心你如何使用它，你可以结束了猥琐的性能。这是一个安全的赌注，不过，如果您有与它的性能问题，你可以通过改善你如何使用SQLite解决这些问题。

SQLite is one of those things where if you know what you are doing it can be remarkably efficient. And if you don't take care in how you use it, you can end up with wretched performance. It is a safe bet, though, if you are having performance issues with it that you can fix them by improving how you are using SQLite.

这篇关于安卓：保持在内存和数据库中的数据在同一时间的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！