Best practice per le prestazioni SQLite

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

Per ottenere prestazioni più rapide, segui questi principi:

• 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 sulle prestazioni.

• Esegui il push del 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 notevolmente le prestazioni.

• Modifica lo schema del database: progetta lo schema del database per aiutare SQLite a creare piani di query e rappresentazioni dei dati efficienti. Indicizza correttamente le tabelle e ottimizza le strutture delle tabelle per migliorare le prestazioni.

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

Ti consigliamo di utilizzare la libreria Jetpack Room.

Configura il database per le prestazioni

Segui i passaggi descritti in questa sezione per configurare il database in modo da ottenere prestazioni ottimali in SQLite.

Abilita il Write-Ahead Logging

SQLite implementa le mutazioni aggiungendole a un log, che occasionalmente compatta nel database. Questo processo è chiamato Write-Ahead Logging (WAL).

Attiva WAL, a meno che tu non stia utilizzando ATTACH DATABASE.

Attiva WAL tramite il framework anziché eseguire PRAGMA journal_mode=WAL autonomamente. Room attiva WAL per impostazione predefinita sui dispositivi che non hanno poca RAM. Senza Room, utilizza una delle seguenti opzioni:

• Il metodo enableWriteAheadLogging
• Il flag open ENABLE_WRITE_AHEAD_LOGGING
• Il metodo setWriteAheadLoggingEnabled di SQLiteOpenHelper

Quando il framework sa che il database utilizza WAL, apre un pool di connessioni in modo che le letture possano essere eseguite contemporaneamente a una scrittura e applica la modalità sincrona WAL descritta nella sezione successiva. Se esegui PRAGMA journal_mode=WAL autonomamente, il framework tratta comunque il database come un database di journal di rollback. Utilizza una singola connessione e i commit mantengono la modalità sincrona FULL predefinita.

Allenta la modalità di sincronizzazione

SQLite offre un'opzione per controllare la modalità sincrona. In modalità FULL, ogni commit genera un fsync per garantire che i dati raggiungano il disco. In questo modo, la durabilità dei dati migliora, ma i commit rallentano. Con WAL, la modalità NORMAL non si sincronizza a ogni commit e protegge comunque il database dal danneggiamento.

Quando attivi WAL tramite il framework, Android applica la modalità sincrona WAL predefinita del dispositivo, ovvero NORMAL in AOSP per Android 9 e versioni successive. I produttori di dispositivi possono modificare questa impostazione predefinita e i database che non utilizzano WAL utilizzano per impostazione predefinita FULL. Per assicurarti che il database utilizzi NORMAL, impostalo quando apri il database:

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

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 interruzione dell'alimentazione o di un kernel panic, i dati di cui è stato eseguito il commit potrebbero andare persi. Tuttavia, grazie al logging, il database non è danneggiato.

Se si arresta in modo anomalo solo l'app, i dati raggiungono comunque il disco. Per la maggior parte delle app, questa impostazione produce miglioramenti delle prestazioni senza costi significativi.

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

Gestisci connessioni e memoria

Ogni connessione al database aperta occupa memoria SQLite: una cache di pagine, un allocatore lookaside e una cache di istruzioni compilate. Anche le pagine dei file di database rimangono nella cache delle pagine del kernel. Segui le pratiche descritte in questa sezione per tenere sotto controllo questa memoria senza rallentare le query.

Apri ogni database una volta

Utilizza una singola istanza SQLiteOpenHelper o RoomDatabase per file di database per l'intero processo, ad esempio rendendola un singleton. Ogni istanza ha il proprio pool di connessioni, le proprie cache di pagine e le proprie cache di istruzioni e due istanze che aprono lo stesso file non possono condividerle. In un benchmark, il consolidamento di due helper che aprivano lo stesso file in uno ha ridotto la memoria della cache delle pagine SQLite di quasi la metà e la latenza delle query p95 di oltre la metà.

Per trovare istanze duplicate, controlla dumpsys dbinfo per lo stesso percorso del database in più di un'intestazione Connection pool for.

Mantieni le modifiche allo schema al di fuori del callback open

