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 prevedibile man mano che i dati aumentano. Se segui queste best practice, riduci 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 nel 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 costruire rappresentazioni dei dati e piani di query efficienti. Indicizza correttamente le tabelle e ottimizza le strutture delle tabelle per migliorare le prestazioni.
Inoltre, puoi utilizzare gli strumenti per la risoluzione dei problemi disponibili per misurare il rendimento del database SQLite e identificare le aree che richiedono ottimizzazione.
Ti consigliamo di utilizzare la libreria Jetpack Room.
Configurare il database per le prestazioni
Segui i passaggi descritti in questa sezione per configurare il database in modo da ottenere un rendimento ottimale in SQLite.
Attivare il logging Write-Ahead
SQLite implementa le mutazioni aggiungendole a un log, che occasionalmente viene compactato nel database. Questa tecnica è chiamata logging Write-Ahead (WAL).
Attiva
WAL
a meno che tu non stia utilizzando ATTACH
DATABASE
.
Allentare la modalità di sincronizzazione
Quando utilizzi WAL, per impostazione predefinita ogni commit emette un fsync
per contribuire ad assicurare che i dati raggiungano il disco. In questo modo viene migliorata la durabilità dei dati, ma rallentano i commit.
SQLite ha un'opzione per controllare la modalità di accesso simultaneo. Se attivi WAL, imposta la modalità sincrona su NORMAL
:
Kotlin
db.execSQL("PRAGMA synchronous = NORMAL")
Java
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 panico del kernel, i dati committati potrebbero andare persi. Tuttavia, grazie alla registrazione, il database non è stato danneggiato.
Se si arresta in modo anomalo solo l'app, i dati raggiungono comunque il disco. Per la maggior parte delle app, questa impostazione consente di migliorare le prestazioni senza costi significativi.
Definisci schemi di tabelle efficienti
Per ottimizzare le prestazioni e ridurre al minimo il consumo di dati, definisci uno schema di tabella efficiente. SQLite crea dati e piani di query efficienti, il che consente di recuperare i dati più rapidamente. Questa sezione fornisce le best practice per la creazione di schemi di tabella.
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 mantiene l'ordine di inserimento. Le query che
filtrano in base a rowid
sono implementate come ricerca rapida nell'albero B, mentre le query che
filtrano in base a id
sono una scansione della tabella lenta.
Se prevedi di eseguire ricerche in base a id
, puoi evitare di memorizzare la colonna rowid
per ridurre la quantità di dati archiviati 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 | Sevier County, TN |
Poiché non devi memorizzare la colonna rowid
, le query id
sono veloci. Nota
che la tabella ora è ordinata in base a id
anziché all'ordine di inserzione.
Accelerare le query con gli indici
SQLite utilizza
indici
per accelerare le query. Quando filtri (WHERE
), ordini (ORDER BY
) o
aggrega (GROUP BY
) una colonna, se la tabella ha un indice per la colonna, la
query viene accelerata.
Nell'esempio precedente, l'applicazione di un filtro in base a city
richiede l'analisi dell'intera tabella:
SELECT id, name
WHERE city = 'London, England';
Per un'app con molte query sulle città, puoi accelerarle con un indice:
CREATE INDEX city_index ON Customers(city);
Un indice viene implementato come una tabella aggiuntiva, ordinata in base alla colonna dell'indice e mappata a rowid
:
città | rowid |
---|---|
Gary, IN | 2 |
Liverpool, Inghilterra | 1 |
Sevier County, TN | 3 |
Tieni presente che il costo di archiviazione della colonna city
è ora raddoppiato, perché ora è presente sia nella tabella originale sia nell'indice. Poiché utilizzi l'indice, il costo dello spazio di archiviazione aggiuntivo vale il vantaggio di query più rapide.
Tuttavia, non gestire un indice che non utilizzi per evitare di pagare il costo di archiviazione senza alcun miglioramento delle prestazioni delle query.
Creare indici con più colonne
Se le query combinano più colonne, puoi creare indici con più colonne per accelerare completamente la query. Puoi anche utilizzare un indice in 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 velocizzare la query con un indice multicolonna 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 è comunque accelerato, mentre l'ordinamento interno richiede una ricerca lineare.
Questo funziona anche con le richieste di prefisso. Ad esempio, un indiceON Customers (city, name)
accelera anche il filtraggio, l'ordinamento e il raggruppamentocity
, poiché la tabella di indice per un indice a 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 diventa un alias di rowid
.
Per le tabelle che hanno una chiave primaria diversa da INTEGER
o un insieme di colonne, valuta la possibilità di utilizzare WITHOUT
ROWID
.
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 una miniatura di un'immagine o una foto di un contatto, puoi archiviare i dati in una colonna BLOB
o in un file, quindi memorizzare il percorso del file 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 filesystem ed è più veloce del
filesystem sottostante
in alcuni casi.
Migliorare le 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.
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()) { ... } }
Java
try (Cursor cursor = db.rawQuery(""" SELECT name FROM Customers LIMIT 10; """, null)) { while (cursor.moveToNext()) { ... } }
Leggi solo le colonne di cui hai bisogno
Evita di selezionare colonne non necessarie, che possono rallentare le query e sprecare risorse. Seleziona solo le colonne in uso.
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) // ... } }
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); ... } }
Tuttavia, è necessaria solo la colonna name
:
Kotlin
db.rawQuery(""" SELECT name FROM Customers; """.trimIndent(), null ).use { cursor -> while (cursor.moveToNext()) { val name = cursor.getString(0) ... } }
Java
try (Cursor cursor = db.rawQuery(""" SELECT name FROM Customers; """, null)) { while (cursor.moveToNext()) { String name = cursor.getString(0); ... } }
Parametrizza le query con le schede SQL, non con la concatenazione di stringhe
La stringa di query potrebbe includere un parametro noto solo in fase di esecuzione, ad esempio quanto segue:
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 genera una stringa diversa e quindi non beneficia della cache delle istruzioni. Ogni chiamata richiede a SQLite di compilarla prima che possa essere eseguita. In alternativa, puoi sostituire l'argomento id
con un
parametro e
legare il valore con 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 volta e memorizzata nella cache. La query compilata viene riutilizzata tra le diverse invocazioni di getNameById(long)
.
Esegui l'iterazione in SQL, non nel codice
Utilizza una singola query che restituisce tutti i risultati scelti come target, anziché un loop programmatico che esegue l'iterazione sulle query SQL per restituire 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 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 ... } }
Java
try (Cursor cursor = db.rawQuery(""" SELECT DISTINCT name FROM Customers; """, null)) { while (cursor.moveToNext()) { // Only iterate over distinct names in Java ... } }
Utilizza le funzioni di aggregazione ove possibile
Utilizza le funzioni di aggregazione per ottenere 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 ... } else { // No customers from Paris ... }
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 ... } else { // No customers from Paris ... } }
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 ... } else { // No customers from Paris ... } }
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 ... } else { // No customers from Paris ... } }
Utilizza le funzioni aggregate di SQLite nel codice della tua app:
COUNT
: conteggia il numero di righe in una colonna.SUM
: somma tutti i valori numerici di una colonna.MIN
oMAX
: determina il valore minimo o massimo. Funziona per colonne numeriche, tipiDATE
e 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 di riga:
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() }
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(); ... }
Tuttavia, se utilizzi 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) }
Java
try (Cursor cursor = db.rawQuery(""" SELECT COUNT(*) FROM Customers; """, null)) { cursor.moveToFirst(); int count = cursor.getInt(0); ... }
Query Nest anziché codice
SQL è componibile e supporta sottoquery, join e vincoli di chiavi esterne. Puoi utilizzare il risultato di una query in un'altra query senza passare per il codice dell'app. In questo modo, riduci la necessità di copiare i dati da SQLite e consenti al motore del database di ottimizzare la query.
Nell'esempio seguente, puoi eseguire una query per trovare la città con più 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()) { ... } } } }
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()) { ... } } } }
Per ottenere il risultato in metà tempo rispetto all'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()) { ... } }
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()) { ... } }
Verificare l'unicità in SQL
Se una riga non deve essere inserita a meno che un determinato valore di colonna non sia univoco nella tabella, potrebbe essere più efficiente applicare questa unicità come vincolo di colonna.
Nell'esempio seguente, viene eseguita una query per convalidare la riga da inserire e un'altra per l'inserimento effettivo:
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 la limitazione univoca in Kotlin o Java, puoi farlo in SQL quando definisci la tabella:
CREATE TABLE Customers(
id INTEGER PRIMARY KEY,
name TEXT,
username TEXT UNIQUE
);
SQLite esegue la stessa operazione:
CREATE TABLE Customers(...);
CREATE UNIQUE INDEX CustomersUsername ON Customers(username);
Ora puoi inserire una riga e lasciare che SQLite controlli la limitazione:
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 supporta gli indici univoci con più colonne:
CREATE TABLE table(...);
CREATE UNIQUE INDEX unique_table ON table(column1, column2, ...);
SQLite convalida le limitazioni più rapidamente e con meno overhead rispetto al codice Kotlin o Java. È consigliabile utilizzare SQLite anziché il codice dell'app.
Eseguire il batch di più inserzioni 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 il rendimento, puoi eseguire inserimenti collettivi:
Kotlin
db.beginTransaction() try { customers.forEach { customer -> db.execSql( "INSERT INTO Customers VALUES (?, ?, ...)", arrayOf(customer.id.toString(), customer.name, ...) ) } } finally { db.endTransaction() }
Java
db.beginTransaction(); try { for (customer : Customers) { db.execSQL( "INSERT INTO Customers VALUES (?, ?, ...)", new String[] { String.valueOf(customer.id), customer.name, ... }); } } finally { db.endTransaction() }
Utilizzare gli strumenti per la risoluzione dei problemi
SQLite fornisce i seguenti strumenti per la risoluzione dei problemi per aiutarti a misurare le prestazioni.
Utilizzare il prompt interattivo di SQLite
Esegui SQLite sul tuo computer per eseguire query e imparare.
Versioni diverse della piattaforma Android utilizzano revisioni diverse di SQLite. Per utilizzare lo stesso motore di un dispositivo Android, usa adb shell
ed esegui sqlite3
sul dispositivo di destinazione.
Puoi chiedere a SQLite di misurare il tempo delle 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 un'analisi completa della tabella senza un indice per trovare tutti i clienti di Parigi. Questa si chiama complessità lineare. SQLite deve leggere tutte le righe e conservare solo quelle corrispondenti 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 ulteriori informazioni, consulta la sezione Pianificazione delle query.
SQLite Analyzer
SQLite offre l'interfaccia a riga di comando (CLI) sqlite3_analyzer
per eseguire il dump di ulteriori informazioni 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
Browser SQLite
Puoi anche installare lo strumento GUI SQLite Browser nella pagina Download di SQLite.
Log di Android
Android misura i tempi delle query SQLite e li 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
Perfetto tracciamento
Quando configuri Perfetto, puoi aggiungere quanto segue per includere le tracce per le singole query:
data_sources {
config {
name: "linux.ftrace"
ftrace_config {
atrace_categories: "database"
}
}
}
Consigliati per te
- Nota: il testo del link viene visualizzato quando JavaScript è disattivato
- Eseguire benchmark nell'integrazione continua
- Frame bloccati
- Creare e misurare i profili di riferimento senza Macrobenchmark