Best practice per le prestazioni di SQLite (viste)

Concetti e implementazione di Jetpack Compose

Android offre il supporto integrato per SQLite, un database SQL efficiente. Segui queste best practice per ottimizzare il rendimento della tua app, assicurandoti che rimanga veloce e prevedibilmente veloce man mano che i dati aumentano. Utilizzando queste best practice, riduci anche la possibilità di riscontrare problemi di rendimento difficili da riprodurre e risolvere.

Per ottenere prestazioni più veloci, segui questi principi di rendimento:

• Leggi meno righe e colonne: ottimizza le query per recuperare solo i dati necessari. Riduci al minimo la quantità di dati letti dal database, perché il recupero di dati in eccesso può influire sul rendimento.

• Trasferisci il lavoro al motore SQLite: esegui operazioni di calcolo, filtro e ordinamento all'interno delle query SQL. L'utilizzo del motore di query di SQLite può migliorare significativamente il rendimento.

• Modifica lo schema del database: progetta lo schema del database in modo che SQLite possa creare piani di query e rappresentazioni dei dati efficienti. Indicizza correttamente le tabelle e ottimizza le strutture delle tabelle per migliorare il rendimento.

Inoltre, puoi utilizzare gli strumenti di risoluzione dei problemi disponibili per misurare il rendimento del tuo database SQLite e identificare le aree che richiedono ottimizzazione.

Ti consigliamo di utilizzare la libreria Jetpack Room.

Configurare il database per il rendimento

Segui i passaggi descritti in questa sezione per configurare il database per un rendimento ottimale in SQLite.

Rilassare la modalità di sincronizzazione

Quando utilizzi WAL, per impostazione predefinita ogni commit emette un fsync per garantire che i dati raggiungano il disco. In questo modo la durabilità dei dati migliora, ma i commit rallentano.

SQLite ha un'opzione per controllare la modalità sincrona. Se abiliti WAL, imposta la modalità sincrona su 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");

In questa impostazione, un commit può essere restituito prima che i dati vengano archiviati su un disco. Se si verifica un arresto del dispositivo, ad esempio in caso di perdita di alimentazione o di kernel panic, i dati di cui è stato eseguito il commit potrebbero andare persi. Tuttavia, grazie alla registrazione, il database non viene danneggiato.

Se si arresta in modo anomalo solo la tua app, i dati raggiungono comunque il disco. Per la maggior parte delle app, questa impostazione comporta miglioramenti del rendimento senza costi materiali.

Nota: se la tua app ha più database, utilizza la stessa impostazione sincrona ovunque nel caso in cui esistano dipendenze dei dati tra database diversi.

Migliorare il rendimento delle query

Segui queste best practice per migliorare il rendimento delle query in SQLite riducendo al minimo i tempi di risposta e massimizzando l'efficienza di elaborazione.

Nota: molti degli esempi in questa pagina includono una clausola LIMIT. Questa è una best practice perché le query che potrebbero restituire molte righe hanno implicazioni sul rendimento quando restituiscono set di dati di grandi dimensioni. L'eccezione è rappresentata dalle query che restituiscono implicitamente un set di dati con vincoli di dimensioni. Ad esempio, la ricerca in un campo definito come UNIQUE può restituire solo zero o una riga.

Leggi solo le righe di cui hai bisogno

I filtri ti consentono di restringere i risultati specificando determinati criteri, come intervallo di date, località o nome. I limiti ti consentono di controllare il numero di risultati visualizzati:

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

Leggi solo le colonne di cui hai bisogno

Evita di selezionare colonne non necessarie, perché possono rallentare le query e sprecare risorse. Seleziona invece solo le colonne utilizzate.

Nell'esempio seguente, selezioni id, name e 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
  }
}

Tuttavia, hai bisogno solo della colonna 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
  }
}

Parametrizzare le query

La stringa di query potrebbe includere un parametro noto solo in fase di runtime, ad esempio:

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

Nel codice precedente, ogni query crea una stringa diversa e quindi non sfrutta la cache delle istruzioni. Ogni chiamata richiede che SQLite la compili prima di poterla eseguire. In alternativa, puoi sostituire l'argomento id con un parametro e associare il valore a 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;
    }
  }
}

