SQLite 性能最佳实践

Android 内置支持 SQLite，这是一种高效的 SQL 数据库。请遵循以下最佳实践来优化应用性能，确保随着数据增长，应用始终保持快速且可预测的运行速度。通过使用这些最佳实践，您还可以降低遇到难以重现和排查的性能问题的可能性。

为实现更快性能，请遵循以下性能原则

  • 读取更少的行和列：优化您的查询以仅检索必要的数据。最大限度地减少从数据库读取的数据量，因为过多的数据检索会影响性能。

  • 将工作推送到 SQLite 引擎：在 SQL 查询中执行计算、过滤和排序操作。使用 SQLite 的查询引擎可以显著提高性能。

  • 修改数据库架构：设计您的数据库架构以帮助 SQLite 构建高效的查询计划和数据表示。正确索引表并优化表结构以提升性能。

此外，您可以使用可用的问题排查工具来衡量 SQLite 数据库的性能，以帮助识别需要优化的区域。

我们建议使用 Jetpack Room 库。

配置数据库以提升性能

请按照本部分中的步骤配置您的数据库，以在 SQLite 中获得最佳性能。

启用预写式日志 (WAL)

SQLite 通过将更改追加到日志来实现变动，日志会不时地压缩到数据库中。这称为预写式日志 (WAL)。

请启用 WAL，除非您正在使用 ATTACH DATABASE。

放宽同步模式

使用 WAL 时，默认情况下，每次提交都会发出一个 fsync，以帮助确保数据到达磁盘。这可以提高数据持久性，但会减慢提交速度。

SQLite 有一个选项可以控制同步模式。如果您启用 WAL，请将同步模式设置为 NORMAL

Kotlin

db.execSQL("PRAGMA synchronous = NORMAL")

Java

db.execSQL("PRAGMA synchronous = NORMAL");

在此设置中，提交可以在数据存储到磁盘之前返回。如果发生设备关机（例如断电或内核崩溃），提交的数据可能会丢失。但是，由于日志记录的存在，您的数据库不会损坏。

如果只有您的应用崩溃，您的数据仍然会到达磁盘。对于大多数应用而言，此设置可以提高性能，而无需支付实质性成本。

定义高效的表架构

为了优化性能和最大限度地减少数据消耗，请定义高效的表架构。SQLite 会构建高效的查询计划和数据，从而加快数据检索速度。本部分提供了创建表架构的最佳实践。

考虑使用 INTEGER PRIMARY KEY

对于此示例，定义并填充表，如下所示

CREATE TABLE Customers(
  id INTEGER,
  name TEXT,
  city TEXT
);
INSERT INTO Customers Values(456, 'John Lennon', 'Liverpool, England');
INSERT INTO Customers Values(123, 'Michael Jackson', 'Gary, IN');
INSERT INTO Customers Values(789, 'Dolly Parton', 'Sevier County, TN');

表输出如下所示

rowid id name city
1 456 John Lennon 英国利物浦
2 123 Michael Jackson 印第安纳州加里
3 789 Dolly Parton 田纳西州塞维尔县

列 rowid 是一个保留插入顺序的索引。按 rowid 过滤的查询被实现为快速 B 树搜索，但按 id 过滤的查询是慢表扫描。

如果您计划按 id 进行查找，您可以避免存储 rowid 列，以减少存储中的数据量并整体加快数据库速度

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT
);

您的表现在如下所示

id name city
123 Michael Jackson 印第安纳州加里
456 John Lennon 英国利物浦
789 Dolly Parton 田纳西州塞维尔县

由于您无需存储 rowid 列，id 查询速度很快。请注意，表现在根据 id 而不是插入顺序进行排序。

使用索引加速查询

SQLite 使用索引来加速查询。当对某个列进行过滤 (WHERE)、排序 (ORDER BY) 或聚合 (GROUP BY) 时，如果表针对该列具有索引，则查询会加速。

在前面的示例中，按 city 过滤需要扫描整个表

SELECT id, name
WHERE city = 'London, England';

对于有大量城市查询的应用，您可以使用索引来加速这些查询

CREATE INDEX city_index ON Customers(city);

索引被实现为附加表，按索引列排序并映射到 rowid

