Best Practices für die SQLite-Leistung (Ansichten)

Konzepte und Jetpack Compose-Implementierung

Android bietet integrierte Unterstützung für SQLite, eine effiziente SQL-Datenbank. Befolgen Sie diese Best Practices, um die Leistung Ihrer Anwendung zu optimieren und dafür zu sorgen, dass sie auch bei wachsendem Datenvolumen schnell und vorhersehbar schnell bleibt. Wenn Sie diese Best Practices befolgen, verringern Sie auch die Wahrscheinlichkeit, dass Leistungsprobleme auftreten, die schwer zu reproduzieren und zu beheben sind.

Beachten Sie die folgenden Leistungsprinzipien, um eine schnellere Leistung zu erzielen:

  • Weniger Zeilen und Spalten lesen: Optimieren Sie Ihre Abfragen, um nur die erforderlichen Daten abzurufen. Minimieren Sie die Menge der aus der Datenbank gelesenen Daten, da ein übermäßiger Datenabruf die Leistung beeinträchtigen kann.

  • Arbeit an die SQLite-Engine übertragen: Berechnungen, Filter- und Sortiervorgänge innerhalb der SQL-Abfragen ausführen. Die Verwendung der Abfrage-Engine von SQLite kann die Leistung erheblich verbessern.

  • Datenbankschema ändern: Entwerfen Sie Ihr Datenbankschema so, dass SQLite effiziente Abfragepläne und Datendarstellungen erstellen kann. Tabellen richtig indexieren und Tabellenstrukturen optimieren, um die Leistung zu verbessern.

Außerdem können Sie die verfügbaren Tools zur Fehlerbehebung verwenden, um die Leistung Ihrer SQLite-Datenbank zu messen und Bereiche zu identifizieren, die optimiert werden müssen.

Wir empfehlen die Verwendung der Jetpack Room-Bibliothek.

Datenbank für Leistung konfigurieren

Folgen Sie den Schritten in diesem Abschnitt, um Ihre Datenbank für eine optimale Leistung in SQLite zu konfigurieren.

Synchronisierungsmodus lockern

SQLite bietet eine Option zur Steuerung des synchronen Modus. Im Modus FULL gibt jeder Commit einen fsync aus, um sicherzustellen, dass die Daten das Laufwerk erreichen. Dies verbessert die Datenbeständigkeit, verlangsamt jedoch Ihre Commits. Mit WAL wird der NORMAL-Modus nicht bei jedem Commit synchronisiert und die Datenbank wird trotzdem vor Beschädigung geschützt.

Wenn du WAL über das Framework aktivierst, wendet Android den standardmäßigen synchronen WAL-Modus des Geräts an. Dieser ist in AOSP für Android 9 und höher NORMAL. Gerätehersteller können diese Standardeinstellung ändern. Datenbanken, die WAL nicht verwenden, haben standardmäßig FULL. Damit Ihre Datenbank NORMAL verwendet, legen Sie dies beim Öffnen der Datenbank fest:

Kotlin

// When opening the database
val params = SQLiteDatabase.OpenParams.Builder()
    .addOpenFlags(SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING)
    // SYNC_MODE_NORMAL is available in API level 33 and higher.
    // On API levels 28 to 32, pass "NORMAL" instead.
    .setSynchronousMode(SQLiteDatabase.SYNC_MODE_NORMAL)
    .build()
// With SQLiteOpenHelper, call setOpenParams(params) in your constructor.

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

Java

// When opening the database
SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
    .addOpenFlags(SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING)
    // SYNC_MODE_NORMAL is available in API level 33 and higher.
    // On API levels 28 to 32, pass "NORMAL" instead.
    .setSynchronousMode(SQLiteDatabase.SYNC_MODE_NORMAL)
    .build();
// With SQLiteOpenHelper, call setOpenParams(params) in your constructor.

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

In dieser Einstellung kann ein Commit zurückgegeben werden, bevor die Daten auf einem Laufwerk gespeichert werden. Wenn ein Gerät heruntergefahren wird, z. B. bei einem Stromausfall oder einer Kernel-Panik, können die festgeschriebenen Daten verloren gehen. Aufgrund des Loggings wird Ihre Datenbank jedoch nicht beschädigt.

Wenn nur Ihre Anwendung abstürzt, werden Ihre Daten trotzdem auf das Laufwerk geschrieben. Bei den meisten Apps führt diese Einstellung zu Leistungsverbesserungen ohne nennenswerte Kosten.

Abfrageleistung verbessern

Befolgen Sie diese Best Practices, um die Abfrageleistung in SQLite zu verbessern, indem Sie die Antwortzeiten minimieren und die Verarbeitungseffizienz maximieren.

Nur die benötigten Zeilen lesen

Mit Filtern können Sie die Ergebnisse eingrenzen, indem Sie bestimmte Kriterien wie Zeitraum, Standort oder Name angeben. Mit Limits können Sie die Anzahl der angezeigten Ergebnisse steuern:

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

Nur die benötigten Spalten lesen

Vermeiden Sie die Auswahl nicht benötigter Spalten, da dies Ihre Abfragen verlangsamen und Ressourcen verschwenden kann. Wählen Sie stattdessen nur die Spalten aus, die verwendet werden.

Im folgenden Beispiel wählen Sie id, name und phone aus:

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