Ora la query può essere compilata una sola volta e memorizzata nella cache. La query compilata viene riutilizzata tra le diverse invocazioni di getNameById(long).

Attenzione: se l'argomento di input è un altro oggetto meno vincolato di un semplice numero, la concatenazione di stringhe potrebbe portare a una vulnerabilità di sicurezza SQL injection. Utilizza sempre i parametri per le variabili o i dati non attendibili.

Utilizza DISTINCT per i valori univoci

L'utilizzo della parola chiave DISTINCT può migliorare il rendimento delle query riducendo la quantità di dati da elaborare. Ad esempio, se vuoi restituire solo i valori univoci di una colonna, utilizza 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
  }
}

Utilizza le funzioni di aggregazione quando possibile

Utilizza le funzioni di aggregazione per i risultati aggregati senza dati di riga. Ad esempio, il seguente codice verifica se esiste almeno una riga corrispondente:

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

Per recuperare solo la prima riga, puoi utilizzare EXISTS() per restituire 0 se non esiste una riga corrispondente e 1 se una o più righe corrispondono:

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

Utilizza le funzioni di aggregazione SQLite nel codice dell'app:

• COUNT: conta il numero di righe in una colonna.
• SUM: aggiunge tutti i valori numerici in una colonna.
• MIN o MAX: determina il valore più basso o più alto. Funziona per le colonne numeriche, i tipi DATE e i tipi di testo.
• AVG: trova il valore numerico medio.
• GROUP_CONCAT: concatena le stringhe con un separatore facoltativo.

Utilizza COUNT() anziché Cursor.getCount()

Nell'esempio seguente, la Cursor.getCount() funzione legge tutte le righe del database e restituisce tutti i valori delle righe:

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
}

Tuttavia, utilizzando COUNT(), il database restituisce solo il conteggio:

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
}

Incorpora le query nel codice

SQL è componibile e supporta sottoquery, join e vincoli di chiave esterna. Puoi utilizzare il risultato di una query in un'altra query senza passare per il codice dell'app. In questo modo si riduce la necessità di copiare i dati da SQLite e il motore di database può ottimizzare la query.

Nell'esempio seguente, puoi eseguire una query per trovare la città con il maggior numero di clienti, quindi utilizzare il risultato in un'altra query per trovare tutti i clienti di quella città:

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

Per ottenere il risultato in metà del tempo dell'esempio precedente, utilizza una singola query SQL con istruzioni nidificate:

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

Verificare l'unicità in SQL

Se una riga non deve essere inserita a meno che un determinato valore della colonna non sia univoco nella tabella, potrebbe essere più efficiente applicare l'unicità come vincolo di colonna.

Nell'esempio seguente, viene eseguita una query per convalidare la riga da inserire e un'altra per inserirla effettivamente:

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

Anziché controllare il vincolo univoco in Kotlin o Java, puoi controllarlo in SQL quando definisci la tabella:

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

SQLite esegue la stessa operazione di:

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

Nota: per username viene creata una tabella di indici che utilizza spazio di archiviazione aggiuntivo. Per ulteriori informazioni sull'esecuzione di query su una tabella di indici, consulta Accelerare le query con gli indici.

Ora puoi inserire una riga e lasciare che SQLite controlli il vincolo:

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

Nota: se definisci INTEGER PRIMARY KEY, viene applicato un vincolo univoco a quella colonna e non viene utilizzata una tabella di indici aggiuntiva.

SQLite supporta indici univoci con più colonne:

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

SQLite convalida i vincoli più velocemente e con un overhead inferiore rispetto al codice Kotlin o Java. È una best practice utilizzare SQLite anziché il codice dell'app.

Raggruppa più inserimenti in una singola transazione

Una transazione esegue il commit di più operazioni, il che migliora non solo l'efficienza, ma anche la correttezza. Per migliorare la coerenza dei dati e accelerare il rendimento, puoi raggruppare gli inserimenti:

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

Nota: può verificarsi una sola transazione di scrittura alla volta. Utilizza MoreExecutors.newSequentialExecutor(Executor) per serializzare le scritture.