Sprawdzone metody dotyczące wydajności SQLite

Android oferuje wbudowaną obsługę SQLite – wydajnej bazy danych SQL. Postępuj zgodnie z tymi sprawdzonymi metodami, aby zoptymalizować wydajność swojej aplikacji, zapewniając jej działanie w miarę wzrostu ilości danych w przewidywalny sposób. Stosowanie tych sprawdzonych metod zmniejsza też ryzyko wystąpienia problemów z wydajnością, które są trudne do odtworzenia i rozwiązywania.

Aby szybciej osiągnąć skuteczność, przestrzegaj tych zasad:

• Odczytuj mniej wierszy i kolumn: możesz optymalizować zapytania, aby pobierać tylko niezbędne dane. Ogranicz ilość danych odczytywanych z bazy danych, ponieważ ich nadmierne pobieranie może mieć wpływ na wydajność.

• Przekazywanie zadań do mechanizmu SQLite: możesz wykonywać operacje obliczeniowe, filtrowania i sortowania w ramach zapytań SQL. Użycie mechanizmu zapytań SQLite może znacznie zwiększyć wydajność.

• Modyfikowanie schematu bazy danych: zaprojektuj schemat bazy danych, aby ułatwić SQLite tworzenie wydajnych planów zapytań i reprezentacji danych. Prawidłowo indeksuj tabele i optymalizuj ich struktury, aby zwiększyć wydajność.

Dodatkowo możesz używać dostępnych narzędzi do rozwiązywania problemów, aby mierzyć wydajność bazy danych SQLite i w ten sposób identyfikować obszary wymagające optymalizacji.

Zalecamy skorzystanie z biblioteki Jetpack Room.

Skonfiguruj bazę danych pod kątem wydajności

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

Włącz logowanie z wyprzedzeniem

SQLite wdraża mutacje, dołączając je do logu, który czasami kompaktuje do bazy danych. Jest to tzw. Zapisywanie z wyprzedzeniem (WAL).

Włącz WAL, chyba że używasz ATTACH DATABASE.

Rozluźnij tryb synchronizacji

Gdy używany jest zapisywanie logów z wyprzedzeniem, domyślnie każde zatwierdzenie wysyła fsync, aby zapewnić, że dane dotrą na dysk. Zwiększa to trwałość danych, ale spowalnia obciążenia.

SQLite ma opcję sterowania trybem synchronicznym. Jeśli włączysz zapisywanie logów z wyprzedzeniem, ustaw tryb synchroniczny na NORMAL:

db.execSQL("PRAGMA synchronous = NORMAL")

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

W tym ustawieniu zatwierdzenie może zostać zwrócone, zanim dane zostaną zapisane na dysku. Jeśli urządzenie zostanie wyłączone, na przykład w wyniku utraty zasilania lub paniki z nią związane, przesłane dane mogą zostać utracone. Dzięki logowaniu Twoja baza danych nie jest uszkodzona.

Jeśli tylko aplikacja ulegnie awarii, dane nadal będą znajdować się na dysku. W przypadku większości aplikacji to ustawienie pozwala poprawić wydajność bez żadnych dodatkowych kosztów.

Definiowanie wydajnych schematów tabel

Aby zoptymalizować wydajność i zminimalizować wykorzystanie danych, zdefiniuj wydajny schemat tabeli. SQLite tworzy wydajne plany i dane zapytań, co przyspiesza pobieranie danych. W tej sekcji znajdziesz sprawdzone metody tworzenia schematów tabel.

Rozważ: INTEGER PRIMARY KEY

Na potrzeby tego przykład zdefiniuj i wypełnij tabelę w ten sposób:

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

Dane wyjściowe tabeli są takie:

Kolumna rowid zawiera indeks, który zachowuje zamówienie reklamowe. Zapytania z filtrem rowid są implementowane jako szybkie wyszukiwanie B-tree, ale zapytania z filtrem id to powolne skanowanie tabeli.

Jeśli planujesz wyszukiwanie do id, możesz uniknąć przechowywania kolumny rowid, aby zmniejszyć ilość miejsca na dane i ogólnie przyspieszyć bazę danych:

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

Tabela wygląda teraz tak:

Ponieważ kolumna rowid nie musi być przechowywana, zapytania id działają szybko. Zwróć uwagę, że tabela jest teraz sortowana na podstawie kolumny id, a nie zamówienia reklamowego.

Przyspiesz zapytania dzięki indeksom

SQLite używa indeksów do przyspieszania zapytań. Podczas filtrowania (WHERE), sortowania (ORDER BY) lub agregowania (GROUP BY) kolumny, jeśli tabela ma indeks kolumny, wykonywanie zapytania jest przyspieszone.