Il callback onOpen di SQLiteOpenHelper viene eseguito ogni volta che il database viene aperto, così come il callback onOpen di RoomDatabase.Callback. Inserisci istruzioni come CREATE TABLE IF NOT EXISTS e CREATE INDEX IF NOT EXISTS in onCreate e onUpgrade o nelle migrazioni di Room. Non chiamare setVersion in onOpen, perché SQLiteOpenHelper imposta già la versione dopo onCreate e onUpgrade. Anche se lo schema esiste già, queste istruzioni vengono comunque eseguite a ogni apertura e setVersion scrive nel file del database.

Dimensiona la cache delle istruzioni preparate

Android memorizza nella cache le istruzioni compilate per ogni connessione. La cache contiene 25 istruzioni per impostazione predefinita e puoi aumentarla fino a MAX_SQL_CACHE_SIZE (100) con setMaxSqlCacheSize. Se un database esegue più di 25 istruzioni distinte nei suoi percorsi caldi, la cache si sovraccarica e SQLite compila le stesse istruzioni più volte.

Innanzitutto, parametrizza le query. Nessuna dimensione della cache può aiutare l'SQL creato con la concatenazione di stringhe, perché ogni stringa è una nuova istruzione. Poi controlla i successi e gli errori della cache delle istruzioni in POOL STATS in dumpsys meminfo. Ad esempio, il database phenotype.db nell'output di esempio di questa sezione ha 147.921 hit e 89.616 fallimenti, ovvero una percentuale di fallimenti del 38%. Aumentando le dimensioni della cache su un database come questo, è possibile eliminare quasi tutti i mancati riscontri. Ogni istruzione memorizzata nella cache utilizza alcuni kilobyte di memoria.

Per aumentare le dimensioni della cache, esegui l'override di onConfigure nella sottoclasse SQLiteOpenHelper:

override fun onConfigure(db: SQLiteDatabase) {
    db.setMaxSqlCacheSize(SQLiteDatabase.MAX_SQL_CACHE_SIZE)
}

Se utilizzi Room, chiama lo stesso metodo su SupportSQLiteDatabase nel callback onOpen di RoomDatabase.Callback.

Chiudi i database inattivi, ma tieni aperti quelli occupati

Un database aperto mantiene le connessioni, le cache e i descrittori dei file anche quando è inattivo. In un benchmark, un database Room inattivo in modalità WAL che è rimasto aperto ha occupato circa 678 KB di memoria cache della pagina SQLite e 5 descrittori del file. Se utilizzi un database solo occasionalmente, ad esempio durante una sincronizzazione giornaliera, chiudilo quando hai finito.

Per chiudere in sicurezza i database inattivi, segui queste linee guida:

• Room può chiudere un database inattivo per te con setAutoCloseTimeout, che è un'API sperimentale. Room riapre il database al successivo accesso ed esegue nuovamente il callback onOpen, quindi inserisci qui qualsiasi configurazione di connessione.
• Evita i metodi setIdleConnectionTimeout di SQLiteOpenHelper e SQLiteDatabase.OpenParams.Builder. Sono deprecati nel livello API 29 perché chiudono le connessioni senza ripristinare le impostazioni applicate con le istruzioni PRAGMA.
• Non chiudere un database su cui esegui query ogni pochi secondi. Un timeout più breve dell'intervallo tra le query comporta la chiusura e la riapertura ripetute del database, il che comporta l'eliminazione della cache delle pagine e della cache delle istruzioni ogni volta e rallenta la query successiva.

Considera la memoria SQLite

La memoria SQLite può essere difficile da individuare. La cache delle pagine e la cache delle istruzioni SQLite sono allocazioni heap native. Tuttavia, Android non attiva l'I/O mappato in memoria di SQLite per impostazione predefinita, quindi SQLite legge i file di database con letture di file normali e i contenuti dei file memorizzati nella cache del kernel sono pagine di file non mappate. Non vengono conteggiate ai fini del PSS della tua app in dumpsys meminfo. Sui dispositivi che utilizzano il monitoraggio della memoria cgroup v2, il kernel addebita comunque queste pagine al cgroup della memoria della tua app. Per ridurre questo footprint nascosto, leggi meno pagine utilizzando gli indici e leggendo solo le righe e le colonne necessarie. La chiusura dei database che non ti servono libera anche le relative cache SQLite.

