Prácticas recomendadas para el rendimiento de SQLite

Android ofrece compatibilidad integrada con SQLite, una base de datos SQL eficiente. Sigue estas prácticas recomendadas para optimizar el rendimiento de tu app y asegurarte de que se mantenga rápida y predecible a medida que crezcan tus datos. Si usas estas prácticas recomendadas, también reduces la posibilidad de encontrar problemas de rendimiento que son difíciles de reproducir y solucionar.

Para lograr un rendimiento más rápido, sigue estos principios:

• Lee menos filas y columnas: Optimiza tus consultas para recuperar solo los datos necesarios. Minimiza la cantidad de datos que se leen en la base de datos, ya que la recuperación de datos en exceso puede afectar el rendimiento.

• Envía el trabajo al motor SQLite: Realiza las operaciones de procesamiento, filtrado y ordenamiento dentro de las consultas en SQL. El uso del motor de consultas de SQLite puede mejorar significativamente el rendimiento.

• Modifica el esquema de la base de datos: Diseña el esquema de tu base de datos para ayudar a SQLite a crear planes de consulta y representaciones de datos eficientes. Indexa las tablas correctamente y optimiza sus estructuras para mejorar el rendimiento.

Además, con las herramientas de solución de problemas disponibles, puedes medir el rendimiento de la base de datos SQLite para identificar las áreas que requieren optimización.

Te recomendamos usar la biblioteca Room de Jetpack.

Cómo configurar la base de datos para mejorar el rendimiento

Sigue los pasos de esta sección para configurar tu base de datos y obtener un rendimiento óptimo en SQLite.

Habilita el almacenamiento de registros de escritura anticipada

Para implementar mutaciones, SQLite las adjunta a un registro, que ocasionalmente compacta en la base de datos. Esto se denomina almacenamiento de registros de escritura anticipada (WAL).

Habilita WAL, a menos que uses ATTACH DATABASE.

Disminuye la rigurosidad del modo de sincronización

Cuando usas WAL, de forma predeterminada, cada confirmación emite un objeto fsync para garantizar que los datos lleguen al disco. Esto mejora la durabilidad de los datos, pero ralentiza las confirmaciones.

SQLite tiene la opción de controlar el modo síncrono. Si habilitas WAL, establece el modo síncrono en NORMAL:

db.execSQL("PRAGMA synchronous = NORMAL")

db.execSQL("PRAGMA synchronous = NORMAL");

Con esta configuración, se puede mostrar una confirmación antes de que los datos se almacenen en un disco. Si un dispositivo se apaga, por ejemplo, debido a un corte de energía o un error irrecuperable del kernel, es posible que se pierdan los datos confirmados. Sin embargo, gracias al almacenamiento de registros, la base de datos no se daña.

Si solo tu app falla, tus datos aún llegarán al disco. Para la mayoría de las apps, este parámetro de configuración mejora el rendimiento sin implicar un costo material.

Cómo definir esquemas de tabla eficientes

Para optimizar el rendimiento y minimizar el consumo de datos, define un esquema de tabla eficiente. SQLite construye datos y planes de consultas eficientes, lo que lleva a una recuperación de datos más rápida. En esta sección, se proporcionan prácticas recomendadas para crear esquemas de tablas.

Considera INTEGER PRIMARY KEY

En este ejemplo, define y completa una tabla de la siguiente manera:

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

El resultado de la tabla es el siguiente:

La columna rowid es un índice que conserva el orden de inserción. Las consultas que filtran por rowid se implementan como una búsqueda rápida del árbol B, pero las consultas que filtran por id implican un análisis lento de la tabla.

Si planeas realizar búsquedas por id, puedes evitar almacenar la columna rowid para lograr tener menos datos en el almacenamiento y una base de datos generalmente más rápida:

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

Ahora, la tabla se ve de la siguiente manera:

Dado que no necesitas almacenar la columna rowid, las consultas por id son rápidas. Ten en cuenta que la tabla ahora se ordena según el id, en lugar de hacerlo según el orden de inserción.

Acelera las consultas con índices

SQLite usa índices para acelerar las consultas. Cuando se filtra (WHERE), se ordena (ORDER BY) o se agrega (GROUP BY) una columna, si la tabla tiene un índice para la columna, se acelera la consulta.

En el ejemplo anterior, filtrar por city requiere analizar toda la tabla:

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

Si tienes una app con muchas consultas de ciudades, puedes acelerar esas consultas con un índice:

