在 Android 上同时将数据保存在内存和数据库中的最佳实践

Best practice for keeping data in memory and database at same time on Android

提问by Christian

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 want to avoid to query the database as most as we can, so we decided to keep certain data always in memory.

我们正在设计一个包含大量数据（“客户”、“产品”、“订单”...）的 Android 应用程序，我们不想每次需要一些记录时都查询 SQLite。我们希望尽可能避免查询数据库，因此我们决定将某些数据始终保留在内存中。

Our initial idea is to create two simple classes:

我们最初的想法是创建两个简单的类：

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.

2. "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.

1. “MemoryRecord”：一个基本上包含对象数组（字符串、整数、双精度、日期时间等）的类，这些对象是表记录中的数据，以及从中获取/取出这些数据的所有方法大批。

2. “MemoryTable”：一个基本上包含 [Key,MemoryRecord] 的 Map 的类，以及操作此 Map 和向/从数据库插入/更新/删除记录的所有方法。

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

这些类将派生到我们数据库中的每一种表。当然还有其他有用的方法没有在上面列出，但在这一点上它们并不重要。

So, when starting the app, we will load those tables from an 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 the database right after.

因此，在启动应用程序时，我们将使用这些类将这些表从 SQLite 数据库加载到内存中，并且每次我们需要更改某些数据时，我们都会在内存中更改并立即将其发布到数据库中。

But, we want some help/advice from you. Can you suggest something more simple or efficient to implement such a 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.

我理解你们想向我展示的东西，我为此感谢你们。

But, let's say we have a table with 2000 records, and I will need 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 information in the list, and this while it's "flying" (and as you know, we must be very fast at this moment).

但是，假设我们有一个包含 2000 条记录的表，我需要列出这些记录。对于每一个，我必须查询其他 30 个表（其中一些有 1000 条记录，其他有 10 条记录）以在列表中添加其他信息，而这在它“飞行”的同时（如您所知，我们必须非常快）此刻）。

Now you'll be going to 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 可以非常快，如果你的数据库设计得很好，等等......”。

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

好的，但是这个查询会变得非常复杂并且肯定，即使 SQLite 非常快，它也会“太”慢（正如我所确认的，2 到 4 秒，这对我们来说不是一个可接受的时间）。

Another complicator is that, depending on 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.

另一个复杂的是，根据用户交互，我们需要“重新查询”所有记录，因为涉及的表不一样，我们必须与另一组表“重新连接”。

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 other 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 waste of time, because no matter fast SQLite is, it will always be more expensive to query, cursor, fetch, etc... than just grabbing the record from a kind of "memory cache". I want to make clear that we don't plan to keep all data in memory always, just some tables we query very often.

所以，另一种方法是只带上主记录（无论用户做什么或想要什么，这都不会改变）而不加入（这非常快！）并且每次我们想要一些数据时查询其他表。请注意，在只有 10 条记录的表上，我们将多次获取相同的记录。在这种情况下，这是浪费时间，因为无论 SQLite 速度如何，查询、游标、获取等总是比从某种“内存缓存”中获取记录更昂贵。我想说明的是，我们不打算将所有数据始终保留在内存中，只是我们经常查询的一些表。

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?"

我们来到了最初的问题：“缓存”这些记录的最佳方法是什么？我真的很想把讨论重点放在这个上，而不是“为什么需要缓存数据？”

回答by hackbod

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 hugepain for you, with no clear gain.

平台上的绝大多数应用程序（联系人、电子邮件、Gmail、日历等）都没有这样做。其中一些具有极其复杂的数据库模式，可能包含大量数据，因此不需要这样做。你提议做的事情会给你带来巨大的痛苦，而且没有明显的收益。

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:

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

• You have really complicated data schemas.
• You have a very large amount of data.

• 你有非常复杂的数据模式。
• 你有大量的数据。

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.

实际上优化模式可能有点像魔术（我不是这方面的专家），但是需要注意的一些事情是在您将查询的行上正确创建索引，设计连接以便它们采用有效的路径等. 我相信有很多人可以在这方面为您提供帮助。

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.

您还可以尝试查看一些平台数据库的来源，以了解如何设计以获得良好性能的一些想法。例如，Contacts 数据库（特别是从 2.0 开始）非常复杂，并且有很多优化可以在相对较大的数据和具有大量不同类型查询的可扩展数据集上提供良好的性能。

Update:

更新：

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.

这里很好地说明了数据库优化的重要性。在 Android 的媒体提供商数据库中，新版本的平台显着更改了架构以添加一些新功能。将现有媒体数据库修改为新架构的升级代码可能需要 8 分钟或更长时间才能执行。

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.

一位工程师进行了优化，将真实测试数据库的升级时间从 8 分钟缩短到 8 秒。60 倍的性能提升。

What was this optimization?

这是什么优化？

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.

它是在升级时在升级操作中使用的重要列上创建临时索引。（然后在完成后将其删除。）因此，即使在升级期间使用的列上构建索引所需的时间也包括 60 倍的性能提升。

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.

SQLite 是其中之一，如果您知道自己在做什么，它可以非常高效。如果您不注意如何使用它，最终可能会导致性能不佳。不过，这是一个安全的赌注，如果您遇到性能问题，您可以通过改进 SQLite 的使用方式来解决这些问题。

回答by dhaag23

The problem with a memory cache is of course that you need to keep it in sync with the database. I've found that querying the database is actually quite fast, and you may be pre-optimizing here. I've done a lot of tests on queries with different data sets and they never take more than 10-20 ms.

内存缓存的问题当然是你需要让它与数据库保持同步。我发现查询数据库实际上非常快，您可能在此处进行了预优化。我对使用不同数据集的查询进行了大量测试，它们的时间从不超过 10-20 毫秒。

It all depends on how you're using the data, of course. ListViews are quite well optimized to handle large numbers of rows (I've tested into the 5000 range with no real issues).

当然，这完全取决于您如何使用数据。ListViews 非常适合处理大量行（我已经测试到 5000 范围内，没有出现实际问题）。

If you are going to stay with the memory cache, you may want have the database notify the cache when it's contents change and then you can update the cache. That way anyone can update the database without knowing about the caching. Also, if you build a ContentProvider over your database, you can use the ContentResolver to notify you of changes if you register using registerContentObserver.

如果您打算继续使用内存缓存，您可能希望数据库在内容更改时通知缓存，然后您可以更新缓存。这样任何人都可以在不知道缓存的情况下更新数据库。此外，如果您在数据库上构建 ContentProvider，并且您使用 registerContentObserver 注册，则可以使用 ContentResolver 通知您更改。