Definisci schemi di tabella efficienti

Per ottimizzare le prestazioni e ridurre al minimo il consumo di dati, definisci uno schema di tabella efficiente. SQLite crea piani di query e dati efficienti, il che consente un recupero dei dati più rapido. Questa sezione fornisce le best practice per la creazione degli schemi delle tabelle.

Prendi in considerazione INTEGER PRIMARY KEY

Per questo esempio, definisci e compila una tabella come segue:

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

L'output della tabella è il seguente:

rowid	id	nome	città
1	456	John Lennon	Liverpool, Inghilterra
2	123	Michael Jackson	Gary, IN
3	789	Dolly Parton	Contea di Sevier, TN

La colonna rowid è un indice che conserva l'ordine di inserimento. Le query che filtrano in base a rowid vengono implementate come una ricerca rapida con albero B, mentre le query che filtrano in base a id sono una scansione lenta della tabella.

Se prevedi di eseguire ricerche per id, puoi evitare di archiviare la colonna rowid per ridurre i dati nello spazio di archiviazione e avere un database complessivamente più veloce:

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

La tabella ora ha il seguente aspetto:

id	nome	città
123	Michael Jackson	Gary, IN
456	John Lennon	Liverpool, Inghilterra
789	Dolly Parton	Contea di Sevier, TN

Poiché non è necessario archiviare la colonna rowid, le query id sono veloci. Tieni presente che la tabella è ora ordinata in base a id anziché all'ordine di inserzione.

Accelera le query con gli indici

SQLite utilizza gli indici per accelerare le query. Quando filtri (WHERE), ordini (ORDER BY) o aggreghi (GROUP BY) una colonna, se la tabella ha un indice per la colonna, la query viene accelerata.

Nell'esempio precedente, il filtro in base a city richiede la scansione dell'intera tabella:

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

Per un'app con molte query sulle città, puoi accelerare queste query con un indice:

CREATE INDEX city_index ON Customers(city);

Un indice viene implementato come tabella aggiuntiva, ordinata in base alla colonna dell'indice e mappata a rowid:

città	rowid
Gary, IN	2
Liverpool, Inghilterra	1
Contea di Sevier, TN	3

Tieni presente che il costo di archiviazione della colonna city è ora raddoppiato, perché è presente sia nella tabella originale sia nell'indice. Poiché utilizzi l'indice, il costo dello spazio di archiviazione aggiuntivo è compensato dal vantaggio di query più rapide. Tuttavia, non mantenere un indice che non utilizzi per evitare di pagare il costo di archiviazione senza ottenere un miglioramento delle prestazioni delle query.

Crea indici su più colonne

Se le query combinano più colonne, puoi creare indici su più colonne per accelerare completamente la query. Puoi anche utilizzare un indice su una colonna esterna e lasciare che la ricerca interna venga eseguita come scansione lineare.

Ad esempio, data la seguente query:

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

Puoi accelerare la query con un indice su più colonne nello stesso ordine specificato nella query:

CREATE INDEX city_name_index ON Customers(city, name);

Tuttavia, se hai un indice solo su city, l'ordinamento esterno viene comunque accelerato, mentre l'ordinamento interno richiede una scansione lineare.

Questo funziona anche con le richieste di prefisso. Ad esempio, un indice ON Customers (city, name) accelera anche il filtro, l'ordinamento e il raggruppamento per city, poiché la tabella dell'indice per un indice su più colonne è ordinata in base agli indici specificati nell'ordine specificato.

Prendi in considerazione WITHOUT ROWID

Per impostazione predefinita, SQLite crea una colonna rowid per la tabella, dove rowid è un INTEGER PRIMARY KEY AUTOINCREMENT implicito. Se hai già una colonna INTEGER PRIMARY KEY, questa colonna diventa un alias di rowid.

Per le tabelle che hanno una chiave primaria diversa da INTEGER o una combinazione di colonne, prendi in considerazione WITHOUT ROWID.

Avviso: le tabelle che utilizzano WITHOUT ROWID possono comportare penalità in termini di prestazioni se le loro chiavi primarie sono di grandi dimensioni. Per saperne di più, consulta i casi d'uso.

