有助提升 SQLite 性能的最佳实践

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

要想实现更快的性能，请遵循以下性能原则：

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

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

  • 修改数据库架构：设计数据库架构，帮助 SQLite 构建高效的查询计划和数据表示法。适当地为表添加索引并优化表结构，以便提升性能。

此外，您还可以使用可用的问题排查工具来衡量 SQLite 数据库的性能，以便确定需要优化的方面。

我们建议使用 Jetpack Room 库。

配置数据库以提升性能

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

启用预写式日志记录

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 Liverpool, England
2 123 Michael Jackson Gary, IN
3 789 Dolly Parton Sevier County, TN

rowid 列是用于保存插入顺序的索引。对于按 rowid 过滤的查询，会执行快速的 B 树搜索，而对于按 id 过滤的查询，则会执行缓慢的表扫描。

如果您打算按 id 进行查找，可以避免存储 rowid 列，以便减少存储的数据，并提高数据库的总体速度：

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

您的表现在如下所示：

id name city
123 Michael Jackson Gary, IN
456 John Lennon Liverpool, England
789 Dolly Parton Sevier County, TN

由于您不需要存储 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
Gary, IN 2
Liverpool, England 1
Sevier County, TN 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 查询慢大约 1,000 倍。

对唯一值使用 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 需要读取所有行，并且仅保留与 Paris 的客户匹配的行。如需修复此问题，您可以添加索引：

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 浏览器。

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 tracing

### Perfetto tracing {:#perfetto-tracing}

When [configuring Perfetto](https://perfetto.dev/docs/concepts/config), you may
add the following to include tracks for individual queries:

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