Praktik terbaik untuk performa SQLite

Android menawarkan dukungan bawaan untuk SQLite, database SQL yang efisien. Ikuti praktik terbaik ini untuk mengoptimalkan performa aplikasi Anda, memastikannya tetap cepat dan dapat diprediksi dengan cepat seiring pertumbuhan data Anda. Dengan menggunakan praktik terbaik ini, Anda juga mengurangi kemungkinan terjadinya masalah performa yang sulit direproduksi dan dipecahkan.

Untuk mencapai performa yang lebih cepat, ikuti prinsip performa berikut:

  • Membaca lebih sedikit baris dan kolom: Optimalkan kueri untuk mengambil data yang diperlukan saja. Minimalkan jumlah data yang dibaca dari database, karena pengambilan data yang berlebihan dapat memengaruhi performa.

  • Mendorong tugas ke mesin SQLite: Lakukan operasi komputasi, pemfilteran, dan pengurutan dalam kueri SQL. Menggunakan mesin kueri SQLite dapat meningkatkan performa secara signifikan.

  • Mengubah skema database: Desain skema database Anda untuk membantu SQLite membuat rencana kueri dan representasi data yang efisien. Indeks tabel dengan benar dan optimalkan struktur tabel untuk meningkatkan performa.

Selain itu, Anda dapat menggunakan alat pemecahan masalah yang tersedia untuk mengukur performa database SQLite untuk membantu mengidentifikasi area yang memerlukan pengoptimalan.

Sebaiknya gunakan library Jetpack Room.

Mengonfigurasi database untuk meningkatkan performa

Ikuti langkah-langkah di bagian ini untuk mengonfigurasi database Anda agar performanya optimal di SQLite.

Mengaktifkan Write-Ahead Logging

SQLite menerapkan mutasi dengan menambahkannya ke log, yang terkadang dipadatkan ke dalam database. Tindakan ini disebut Write-Ahead Logging (WAL).

Aktifkan WAL kecuali jika Anda menggunakan ATTACH DATABASE.

Melonggarkan mode sinkronisasi

Saat menggunakan WAL, secara default setiap commit akan mengeluarkan fsync untuk membantu memastikan bahwa data mencapai disk. Hal ini meningkatkan ketahanan data, tetapi memperlambat commit Anda.

SQLite memiliki opsi untuk mengontrol mode sinkron. Jika Anda mengaktifkan WAL, setel mode sinkron ke NORMAL:

Kotlin

db.execSQL("PRAGMA synchronous = NORMAL")

Java

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

Di setelan ini, commit dapat ditampilkan sebelum data disimpan di disk. Jika penonaktifan perangkat terjadi, misalnya karena kehilangan daya atau kernel panic, data yang di-commit bisa saja hilang. Namun, karena logging, database Anda tidak rusak.

Jika hanya aplikasi Anda yang error, data akan tetap masuk ke disk. Untuk sebagian besar aplikasi, setelan ini menghasilkan peningkatan performa tanpa biaya material.

Menentukan skema tabel yang efisien

Untuk mengoptimalkan performa dan meminimalkan konsumsi data, tentukan skema tabel yang efisien. SQLite membuat data dan rencana kueri yang efisien, yang mengarah ke pengambilan data yang lebih cepat. Bagian ini memberikan praktik terbaik untuk membuat skema tabel.

Pertimbangkan INTEGER PRIMARY KEY

Untuk contoh ini, tentukan dan isi tabel sebagai berikut:

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');

Output tabelnya adalah sebagai berikut:

rowid id nama kota
1 456 John Lennon Liverpool, Inggris
2 123 Michael Jackson Gary, IN
3 789 Dolly Parton Sevier County, TN

Kolom rowid adalah indeks yang mempertahankan urutan penyisipan. Kueri yang memfilter menurut rowid diimplementasikan sebagai penelusuran hierarki B yang cepat, tetapi kueri yang memfilter menurut id merupakan pemindaian tabel yang lambat.

Jika berencana melakukan pencarian menurut id, Anda dapat menghindari penyimpanan kolom rowid untuk mendapatkan lebih sedikit data dalam penyimpanan dan database yang lebih cepat secara keseluruhan:

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

Tabel Anda sekarang terlihat seperti berikut:

id nama kota
123 Michael Jackson Gary, IN
456 John Lennon Liverpool, Inggris
789 Dolly Parton Sevier County, TN

Karena Anda tidak perlu menyimpan kolom rowid, kueri id dapat dipercepat. Perhatikan bahwa tabel kini diurutkan berdasarkan id, bukan urutan penyisipan.

Mempercepat kueri dengan indeks

SQLite menggunakan indeks untuk mempercepat kueri. Saat memfilter (WHERE), mengurutkan (ORDER BY), atau menggabungkan (GROUP BY) kolom, jika tabel memiliki indeks untuk kolom tersebut, kueri akan dipercepat.

Pada contoh sebelumnya, pemfilteran menurut city mengharuskan pemindaian seluruh tabel:

SELECT id, name
WHERE city = 'London, England';