Archivia i dati di piccole dimensioni come BLOB e quelli di grandi dimensioni come file

Se vuoi associare dati di grandi dimensioni a una riga, ad esempio la miniatura di un'immagine o una foto per un contatto, puoi archiviare i dati in una colonna BLOB o in un file, quindi archiviare il percorso nella colonna.

I file vengono generalmente arrotondati per eccesso a incrementi di 4 kB. Per i file molto piccoli, in cui l'errore di arrotondamento è significativo, è più efficiente archiviarli nel database come BLOB. SQLite riduce al minimo le chiamate al file system e, in alcuni casi, è più veloce del file system sottostante.

Nota: su Android, valuta la possibilità di utilizzare un file per tutti i dati che sono multipli di 4 kB.

Miglioramento delle prestazioni delle query

Segui queste best practice per migliorare le prestazioni 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 buona pratica perché le query che potrebbero restituire molte righe hanno implicazioni sulle prestazioni quando restituiscono set di dati di grandi dimensioni. L'unica eccezione riguarda le query che restituiscono implicitamente un set di dati con dimensioni limitate. Ad esempio, la ricerca in un campo definito come UNIQUE può restituire solo zero o una riga.

Leggi solo le righe necessarie

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

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

Leggi solo le colonne necessarie

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

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

Tuttavia, è necessaria solo la colonna name:

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

Parametrizza le query

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

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

Nel codice precedente, ogni query crea una stringa diversa e, di conseguenza, non sfrutta la cache delle istruzioni. Ogni chiamata richiede che SQLite la compili prima di poterla eseguire. Puoi invece sostituire l'argomento id con un parametro e associare il valore a selectionArgs:

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

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

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

Esegui l'iterazione in SQL, non nel codice

Utilizza una singola query che restituisca tutti i risultati mirati, anziché un ciclo programmatico che esegue l'iterazione sulle query SQL per restituire i singoli risultati. Il loop programmatico è circa 1000 volte più lento di una singola query SQL.

Utilizza DISTINCT per i valori univoci

L'utilizzo della parola chiave DISTINCT può migliorare le prestazioni delle query riducendo la quantità di dati da elaborare. Ad esempio, se vuoi restituire solo i valori univoci di una colonna, utilizza DISTINCT:

db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Only iterate over distinct names in Kotlin
        // 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:

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

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:

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

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

• COUNT: conta il numero di righe in una colonna.
• SUM: somma tutti i valori numerici di 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 funzione Cursor.getCount() legge tutte le righe del database e restituisce tutti i valori delle righe:

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

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

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

Query nidificate anziché 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 attraverso il codice dell'app. In questo modo si riduce la necessità di copiare i dati da SQLite e si consente al motore del database di 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à:

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

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

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

Verifica l'unicità in SQL

Se una riga non deve essere inserita a meno che un particolare valore di colonna non sia univoco nella tabella, potrebbe essere più efficiente applicare questa univocità come vincolo di colonna.

Nell'esempio seguente, viene eseguita una query per convalidare la riga da inserire e un'altra per l'inserimento effettivo:

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

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

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

SQLite fa lo stesso di quanto segue:

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

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

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

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

Nota: se definisci INTEGER PRIMARY KEY, a quella colonna viene applicato un vincolo univoco e non viene utilizzata una tabella di indice 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. È buona norma utilizzare SQLite anziché il codice dell'app.

Raggruppa più inserimenti in un'unica 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 le prestazioni, puoi raggruppare gli inserimenti:

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

Al di fuori di una transazione esplicita, ogni scrittura viene eseguita nella propria transazione implicita e paga l'intero costo di un commit, incluso un fsync in modalità sincrona FULL. Il raggruppamento in batch paga questo costo una sola volta, il che può rendere un batch di scritture più veloce di un ordine di grandezza o più.

Nota: è possibile eseguire una sola transazione di scrittura alla volta. Utilizza MoreExecutors.newSequentialExecutor(Executor) per serializzare le scritture.

Utilizza gli strumenti per la risoluzione dei problemi

SQLite fornisce i seguenti strumenti di risoluzione dei problemi per aiutarti a misurare le prestazioni.

Utilizza il prompt interattivo di SQLite

