Bonnes pratiques pour les performances de SQLite

Android est compatible avec SQLite, une base de données SQL particulièrement efficace. Suivez ces bonnes pratiques pour optimiser les performances de votre application afin qu'elle reste rapide à long terme tandis que votre volume de données augmente. En appliquant ces bonnes pratiques, vous limiterez également le risque de rencontrer des problèmes de performances difficiles à reproduire et à résoudre.

Pour des performances plus rapides, suivez ces principes :

• Lire moins de lignes et de colonnes : optimisez vos requêtes pour ne récupérer que les données nécessaires. Réduisez la quantité de données lues à partir de la base de données, car une récupération excessive de données peut affecter les performances.

• Transférer les tâches vers le moteur SQLite : effectuez les opérations de calcul, de filtrage et de tri dans des requêtes SQL. L'utilisation du moteur de requêtes de SQLite peut améliorer considérablement les performances.

• Modifier le schéma de la base de données : concevez votre schéma de base de données pour aider SQLite à créer des plans de requête et des représentations de données efficaces. Ajoutez correctement des indices dans les tables et optimisez leur structure pour améliorer les performances.

De plus, vous pouvez utiliser les outils de dépannage disponibles pour mesurer les performances de votre base de données SQLite et identifier les domaines à optimiser.

Nous vous recommandons d'utiliser la bibliothèque Jetpack Room.

Configurer la base de données pour optimiser les performances

Suivez la procédure décrite dans cette section pour configurer votre base de données afin d'optimiser les performances dans SQLite.

Activer la journalisation WAL

SQLite implémente les mutations en les ajoutant à un journal, qu'il affiche occasionnellement dans la base de données. Il s'agit de la journalisation WAL (Write-Ahead Logging).

Activez la journalisation WAL, sauf si vous utilisez ATTACH DATABASE.

Activez la journalisation WAL via le framework au lieu d'exécuter vous-même PRAGMA journal_mode=WAL. Room active WAL par défaut sur les appareils qui ne sont pas des appareils à faible RAM. Sans Room, utilisez l'une des options suivantes :

• La méthode enableWriteAheadLogging
• L'option d'ouverture ENABLE_WRITE_AHEAD_LOGGING
• La méthode setWriteAheadLoggingEnabled de SQLiteOpenHelper

Lorsque le framework sait que la base de données utilise le WAL, il ouvre un pool de connexions afin que les lectures puissent s'exécuter simultanément avec une écriture, et il applique le mode synchrone du WAL décrit dans la section suivante. Si vous exécutez vous-même PRAGMA journal_mode=WAL, le framework traite toujours la base de données comme une base de données de journal de rollback. Il utilise une seule connexion et les commits conservent le mode synchrone FULL par défaut.

Assouplir le mode de synchronisation

SQLite propose une option pour contrôler le mode synchrone. En mode FULL, chaque commit émet un fsync pour garantir que les données atteignent le disque. Cela améliore la durabilité des données, mais ralentit les commits. Avec la journalisation WAL, le mode NORMAL ne se synchronise pas à chaque commit et protège toujours la base de données contre la corruption.

Lorsque vous activez WAL via le framework, Android applique le mode synchrone WAL par défaut de l'appareil, qui est NORMAL dans AOSP pour Android 9 et versions ultérieures. Les fabricants d'appareils peuvent modifier cette valeur par défaut, et les bases de données qui n'utilisent pas WAL sont définies par défaut sur FULL. Pour vous assurer que votre base de données utilise NORMAL, définissez-le lorsque vous ouvrez la base de données :

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

Avec ce paramètre, un commit peut être renvoyé avant que les données ne soient stockées sur un disque. En cas d'arrêt d'un appareil, par exemple en cas de panne de courant ou de panique du noyau, les données validées peuvent être perdues. Cependant, en raison de la journalisation, votre base de données n'est pas corrompue.

Si seule votre application plante, vos données atteindront toujours le disque. Pour la plupart des applications, ce paramètre permet d'améliorer les performances sans frais matériels.

