SQLite のパフォーマンスに関するベスト プラクティス

Android には、効率的な SQL データベースである SQLite の組み込みサポートが用意されています。データの増加に伴ってアプリの速度と予測可能性を維持するには、以下のベスト プラクティスに沿ってアプリのパフォーマンスを最適化してください。これらのベスト プラクティスを使用すると、再現とトラブルシューティングが難しいパフォーマンスの問題が発生する可能性も低くなります。

パフォーマンスを改善するには、次のパフォーマンス原則に従います。

  • 読み取り対象の行数と列数を減らす: 必要なデータのみを取得するようにクエリを最適化します。データ取得量が過剰になるとパフォーマンスに影響する可能性があるため、データベースから読み取られるデータの量は最小限に抑えてください。

  • SQLite エンジンに処理を push する: SQL クエリ内で計算、フィルタリング、並べ替えを行います。SQLite のクエリエンジンを使用すると、パフォーマンスが大幅に向上する場合があります。

  • データベース スキーマを変更する: SQLite で効率的なクエリプランとデータ表現を作成できるようにデータベース スキーマを設計します。テーブルを適切にインデックス登録し、テーブル構造を最適化してパフォーマンスを改善します。

また、利用可能なトラブルシューティング ツールを使用して、最適化が必要な領域を特定できるように SQLite データベースのパフォーマンスを測定することもできます。

Jetpack Room ライブラリを使用することをおすすめします。

パフォーマンスを重視してデータベースを構成する

このセクションの手順に沿って、SQLite でパフォーマンスを最適化するためにデータベースを構成します。

write-ahead log 書き込みを有効にする

SQLite は、ログに追加することによってミューテーションを実装し、場合によってはデータベースに圧縮します。これは、write-ahead log 書き込み（WAL）と呼ばれます。

ATTACH DATABASE を使用している場合を除き、WAL を有効にします。

同期モードを緩和する

WAL を使用する場合、デフォルトでは、データが確実にディスクに届くようにすべての commit で fsync が発行されます。これにより、データの耐久性は向上しますが、commit は低速になります。

SQLite には、同期モードを制御するオプションがあります。WAL を有効にする場合は、同期モードを NORMAL に設定します。

Kotlin

db.execSQL("PRAGMA synchronous = NORMAL")

Java

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

この設定では、データがディスクに保存される前に commit が返される場合があります。停電やカーネル パニックなどが原因で、デバイスがシャットダウンすると、commit されたデータが失われる可能性があります。ただし、ロギングが原因でデータベースが破損することはありません。

アプリのクラッシュのみが発生した状態では、データはディスクに届きます。ほとんどのアプリでは、この設定により、機材に費用を投じることなくパフォーマンスを改善できます。

効率的なテーブル スキーマを定義する

パフォーマンスを最適化し、データの消費を最小限に抑えるには、効率的なテーブル スキーマを定義します。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 ジョン・レノン リバプール（イギリス）
2 123 マイケル・ジャクソン インディアナ州ゲーリー
3 789 ドリー パートン テネシー州セビア郡

rowid 列は広告掲載オーダーを保持するインデックスです。rowid でフィルタリングされるクエリは高速 B ツリー検索として実装されますが、id でフィルタリングされるクエリは低速なテーブル スキャンです。

id によるルックアップを行う場合は、rowid 列の保存を回避することでストレージ内のデータ量が削減され、データベース全体で高速化を実現できます。

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

この結果、テーブルは次のようになります。

id name city
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 にマッピングされます。

city rowid
インディアナ州ゲーリー 2
リバプール（イギリス） 1
テネシー州セビア郡 3

city 列が元のテーブルとインデックスの両方に存在するようになったため、ストレージ費用が 2 倍になりました。インデックスを使用しているため、ストレージを追加すれば、クエリを高速化できるというメリットが得られます。ただし、クエリ パフォーマンスの改善につながらないストレージ費用の支払いが発生しないように、使用していないインデックスは保持しないでください。

複数列インデックスを作成する

クエリで複数の列を組み合わせる場合は、複数列インデックスを作成してクエリを十分に高速化できます。外側の列でインデックスを使用して、内部検索をリニアスキャンとして行うこともできます。

たとえば、次のクエリがあるとします。

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 引数を parameter と 値を 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 で反復処理

プログラマティック広告ではなく、1 つのクエリで、ターゲットとするすべての検索結果を返す 個々の結果を返すために 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
    ...
  }
}

可能な限り集計関数を使用する

行データのない集計結果には、集計関数を使用します。たとえば次のコードでは、一致する行が 1 つ以上あるかどうかを確認します。

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 つ以上の行が一致する場合は 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: 文字列を必要に応じて区切り文字を使用して連結します。

Cursor.getCount() ではなく COUNT() を使用する

次の例では、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 で一意性を確認する

テーブル内で特定の列の値が一意でない限り、行を挿入する必要がない場合は、該当する一意性を列の制約として適用した方が効率的です。

次の例では、挿入される行を検証するために 1 つのクエリを実行し、実際に挿入するために別のクエリを実行します。

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 を使用することをおすすめします。

1 つのトランザクションで複数の挿入をバッチ処理する

1 つのトランザクションで複数のオペレーションが commit されるため、効率だけでなく正確性も向上します。データの整合性を向上させ、パフォーマンスを高速化するには、一括挿入を使用します。

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

インタラクティブ シェルを使用している場合は、常にクエリプランについて説明するように SQLite に指示できます。

sqlite> .eqp on

詳細については、クエリ プランニングをご覧ください。

SQLite Analyzer

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