Esegui SQLite sulla tua macchina per eseguire query e imparare. Le diverse versioni della piattaforma Android utilizzano revisioni diverse di SQLite. Per utilizzare lo stesso motore di un dispositivo Android, utilizza adb shell ed esegui sqlite3 sul dispositivo di destinazione.

Puoi chiedere a SQLite di cronometrare le query:

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

EXPLAIN QUERY PLAN

Puoi chiedere a SQLite di spiegare come intende rispondere a una query utilizzando EXPLAIN QUERY PLAN:

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

L'esempio precedente richiede una scansione completa della tabella senza un indice per trovare tutti i clienti di Parigi. Questa viene chiamata complessità lineare. SQLite deve leggere tutte le righe e conservare solo quelle che corrispondono ai clienti di Parigi. Per risolvere il problema, puoi aggiungere un indice:

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=?

Se utilizzi la shell interattiva, puoi chiedere a SQLite di spiegare sempre i piani di query:

sqlite> .eqp on

Per saperne di più, consulta Pianificazione delle query.

SQLite Analyzer

SQLite offre l'interfaccia a riga di comando (CLI) sqlite3_analyzer per eseguire il dump di informazioni aggiuntive che possono essere utilizzate per risolvere i problemi di prestazioni. Per l'installazione, visita la pagina di download di SQLite.

Puoi utilizzare adb pull per scaricare un file di database da un dispositivo di destinazione alla tua workstation per l'analisi:

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

SQLite Browser

Puoi anche installare lo strumento GUI SQLite Browser nella pagina di download di SQLite.

Logging Android

Android cronometra le query SQLite e le registra per te:

# Enable query time logging
$ adb shell setprop log.tag.SQLiteTime VERBOSE
# Disable query time logging
$ adb shell setprop log.tag.SQLiteTime ERROR

In Android 13 e versioni successive, puoi anche registrare solo le operazioni lente. Imposta una soglia in millisecondi, quindi riavvia l'app in modo che legga il tag di log:

$ adb shell setprop log.tag.SQLiteSlowQueries VERBOSE
$ adb shell setprop db.log.slow_query_threshold 100
$ adb shell am force-stop <package-name>

Le operazioni lente vengono visualizzate in logcat sotto il tag SQLiteConnection con la loro durata, l'SQL e il percorso del database. Per impostare la soglia per una singola app, utilizza db.log.slow_query_threshold.<uid>, dove <uid> è l'UID dell'app.

Tracciamento Perfetto

Quando configuri Perfetto, aggiungi la categoria atrace database per includere le operazioni SQLite nella traccia. Per vedere cosa sta facendo ogni thread durante l'esecuzione di SQL, aggiungi anche gli eventi dello scheduler:

data_sources {
  config {
    name: "linux.ftrace"
    ftrace_config {
      ftrace_events: "sched/sched_switch"
      ftrace_events: "sched/sched_waking"
      ftrace_events: "sched/sched_blocked_reason"
      atrace_categories: "database"
    }
  }
}

Android traccia ogni operazione del database come una sezione denominata in base all'operazione e al relativo SQL, ad esempio executeForCursorWindow SELECT * FROM message WHERE body LIKE ?. L'SQL mantiene i segnaposto ? e viene troncato a 256 caratteri. Gli argomenti di associazione non sono inclusi. Le operazioni includono open, close, prepare, execute e le varianti executeFor. Una sezione prepare non significa che la cache delle istruzioni ha avuto un fallimento.

Il layout del canale dipende dalla versione di Android:

• In Android 17 e versioni successive, ogni connessione ha una traccia asincrona nel processo dell'app denominata SQLiteConnection- seguita da un numero di connessione. Le transazioni vengono visualizzate come sezioni TRANSACTION-EXCLUSIVE, TRANSACTION-IMMEDIATE o TRANSACTION-DEFERRED, con le istruzioni eseguite al loro interno nidificate sotto. Il thread che esegue un'operazione riceve anche eventi istantanei START- e END- sulla traccia del thread, per ogni transazione e per ogni istruzione al di fuori di una transazione. I nomi di questi eventi terminano con il nome del canale di connessione, ad esempio -SQLiteConnection-0.
• In Android 16 e versioni precedenti, ogni istruzione SQL distinta ha la propria traccia asincrona nel processo dell'app e le transazioni non vengono tracciate.