Remarque : Si votre application comporte plusieurs bases de données, utilisez le même paramètre synchrone partout où il existe des dépendances de données entre différentes bases de données.

Gérer les connexions et la mémoire

Chaque connexion à une base de données ouverte contient de la mémoire SQLite : un cache de pages, un allocateur lookaside et un cache d'instructions compilées. Les pages du fichier de base de données restent également dans le cache de pages du noyau. Suivez les pratiques de cette section pour contrôler cette mémoire sans ralentir vos requêtes.

Ouvrir chaque base de données une fois

Utilisez une seule instance SQLiteOpenHelper ou RoomDatabase par fichier de base de données pour l'ensemble de votre processus, par exemple en la définissant comme singleton. Chaque instance possède son propre pool de connexions, ses propres caches de pages et ses propres caches d'instructions. Deux instances qui ouvrent le même fichier ne peuvent pas les partager. Dans un benchmark, la consolidation de deux assistants qui ouvraient le même fichier en un seul a permis de réduire de près de moitié la mémoire cache de la page SQLite et de plus de moitié la latence des requêtes p95.

Pour trouver des instances en double, recherchez dumpsys dbinfo pour le même chemin d'accès à la base de données sous plusieurs en-têtes Connection pool for.

Éviter les modifications de schéma dans le rappel d'ouverture

Le rappel onOpen de SQLiteOpenHelper s'exécute chaque fois que la base de données s'ouvre, tout comme le rappel onOpen de RoomDatabase.Callback. Placez les instructions telles que CREATE TABLE IF NOT EXISTS et CREATE INDEX IF NOT EXISTS dans onCreate et onUpgrade, ou dans les migrations Room. N'appelez pas setVersion dans onOpen, car SQLiteOpenHelper définit déjà la version après onCreate et onUpgrade. Même lorsque le schéma existe déjà, ces instructions s'exécutent toujours à chaque ouverture, et setVersion écrit dans le fichier de base de données.

Dimensionner le cache d'instructions préparées

Android met en cache les instructions compilées pour chaque connexion. Le cache contient 25 instructions par défaut, et vous pouvez l'augmenter jusqu'à MAX_SQL_CACHE_SIZE (100) avec setMaxSqlCacheSize. Si une base de données exécute plus de 25 instructions distinctes dans ses chemins d'accès à chaud, le cache est saturé et SQLite compile les mêmes instructions à plusieurs reprises.

Tout d'abord, paramétrez vos requêtes. Aucune taille de cache ne peut aider le code SQL créé avec la concaténation de chaînes, car chaque chaîne est une nouvelle instruction. Vérifiez ensuite les succès et les défauts de cache d'instructions sous POOL STATS dans dumpsys meminfo. Par exemple, la base de données phenotype.db de l'exemple de résultat de cette section comporte 147 921 accès et 89 616 défauts, soit un taux de défaut de 38 %. Augmenter la taille du cache sur une base de données comme celle-ci peut éliminer presque tous les échecs. Chaque instruction mise en cache utilise quelques kilo-octets de mémoire.

Pour augmenter la taille du cache, remplacez onConfigure dans votre sous-classe SQLiteOpenHelper :

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

Si vous utilisez Room, appelez la même méthode sur SupportSQLiteDatabase dans le rappel onOpen de RoomDatabase.Callback.

Fermer les bases de données inactives, mais laisser les bases de données actives ouvertes

Une base de données ouverte conserve ses connexions, ses caches et ses descripteurs de fichiers, même lorsqu'elle est inactive. Dans un benchmark, une base de données Room inactive en mode WAL qui est restée ouverte contenait environ 678 Ko de mémoire cache de page SQLite et cinq descripteurs de fichiers. Si vous n'utilisez une base de données qu'occasionnellement, par exemple lors d'une synchronisation quotidienne, fermez-la lorsque vous avez terminé.

Pour fermer les bases de données inactives de manière sécurisée, suivez ces consignes :