CREATE INDEX city_index ON Customers(city);

Un índice se implementa como una tabla adicional, que se ordena por la columna de índice y se asigna a rowid:

Ten en cuenta que el costo de almacenamiento de la columna city ahora es el doble, ya que está presente en la tabla original y en el índice. Dado que usas el índice, el costo de almacenamiento adicional vale la pena, ya que las consultas son más rápidas. Sin embargo, no mantengas un índice que no utilices, para evitar pagar el costo de almacenamiento sin obtener una ganancia de rendimiento de las consultas.

Crea índices de varias columnas

Si tus consultas combinan varias columnas, puedes crear índices de varias columnas para acelerar al máximo la consulta. También puedes usar un índice en una columna externa y permitir que la búsqueda interna se realice de manera lineal.

Por ejemplo, dada la siguiente consulta:

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

Puedes acelerar la consulta con un índice de varias columnas que esté en el mismo orden que se especifica en la consulta:

CREATE INDEX city_name_index ON Customers(city, name);

Sin embargo, si solo tienes un índice de city, el orden externo se acelerará, mientras que el orden interno requerirá una búsqueda lineal.

Esto también funciona con consultas de prefijo. Por ejemplo, un índice ON Customers (city, name) también acelera el filtrado, el ordenamiento y la agrupación por city, ya que la tabla de índices de un índice de varias columnas se ordena según los índices dados en el orden determinado.

Considera WITHOUT ROWID

De forma predeterminada, SQLite crea una columna rowid para tu tabla, en la que rowid es un INTEGER PRIMARY KEY AUTOINCREMENT implícito. Si ya tienes una columna que es INTEGER PRIMARY KEY, esta se convierte en un alias de rowid.

Para las tablas que tienen una clave primaria distinta de INTEGER o un compuesto de columnas, considera WITHOUT ROWID.

Almacena los datos pequeños como un BLOB y los datos grandes como un archivo

Si deseas asociar datos grandes con una fila, como la miniatura de una imagen o una foto de un contacto, puedes almacenar los datos en una columna BLOB o en un archivo, y, luego, almacenar la ruta de acceso al archivo en la columna.

Los archivos suelen redondearse en incrementos de hasta 4 KB. Para archivos muy pequeños, en los que el error de redondeo es significativo, es más eficiente almacenarlos en la base de datos como un BLOB. SQLite minimiza las llamadas al sistema de archivos y es más rápido que el sistema de archivos subyacente en algunos casos.

Cómo mejorar el rendimiento de las consultas

Sigue estas prácticas recomendadas para mejorar el rendimiento de las consultas en SQLite minimizando los tiempos de respuesta y maximizando la eficiencia del procesamiento.

Lee solo las filas que necesitas

Los filtros te permiten acotar los resultados a través de la especificación de ciertos criterios, como el período, la ubicación o el nombre. Los límites te permiten controlar la cantidad de resultados que ves:

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

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """, null)) {
  while (cursor.moveToNext()) {
    ...
  }
}

Lee solo las columnas que necesitas

Evita seleccionar columnas innecesarias, ya que pueden ralentizar tus consultas y desperdiciar recursos. En cambio, solo selecciona las columnas que se usan.

En el siguiente ejemplo, se seleccionan id, name y 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)
        // ...
    }
}

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

Sin embargo, solo necesitas la columna name:

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

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(0);
    ...
  }
}

Usa DISTINCT para valores únicos

Usar la palabra clave DISTINCT puede mejorar el rendimiento de tus consultas, ya que reduce la cantidad de datos que se deben procesar. Por ejemplo, si quieres mostrar solo los valores únicos de una columna, usa DISTINCT:

db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Only iterate over distinct names in Kotlin
        ...
    }
}

try (Cursor cursor = db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    // Only iterate over distinct names in Java
    ...
  }
}

Usa funciones de agregación siempre que sea posible

Usa funciones de agregación para obtener resultados agregados sin datos de filas. Por ejemplo, el siguiente código verifica si hay al menos una fila que coincida:

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

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

Para recuperar solo la primera fila, puedes usar EXISTS() para mostrar 0 si no existe una fila coincidente y 1 si una o más filas coinciden:

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

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

Usa funciones de agregación de SQLite en el código de tu app:

• COUNT: Cuenta cuántas filas hay en una columna.
• SUM: Suma todos los valores numéricos de una columna.
• MIN o MAX: Determinan el valor más bajo o más alto. Funcionan con columnas numéricas, tipos de DATE y tipos de texto.
• AVG: Encuentra el valor numérico promedio.
• GROUP_CONCAT: Concatena cadenas con un separador opcional.

Usa COUNT() en lugar de Cursor.getCount()

En el siguiente ejemplo, la función Cursor.getCount() lee todas las filas de la base de datos y muestra todos los valores de fila:

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

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

Sin embargo, cuando usas COUNT(), la base de datos solo muestra el recuento:

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

try (Cursor cursor = db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """, null)) {
  cursor.moveToFirst();
  int count = cursor.getInt(0);
  ...
}