Untuk aplikasi dengan banyak kueri kota, Anda dapat mempercepat kueri tersebut dengan indeks:

CREATE INDEX city_index ON Customers(city);

Indeks diterapkan sebagai tabel tambahan, yang diurutkan berdasarkan kolom indeks dan dipetakan ke rowid:

kota rowid
Gary, IN 2
Liverpool, Inggris 1
Sevier County, TN 3

Perhatikan bahwa biaya penyimpanan kolom city sekarang menjadi dua kali lipat, karena kolom tersebut kini ada di tabel asli dan indeks. Karena Anda menggunakan indeks, biaya penyimpanan tambahan sebanding dengan keuntungannya, yaitu kueri yang lebih cepat. Namun, jangan mempertahankan indeks yang tidak Anda gunakan agar tidak membayar biaya penyimpanan tanpa mendapatkan peningkatan performa kueri.

Membuat indeks multi-kolom

Jika kueri menggabungkan beberapa kolom, Anda dapat membuat indeks multi-kolom untuk sepenuhnya mempercepat kueri. Anda juga dapat menggunakan indeks di kolom luar dan memungkinkan penelusuran di dalam dilakukan sebagai pemindaian linear.

Misalnya, dengan kueri berikut:

SELECT id, name
WHERE city = 'London, England'
ORDER BY city, name

Anda dapat mempercepat kueri dengan indeks multi-kolom dalam urutan yang sama seperti yang ditentukan dalam kueri:

CREATE INDEX city_name_index ON Customers(city, name);

Namun, jika Anda hanya memiliki indeks di city, pengurutan di luar tetap dipercepat, sedangkan pengurutan di dalam memerlukan pemindaian linear.

Hal ini juga berfungsi dengan pertanyaan awalan. Misalnya, indeks ON Customers (city, name) juga mempercepat pemfilteran, pengurutan, dan pengelompokan menurut city, karena tabel indeks untuk indeks multi-kolom diurutkan menurut indeks tertentu dalam urutan tertentu.

Mempertimbangkan WITHOUT ROWID

Secara default, SQLite membuat kolom rowid untuk tabel Anda, dengan rowid sebagai INTEGER PRIMARY KEY AUTOINCREMENT implisit. Jika Anda sudah memiliki kolom yang merupakan INTEGER PRIMARY KEY, kolom ini akan menjadi alias dari rowid.

Untuk tabel yang memiliki kunci utama selain INTEGER atau gabungan kolom, pertimbangkan WITHOUT ROWID.

Menyimpan data kecil sebagai BLOB dan data besar sebagai file

Jika ingin mengaitkan data besar dengan baris, seperti thumbnail gambar atau foto untuk kontak, Anda dapat menyimpan data tersebut dalam kolom BLOB atau dalam file, lalu menyimpan jalur file dalam kolom.

File biasanya dibulatkan ke atas hingga tambahan 4 KB. Untuk file yang sangat kecil, dengan error pembulatan yang signifikan, akan lebih efisien untuk menyimpannya dalam database sebagai BLOB. SQLite meminimalkan panggilan sistem file dan lebih cepat daripada sistem file yang mendasarinya dalam beberapa kasus.

Meningkatkan performa kueri

Ikuti praktik terbaik ini untuk meningkatkan performa kueri di SQLite dengan meminimalkan waktu respons dan memaksimalkan efisiensi pemrosesan.

Hanya baca baris yang diperlukan

Filter memungkinkan Anda mempersempit hasil dengan menentukan kriteria tertentu, seperti rentang tanggal, lokasi, atau nama. Batas memungkinkan Anda mengontrol jumlah hasil yang dilihat:

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()) {
    ...
  }
}

Hanya baca kolom yang diperlukan

Hindari memilih kolom yang tidak diperlukan, yang dapat memperlambat kueri dan membuang resource. Sebagai gantinya, hanya pilih kolom yang digunakan.

Pada contoh berikut, Anda memilih id, name, dan 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);
    ...
  }
}

Namun, Anda hanya memerlukan kolom 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);
    ...
  }
}

Menggunakan DISTINCT untuk nilai unik

Menggunakan kata kunci DISTINCT dapat meningkatkan performa kueri dengan mengurangi jumlah data yang perlu diproses. Misalnya, jika Anda ingin menampilkan nilai unik saja dari kolom, gunakan 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
    ...
  }
}

Menggunakan fungsi gabungan jika memungkinkan

Gunakan fungsi agregat untuk hasil gabungan tanpa data baris. Misalnya, kode berikut memeriksa apakah ada setidaknya satu baris yang cocok:

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

Untuk mengambil baris pertama saja, Anda dapat menggunakan EXISTS() untuk menampilkan 0 jika tidak ada baris yang cocok, dan 1 jika satu atau beberapa baris cocok:

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

Gunakan fungsi agregat SQLite dalam kode aplikasi Anda:

  • COUNT: menghitung jumlah baris dalam kolom.
  • SUM: menambahkan semua nilai numerik dalam kolom.
  • MIN atau MAX: menentukan nilai terendah atau tertinggi. Berfungsi untuk kolom numerik, jenis DATE, dan jenis teks.
  • AVG: menemukan nilai numerik rata-rata.
  • GROUP_CONCAT: menyambungkan string dengan pemisah opsional.