• Room peut fermer une base de données inactive pour vous avec setAutoCloseTimeout, qui est une API expérimentale. Room rouvre la base de données lors du prochain accès et exécute à nouveau votre rappel onOpen. Vous devez donc y placer toute configuration de connexion.
• Évitez les méthodes setIdleConnectionTimeout de SQLiteOpenHelper et SQLiteDatabase.OpenParams.Builder. Elles sont obsolètes au niveau d'API 29, car elles ferment les connexions sans restaurer les paramètres que vous avez appliqués avec les instructions PRAGMA.
• Ne fermez pas une base de données que vous interrogez toutes les quelques secondes. Un délai avant expiration plus court que l'intervalle entre vos requêtes entraîne la fermeture et la réouverture répétées de la base de données, ce qui supprime son cache de pages et son cache d'instructions à chaque fois et ralentit la requête suivante.

Prendre en compte la mémoire SQLite

La mémoire SQLite peut être difficile à repérer. Le cache de pages et le cache d'instructions SQLite sont des allocations de segments de mémoire natifs. Toutefois, Android n'active pas les E/S mappées en mémoire de SQLite par défaut. SQLite lit donc les fichiers de base de données avec des lectures de fichiers standards, et le contenu des fichiers mis en cache par le noyau correspond à des pages de fichiers non mappées. Elles ne sont pas comptabilisées dans la taille de l'ensemble proportionnel (PSS) de votre application dans dumpsys meminfo. Sur les appareils qui utilisent la comptabilisation de la mémoire cgroup v2, le noyau impute toujours ces pages au cgroup de mémoire de votre application. Pour réduire cette empreinte cachée, lisez moins de pages en utilisant des index et en ne lisant que les lignes et les colonnes dont vous avez besoin. La fermeture des bases de données dont vous n'avez pas besoin libère également leurs caches SQLite.

Définir des schémas de table efficaces

Pour optimiser les performances et minimiser la consommation de données, définissez un schéma de table efficace. SQLite construit des plans de requête et des données efficaces, ce qui accélère la récupération des données. Cette section fournit les bonnes pratiques à suivre pour créer des schémas de table.

Envisager INTEGER PRIMARY KEY

Pour cet exemple, définissez et remplissez une table comme suit :

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

Le résultat de cette table se présente comme suit :

rowid	id	name	city
1	456	John Lennon	Liverpool, Angleterre
2	123	Michael Jackson	Gary, IN
3	789	Dolly Parton	Sevier County, TN

La colonne rowid est un indice qui conserve l'ordre d'insertion. Les requêtes filtrées par rowid sont implémentées en tant que recherches B-tree rapides, tandis que celles qui sont filtrées par id impliquent une analyse lente du tableau.

Si vous prévoyez d'effectuer des recherches par id, vous pouvez éviter de stocker la colonne rowid pour avoir moins de données en stockage et une base de données globalement plus rapide :

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

Votre table se présente maintenant comme suit :

id	name	city
123	Michael Jackson	Gary, IN
456	John Lennon	Liverpool, Angleterre
789	Dolly Parton	Sevier County, TN

Étant donné qu'il n'est pas nécessaire de stocker la colonne rowid, les requêtes id sont rapides. Notez que la table est désormais triée en fonction de id au lieu de l'ordre d'insertion.

Accélérer les requêtes avec les indices

SQLite utilise des indices pour accélérer les requêtes. Lors du filtrage (WHERE), du tri (ORDER BY) ou de l'agrégation (GROUP BY) d'une colonne, la requête est accélérée si la table possède un indice pour la colonne.

Dans l'exemple précédent, le filtrage par city nécessite d'analyser la table entière :

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

Pour une application comportant de nombreuses requêtes de ville, vous pouvez accélérer ces requêtes avec un indice :

CREATE INDEX city_index ON Customers(city);

Un indice est implémenté en tant que table supplémentaire, triée en fonction de la colonne d'indice et mappée à rowid :

city	rowid
Gary, IN	2
Liverpool, Angleterre	1
Sevier County, TN	3

