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.

Habilita WAL a través del framework en lugar de ejecutar PRAGMA journal_mode=WAL por tu cuenta. Room habilita WAL de forma predeterminada en los dispositivos que no tienen poca RAM. Sin Room, usa una de las siguientes opciones:

• El método enableWriteAheadLogging
• La marca de apertura ENABLE_WRITE_AHEAD_LOGGING
• El método setWriteAheadLoggingEnabled de SQLiteOpenHelper

Cuando el framework sabe que la base de datos usa WAL, abre un grupo de conexiones para que las lecturas se puedan ejecutar de forma simultánea con una escritura y aplica el modo síncrono de WAL que se describe en la siguiente sección. Si ejecutas PRAGMA journal_mode=WAL por tu cuenta, el framework seguirá tratando la base de datos como una base de datos de registro de reversión. Usa una sola conexión y las confirmaciones mantienen el modo síncrono FULL predeterminado.

Disminuye la rigurosidad del modo de sincronización

SQLite tiene la opción de controlar el modo síncrono. En el modo FULL, 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. Con WAL, el modo NORMAL no se sincroniza en cada confirmación y, aun así, protege la base de datos de daños.

Cuando habilitas WAL a través del framework, Android aplica el modo síncrono de WAL predeterminado del dispositivo, que es NORMAL en AOSP para Android 9 y versiones posteriores. Los fabricantes de dispositivos pueden cambiar este valor predeterminado, y las bases de datos que no usan WAL se establecen de forma predeterminada en FULL. Para asegurarte de que tu base de datos use NORMAL, configúrala cuando la abras:

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

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.

Nota: Si tu app tiene varias bases de datos, usa la misma configuración síncrona en todas partes en caso de que haya dependencias de datos entre diferentes bases de datos.

Administra las conexiones y la memoria

Cada conexión de base de datos abierta contiene memoria de SQLite: una caché de páginas, un asignador de búsqueda y una caché de sentencias compiladas. Las páginas de archivos de la base de datos también permanecen en la caché de páginas del kernel. Sigue las prácticas de esta sección para mantener esta memoria bajo control sin ralentizar tus consultas.

Abre cada base de datos una vez

Usa una sola instancia de SQLiteOpenHelper o RoomDatabase por archivo de base de datos para todo el proceso, por ejemplo, convirtiéndola en un singleton. Cada instancia tiene su propio grupo de conexiones, cachés de páginas y cachés de sentencias, y dos instancias que abren el mismo archivo no pueden compartirlos. En una comparativa, consolidar dos ayudantes que abrían el mismo archivo en uno redujo la memoria de caché de la página de SQLite casi a la mitad y redujo la latencia de la consulta p95 a más de la mitad.

Para encontrar instancias duplicadas, busca dumpsys dbinfo para la misma ruta de acceso de la base de datos en más de un encabezado Connection pool for.

Mantén los cambios de esquema fuera de la devolución de llamada abierta

La devolución de llamada onOpen de SQLiteOpenHelper se ejecuta cada vez que se abre la base de datos, al igual que la devolución de llamada onOpen de RoomDatabase.Callback. Coloca sentencias como CREATE TABLE IF NOT EXISTS y CREATE INDEX IF NOT EXISTS en onCreate y onUpgrade, o en migraciones de Room. No llames a setVersion en onOpen, porque SQLiteOpenHelper ya establece la versión después de onCreate y onUpgrade. Incluso cuando el esquema ya existe, estas sentencias se siguen ejecutando en cada apertura, y setVersion escribe en el archivo de la base de datos.

Establece el tamaño de la caché de sentencias preparadas

Android almacena en caché las declaraciones compiladas para cada conexión. La caché contiene 25 sentencias de forma predeterminada, y puedes aumentarla hasta MAX_SQL_CACHE_SIZE (100) con setMaxSqlCacheSize. Si una base de datos ejecuta más de 25 sentencias distintas en sus rutas de acceso activas, la caché se satura y SQLite compila las mismas sentencias una y otra vez.

Primero, parametriza tus consultas. Ningún tamaño de caché puede ayudar a SQL que se compila con la concatenación de cadenas, ya que cada cadena es una sentencia nueva. Luego, verifica los aciertos y errores de caché de instrucciones en POOL STATS en dumpsys meminfo. Por ejemplo, la base de datos phenotype.db en el resultado de ejemplo de esa sección tiene 147,921 aciertos y 89,616 errores, lo que representa una tasa de errores del 38%. Aumentar el tamaño de la caché en una base de datos como esta puede quitar casi todos los errores. Cada sentencia almacenada en caché usa unos pocos kilobytes de memoria.