I numeri delle connessioni ricominciano da 0 per ogni database, quindi le connessioni principali di tutti i database di un processo condividono la traccia SQLiteConnection-0. Se la tua app utilizza più di un database contemporaneamente, misura le durate con gli eventi START- e END- sulle tracce dei thread.

Figura 1. Una scansione completa della tabella sul thread principale in Android 17.

Puoi analizzare queste sezioni con PerfettoSQL nell'interfaccia utente di Perfetto o con trace_processor. La seguente query trova le operazioni SQLite più frequenti in ogni processo. Questa query funziona su tutte le versioni di Android:

INCLUDE PERFETTO MODULE slices.with_context;

SELECT process_name, name AS operation, COUNT(*) AS count
FROM process_slice
WHERE name GLOB 'open *' OR name GLOB 'close *'
  OR name GLOB 'prepare *' OR name GLOB 'execute*'
  OR name GLOB 'TRANSACTION-*'
GROUP BY process_name, name
ORDER BY count DESC
LIMIT 10;

La seguente query elenca le operazioni SQLite che hanno richiesto più di 1 ms su un thread principale in Android 17 e versioni successive:

INCLUDE PERFETTO MODULE slices.with_context;

WITH marks AS (
  SELECT process_name, utid, is_main_thread, ts, name,
    LEAD(ts) OVER (PARTITION BY utid ORDER BY ts) AS end_ts,
    LEAD(name) OVER (PARTITION BY utid ORDER BY ts) AS end_name
  FROM thread_slice
  WHERE name GLOB 'START-*' OR name GLOB 'END-*'
)
SELECT process_name, ts, ROUND((end_ts - ts) / 1e6, 1) AS dur_ms,
  substr(name, 7) AS operation
FROM marks
WHERE name GLOB 'START-*'
  AND end_name = 'END-' || substr(name, 7)
  AND is_main_thread
  AND end_ts - ts > 1e6
ORDER BY dur_ms DESC
LIMIT 10;

Figura 2. Operazioni SQLite più lunghe di 1 ms su un thread principale.

La seguente query trova i thread che scrivono al di fuori di una transazione esplicita, dove ogni scrittura paga per il proprio commit, in Android 17 e versioni successive:

INCLUDE PERFETTO MODULE slices.with_context;

WITH ops AS (
  SELECT process_name, thread_name, utid, name,
    upper(ltrim(substr(name, instr(name, ' ') + 1))) AS sql
  FROM thread_slice
  WHERE name GLOB 'START-*'
)
SELECT process_name, thread_name,
  SUM(name GLOB 'START-TRANSACTION-*') AS transactions,
  SUM(name GLOB 'START-execute*' AND (sql GLOB 'INSERT *'
    OR sql GLOB 'UPDATE *' OR sql GLOB 'DELETE *' OR sql GLOB 'REPLACE *'))
    AS autocommit_writes
FROM ops
GROUP BY utid
HAVING autocommit_writes > 0
ORDER BY autocommit_writes DESC
LIMIT 10;

dumpsys meminfo

adb shell dumpsys meminfo <package-name> stampa le statistiche relative alla memoria utilizzata dall'app, inclusi i dettagli su SQLite nella sezione DATABASES. Ad esempio, questo è stato preso dall'output di adb shell dumpsys meminfo com.google.android.gms.persistent sul dispositivo di uno sviluppatore:

DATABASES
      pgsz     dbsz   Lookaside(b) cache hits cache misses cache size  Dbname