Notez que le coût de stockage de la colonne city est maintenant double, car elle est désormais présente à la fois dans la table d'origine et dans l'indice. Étant donné que vous utilisez l'indice, le coût de stockage supplémentaire est justifié par l'accélération des requêtes. Cependant, ne conservez pas un indice que vous n'utilisez pas. Vous éviterez ainsi de payer les coûts de stockage sans profiter d'une amélioration des performances des requêtes.

Créer des indices multicolonnes

Si vos requêtes combinent plusieurs colonnes, vous pouvez créer des index multicolonnes pour accélérer entièrement les requêtes. Vous pouvez également utiliser un indice au niveau d'une colonne extérieure et laisser la recherche interne effectuer une analyse linéaire.

Par exemple, prenons la requête suivante :

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

Vous pouvez accélérer la requête avec un indice multicolonne dans le même ordre que celui spécifié dans la requête :

CREATE INDEX city_name_index ON Customers(city, name);

Toutefois, si vous n'avez qu'un indice au niveau de city, l'ordre extérieur est toujours accéléré, tandis que l'ordre intérieur nécessite une analyse linéaire.

Cela fonctionne également avec les requêtes avec préfixe. Par exemple, un indice ON Customers (city, name) accélère également le filtrage, l'ordre et le regroupement par city, car la table d'un indice multicolonne est triée en fonction des indices indiqués dans l'ordre donné.

Envisager WITHOUT ROWID

Par défaut, SQLite crée une colonne rowid pour votre table, où rowid est un INTEGER PRIMARY KEY AUTOINCREMENT implicite. Si l'une de vos colonnes est déjà un INTEGER PRIMARY KEY, elle deviendra un alias de rowid.

Pour les tables ayant une clé primaire autre qu'INTEGER ou un composite de colonnes, envisagez d'utiliser WITHOUT ROWID.

Avertissement : Les tables qui utilisent WITHOUT ROWID peuvent nuire aux performances si leurs clés primaires sont volumineuses. Pour en savoir plus, consultez les cas d'utilisation.

Stocker les données de petite envergure en tant que BLOB et les données volumineuses sous forme de fichier

Si vous souhaitez associer des données volumineuses à une ligne, comme la vignette d'une image ou la photo d'un contact, vous pouvez stocker ces données dans une colonne BLOB ou dans un fichier. Ensuite, stockez le chemin d'accès au fichier dans la colonne.

Les fichiers sont généralement arrondis par incréments de 4 Ko. Pour les très petits fichiers où l'erreur d'arrondi est significative, il est plus efficace de les stocker dans la base de données en tant que BLOB. SQLite minimise les appels au système de fichiers et est, dans certains cas, plus rapide que le système de fichiers sous-jacent.

Remarque : Sous Android, envisagez d'utiliser un fichier pour les données représentant plusieurs multiples de 4 Ko.

Améliorer les performances des requêtes

Suivez ces bonnes pratiques pour améliorer les performances des requêtes dans SQLite en réduisant les temps de réponse et en optimisant l'efficacité du traitement.

Remarque : De nombreux exemples sur cette page incluent une clause LIMIT. Son utilisation est conseillée, car les requêtes susceptibles de renvoyer de nombreuses lignes affectent les performances lors du renvoi de grands ensembles de données. Les requêtes qui renvoient implicitement un ensemble de données soumis à une contrainte de taille constituent toutefois une exception à cette règle. Par exemple, la recherche sur un champ défini comme UNIQUE ne peut renvoyer qu'une seule ligne ou aucune.

Lire uniquement les lignes dont vous avez besoin

Les filtres vous permettent d'affiner vos résultats en spécifiant certains critères, tels que la plage de dates, le lieu ou le nom. Les limites vous permettent de contrôler le nombre de résultats affichés :

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

Lire uniquement les colonnes dont vous avez besoin

Évitez de sélectionner des colonnes inutiles, car cela peut ralentir les requêtes et gaspiller des ressources. Sélectionnez uniquement les colonnes qui sont utilisées.