Para aumentar el tamaño de la caché, anula onConfigure en tu subclase SQLiteOpenHelper:

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

Si usas Room, llama al mismo método en SupportSQLiteDatabase en la devolución de llamada onOpen de RoomDatabase.Callback.

Cierra las bases de datos inactivas, pero mantén abiertas las que están ocupadas

Una base de datos abierta mantiene sus conexiones, cachés y descriptores de archivos, incluso cuando está inactiva. En una comparativa, una base de datos de Room inactiva en modo WAL que permaneció abierta mantuvo alrededor de 678 KB de memoria caché de páginas de SQLite y 5 descriptores de archivos. Si solo usas una base de datos de vez en cuando, por ejemplo, durante una sincronización diaria, ciérrala cuando termines.

Para cerrar las bases de datos inactivas de forma segura, sigue estos lineamientos:

• Room puede cerrar una base de datos inactiva por ti con setAutoCloseTimeout, que es una API experimental. Room vuelve a abrir la base de datos en el siguiente acceso y vuelve a ejecutar la devolución de llamada onOpen, por lo que debes colocar allí cualquier configuración de conexión.
• Evita los métodos setIdleConnectionTimeout de SQLiteOpenHelper y SQLiteDatabase.OpenParams.Builder. Están obsoletos en el nivel de API 29, ya que cierran conexiones sin restablecer la configuración que aplicaste con las declaraciones PRAGMA.
• No cierres una base de datos que consultas cada pocos segundos. Un tiempo de espera más corto que el intervalo entre tus consultas hace que la base de datos se cierre y se vuelva a abrir de forma repetida, lo que descarta su caché de páginas y de instrucciones cada vez, y hace que la siguiente consulta sea más lenta.

Ten en cuenta la memoria de SQLite

La memoria de SQLite puede ser difícil de detectar. La caché de páginas y la caché de instrucciones de SQLite son asignaciones de montón nativas. Sin embargo, Android no habilita la E/S asignada a la memoria de SQLite de forma predeterminada, por lo que SQLite lee los archivos de la base de datos con lecturas de archivos normales, y el contenido de los archivos que almacena en caché el kernel son páginas de archivos sin asignar. No se tienen en cuenta para el PSS de tu app en dumpsys meminfo. En los dispositivos que usan la contabilización de memoria de cgroup v2, el kernel sigue cargando estas páginas en el cgroup de memoria de tu app. Para reducir esta huella oculta, lee menos páginas con índices y lee solo las filas y columnas que necesitas. Cerrar las bases de datos que no necesitas también libera sus cachés de SQLite.

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:

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

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:

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

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:

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

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.

Advertencia: Las tablas que usan WITHOUT ROWID pueden generar penalizaciones de rendimiento si sus claves primarias son grandes. Para obtener más información, consulta los casos de uso.

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

Nota: En Android, considera usar un archivo para cualquier dato que sea múltiplo de 4 KB.

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.

Nota: Muchos de los ejemplos de esta página incluyen una cláusula LIMIT. Esta es una práctica recomendada porque las consultas que podrían mostrar muchas filas tienen implicaciones de rendimiento cuando muestran grandes conjuntos de datos. La excepción a esto son las consultas que muestran implícitamente un conjunto de datos de tamaño limitado. Por ejemplo, la búsqueda en un campo definido como UNIQUE solo puede mostrar una fila o ninguna.

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()) {
        // Process cursor data
    }
}

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)
        // Further processing
    }
}

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)
        // Further processing
    }
}

Parametriza las consultas

La cadena de consulta puede incluir un parámetro que solo se conozca en el entorno de ejecución, como el siguiente:

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

En el código anterior, cada consulta construye una cadena diferente y, por lo tanto, no se beneficia de la caché de sentencias. Cada llamada requiere que SQLite la compile antes de que se pueda ejecutar. En su lugar, puedes reemplazar el argumento id por un parámetro y vincular el valor con 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
        }
    }
}

Ahora, la consulta se puede compilar una vez y almacenarse en caché. La consulta compilada se vuelve a usar entre diferentes invocaciones de getNameById(long).

Precaución: Si el argumento de entrada es algún otro objeto menos restringido que solo un número, la concatenación de cadenas podría generar una vulnerabilidad de seguridad de inyección de SQL. Usa siempre parámetros para las variables o los datos no confiables.

Itera en SQL, no en código

Usa una sola consulta que devuelva todos los resultados segmentados, en lugar de un bucle programático que itere en consultas en SQL para devolver resultados individuales. El bucle programático es alrededor de 1,000 veces más lento que una sola consulta en SQL.

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
        // Process distinct name
    }
}

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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
}

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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
    }
}

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

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)
    // Use count
}

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()) {
                // Process inner cursor data
            }
        }
    }
}

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()) {
        // Process cursor data
    }
}

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

