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