提升 SQLite 效能的最佳做法

SQLite 是高效的 SQL 資料庫，而 Android 內建 SQLite 支援功能。只要採用下列最佳做法，就能充分提升應用程式效能，確保即使資料量增加，應用程式仍能維持快速穩定的表現。透過這些最佳做法，您也可以減少遇到效能問題的可能性，畢竟這類問題不容易重現及排解。

如要加快運作效能，請依循下列效能原則：

  • 減少讀取的資料列/欄數量：以最佳方式查詢內容，僅擷取必要資料。請盡可能減少從資料庫讀取的資料量，因為過度擷取資料可能會影響效能。

  • 將工作推送至 SQLite 引擎：在 SQL 查詢中執行運算、篩選和排序作業。使用 SQLite 的查詢引擎可大幅提升效能。

  • 修改資料庫結構定義：妥善設計資料庫的結構定義，讓 SQLite 建構有效的查詢計畫和資料表示法。請為資料表建立適當索引，並改善資料表結構，進而提升效能。

此外，還能使用我們提供的疑難排解工具評估 SQLite 資料庫的效能，找出需要最佳化的部分。

建議使用 Jetpack Room 程式庫。

設定資料庫以提升效能

請按照本節步驟設定資料庫，進而在 SQLite 中獲得最佳效能。

啟用預寫記錄功能

SQLite 實作異動時，會將異動內容附加至記錄檔，而記錄檔偶爾會壓縮到資料庫中，這項技術稱為預寫記錄 (WAL)。

除非您使用 ATTACH DATABASE，否則請啟用 WAL 功能。

放寬同步處理模式

使用 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 姓名 城市
1 456 約翰藍儂 英國利物浦
2 123 麥可傑克森 印第安納州蓋瑞
3 789 桃莉巴頓 田納西州塞維爾郡

rowid 欄是保存插入順序的索引。依 rowid 篩選的查詢會實作為快速的 B 樹搜尋作業，依 id 篩選的查詢則是緩慢的資料表掃描作業。

如果您打算依 id 執行查詢，可避免儲存 rowid 資料欄，進而減少儲存空間中的資料，提升資料庫整體速度：

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

現在資料表會如下所示：

id 姓名 城市
123 麥可傑克森 印第安納州蓋瑞
456 約翰藍儂 英國利物浦
789 桃莉巴頓 田納西州塞維爾郡

由於您不需要儲存 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：

城市 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 查詢慢大約 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 需要讀取所有資料列，但只保留與巴黎客戶相符的資料列。如要修正這個問題，您可以新增索引：

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=?

如果使用互動式殼層，則可要求 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"
    }
  }
}
敬上