En lugar de verificar la restricción de unicidad en Kotlin, 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);

Nota: Se crea una tabla de índices para username, que usa almacenamiento adicional. Si deseas obtener más información para consultar una tabla de índices, visita Cómo acelerar las consultas con índices.

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

Nota: Si defines INTEGER PRIMARY KEY, se aplica una restricción de unicidad a esa columna y no se usa una tabla de índices adicional.

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. 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, "customerValue")
        )
    }
    db.setTransactionSuccessful()
} finally {
    db.endTransaction()
}

Fuera de una transacción explícita, cada escritura se ejecuta en su propia transacción implícita y paga el costo total de una confirmación, incluido un fsync en modo síncrono de FULL. El procesamiento por lotes paga ese costo una vez, lo que puede hacer que un lote de escrituras sea un orden de magnitud o más rápido.

Nota: Solo puede llevarse a cabo una transacción de escritura a la vez. Usa MoreExecutors.newSequentialExecutor(Executor) para serializar escrituras.

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, consulta 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

En Android 13 y versiones posteriores, también puedes registrar solo las operaciones lentas. Establece un umbral en milisegundos y, luego, reinicia la app para que lea la etiqueta de registro:

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

Las operaciones lentas aparecen en logcat con la etiqueta SQLiteConnection, junto con su duración, SQL y ruta de acceso de la base de datos. Para establecer el umbral de una sola app, usa db.log.slow_query_threshold.<uid>, donde <uid> es el UID de la app.

Seguimiento de Perfetto

Cuando configures Perfetto, agrega la categoría database de atrace para incluir las operaciones de SQLite en el registro. Para ver lo que hace cada subproceso mientras ejecuta SQL, agrega también los eventos del programador:

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 realiza un seguimiento de cada operación de la base de datos como un segmento que lleva el nombre de la operación y su SQL, por ejemplo, executeForCursorWindow SELECT * FROM message WHERE body LIKE ?. El SQL conserva sus marcadores de posición ? y se trunca a 256 caracteres. No se incluyen los argumentos de vinculación. Las operaciones incluyen open, close, prepare, execute y las variantes de executeFor. Una porción prepare no significa que la caché de sentencias haya fallado.

El diseño de la pista depende de la versión de Android:

• En Android 17 y versiones posteriores, cada conexión tiene un seguimiento asíncrono en el proceso de la app llamado SQLiteConnection- seguido de un número de conexión. Las transacciones aparecen como porciones TRANSACTION-EXCLUSIVE, TRANSACTION-IMMEDIATE o TRANSACTION-DEFERRED, con las declaraciones que se ejecutaron en ellas anidadas debajo. El subproceso que ejecuta una operación también obtiene eventos instantáneos START- y END- en su pista de subprocesos, para cada transacción y para cada sentencia fuera de una transacción. Los nombres de estos eventos terminan con el nombre de la pista de conexión, por ejemplo, -SQLiteConnection-0.
• En Android 16 y versiones anteriores, cada sentencia de SQL distinta tiene su propio segmento asíncrono en el proceso de la app, y no se realiza un seguimiento de las transacciones.

Los números de conexión se reinician en 0 para cada base de datos, por lo que las conexiones principales de todas las bases de datos de un proceso comparten la pista SQLiteConnection-0. Si tu app usa más de una base de datos al mismo tiempo, mide las duraciones con los eventos START- y END- en las pistas de subprocesos.

Figura 1: Un análisis completo de la tabla en el subproceso principal en Android 17

Puedes analizar estos segmentos con PerfettoSQL en la IU de Perfetto o con trace_processor. La siguiente consulta encuentra las operaciones de SQLite más frecuentes en cada proceso. Esta consulta funciona en todas las versiones de 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;

En la siguiente consulta, se enumeran las operaciones de SQLite que tardaron más de 1 ms en un subproceso principal en Android 17 y versiones posteriores:

INCLUDE PERFETTO MODULE slices.with_context;

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

Figura 2: Operaciones de SQLite que tardan más de 1 ms en un subproceso principal

La siguiente consulta busca subprocesos que escriben fuera de una transacción explícita, en la que cada escritura paga por su propia confirmación, en Android 17 y versiones posteriores:

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> imprime estadísticas relacionadas con el uso de memoria de la app, incluidos los detalles sobre SQLite en la sección DATABASES. Por ejemplo, esto se tomó del resultado de adb shell dumpsys meminfo com.google.android.gms.persistent en el dispositivo de un desarrollador:

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 y versiones posteriores imprimen las tablas PER CONNECTION STATS y POOL STATS que se muestran en el ejemplo. Android 13 y versiones anteriores solo imprimen la tabla por conexión, con las estadísticas de caché combinadas en una sola columna cache como hits/misses/size.