city rowid
印第安纳州加里 2
英国利物浦 1
田纳西州塞维尔县 3

请注意，city 列的存储成本现在增加了一倍，因为它同时存在于原始表和索引中。由于您正在使用索引，因此增加的存储成本值得更快的查询所带来的好处。但是，不要维护您不使用的索引，以避免支付存储成本却无法获得查询性能提升。

创建多列索引

如果您的查询组合了多列，您可以创建多列索引以全面加速查询。您还可以在外部列上使用索引，并让内部搜索作为线性扫描完成。

例如，给定以下查询

SELECT id, name
WHERE city = 'London, England'
ORDER BY city, name

您可以使用多列索引以与查询中指定的相同顺序来加速查询

CREATE INDEX city_name_index ON Customers(city, name);

但是，如果您仅在 city 上有一个索引，则外部排序仍会加速，而内部排序需要线性扫描。

这同样适用于前缀查询。例如，索引 ON Customers (city, name) 也能加速按 city 进行的过滤、排序和分组，因为多列索引的索引表是按给定索引的给定顺序排序的。

考虑使用 WITHOUT ROWID

默认情况下，SQLite 会为您的表创建一个 rowid 列，其中 rowid 是隐式的 INTEGER PRIMARY KEY AUTOINCREMENT。如果您已经有一个 INTEGER PRIMARY KEY 列，则此列将成为 rowid 的别名。

对于主键不是 INTEGER 或列组合的表，请考虑使用 WITHOUT ROWID。

将小数据存储为 BLOB，将大数据存储为文件

如果您想将大数据与行关联，例如图像缩略图或联系人照片，您可以将数据存储在 BLOB 列中，或者存储在文件中，然后将文件路径存储在列中。

文件通常向上取整到 4 KB 的增量。对于非常小的文件，如果舍入误差显著，将其作为 BLOB 存储在数据库中会更高效。SQLite 最小化文件系统调用，并且在某些情况下比底层文件系统更快。

提高查询性能

遵循这些最佳实践，通过最大限度地缩短响应时间和最大限度地提高处理效率来提高 SQLite 中的查询性能。

仅读取您需要的行

过滤器允许您通过指定某些条件（例如日期范围、位置或名称）来缩小结果范围。限制允许您控制看到的结果数量

Kotlin

db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """, null)) {
  while (cursor.moveToNext()) {
    ...
  }
}

仅读取您需要的列

避免选择不需要的列，这会减慢查询速度并浪费资源。相反，只选择已使用的列。

在以下示例中，您选择 id、name 和 phone

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT id, name, phone
    FROM customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(1)
        // ...
    }
}

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id, name, phone
    FROM customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(1);
    ...
  }
}

但是，您只需要 name 列

Kotlin

db.rawQuery("""
    SELECT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(0)
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(0);
    ...
  }
}

使用 SQL 卡片参数化查询，而不是字符串拼接

您的查询字符串可能包含一个仅在运行时才知道的参数，例如以下内容

Kotlin

fun getNameById(id: Long): String? 
    db.rawQuery(
        "SELECT name FROM customers WHERE id=$id", null
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

Java

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery(
      "SELECT name FROM customers WHERE id=" + id, null)) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

在前面的代码中，每个查询都构造一个不同的字符串，因此无法从语句缓存中受益。每次调用都需要 SQLite 在执行之前对其进行编译。相反，您可以用一个参数替换 id 参数，并使用 selectionArgs 绑定值

Kotlin

fun getNameById(id: Long): String? {
    db.rawQuery(
        """
          SELECT name
          FROM customers
          WHERE id=?
        """.trimIndent(), arrayOf(id.toString())
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

Java

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery("""
          SELECT name
          FROM customers
          WHERE id=?
      """, new String[] {String.valueOf(id)})) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

现在，查询可以编译一次并进行缓存。已编译的查询会在 getNameById(long) 的不同调用之间重复使用。

在 SQL 中迭代，而不是在代码中迭代

使用单个查询返回所有目标结果，而不是通过程序化循环迭代 SQL 查询以返回单个结果。程序化循环比单个 SQL 查询慢约 1000 倍。

使用 DISTINCT 获取唯一值

使用 DISTINCT 关键字可以通过减少需要处理的数据量来提高查询性能。例如，如果您只想从列中返回唯一值，请使用 DISTINCT

Kotlin

db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Only iterate over distinct names in Kotlin
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    // Only iterate over distinct names in Java
    ...
  }
}