Consultas de Nest en lugar de código

SQL es componible y admite subconsultas, uniones y restricciones de claves externas. Puedes usar el resultado de una consulta en otra sin revisar el código de la app. Esto reduce la necesidad de copiar datos de SQLite y permite que el motor de base de datos optimice tu consulta.

En el siguiente ejemplo, puedes ejecutar una consulta para averiguar qué ciudad tiene la mayor cantidad de clientes y, luego, utilizar el resultado en otra consulta para encontrar todos los clientes de esa ciudad:

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

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

Para obtener el resultado en la mitad del tiempo del ejemplo anterior, usa una sola consulta en SQL con sentencias anidadas:

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

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

Comprueba la unicidad en SQL

Si no se debe insertar una fila a menos que el valor de una columna en particular sea único en la tabla, podría ser más eficiente aplicar esa unicidad como una restricción de columna.

En el siguiente ejemplo, se ejecuta una consulta para validar la fila que se insertará y otra para insertarla:

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

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

En lugar de verificar la restricción de unicidad en Kotlin o Java, puedes verificarla en SQL cuando defines la tabla:

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

SQLite hace lo siguiente:

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

Ahora, puedes insertar una fila y permitir que SQLite verifique la restricción:

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

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 admite índices únicos con varias columnas:

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

SQLite valida las restricciones más rápido y con menos sobrecarga que el código Kotlin o Java. Una práctica recomendada consiste en usar SQLite en lugar del código de la app.

Agrupa varias inserciones en una sola transacción

Una transacción confirma varias operaciones, lo que mejora no solo la eficiencia, sino también la precisión. Para mejorar la coherencia de los datos y acelerar el rendimiento, puedes realizar inserciones por lotes:

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

db.beginTransaction();
try {
  for (customer : Customers) {
    db.execSQL(
        "INSERT INTO Customers VALUES (?, ?, ...)",
        new String[] {
          String.valueOf(customer.id),
          customer.name,
          ...
        });
  }
} finally {
  db.endTransaction()
}

Usa herramientas para solucionar problemas

SQLite proporciona las siguientes herramientas de solución de problemas para ayudarte a medir el rendimiento.

Usa el prompt interactivo de SQLite

Ejecuta SQLite en tu máquina para ejecutar consultas y aprender. Las diferentes versiones de la plataforma de Android usan distintas revisiones de SQLite. Para usar el mismo motor que tiene un dispositivo con Android, usa adb shell y ejecuta sqlite3 en tu dispositivo de destino.

Puedes pedirle a SQLite que programe consultas:

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

EXPLAIN QUERY PLAN

Puedes pedirle a SQLite que explique cómo responderá una consulta usando EXPLAIN QUERY PLAN:

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

El ejemplo anterior requiere un análisis completo de la tabla sin un índice para encontrar todos los clientes de París. Esto se denomina complejidad lineal. SQLite necesita leer todas las filas y solo mantener las filas que coincidan con los clientes de París. Para solucionar esto, puedes agregar un índice:

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 usas la shell interactiva, puedes pedirle a SQLite que siempre explique los planes de consulta:

sqlite> .eqp on

Para obtener más información, ve a Planificación de consultas.

SQLite Analyzer

SQLite ofrece la interfaz de línea de comandos (CLI) sqlite3_analyzer para volcar información adicional que se puede usar para solucionar problemas de rendimiento. Visita la página de descarga de SQLite para instalarla.

Puedes usar adb pull para descargar un archivo de base de datos de un dispositivo de destino en tu estación de trabajo para su análisis:

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

SQLite Browser

También puedes instalar la herramienta de GUI SQLite Browser desde la página de descargas de SQLite.

Registro de Android

Android calcula el tiempo de las consultas de SQLite y las registra por ti:

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