En PER CONNECTION STATS, encontrarás lo siguiente:

• pgsz: Es el tamaño de una página de la base de datos, en KB.
• dbsz: Es el tamaño de todo el archivo de la base de datos, en KB.
• Lookaside(b): Es la cantidad de ranuras de memoria de búsqueda que usa la conexión. A pesar del (b) en el encabezado, este es un recuento de ranuras, no de bytes.
• cache hits, cache misses y cache size: Son las estadísticas de la caché de sentencias preparadas de la conexión. Estos valores cuentan las sentencias de SQL compiladas, no las páginas de la base de datos.
• Dbname: Es la ruta de acceso al archivo de la base de datos. Las conexiones adicionales a la misma base de datos tienen un número entre paréntesis, como (1). Con WAL, una base de datos puede tener varias conexiones para que las lecturas se puedan ejecutar en paralelo.

En POOL STATS, encontrarás las estadísticas de la caché de sentencias preparadas para cada grupo de conexiones:

• cache hits: SQLite almacena en caché las sentencias preparadas y, cuando ejecuta consultas, intenta reutilizarlas para ahorrar esfuerzo y memoria en la compilación de sentencias de SQL. Es la cantidad de aciertos de caché de sentencias.
• cache misses: Es la cantidad de errores de caché de sentencias.
• cache size: En Android 16 QPR2 y versiones posteriores, es la cantidad de sentencias preparadas en la caché, sumadas en las conexiones inactivas del grupo. En Android 14 a Android 16 QPR1, este valor es la suma de aciertos y errores, y no representa el tamaño de la caché.

Usa estas estadísticas para decidir qué corregir:

• Una tasa de errores alta significa que SQLite compila las mismas declaraciones de forma repetida. Primero, busca SQL que se haya compilado con concatenación de cadenas y parametrizalo. Si tus consultas ya están parametrizadas y el cache size por conexión está en el límite de 25, aumenta el tamaño de la caché.
• Una ruta de acceso de la base de datos que aparece más de una vez sin un sufijo de estilo (1) significa que tu app abrió el mismo archivo dos veces. Abre cada base de datos una vez.
• Una base de datos que tu app usa con poca frecuencia sigue ocupando memoria mientras está abierta. Cierra la app cuando termines.

dumpsys dbinfo

adb shell dumpsys dbinfo <package-name> imprime el estado de cada grupo de conexiones en tu app. Agrega -v para enumerar también las sentencias en la caché de sentencias preparadas de cada conexión. El siguiente resultado de ejemplo es de Android 17, con rutas de acceso y líneas largas acortadas:

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

Busca lo siguiente:

• Configuration: el modo de diario y el modo síncrono del grupo. En Android 13 y versiones posteriores, esta línea muestra los modos que aplicó el framework, incluidos los valores predeterminados de la plataforma. En el ejemplo, events.db usa el registro de reversión con el modo síncrono FULL, por lo que cada confirmación espera una sincronización completa. Esta línea no refleja las sentencias PRAGMA que ejecutas por tu cuenta.
• Max connections: Un valor de 1 para una base de datos que admite operaciones de escritura significa que el framework no considera que la base de datos esté en modo WAL, por lo que las lecturas y escrituras comparten una sola conexión.
• Total execution time, Total statements executed y Average time per statement: Disponibles en Android 13 y versiones posteriores. Úsalas para encontrar tus bases de datos más activas.
• Most recently executed operations: las últimas 20 operaciones en cada conexión, con su duración y SQL. Busca patrones, como la misma consulta que se ejecuta en un bucle o muchas escrituras seguidas fuera de una transacción. En Android 15 QPR1 y versiones posteriores, esta lista también incluye transacciones explícitas.
• totalLongOperations y Operations exceeding 2000ms: Disponibles en Android 15 QPR1 y versiones posteriores. Muestran cuántas operaciones y transacciones tardaron más de 2 segundos y enumeran las últimas 10.
• Acquired connections y Connection waiters: Son las conexiones que están en uso y los subprocesos que esperan una conexión. Los elementos en espera indican contención, como una transacción de escritura larga que bloquea otras escrituras.
• Prepared statement cache: Se imprime con -v. Muchas entradas que solo difieren en los valores literales apuntan a SQL que se compila con la concatenación de cadenas.
• La misma ruta en más de un encabezado Connection pool for significa que tu app abrió el mismo archivo dos veces.

Recursos adicionales

Contenido de vistas

• Prácticas recomendadas para el rendimiento de SQLite (vistas)