尽可能使用聚合函数

使用聚合函数获取聚合结果，而无需行数据。例如，以下代码检查是否存在至少一行匹配的行

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        // At least one customer from Paris
        ...
    } else {
        // No customers from Paris
        ...
}

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """, null)) {
  if (cursor.moveToFirst()) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

要仅获取第一行，您可以使用 EXISTS()，如果不存在匹配行则返回 0，如果有一行或多行匹配则返回 1

Kotlin

db.rawQuery("""
    SELECT EXISTS (
        SELECT null
        FROM Customers
        WHERE city = 'Paris';
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        // At least one customer from Paris
        ...
    } else {
        // No customers from Paris
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM Customers
      WHERE city = 'Paris'
    );
    """, null)) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

在您的应用代码中使用 SQLite 聚合函数

  • COUNT：计算列中有多少行。
  • SUM：将列中的所有数值相加。
  • MIN 或 MAX：确定最低或最高值。适用于数值列、DATE 类型和文本类型。
  • AVG：查找平均数值。
  • GROUP_CONCAT：使用可选分隔符连接字符串。

使用 COUNT() 而不是 Cursor.getCount()

在以下示例中，Cursor.getCount() 函数从数据库中读取所有行并返回所有行值

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    val count = cursor.getCount()
}

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id
    FROM Customers;
    """, null)) {
  int count = cursor.getCount();
  ...
}

但是，通过使用 COUNT()，数据库只返回计数

Kotlin

db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    cursor.moveToFirst()
    val count = cursor.getInt(0)
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """, null)) {
  cursor.moveToFirst();
  int count = cursor.getInt(0);
  ...
}

嵌套查询而不是代码

SQL 是可组合的，支持子查询、联接和外键约束。您可以在另一个查询中使用一个查询的结果，而无需通过应用代码。这减少了从 SQLite 复制数据的需要，并允许数据库引擎优化您的查询。

在以下示例中，您可以运行一个查询来查找哪个城市拥有最多的客户，然后使用该结果在另一个查询中查找来自该城市的所有客户

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        val topCity = cursor.getString(0)
        db.rawQuery("""
            SELECT name, city
            FROM Customers
            WHERE city = ?;
        """.trimIndent(),
        arrayOf(topCity)).use { innerCursor ->
            while (innerCursor.moveToNext()) {
                ...
            }
        }
    }
}

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """, null)) {
  if (cursor.moveToFirst()) {
    String topCity = cursor.getString(0);
    try (Cursor innerCursor = db.rawQuery("""
        SELECT name, city
        FROM Customers
        WHERE city = ?;
        """, new String[] {topCity})) {
        while (innerCursor.moveToNext()) {
          ...
        }
    }
  }
}

要在前一个示例的一半时间内获得结果，请使用包含嵌套语句的单个 SQL 查询

Kotlin

db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
        SELECT city
        FROM Customers
        GROUP BY city
        ORDER BY COUNT (*) DESC
        LIMIT 1;
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToNext()) {
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
      SELECT city
      FROM Customers
      GROUP BY city
      ORDER BY COUNT(*) DESC
      LIMIT 1
    );
    """, null)) {
  while(cursor.moveToNext()) {
    ...
  }
}

在 SQL 中检查唯一性

如果一行必须在表中某个特定列的值唯一时才能插入，那么将该唯一性作为列约束来强制执行可能会更高效。

在以下示例中，一个查询用于验证要插入的行，另一个查询用于实际插入

Kotlin

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT EXISTS (
        SELECT null
        FROM customers
        WHERE username = ?
    );
    """.trimIndent(),
    arrayOf(customer.username)
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        throw AddCustomerException(customer)
    }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    arrayOf(
        customer.id.toString(),
        customer.name,
        customer.username
    )
)