Dans l'exemple suivant, vous sélectionnez id, name et 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
    }
}

Cependant, vous n'avez besoin que de la colonne name :

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

Paramétrer des requêtes

Votre chaîne de requête peut inclure un paramètre qui n'est connu qu'au moment de l'exécution, comme suit :

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

Dans le code précédent, chaque requête construit une chaîne différente et ne bénéficie donc pas du cache d'instructions. Chaque appel nécessite que SQLite le compile avant de pouvoir l'exécuter. Vous pouvez plutôt remplacer l'argument id par un paramètre et lier la valeur à 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
        }
    }
}

La requête peut désormais être compilée une fois et mise en cache. La requête compilée est réutilisée entre différentes appels de getNameById(long).

Attention : Si l'argument d'entrée est un autre objet moins contraint qu'un simple nombre, la concaténation de chaînes peut entraîner une faille de sécurité d'injection SQL. Utilisez toujours des paramètres pour les variables ou les données non fiables.

Itérer en SQL, pas dans le code

Utilisez une seule requête qui renvoie tous les résultats ciblés, au lieu d'une boucle programmatique qui effectue une itération sur les requêtes SQL pour renvoyer des résultats individuels. La boucle programmatique est environ 1 000 fois plus lente qu'une seule requête SQL.

Utiliser DISTINCT pour les valeurs uniques

L'utilisation du mot clé DISTINCT contribue à améliorer les performances des requêtes en réduisant la quantité de données à traiter. Par exemple, si vous souhaitez ne renvoyer que les valeurs uniques d'une colonne, utilisez 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
    }
}

Utiliser des fonctions d'agrégation autant que possible

Utilisez des fonctions d'agrégation pour obtenir des résultats agrégés sans données de ligne. Par exemple, le code suivant vérifie s'il existe au moins une ligne correspondante :

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

Pour récupérer uniquement la première ligne, vous pouvez utiliser EXISTS() afin de renvoyer 0 si aucune ligne ne correspond et 1 si une ou plusieurs lignes correspondent :

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

Utilisez des fonctions d'agrégation SQLite dans le code de votre application :

• COUNT : comptabilise le nombre de lignes dans une colonne.
• SUM : ajoute toutes les valeurs numériques d'une colonne.
• MIN ou MAX : détermine la valeur la plus faible ou la plus élevée. Fonctionne pour les colonnes numériques, les types DATE et les types de texte.
• AVG : détermine la valeur numérique moyenne.
• GROUP_CONCAT : concatène des chaînes avec un séparateur facultatif.

Utiliser COUNT() à la place de Cursor.getCount()

Dans l'exemple suivant, la fonction Cursor.getCount() lit toutes les lignes de la base de données et renvoie toutes les valeurs de ligne :

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

Cependant, si vous utilisez COUNT(), la base de données ne renvoie que le nombre de lignes :

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

Imbriquer des requêtes au lieu de code

SQL est composable et est compatible avec les sous-requêtes, les jointures et les contraintes de clé étrangère. Vous pouvez inclure le résultat d'une seule requête dans une autre sans avoir à passer par le code de l'application. Cette approche vous évite d'avoir à copier des données à partir de SQLite et permet au moteur de base de données d'optimiser la requête.

Dans l'exemple suivant, vous pouvez exécuter une requête permettant d'identifier la ville qui compte le plus de clients, puis utiliser ce résultat dans une autre requête pour trouver tous les clients de cette ville :

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

Pour obtenir le résultat deux fois plus rapidement que dans l'exemple précédent, utilisez une seule requête SQL avec des instructions imbriquées :

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

Vérifier les valeurs uniques en SQL

Si une ligne ne doit être insérée que dans le cas où une valeur de colonne particulière est unique dans la table, il peut être plus efficace d'appliquer cette condition en tant que contrainte de colonne.

Dans l'exemple suivant, une seule requête est exécutée pour valider la ligne à insérer et une autre à insérer :

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

