问题描述
我们正在设计一个包含大量数据(客户"，产品"，订单" ...)的Android应用，并且我们不想每次需要记录时都查询SQLite.我们希望避免尽可能多地查询数据库，因此我们决定将某些数据始终保留在内存中.
我们最初的想法是创建两个简单的类:
-
"MemoryRecord":一个基本上包含对象数组(字符串，整数，双精度，日期时间等)的类，这些对象是表记录中的数据，以及获取这些对象的所有方法数据从该数组输入/输出.
-
"MemoryTable":一个基本上包含[Key，MemoryRecord]的Map以及用于操纵该Map以及将记录插入/更新/从数据库中删除记录的所有方法的类.
这些类将派生到数据库中我们拥有的每种表中.当然，还有其他有用的方法未在上面列出，但是在这一点上它们并不重要.
因此，在启动应用程序时，我们将使用这些类将这些表从SQLite数据库加载到内存中，并且每次我们需要更改某些数据时，我们都将更改内存并将其立即发布到数据库中. /p>
但是，我们需要您的帮助/建议.您可以提出一些更简单或更有效的方法来实现这种方法吗?还是一些现有的班级已经为我们做到了?
我了解你们想向我展示的内容，对此我表示感谢.
但是，假设我们有一个包含2000条记录的表，我将需要列出这些记录.对于每个表，我必须查询其他30个表(其中一些表具有1000条记录，另一些表具有10条记录)以在列表中添加其他信息，而这是飞速"的(众所周知，我们必须非常快此时).
现在您将要说:只需使用所有这些'joins'建立您的主查询，并将您需要的所有内容一步一步即可.如果您的数据库设计合理，SQLite可以非常快. ".
好的，但是此查询将变得非常复杂且确定，即使SQLite很快，也会太"慢(如我所证实的，每2到4秒，这对于我们来说是不可接受的时间)
另一个麻烦的问题是，取决于用户的交互作用，我们需要重新查询"所有记录，因为所涉及的表并不相同，因此我们必须重新联接"另一组表.
因此，另一种选择是只带主记录(无论用户做什么或想要什么，它都不会改变)，而无需联接(这非常快！)，并且每次需要一些数据时都查询其他表.请注意，在仅包含10条记录的表上，我们将多次读取相同的记录.在这种情况下，这是浪费时间，因为无论快速的SQLite是什么，查询，游标，提取等总是比从某种内存缓存"中获取记录要昂贵得多.我想说明一下，我们并不打算将所有数据始终保留在内存中，而只是计划我们经常查询的一些表.
我们想到了最初的问题:缓存"这些记录的最佳方法是什么?我真的很想将讨论重点放在该主题上，而不是为什么要缓存数据?"
平台上的绝大多数应用程序(联系人，电子邮件，Gmail，日历等)都不这样做.其中一些具有极其复杂的数据库架构，其中可能包含大量数据，因此不需要这样做.您打算做的是会给您造成巨大的痛苦，而没有明显的收获.
您应该首先专注于设计数据库和架构，以便能够进行高效的查询.我可以想到的两个主要原因是数据库访问速度很慢:
- 您的数据架构非常复杂.
- 您有大量数据.
如果您将要拥有大量数据，则无论如何都无法将其全部保存在内存中，因此这是一个死胡同.如果您具有复杂的结构，则在两种情况下都可以通过对其进行优化以提高性能来受益.在这两种情况下，您的数据库架构都将是获得良好性能的关键.
实际上，优化架构可能有点荒唐(我也不是专家)，但是要注意的一些事情是在要查询的行上正确创建索引，设计联接，这样它们就可以高效地工作.路径等.我敢肯定，有很多人可以在这个领域为您提供帮助.
您还可以尝试查看一些平台数据库的来源，以获取有关如何设计以实现良好性能的一些想法.例如，Contacts数据库(尤其是从2.0开始)非常复杂，并且进行了很多优化，以针对较大的数据和具有许多不同种类查询的可扩展数据集提供良好的性能.
更新:
这很好地说明了数据库优化的重要性.在Android的媒体提供商数据库中，该平台的较新版本对架构进行了重大更改，以添加一些新功能.将现有媒体数据库修改为新架构的升级代码可能需要8分钟或更长时间才能执行.
工程师进行了优化，将真实测试数据库的升级时间从8分钟减少到8秒.性能提高了60倍.
此优化是什么?
它是在升级时在升级操作中使用的重要列上创建一个临时索引. (然后在完成后将其删除.)因此，虽然性能提高了60倍，但它还包括了在升级期间使用的其中一列上建立索引所需的时间.
SQLite是其中的一件事，如果您知道自己在做什么，它就会非常高效.而且，如果您不小心使用它，那么最终可能会遇到糟糕的性能.不过，这是一个安全的选择，如果您遇到性能问题，可以通过改进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 want to avoid to query the database as most as we can, so we decided to keep certain data always in memory.
Our initial idea is to create two simple classes:
"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": 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 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.
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).
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...".
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).
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.
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?"
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:
- 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.
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.
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.
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.
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.
这篇关于在Android上同时将数据保存在内存和数据库中的最佳做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！