PER CONNECTION STATS
         4       52             45     8    41     6  /data/user/10/com.google.android.gms/databases/gaia-discovery
         4        8                    0     0     0    (attached) temp
         4       52             56     5    23     6  /data/user/10/com.google.android.gms/databases/gaia-discovery (1)
         4      252             95   233   124    12  /data/user_de/10/com.google.android.gms/databases/phenotype.db
         4        8                    0     0     0    (attached) temp
         4      252             17     0    17     1  /data/user_de/10/com.google.android.gms/databases/phenotype.db (1)
         4     9280            105 103169 69805    25  /data/user/10/com.google.android.gms/databases/phenotype.db
         4       20                    0     0     0    (attached) temp
         4     9280            108 13877  6394    25  /data/user/10/com.google.android.gms/databases/phenotype.db (2)
         4        8                    0     0     0    (attached) temp
         4     9280            105 12548  5519    25  /data/user/10/com.google.android.gms/databases/phenotype.db (3)
         4        8                    0     0     0    (attached) temp
         4     9280            107 18328  7886    25  /data/user/10/com.google.android.gms/databases/phenotype.db (1)
         4        8                    0     0     0    (attached) temp
         4       36             51   156    29     5  /data/user/10/com.google.android.gms/databases/mobstore_gc_db_v0
         4       36             97    47    27    10  /data/user/10/com.google.android.gms/databases/context_feature_default.db
         4       36             56     3    16     4  /data/user/10/com.google.android.gms/databases/context_feature_default.db (2)
         4      300             40  2111    24     5  /data/user/10/com.google.android.gms/databases/gservices.db
         4      300             39     3    17     4  /data/user/10/com.google.android.gms/databases/gservices.db (1)
         4       20             17     0    14     1  /data/user/10/com.google.android.gms/databases/gms.notifications.db
         4       20             33     1    15     2  /data/user/10/com.google.android.gms/databases/gms.notifications.db (1)
         4      120             40   143   163     4  /data/user/10/com.google.android.gms/databases/android_pay
         4      120            123    86    32    19  /data/user/10/com.google.android.gms/databases/android_pay (1)
         4       28             33     4    17     3  /data/user/10/com.google.android.gms/databases/googlesettings.db
POOL STATS
     cache hits  cache misses    cache size  Dbname
             13            68            81  /data/user/10/com.google.android.gms/databases/gaia-discovery
            233           145           378  /data/user_de/10/com.google.android.gms/databases/phenotype.db
         147921         89616        237537  /data/user/10/com.google.android.gms/databases/phenotype.db
            156            30           186  /data/user/10/com.google.android.gms/databases/mobstore_gc_db_v0
             50            57           107  /data/user/10/com.google.android.gms/databases/context_feature_default.db
           2114            43          2157  /data/user/10/com.google.android.gms/databases/gservices.db
              1            31            32  /data/user/10/com.google.android.gms/databases/gms.notifications.db
            229           197           426  /data/user/10/com.google.android.gms/databases/android_pay
              4            18            22  /data/user/10/com.google.android.gms/databases/googlesettings.db

Android 14 e versioni successive stampano le tabelle PER CONNECTION STATS e POOL STATS mostrate nell'esempio. Android 13 e versioni precedenti stampano solo la tabella per connessione, con le statistiche della cache combinate in una singola colonna cache come hits/misses/size.

In PER CONNECTION STATS, troverai:

• pgsz: le dimensioni di una pagina del database, in KB.
• dbsz: le dimensioni dell'intero file di database, in kB.
• Lookaside(b): il numero di slot di memoria lookaside utilizzati dalla connessione. Nonostante la presenza di (b) nell'intestazione, si tratta di un conteggio di slot, non di byte.
• cache hits, cache misses e cache size: statistiche per la cache delle istruzioni preparate della connessione. Questi valori conteggiano le istruzioni SQL compilate, non le pagine del database.
• Dbname: il percorso del file del database. Alle connessioni aggiuntive allo stesso database viene aggiunto un numero tra parentesi, ad esempio (1). Con WAL, un database può avere più connessioni in modo che le letture possano essere eseguite in parallelo.

In POOL STATS, troverai le statistiche della cache delle istruzioni preparate per ogni pool di connessioni:

• cache hits: SQLite memorizza nella cache le istruzioni preparate e tenta di riutilizzarle durante l'esecuzione delle query, per risparmiare tempo e memoria durante la compilazione delle istruzioni SQL. Questo è il numero di successi della cache delle istruzioni.
• cache misses: il numero di fallimenti della cache delle istruzioni.
• cache size: in Android 16 QPR2 e versioni successive, il numero di istruzioni preparate nella cache, sommate alle connessioni inattive del pool. In Android 14 fino ad Android 16 QPR1, questo valore è la somma di hit e miss e non rappresenta le dimensioni della cache.

