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.

Assouplir le mode de synchronisation

Lorsque vous utilisez la journalisation WAL, 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.

SQLite propose une option pour contrôler le mode synchrone. Si vous activez la journalisation WAL, définissez le mode synchrone sur NORMAL :

Kotlin

db.execSQL("PRAGMA synchronous = NORMAL")

Java

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.

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

Prenons l'exemple de 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.

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.

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.

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 :

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

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 :

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

Cependant, vous n'avez besoin que de la colonne 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);
    ...
  }
}

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 :

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

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 :

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

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 :

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

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 :

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

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

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

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 :

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

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 :

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

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 :

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

Au lieu de vérifier la contrainte de valeur unique en Kotlin ou Java, 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);

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

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 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 ou Java. 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 :

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

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 permettant de 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
```### Perfetto tracing

### Perfetto tracing {:#perfetto-tracing}

When [configuring Perfetto](https://perfetto.dev/docs/concepts/config), you may
add the following to include tracks for individual queries:

```protobuf
data_sources {
  config {
    name: "linux.ftrace"
    ftrace_config {
      atrace_categories: "database"
    }
  }
}