W poprzednim przykładzie filtrowanie według parametru city wymaga przeskanowania całej tabeli:

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

W przypadku aplikacji z dużą liczbą zapytań dotyczących miast możesz przyspieszyć te zapytania za pomocą indeksu:

CREATE INDEX city_index ON Customers(city);

Indeks jest zaimplementowany jako dodatkowa tabela posortowana według kolumny indeksu i zmapowana na wartość rowid:

Zauważ, że koszt miejsca na dane kolumny city jest teraz podwójny, ponieważ jest on teraz obecny zarówno w pierwotnej tabeli, jak i w indeksie. Używasz indeksu, więc koszt dodatkowego miejsca na dane jest wart korzyści szybszych zapytań. Nie utrzymuj jednak indeksu, którego nie używasz, aby uniknąć opłat za miejsce na dane za brak wzrostu wydajności zapytań.

Tworzenie indeksów wielokolumnowych

Jeśli zapytania łączą wiele kolumn, możesz tworzyć indeksy wielokolumnowe, aby w pełni przyspieszyć wykonywanie zapytania. Możesz też użyć indeksu w kolumnie zewnętrznej i zezwolić na wyszukiwanie w środku za pomocą skanowania liniowego.

Przykładowo w przypadku następującego zapytania:

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

Możesz przyspieszyć zapytanie za pomocą indeksu wielokolumnowego w tej samej kolejności, w jakiej zostało określone w zapytaniu:

CREATE INDEX city_name_index ON Customers(city, name);

Jeśli jednak indeks masz tylko w city, porządkowanie zewnętrzne nadal będzie przyspieszone, a porządkowanie wewnętrzne wymaga skanowania liniowego.

Działa to też w przypadku zapytań opartych na prefiksie. Na przykład indeks ON Customers (city, name) przyspiesza też filtrowanie, sortowanie i grupowanie według wartości city, ponieważ tabela indeksu indeksu wielokolumnowego jest uporządkowana według podanych indeksów w podanej kolejności.

Rozważ: WITHOUT ROWID

Domyślnie SQLite tworzy dla tabeli kolumnę rowid, gdzie rowid to niejawna wartość INTEGER PRIMARY KEY AUTOINCREMENT. Jeśli masz już kolumnę z wartością INTEGER PRIMARY KEY, ta kolumna stanie się aliasem kolumny rowid.

W przypadku tabel, które mają klucz podstawowy inny niż INTEGER lub złożony z kolumn, rozważ użycie WITHOUT ROWID.

Przechowuj małe dane w formacie BLOB, a duże jako plik

Jeśli chcesz powiązać duże dane z wierszem, na przykład miniaturę obrazu lub zdjęcie kontaktu, możesz je zapisać w kolumnie BLOB lub w pliku, a potem zapisać w tej kolumnie ścieżkę pliku.

Pliki są zwykle zaokrąglane do przyrostów co 4 KB. W przypadku bardzo małych plików, gdzie błąd zaokrąglania jest istotny, lepiej zapisać je w bazie danych jako BLOB. SQLite minimalizuje liczbę wywołań systemu plików i w niektórych przypadkach jest szybsza niż bazowy system plików.

Zwiększ wydajność zapytań

Postępuj zgodnie z tymi sprawdzonymi metodami, aby poprawić wydajność zapytań w SQLite przez zminimalizowanie czasów odpowiedzi i maksymalizację wydajności przetwarzania.

Odczytuj tylko potrzebne wiersze

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

db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        ...
    }
}

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

Odczytuj tylko potrzebne kolumny

Unikaj wybierania niepotrzebnych kolumn, ponieważ może to spowolnić wykonywanie zapytań i zużywać zasoby. Zamiast tego wybierz tylko używane kolumny.

W tym przykładzie wybierasz id, name i phone:

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

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

Potrzebujesz jednak tylko kolumny name:

