Sprawdzone metody dotyczące wydajności SQLite (widoki)

Pojęcia i implementacja w Jetpack Compose

Android oferuje wbudowaną obsługę SQLite, wydajnej bazy danych SQL. Aby zoptymalizować wydajność aplikacji i zapewnić jej szybkość oraz przewidywalną szybkość działania w miarę wzrostu ilości danych, stosuj te sprawdzone metody. Stosując te sprawdzone metody, zmniejszasz też ryzyko wystąpienia problemów z wydajnością, które trudno odtworzyć i rozwiązać.

Aby uzyskać większą szybkość działania, postępuj zgodnie z tymi zasadami:

  • Odczytuj mniej wierszy i kolumn: optymalizuj zapytania, aby pobierać tylko niezbędne dane. zminimalizować ilość danych odczytywanych z bazy danych, ponieważ nadmierne pobieranie danych może wpływać na wydajność;

  • Przekazywanie pracy do silnika SQLite: wykonywanie obliczeń, filtrowania i sortowania w zapytaniach SQL. Korzystanie z silnika zapytań SQLite może znacznie zwiększyć wydajność.

  • Zmodyfikuj schemat bazy danych: zaprojektuj schemat bazy danych tak, aby SQLite mogło tworzyć wydajne plany zapytań i reprezentacje danych. Prawidłowo indeksuj tabele i optymalizuj ich struktury, aby zwiększyć wydajność.

Możesz też użyć dostępnych narzędzi do rozwiązywania problemów, aby zmierzyć wydajność bazy danych SQLite i określić obszary wymagające optymalizacji.

Zalecamy używanie biblioteki Jetpack Room.

Konfigurowanie bazy danych pod kątem wydajności

Aby skonfigurować bazę danych pod kątem optymalnej wydajności w SQLite, wykonaj czynności opisane w tej sekcji.

Zmień tryb synchronizacji

Podczas korzystania z WAL domyślnie każde zatwierdzenie wysyła polecenie fsync, aby zapewnić, że dane trafią na dysk. Zwiększa to trwałość danych, ale spowalnia zatwierdzanie zmian.

SQLite ma opcję sterowania trybem synchronicznym. Jeśli włączysz WAL, ustaw tryb synchroniczny na 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");

W tym ustawieniu zatwierdzenie może zostać zwrócone, zanim dane zostaną zapisane na dysku. Jeśli nastąpi wyłączenie urządzenia, np. z powodu utraty zasilania lub błędu jądra, zatwierdzone dane mogą zostać utracone. Jednak dzięki rejestrowaniu dziennika baza danych nie jest uszkodzona.

Jeśli awarii ulegnie tylko aplikacja, dane nadal trafią na dysk. W przypadku większości aplikacji to ustawienie zwiększa wydajność bez ponoszenia znaczących kosztów.

Zwiększanie wydajności zapytań

Aby zwiększyć wydajność zapytań w SQLite, postępuj zgodnie z tymi sprawdzonymi metodami, które pozwolą Ci zminimalizować czas odpowiedzi i zmaksymalizować efektywność przetwarzania.

Odczytywanie tylko potrzebnych wierszy

Filtry pozwalają zawęzić wyniki przez określenie pewnych kryteriów, takich jak zakres dat, lokalizacja lub nazwa. Limity pozwalają kontrolować liczbę wyświetlanych wyników:

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

Odczytywanie tylko potrzebnych kolumn

Unikaj wybierania niepotrzebnych kolumn, które mogą spowolnić zapytania i marnować zasoby. Zamiast tego wybierz tylko kolumny, które są używane.

W przykładzie poniżej wybierasz id, name i 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
  }
}

Wystarczy jednak kolumna 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
  }
}

Parametryzowanie zapytań

Ciąg zapytania może zawierać parametr, który jest znany tylko w czasie działania programu, np.:

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

W powyższym kodzie każde zapytanie tworzy inny ciąg znaków, więc nie korzysta z pamięci podręcznej instrukcji. Każde wywołanie wymaga skompilowania przez SQLite, zanim będzie można je wykonać. Zamiast tego możesz zastąpić argument id parametrem i powiązać wartość z 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;
    }
  }
}

