android - 从 Android 上的 UI 线程访问 SQLite 虚拟表是否安全？

Question

我需要维护一个字符串列的数据表，并为所有列启用全文搜索。

我从 Android 文档中了解到:

虚拟表的行为类似于 SQLite 表，但通过回调读取和写入内存中的对象，而不是数据库文件。

为此目的使用 SQLite 虚拟表并从 android 上的主 UI 线程访问该表而不出现 ANR 是否安全？

或者我应该选择第三方选项之一，例如:

https://code.google.com/archive/p/multiindexcontainer/

https://github.com/npgall/cqengine

Answer 1

来自 The Virtual Table Mechanism Of SQLite :

From the perspective of an SQL statement, the virtual table object looks like any other table or view.

But behind the scenes, queries and updates on a virtual table invoke callback methods of the virtual table object instead of reading and writing to the database file.

因此，从 Android 的角度来看，SQLite 虚拟表的行为与任何其他表或 View 完全一样，并且根据 the documentation about ANRs :

(...)

In any situation in which your app performs a potentially lengthy operation, you should not perform the work on the UI thread, but instead create a worker thread and do most of the work there. This keeps the UI thread (which drives the user interface event loop) running and prevents the system from concluding that your code has frozen. Because such threading usually is accomplished at the class level, you can think of responsiveness as a class problem. (Compare this with basic code performance, which is a method-level concern.)

In Android, application responsiveness is monitored by the Activity Manager and Window Manager system services. Android will display the ANR dialog for a particular application when it detects one of the following conditions:

• No response to an input event (such as key press or screen touch events) within 5 seconds.

• A BroadcastReceiver hasn't finished executing within 10 seconds.

所以，答案是否定的。如果你想避免 ANR，从主线程访问 SQLite 虚拟表是不安全的。您可以使用 AsyncTasks访问您的数据库。

评论更新:

虽然从 SQLite 的角度来看整个表都在内存中，但从 Android 的角度来看，系统需要访问数据库，这可能会导致性能问题，例如，如果表足够大，或者取决于实现，

Because they can be long-running, be sure that you call getWritableDatabase() or getReadableDatabase() in a background thread, such as with AsyncTask or IntentService

(来自 Saving Data in SQL Databases)

@Chebyr 正在使用一个包含 5000 行和 15 个字符串列的表，该表通过 5 个游标的连接形成，用作内存缓存以实现快速访问，并为过滤操作建立索引。因此，假设字符串的长度为 7 个字符，大约为 500KB，不会非常消耗内存(取决于索引解决方案的实现)，而且我不希望使用索引访问它会非常耗时。如果数据是静态的，我会尝试使用@Chebyr 提出的解决方案之一并测量时间和内存(我刚刚使用普通 Java 示例测试了 CQEngine，看起来很有希望)。

此外，Best practice for keeping data in memory and database at same time on Android会有帮助。