Sie benötigen jedoch nur die Spalte 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
  }
}

Abfragen parametrisieren

Ihr Abfragestring kann einen Parameter enthalten, der nur zur Laufzeit bekannt ist, z. B.:

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

Im vorherigen Code wird für jede Abfrage ein anderer String erstellt. Daher wird der Anweisungscache nicht genutzt. Bei jedem Aufruf muss SQLite die Abfrage kompilieren, bevor sie ausgeführt werden kann. Stattdessen können Sie das Argument id durch einen Parameter ersetzen und den Wert mit selectionArgs binden:

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

Jetzt kann die Abfrage einmal kompiliert und im Cache gespeichert werden. Die kompilierte Abfrage wird zwischen verschiedenen Aufrufen von getNameById(long) wiederverwendet.

DISTINCT für eindeutige Werte verwenden

Mit dem Keyword DISTINCT können Sie die Leistung Ihrer Abfragen verbessern, da die Menge der zu verarbeitenden Daten reduziert wird. Wenn Sie beispielsweise nur die eindeutigen Werte aus einer Spalte zurückgeben möchten, verwenden Sie 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
  }
}

Nach Möglichkeit Aggregatfunktionen verwenden

Verwenden Sie Aggregatfunktionen für aggregierte Ergebnisse ohne Zeilendaten. Mit dem folgenden Code wird beispielsweise geprüft, ob mindestens eine übereinstimmende Zeile vorhanden ist:

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

Wenn Sie nur die erste Zeile abrufen möchten, können Sie EXISTS() verwenden, um 0 zurückzugeben, wenn keine übereinstimmende Zeile vorhanden ist, und 1, wenn eine oder mehrere Zeilen übereinstimmen:

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

Verwenden Sie SQLite-Aggregatfunktionen im App-Code:

  • COUNT: Zählt, wie viele Zeilen in einer Spalte vorhanden sind.
  • SUM: Addiert alle numerischen Werte in einer Spalte.
  • MIN oder MAX: Bestimmt den niedrigsten oder höchsten Wert. Funktioniert für numerische Spalten, DATE-Typen und Texttypen.
  • AVG: Ermittelt den durchschnittlichen numerischen Wert.
  • GROUP_CONCAT: Verkettet Strings mit einem optionalen Trennzeichen.

COUNT() anstelle von Cursor.getCount() verwenden

Im folgenden Beispiel liest die Funktion Cursor.getCount() alle Zeilen aus der Datenbank und gibt alle Zeilenwerte zurück:

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
}

Wenn Sie jedoch COUNT() verwenden, gibt die Datenbank nur die Anzahl zurück:

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
}

Abfragen statt Code verschachteln

SQL ist zusammensetzbar und unterstützt Unterabfragen, Joins und Fremdschlüsseleinschränkungen. Sie können das Ergebnis einer Abfrage in einer anderen Abfrage verwenden, ohne den Anwendungscode zu durchlaufen. Dadurch müssen weniger Daten aus SQLite kopiert werden und die Datenbank-Engine kann Ihre Abfrage optimieren.

Im folgenden Beispiel können Sie eine Abfrage ausführen, um herauszufinden, in welcher Stadt die meisten Kunden leben. Anschließend können Sie das Ergebnis in einer anderen Abfrage verwenden, um alle Kunden aus dieser Stadt zu finden:

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

Wenn Sie das Ergebnis in der Hälfte der Zeit des vorherigen Beispiels erhalten möchten, verwenden Sie eine einzelne SQL-Abfrage mit verschachtelten Anweisungen:

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

Eindeutigkeit in SQL prüfen

Wenn eine Zeile nur eingefügt werden darf, wenn ein bestimmter Spaltenwert in der Tabelle eindeutig ist, kann es effizienter sein, diese Eindeutigkeit als Spalteneinschränkung zu erzwingen.

Im folgenden Beispiel wird eine Abfrage ausgeführt, um die einzufügende Zeile zu validieren, und eine weitere, um die Zeile tatsächlich einzufügen:

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

Anstatt die Unique-Einschränkung in Kotlin oder Java zu prüfen, können Sie sie in SQL prüfen, wenn Sie die Tabelle definieren:

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

SQLite macht dasselbe wie Folgendes:

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

Jetzt können Sie eine Zeile einfügen und die Einschränkung von SQLite prüfen lassen:

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 unterstützt eindeutige Indexe mit mehreren Spalten:

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

SQLite validiert Einschränkungen schneller und mit weniger Aufwand als Kotlin- oder Java-Code. Es empfiehlt sich, SQLite anstelle von App-Code zu verwenden.

Mehrere Einfügungen in einer einzelnen Transaktion zusammenfassen

Eine Transaktion führt mehrere Vorgänge aus, was nicht nur die Effizienz, sondern auch die Korrektheit verbessert. Wenn Sie die Datenkonsistenz verbessern und die Leistung beschleunigen möchten, können Sie Einfügungen im Batch ausführen:

Kotlin

db.beginTransaction()
try {
    customers.forEach { customer ->
        db.execSQL(
            "INSERT INTO Customers VALUES (?, ?, ?)",
            arrayOf(customer.id.toString(), customer.name, "customerValue")
        )
    }
    db.setTransactionSuccessful()
} finally {
    db.endTransaction()
}

Java

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