Java

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM customers
      WHERE username = ?
    );
    """, new String[] { customer.username })) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    throw new AddCustomerException(customer);
  }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    new String[] {
      String.valueOf(customer.id),
      customer.name,
      customer.username,
    });

您可以在定义表时在 SQL 中检查唯一性约束，而不是在 Kotlin 或 Java 中检查

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  username TEXT UNIQUE
);

SQLite 的操作与以下相同

CREATE TABLE Customers(...);
CREATE UNIQUE INDEX CustomersUsername ON Customers(username);

现在您可以插入一行，让 SQLite 检查约束

Kotlin

try {
    db.execSql(
        "INSERT INTO Customers VALUES (?, ?, ?)",
        arrayOf(customer.id.toString(), customer.name, customer.username)
    )
} catch(e: SQLiteConstraintException) {
    throw AddCustomerException(customer, e)
}

Java

try {
  db.execSQL(
      "INSERT INTO Customers VALUES (?, ?, ?)",
      new String[] {
        String.valueOf(customer.id),
        customer.name,
        customer.username,
      });
} catch (SQLiteConstraintException e) {
  throw new AddCustomerException(customer, e);
}

SQLite 支持具有多列的唯一索引

CREATE TABLE table(...);
CREATE UNIQUE INDEX unique_table ON table(column1, column2, ...);

与 Kotlin 或 Java 代码相比，SQLite 验证约束的速度更快，开销更少。最佳实践是使用 SQLite 而不是应用代码。

在单个事务中批量插入多个数据

事务会提交多个操作，这不仅提高了效率，也提高了正确性。为了提高数据一致性并加快性能，您可以批量插入

Kotlin

db.beginTransaction()
try {
    customers.forEach { customer ->
        db.execSql(
            "INSERT INTO Customers VALUES (?, ?, ...)",
            arrayOf(customer.id.toString(), customer.name, ...)
        )
    }
} finally {
    db.endTransaction()
}

Java

db.beginTransaction();
try {
  for (customer : Customers) {
    db.execSQL(
        "INSERT INTO Customers VALUES (?, ?, ...)",
        new String[] {
          String.valueOf(customer.id),
          customer.name,
          ...
        });
  }
} finally {
  db.endTransaction()
}

使用问题排查工具

SQLite 提供了以下问题排查工具来帮助衡量性能。

使用 SQLite 的交互式提示符

在您的机器上运行 SQLite 来执行查询和学习。不同的 Android 平台版本使用不同版本的 SQLite。要使用 Android 设备上的相同引擎，请使用 adb shell 并在目标设备上运行 sqlite3。

您可以要求 SQLite 对查询进行计时

sqlite> .timer on
sqlite> SELECT ...
Run Time: real ... user ... sys ...

EXPLAIN QUERY PLAN

您可以使用 EXPLAIN QUERY PLAN 要求 SQLite 解释它打算如何回答查询

sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SCAN Customers

前面的示例需要对整个表进行全表扫描（无索引）来查找来自巴黎的所有客户。这称为线性复杂度。SQLite 需要读取所有行并只保留与来自巴黎的客户匹配的行。要解决此问题，您可以添加索引

sqlite> CREATE INDEX Idx1 ON Customers(city);
sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SEARCH test USING INDEX Idx1 (city=?

如果您使用交互式 shell，您可以要求 SQLite 始终解释查询计划

sqlite> .eqp on

如需了解更多信息，请参阅查询规划。

SQLite 分析器

SQLite 提供了 sqlite3_analyzer 命令行界面 (CLI)，用于转储可用于排查性能问题的额外信息。要安装，请访问 SQLite 下载页面。

您可以使用 adb pull 将数据库文件从目标设备下载到您的工作站进行分析

adb pull /data/data/<app_package_name>/databases/<db_name>.db

SQLite 浏览器

您还可以在 SQLite 下载页面上安装 GUI 工具 SQLite Browser。

Android 日志记录

Android 会为您对 SQLite 查询进行计时并记录它们

# Enable query time logging
$ adb shell setprop log.tag.SQLiteTime VERBOSE
# Disable query time logging
$ adb shell setprop log.tag.SQLiteTime ERROR

Perfetto 跟踪

配置 Perfetto 时，您可以添加以下内容以包含单个查询的轨道

data_sources {
  config {
    name: "linux.ftrace"
    ftrace_config {
      atrace_categories: "database"
    }
  }
}