Utilizza queste statistiche per decidere cosa correggere:

• Un tasso di mancati riscontri elevato significa che SQLite compila ripetutamente le stesse istruzioni. Innanzitutto, cerca l'SQL creato con la concatenazione di stringhe e parametrizzalo. Se le query sono già parametrizzate e il valore cache size per connessione è al limite di 25, aumenta le dimensioni della cache.
• Un percorso del database che appare più di una volta senza un suffisso in stile (1) significa che l'app ha aperto lo stesso file due volte. Apri ogni database una volta.
• Un database che la tua app utilizza raramente occupa comunque memoria mentre è aperto. Chiudilo quando hai finito.

dumpsys dbinfo

adb shell dumpsys dbinfo <package-name> stampa lo stato di ogni pool di connessioni nella tua app. Aggiungi -v per elencare anche le istruzioni nella cache delle istruzioni preparate di ogni connessione. L'output di esempio seguente proviene da Android 17, con percorsi e righe lunghe abbreviati:

** Database info for pid 2705 [com.example.app] **
Connection pool for /data/user/0/com.example.app/databases/settings.db:
  Open: true
  Max connections: 4
  Total execution time (ms): 4963
  Total statements executed: 231239
  Average time per statement (ms): 0
  Configuration: openFlags=805306368, ..., journalMode=WAL, syncMode=NORMAL
  IsReadOnlyDatabase: false
  Available primary connection:
    <none>
  Available non-primary connections:
    <none>
  Acquired connections:
    <none>
  Connection waiters:
    <none>
Connection pool for /data/user/0/com.example.app/databases/events.db:
  Open: true
  Max connections: 1
  Total execution time (ms): 842
  Total statements executed: 840
  Average time per statement (ms): 1
  Configuration: openFlags=268435456, ..., journalMode=TRUNCATE, syncMode=FULL
  IsReadOnlyDatabase: false
  Available primary connection:
    Connection #0:
      isPrimaryConnection: true
      onlyAllowReadOnlyOperations: false
      totalLongOperations: 0
      Most recently executed operations:
        0: [10-26 04:22:32.133] executeForChangedRowCount took 0ms -
           succeeded, sql="DELETE FROM params WHERE app_id=?", path=...
        1: [10-26 04:22:32.133] prepare took 0ms - succeeded,
           sql="DELETE FROM params WHERE app_id=?", path=...
        ...

Cerca quanto segue:

• Configuration: la modalità di journaling e la modalità sincrona del pool. In Android 13 e versioni successive, questa riga mostra le modalità applicate dal framework, incluse le impostazioni predefinite della piattaforma. Nell'esempio, events.db utilizza il journal di rollback con la modalità sincrona FULL, quindi ogni commit attende una sincronizzazione completa. Questa riga non riflette le istruzioni PRAGMA eseguite da te.
• Max connections: un valore pari a 1 per un database scrivibile indica che il framework non considera il database in modalità WAL, pertanto le letture e le scritture condividono una singola connessione.
• Total execution time, Total statements executed e Average time per statement: disponibili in Android 13 e versioni successive. Utilizzale per trovare i database più attivi.
• Most recently executed operations: le ultime 20 operazioni su ogni connessione, con la relativa durata e l'istruzione SQL. Cerca pattern come la stessa query eseguita in un loop o molte scritture di fila al di fuori di una transazione. In Android 15 QPR1 e versioni successive, questo elenco include anche le transazioni esplicite.
• totalLongOperations e Operations exceeding 2000ms: disponibili in Android 15 QPR1 e versioni successive. Mostrano il numero di operazioni e transazioni che hanno richiesto più di 2 secondi ed elencano le ultime 10.
• Acquired connections e Connection waiters: connessioni in uso e thread in attesa di una connessione. I waiter indicano una contesa, ad esempio una transazione di scrittura lunga che blocca altre scritture.
• Prepared statement cache: stampato con -v. Molte voci che differiscono solo per i valori letterali indicano un codice SQL creato con la concatenazione di stringhe.
• Lo stesso percorso sotto più di un'intestazione Connection pool for indica che l'app ha aperto lo stesso file due volte.

Risorse aggiuntive

Visualizza contenuti

• Best practice per le prestazioni di SQLite (viste)