SQLite 性能最佳实践

Android 提供了 对 SQLite 的内置支持，SQLite 是一个高效的 SQL 数据库。遵循以下最佳实践来优化应用的性能，确保其在数据增长时保持快速和可预测的快速。通过使用这些最佳实践，您还可以减少遇到难以重现和排查的性能问题的可能性。

为了获得更快的性能，请遵循以下性能原则

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

  • 将工作推送到 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 查询慢 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,
    });

无需在 Kotlin 或 Java 中检查唯一约束，您可以在定义表时在 SQL 中检查它。

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, ...);

SQLite 验证约束的速度更快，并且开销小于 Kotlin 或 Java 代码。最佳实践是使用 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。

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

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

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