Praktik terbaik untuk performa SQLite (Tampilan)

Konsep dan penerapan Jetpack Compose

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.

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

// When opening the database
val paramsBuilder: SQLiteDatabase.OpenParams.Builder = SQLiteDatabase.OpenParams.Builder()
paramsBuilder.journalMode = SQLiteDatabase.SYNC_MODE_NORMAL

// Or: after having opened the database
db.execSQL("PRAGMA synchronous = NORMAL");

Java

// When opening the database
SQLiteDatabase.OpenParams.Builder paramsBuilder = new SQLiteDatabase.OpenParams.Builder();
paramsBuilder.setJournalMode(SQLiteDatabase.SYNC_MODE_NORMAL);

// Or: after having opened the database
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.

Catatan: Jika aplikasi Anda memiliki beberapa database, gunakan setelan sinkron yang sama di mana pun jika ada dependensi data antar-database yang berbeda.

Meningkatkan performa kueri

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

Catatan: Banyak contoh di halaman ini yang menyertakan klausul LIMIT. Ini adalah praktik yang baik karena kueri yang dapat menampilkan banyak baris memiliki implikasi performa saat menampilkan set data yang besar. Pengecualian untuk hal ini adalah untuk kueri yang secara implisit menampilkan set data dengan batasan ukuran. Misalnya, penelusuran di kolom yang ditentukan sebagai UNIQUE hanya dapat menampilkan nol atau satu baris.

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()) {
        // Process cursor data
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """, null)) {
  while (cursor.moveToNext()) {
    // Process cursor data
  }
}

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)
        // Further processing
    }
}

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);
    // Further processing
  }
}

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)
        // Further processing
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(0);
    // Further processing
  }
}

Membuat parameter kueri

String kueri Anda dapat menyertakan parameter yang hanya diketahui saat runtime, seperti berikut:

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

Dalam kode sebelumnya, setiap kueri membuat string yang berbeda, sehingga tidak mendapatkan manfaat dari cache pernyataan. Setiap panggilan memerlukan SQLite untuk mengompilasinya sebelum dapat dieksekusi. Sebagai gantinya, Anda dapat mengganti argumen id dengan parameter dan mengikat nilai dengan 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;
    }
  }
}

Sekarang, kueri dapat dikompilasi sekali dan di-cache. Kueri yang dikompilasi digunakan kembali di antara berbagai pemanggilan getNameById(long).

Perhatian: Jika argumen input adalah objek lain yang kurang dibatasi daripada hanya angka, penggabungan string dapat menyebabkan kerentanan keamanan injeksi SQL. Selalu gunakan parameter untuk variabel atau data yang tidak tepercaya.

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
        // Process distinct name
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    // Only iterate over distinct names in Java
    // Process distinct name
  }
}

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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
}

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
    // Handle found
  } else {
    // No customers from Paris
    // Handle not found
  }
}

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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
    }
}

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
    // Handle found
  } else {
    // No customers from Paris
    // Handle not found
  }
}

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()
    // Use count
}

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();
  // Use count
}

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)
    // Use count
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """, null)) {
  cursor.moveToFirst();
  int count = cursor.getInt(0);
  // Use count
}

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()) {
                // Process inner cursor data
            }
        }
    }
}

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()) {
          // Process inner cursor data
        }
    }
  }
}

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()) {
        // Process cursor data
    }
}

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()) {
    // Process cursor data
  }
}

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

Catatan: Tabel indeks dibuat untuk username, yang menggunakan penyimpanan ekstra. Untuk mengetahui informasi selengkapnya tentang cara menjalankan kueri tabel indeks, lihat Mempercepat kueri dengan indeks.

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

Catatan: Jika Anda menentukan INTEGER PRIMARY KEY, batasan unik berlaku untuk kolom tersebut dan tidak menggunakan tabel indeks tambahan.

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, "customerValue")
        )
    }
} finally {
    db.endTransaction()
}

Java

db.beginTransaction();
try {
  for (customer : Customers) {
    db.execSQL(
        "INSERT INTO Customers VALUES (?, ?, ?)",
        new String[] {
          String.valueOf(customer.id),
          customer.name,
          "customerValue"
        });
  }
} finally {
  db.endTransaction()
}

Catatan: Hanya satu transaksi tulis yang dapat terjadi dalam satu waktu. Gunakan MoreExecutors.newSequentialExecutor(Executor) untuk membuat serialisasi operasi tulis.