db.rawQuery("""
    SELECT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(0)
        ...
    }
}

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

Użyj DISTINCT dla unikalnych wartości

Używanie słowa kluczowego DISTINCT może zwiększyć skuteczność zapytań dzięki zmniejszeniu ilości danych do przetworzenia. Jeśli np. chcesz zwrócić tylko unikalne wartości z kolumny, użyj funkcji DISTINCT:

db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Only iterate over distinct names in Kotlin
        ...
    }
}

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

Gdy to możliwe, używaj funkcji agregacji

Do wyników agregowanych bez danych wierszy używaj funkcji agregacji. Na przykład ten kod sprawdza, czy istnieje co najmniej 1 pasujący wiersz:

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

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

Aby pobrać tylko pierwszy wiersz, możesz za pomocą funkcji EXISTS() zwrócić wartość 0, jeśli pasujący wiersz nie istnieje, oraz wartość 1, jeśli pasuje co najmniej 1 wiersz:

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

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

Użyj funkcji agregacji SQLite w kodzie aplikacji:

• COUNT: zlicza liczbę wierszy 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 tekstowe z opcjonalnym separatorem.

Używaj COUNT() zamiast Cursor.getCount()

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

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

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

Jeśli jednak użyjesz funkcji COUNT(), baza danych zwraca tylko liczbę:

db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    cursor.moveToFirst()
    val count = cursor.getInt(0)
}

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

Zapytania dotyczące Nest zamiast kodu

Język SQL jest funkcją kompozycyjną i obsługuje podzapytania, złączenia i ograniczenia klucza obcego. Rezultatu jednego zapytania możesz użyć w innym zapytaniu bez przechodzenia do kodu aplikacji. Ogranicza to potrzebę kopiowania danych z SQLite i pozwala silnikowi bazy danych optymalizować zapytanie.

W tym przykładzie możesz uruchomić zapytanie, aby sprawdzić, w którym mieście jest najwięcej klientów, a następnie użyć wyniku innego zapytania, aby znaleźć wszystkich klientów z tego miasta:

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

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

Aby uzyskać wynik o połowę szybciej z poprzedniego przykładu, użyj pojedynczego zapytania SQL z zagnieżdżonymi instrukcjami:

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

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

Sprawdź unikalność w SQL

Jeśli nie można wstawić wiersza, chyba że konkretna wartość kolumny jest unikalna w tabeli, skuteczniejszym rozwiązaniem może być wymuszenie tej unikalności jako ograniczenie kolumny.

W poniższym przykładzie jedno zapytanie sprawdza, czy wiersz ma zostać wstawiony, a drugie – rzeczywiste wstawienie:

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

// 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ć ograniczenie unikalne w Kotlin lub Javie, możesz sprawdzić je w SQL podczas definiowania tabeli:

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

SQLite działa tak samo jak poniżej:

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

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

try {
    db.execSql(
        "INSERT INTO Customers VALUES (?, ?, ?)",
        arrayOf(customer.id.toString(), customer.name, customer.username)
    )
} catch(e: SQLiteConstraintException) {
    throw AddCustomerException(customer, e)
}

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

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

SQLite weryfikuje ograniczenia szybciej i przy mniejszym nakładzie pracy niż w przypadku kodu Kotlin lub Java. Sprawdzoną metodą jest używanie SQLite zamiast kodu aplikacji.

Zbiorcze wstawianie wielu reklam w ramach 1 transakcji

Transakcja zawiera wiele operacji, co zwiększa nie tylko wydajność, ale także poprawność. Aby zwiększyć spójność danych i przyspieszyć wydajność, możesz przesyłać zbiorczo wstawienia:

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

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

Używanie narzędzi do rozwiązywania problemów

SQLite udostępnia poniższe narzędzia do rozwiązywania problemów, które pomagają mierzyć wydajność.

Użyj interaktywnego promptu SQLite

Uruchom SQLite na swoim komputerze, aby wykonywać zapytania i uczyć się. Różne wersje platformy Androida używają różnych wersji SQLite. Aby używać tego samego silnika, który działa na urządzeniu z Androidem, wpisz adb shell i uruchom sqlite3 na urządzeniu docelowym.

Możesz poprosić SQLite o określanie czasu wykonywania zapytań:

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

EXPLAIN QUERY PLAN

Możesz poprosić SQLite o wyjaśnienie, jak ma odpowiadać na zapytanie, używając EXPLAIN QUERY PLAN:

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

Poprzedni przykład wymaga pełnego skanowania tabeli bez indeksu, aby znaleźć wszystkich klientów z Paryża. Jest to tzw. złożoność liniowa. SQLite musi odczytać wszystkie wiersze i zachować tylko te, które odpowiadają klientom z Paryża. Aby rozwiązać ten problem, możesz dodać 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=?

Jeśli używasz interaktywnej powłoki, możesz poprosić SQLite o wyjaśnienie planów zapytań:

sqlite> .eqp on

Więcej informacji znajdziesz w artykule o planowaniu zapytań.

Analizator SQLite

SQLite oferuje interfejs wiersza poleceń sqlite3_analyzer (CLI) do pobierania dodatkowych informacji, które przydają się do rozwiązywania problemów z wydajnością. Aby zainstalować, wejdź na stronę pobierania SQLite.

Za pomocą adb pull możesz pobrać plik bazy danych z urządzenia docelowego na stację roboczą w celu przeprowadzenia analizy:

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

Przeglądarka SQLite

Możesz też zainstalować narzędzie GUI SQLite Browser na stronie pobranych plików SQLite.

Logowanie w Androidzie

Android łączy zapytania SQLite i rejestruje je za Ciebie:

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