Teraz zapytanie można skompilować i zapisać w pamięci podręcznej. Skompilowane zapytanie jest ponownie używane między różnymi wywołaniami funkcji getNameById(long).

Używaj symbolu DISTINCT w przypadku unikalnych wartości.

Użycie słowa kluczowego DISTINCT może zwiększyć skuteczność zapytań, ponieważ zmniejsza ilość danych, które trzeba przetworzyć. Jeśli na przykład chcesz zwrócić tylko unikalne wartości z kolumny, użyj funkcji 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
  }
}

W miarę możliwości używaj funkcji agregacji

Używaj funkcji agregacji, aby uzyskać zagregowane wyniki bez danych wierszy. Na przykład poniższy kod sprawdza, czy istnieje co najmniej jeden pasujący wiersz:

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

Aby pobrać tylko pierwszy wiersz, możesz użyć funkcji EXISTS(), która zwraca wartość 0, jeśli pasujący wiersz nie istnieje, oraz 1, jeśli pasuje co najmniej 1 wiersz:

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

Używaj w kodzie aplikacji funkcji agregujących SQLite:

  • COUNT: zlicza wiersze w kolumnie.
  • SUM: dodaje wszystkie wartości liczbowe w kolumnie.
  • MIN lub MAX: określa najniższą lub najwyższą wartość. Działa w przypadku kolumn liczbowych, typów DATE i typów tekstowych.
  • AVG: znajduje średnią wartość liczbową.
  • GROUP_CONCAT: łączy ciągi znaków z opcjonalnym separatorem.

Użyj COUNT() w zamian zasady Cursor.getCount()

W tym przykładzie funkcja Cursor.getCount() odczytuje wszystkie wiersze z bazy danych i zwraca wszystkie wartości wierszy:

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
}

Jednak po użyciu COUNT() baza danych zwróci tylko liczbę:

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
}

Zagnieżdżanie zapytań zamiast kodu

SQL jest kompozycyjny i obsługuje podzapytania, złączenia i ograniczenia klucza obcego. Możesz użyć wyniku jednego zapytania w innym zapytaniu bez konieczności pisania kodu aplikacji. Zmniejsza to konieczność kopiowania danych z SQLite i umożliwia silnikowi bazy danych optymalizację zapytania.

W tym przykładzie możesz uruchomić zapytanie, aby sprawdzić, które miasto ma najwięcej klientów, a następnie użyć wyniku w innym zapytaniu, aby znaleźć wszystkich klientów z tego miasta:

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

Aby uzyskać wynik w połowie czasu poprzedniego przykładu, użyj pojedynczego zapytania SQL z zagnieżdżonymi instrukcjami:

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

Sprawdzanie unikalności w SQL

Jeśli wiersz nie może zostać wstawiony, chyba że wartość określonej kolumny jest unikalna w tabeli, bardziej efektywne może być wymuszenie tej unikalności jako ograniczenia kolumny.

W tym przykładzie uruchamiane jest jedno zapytanie w celu sprawdzenia wiersza, który ma zostać wstawiony, a drugie w celu wstawienia go:

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

Zamiast sprawdzać unikalne ograniczenie w języku Kotlin lub Java, możesz to zrobić w SQL podczas definiowania tabeli:

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

SQLite robi to samo co:

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

Teraz możesz wstawić wiersz i pozwolić SQLite sprawdzić ograniczenie:

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 obsługuje indeksy unikalne z wieloma kolumnami:

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

SQLite weryfikuje ograniczenia szybciej i przy mniejszym obciążeniu niż kod w języku Kotlin lub Java. Sprawdzoną metodą jest używanie SQLite zamiast kodu aplikacji.

Wstawianie wielu elementów w ramach jednej transakcji

Transakcja obejmuje wiele operacji, co zwiększa nie tylko wydajność, ale też poprawność. Aby zwiększyć spójność danych i przyspieszyć działanie, możesz wykonywać wstawienia partiami:

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