Au lieu de vérifier la contrainte de valeur unique en Kotlin, vous pouvez le faire en SQL lorsque vous définissez la table :

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

SQLite effectue les mêmes opérations que :

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

Remarque : Une table d'indices est créée pour username, ce qui utilise de l'espace de stockage supplémentaire. Pour découvrir comment interroger une table d'index, consultez Accélérer les requêtes avec les index.

Vous pouvez maintenant insérer une ligne et laisser SQLite vérifier la contrainte :

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

Remarque : Si vous définissez INTEGER PRIMARY KEY, une contrainte de valeur unique s'applique à cette colonne et n'utilise pas de table d'indices supplémentaire.

SQLite prendre en charge les indices uniques comportant plusieurs colonnes :

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

SQLite valide les contraintes plus rapidement et en impliquant moins de frais que le code Kotlin. Il est recommandé d'utiliser SQLite plutôt que le code de l'application.

Regrouper plusieurs insertions en une seule transaction

Une transaction valide plusieurs opérations, ce qui améliore non seulement l'efficacité, mais aussi l'exactitude. Pour améliorer la cohérence des données et accélérer les performances, vous pouvez regrouper les insertions par lot :

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

En dehors d'une transaction explicite, chaque écriture s'exécute dans sa propre transaction implicite et paie le coût total d'un commit, y compris un fsync en mode synchrone FULL. Le traitement par lot ne paie ce coût qu'une seule fois, ce qui peut accélérer un lot d'écritures d'un ordre de grandeur ou plus.

Remarque : Seule une transaction d'écriture peut avoir lieu à la fois. Utilisez MoreExecutors.newSequentialExecutor(Executor) pour sérialiser les écritures.

Utiliser les outils de dépannage

SQLite fournit les outils de dépannage suivants pour vous aider à mesurer les performances.

Utiliser l'invite interactive de SQLite

Exécutez SQLite sur votre ordinateur pour exécuter des requêtes et apprendre. Les différentes versions de la plate-forme Android utilisent différentes révisions de SQLite. Pour utiliser le même moteur que celui d'un appareil Android, utilisez adb shell et exécutez sqlite3 sur votre appareil cible.

Vous pouvez demander à SQLite de chronométrer des requêtes :

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

EXPLAIN QUERY PLAN

Vous pouvez demander à SQLite d'expliquer comment il a l'intention de répondre à une requête via EXPLAIN QUERY PLAN :

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

L'exemple précédent nécessite une analyse complète de la table sans indice pour trouver tous les clients de Paris. C'est ce qu'on appelle la complexité linéaire. SQLite doit lire toutes les lignes et ne conserver que celles qui correspondent aux clients de Paris. Pour résoudre ce problème, vous pouvez ajouter 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=?

Si vous utilisez l'interface système interactive, vous pouvez demander à SQLite d'expliquer systématiquement les plans de requête :

sqlite> .eqp on

Pour en savoir plus, consultez la page Planification des requêtes.

Analyseur SQLite

SQLite propose l'interface de ligne de commande (CLI) sqlite3_analyzer pour fournir des informations supplémentaires qui peuvent aider à résoudre les problèmes de performances. Pour l'installer, accédez à la page de téléchargement de SQLite.

Vous pouvez utiliser adb pull pour télécharger un fichier de base de données d'un appareil cible vers votre poste de travail à des fins d'analyse :

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

Navigateur SQLite

Vous pouvez également installer l'outil IUG navigateur SQLite sur la page de téléchargements de SQLite.

Journalisation Android

Android interroge les requêtes SQLite et les consigne pour vous :

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

Sur Android 13 ou version ultérieure, vous pouvez également n'enregistrer que les opérations lentes. Définissez un seuil en millisecondes, puis redémarrez votre application pour qu'elle lise le tag de journal :

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

Les opérations lentes s'affichent dans logcat sous le tag SQLiteConnection avec leur durée, leur code SQL et le chemin d'accès à la base de données. Pour définir le seuil d'une seule application, utilisez db.log.slow_query_threshold.<uid>, où <uid> est l'UID de l'application.