Menggunakan COUNT(), bukan Cursor.getCount()

Dalam contoh berikut, fungsi Cursor.getCount() membaca semua baris dari database dan menampilkan semua nilai baris:

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();
  ...
}

Namun, dengan menggunakan COUNT(), database hanya menampilkan jumlah:

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

Kueri Nest, bukan kode

SQL adalah composable dan mendukung subkueri, gabungan, dan batasan kunci asing. Anda dapat menggunakan hasil dari satu kueri untuk kueri lain tanpa memeriksa kode aplikasi. Hal ini mengurangi kebutuhan untuk menyalin data dari SQLite dan memungkinkan mesin database mengoptimalkan kueri Anda.

Pada contoh berikut, Anda dapat menjalankan kueri untuk menemukan kota yang memiliki pelanggan terbanyak, lalu menggunakan hasil dalam kueri lain untuk menemukan semua pelanggan dari kota tersebut:

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()) {
          ...
        }
    }
  }
}

Untuk mendapatkan hasilnya dengan durasi waktu setengah saja dari contoh sebelumnya, gunakan satu kueri SQL dengan pernyataan bertingkat:

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()) {
    ...
  }
}

Memeriksa keunikan di SQL

Jika baris tidak boleh disisipkan kecuali nilai kolom tertentu bersifat unik dalam tabel, akan lebih efisien untuk menerapkan keunikan tersebut sebagai batasan kolom.

Dalam contoh berikut, satu kueri dijalankan untuk memvalidasi baris yang akan disisipkan dan baris lain untuk benar-benar disisipkan:

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

Daripada memeriksa batasan unik di Kotlin atau Java, Anda dapat memeriksanya di SQL saat menentukan tabel:

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

SQLite melakukan hal yang sama dengan berikut ini:

CREATE TABLE Customers(...);
CREATE UNIQUE INDEX CustomersUsername ON Customers(username);

Sekarang Anda dapat menyisipkan baris dan membiarkan SQLite memeriksa batasannya:

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 mendukung indeks unik dengan beberapa kolom:

CREATE TABLE table(...);
CREATE UNIQUE INDEX unique_table ON table(column1, column2, ...);

SQLite memvalidasi batasan lebih cepat dan dengan overhead yang lebih sedikit daripada kode Kotlin atau Java. Praktik terbaiknya adalah menggunakan SQLite, bukan kode aplikasi

Mengelompokkan beberapa penyisipan dalam satu transaksi

Suatu transaksi meng-commit beberapa operasi, yang tidak hanya meningkatkan efisiensi, tetapi juga ketepatan. Untuk meningkatkan konsistensi data dan mempercepat performa, Anda dapat mengelompokkan penyisipan:

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()
}

Menggunakan alat pemecahan masalah

SQLite menyediakan alat pemecahan masalah berikut untuk membantu mengukur performa.

Menggunakan perintah interaktif SQLite

Jalankan SQLite di komputer Anda untuk menjalankan kueri dan belajar. Versi platform Android yang berbeda menggunakan revisi SQLite yang berbeda. Untuk menggunakan mesin yang sama dengan yang ada di perangkat Android, gunakan adb shell dan jalankan sqlite3 di perangkat target Anda.

Anda dapat meminta SQLite untuk menghitung waktu kueri:

sqlite> .timer on
sqlite> SELECT ...
Run Time: real ... user ... sys ...

EXPLAIN QUERY PLAN

Anda dapat meminta SQLite untuk menjelaskan cara menjawab kueri dengan menggunakan EXPLAIN QUERY PLAN:

sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SCAN Customers

Contoh sebelumnya memerlukan pemindaian tabel penuh tanpa indeks untuk menemukan semua pelanggan dari Paris. Hal ini disebut kompleksitas linear. SQLite perlu membaca semua baris dan hanya menyimpan baris yang cocok dengan pelanggan dari Paris. Untuk memperbaikinya, Anda dapat menambahkan indeks:

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

Jika menggunakan shell interaktif, Anda dapat meminta SQLite untuk selalu menjelaskan rencana kueri:

sqlite> .eqp on

Untuk mengetahui informasi selengkapnya, lihat Perencanaan Kueri.

Penganalisis SQLite

SQLite menawarkan antarmuka command line (CLI) sqlite3_analyzer untuk membuang informasi tambahan yang dapat digunakan untuk memecahkan masalah performa. Untuk menginstal, buka Halaman Download SQLite.

Anda dapat menggunakan adb pull untuk mendownload file database dari perangkat target ke workstation untuk dianalisis:

adb pull /data/data/<app_package_name>/databases/<db_name>.db

Browser SQLite

Anda juga dapat menginstal alat GUI Browser SQLite di halaman Download SQLite.

Logging Android

Android akan menghitung waktu kueri SQLite dan mencatatnya untuk Anda:

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