Traçage Perfetto

Lorsque vous configurez Perfetto, ajoutez la catégorie atrace database pour inclure les opérations SQLite dans la trace. Pour voir ce que fait chaque thread pendant qu'il exécute du code SQL, ajoutez également les événements du planificateur :

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 trace chaque opération de base de données sous la forme d'une tranche nommée d'après l'opération et son code SQL, par exemple executeForCursorWindow SELECT * FROM message WHERE body LIKE ?. Le code SQL conserve ses espaces réservés ? et est tronqué à 256 caractères. Les arguments de liaison ne sont pas inclus. Les opérations incluent open, close, prepare, execute et les variantes executeFor. Une tranche prepare ne signifie pas que le cache d'instructions a échoué.

La disposition de la piste dépend de la version d'Android :

• Dans Android 17 et versions ultérieures, chaque connexion possède une trace asynchrone dans le processus de l'application, nommée SQLiteConnection- suivie d'un numéro de connexion. Les transactions s'affichent sous forme de segments TRANSACTION-EXCLUSIVE, TRANSACTION-IMMEDIATE ou TRANSACTION-DEFERRED, avec les instructions qui ont été exécutées imbriquées en dessous. Le thread qui exécute une opération reçoit également des événements instantanés START- et END- sur sa piste de thread, pour chaque transaction et pour chaque instruction en dehors d'une transaction. Les noms de ces événements se terminent par le nom de la trace de connexion, par exemple -SQLiteConnection-0.
• Sous Android 16 et versions antérieures, chaque instruction SQL distincte possède sa propre trace asynchrone dans le processus de l'application, et les transactions ne sont pas tracées.

Les numéros de connexion redémarrent à 0 pour chaque base de données. Par conséquent, les connexions principales de toutes les bases de données d'un processus partagent la trace SQLiteConnection-0. Si votre application utilise plusieurs bases de données en même temps, mesurez les durées avec les événements START- et END- sur les pistes de thread.

Figure 1. Une analyse complète de la table sur le thread principal dans Android 17.

Vous pouvez analyser ces segments avec PerfettoSQL dans l'UI Perfetto ou avec trace_processor. La requête suivante recherche les opérations SQLite les plus fréquentes dans chaque processus. Cette requête fonctionne sur toutes les versions d'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 requête suivante liste les opérations SQLite qui ont pris plus de 1 ms sur un thread principal dans Android 17 et versions ultérieures :

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;

Figure 2. Opérations SQLite de plus de 1 ms sur un thread principal.

La requête suivante recherche les threads qui écrivent en dehors d'une transaction explicite, où chaque écriture paie pour son propre commit, sous Android 17 ou version ultérieure :

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> affiche les statistiques liées à l'utilisation de la mémoire par l'application, y compris les détails sur SQLite dans la section DATABASES. Par exemple, l'extrait suivant provient de la sortie de adb shell dumpsys meminfo com.google.android.gms.persistent sur l'appareil d'un développeur :

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 ou version ultérieure affiche les tables PER CONNECTION STATS et POOL STATS présentées dans l'exemple. Android 13 et versions antérieures n'impriment que la table par connexion, avec les statistiques de cache combinées dans une seule colonne cache sous la forme hits/misses/size.

Sous PER CONNECTION STATS, vous trouverez les informations suivantes :

• pgsz : taille d'une page de base de données, en Ko.
• dbsz : taille de l'intégralité du fichier de base de données, en Ko.
• Lookaside(b) : nombre d'emplacements de mémoire lookaside utilisés par la connexion. Malgré la présence de (b) dans l'en-tête, il s'agit d'un nombre d'emplacements, et non d'octets.
• cache hits, cache misses et cache size : statistiques du cache d'instructions préparées de la connexion. Ces valeurs comptabilisent les instructions SQL compilées, et non les pages de base de données.
• Dbname : chemin d'accès au fichier de base de données. Les connexions supplémentaires à la même base de données sont suivies d'un numéro entre parenthèses, par exemple (1). Avec WAL, une base de données peut avoir plusieurs connexions, ce qui permet d'exécuter des lectures en parallèle.

Sous POOL STATS, vous trouverez les statistiques du cache des instructions préparées pour chaque pool de connexions :

• cache hits : SQLite met en cache les instructions préparées et tente de les réutiliser lors de l'exécution de requêtes, afin d'économiser du temps et de la mémoire lors de la compilation des instructions SQL. Il s'agit du nombre de succès de cache d'instructions.
• cache misses : nombre d'échecs de cache d'instructions.
• cache size : dans Android 16 QPR2 et versions ultérieures, nombre d'instructions préparées dans le cache, additionné sur les connexions inactives du pool. Dans Android 14 à Android 16 QPR1, cette valeur correspond à la somme des hits et des miss, et ne représente pas la taille du cache.

Utilisez ces statistiques pour décider des problèmes à corriger :

• Un taux d'échec élevé signifie que SQLite compile les mêmes instructions de manière répétée. Tout d'abord, recherchez le code SQL créé avec la concaténation de chaînes et paramétrez-le. Si vos requêtes sont déjà paramétrées et que la valeur cache size par connexion est à la limite de 25, augmentez la taille du cache.
• Un chemin d'accès à la base de données qui apparaît plusieurs fois sans suffixe de style (1) signifie que votre application a ouvert le même fichier deux fois. Ouvrez chaque base de données une fois.
• Une base de données que votre application utilise rarement occupe toujours de la mémoire lorsqu'elle est ouverte. Fermez-le lorsque vous avez terminé.

dumpsys dbinfo

adb shell dumpsys dbinfo <package-name> affiche l'état de chaque pool de connexions de votre application. Ajoutez -v pour lister également les instructions dans le cache d'instructions préparé de chaque connexion. L'exemple de résultat suivant provient d'Android 17, avec des chemins d'accès et des lignes longues raccourcis :

** 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=...
        ...

Recherchez les éléments suivants :

• Configuration : mode journal et mode synchrone du pool. Sous Android 13 ou version ultérieure, cette ligne indique les modes appliqués par le framework, y compris les valeurs par défaut de la plate-forme. Dans l'exemple, events.db utilise le journal de rollback avec le mode synchrone FULL. Par conséquent, chaque commit attend une synchronisation complète. Cette ligne ne reflète pas les instructions PRAGMA que vous exécutez vous-même.
• Max connections : une valeur de 1 pour une base de données accessible en écriture signifie que le framework ne considère pas que la base de données est en mode WAL. Par conséquent, les lectures et les écritures partagent une seule connexion.
• Total execution time, Total statements executed et Average time per statement : disponibles sur Android 13 et versions ultérieures. Utilisez-les pour identifier vos bases de données les plus actives.
• Most recently executed operations : les 20 dernières opérations sur chaque connexion, avec leur durée et leur code SQL. Recherchez des schémas tels que la même requête exécutée en boucle ou de nombreuses écritures consécutives en dehors d'une transaction. Dans Android 15 QPR1 et versions ultérieures, cette liste inclut également les transactions explicites.
• totalLongOperations et Operations exceeding 2000ms : disponibles dans Android 15 QPR1 et versions ultérieures. Ils indiquent le nombre d'opérations et de transactions qui ont pris plus de deux secondes, et listent les 10 dernières.
• Acquired connections et Connection waiters : connexions en cours d'utilisation et threads en attente d'une connexion. Les waiters indiquent un conflit, par exemple une transaction d'écriture longue qui bloque d'autres écritures.
• Prepared statement cache : imprimé avec -v. De nombreuses entrées qui ne diffèrent que par des valeurs littérales pointent vers du code SQL créé avec une concaténation de chaînes.
• Si le même chemin d'accès apparaît sous plusieurs en-têtes Connection pool for, cela signifie que votre application a ouvert le même fichier deux fois.

Ressources supplémentaires

Contenu des vues

• Bonnes pratiques pour les performances de SQLite (vues)