Optimización de PRAGMA en SQLite

Introducción

En este laboratorio, explorará el ajuste de SQLite PRAGMA para optimizar el rendimiento y la fiabilidad de la base de datos. Aprenderá a configurar aspectos clave del comportamiento de SQLite utilizando sentencias PRAGMA. Cubriremos la configuración del modo de diario (journal mode), la habilitación de las comprobaciones de clave externa (foreign key checks), la realización de comprobaciones de integridad (integrity checks) y el ajuste del tamaño de la caché (cache size).

Skills Graph

Crear una base de datos y configurar el modo de diario (Journal Mode)

En este paso, creará una base de datos SQLite y configurará su modo de diario (journal mode). El modo de diario controla cómo SQLite gestiona las transacciones y garantiza la integridad de los datos.

Primero, abra su terminal en la VM de LabEx. Su ruta predeterminada es /home/labex/project.

Cree una base de datos SQLite llamada test.db e ingrese al shell de SQLite utilizando el siguiente comando:

sqlite3 test.db

Este comando crea el archivo de base de datos test.db (si no existe) y abre la herramienta de línea de comandos de SQLite. Verá un indicador como este:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Ahora, configuremos el modo de diario. SQLite ofrece varios modos de diario, incluyendo DELETE, TRUNCATE, PERSIST, MEMORY, WAL y OFF. WAL (Write-Ahead Logging - Registro por adelantado) proporciona un buen equilibrio entre rendimiento y fiabilidad.

Ejecute el siguiente comando SQL para establecer el modo de diario en WAL:

PRAGMA journal_mode=WAL;

Este comando configura la base de datos para usar Write-Ahead Logging (WAL). WAL mejora la concurrencia y el rendimiento al escribir los cambios en un archivo WAL separado antes de aplicarlos a la base de datos.

Para verificar el modo de diario, ejecute:

PRAGMA journal_mode;

Resultado esperado:

wal

Esto confirma que el modo de diario está establecido en WAL.

Habilitar el soporte de claves externas (Foreign Key Support)

En este paso, habilitará el soporte de claves externas (foreign key support) en su base de datos SQLite. Las claves externas (foreign keys) imponen relaciones entre tablas y ayudan a mantener la integridad de los datos.

Dentro del shell de SQLite (si salió en el paso anterior, vuelva a conectarse con sqlite3 test.db), ejecute el siguiente comando:

PRAGMA foreign_keys = ON;

Este comando habilita la aplicación de claves externas (foreign key enforcement) para la conexión de base de datos actual. Tenga en cuenta que debe habilitar las claves externas para cada nueva conexión a la base de datos.

Para verificar que el soporte de claves externas esté habilitado, ejecute:

PRAGMA foreign_keys;

Resultado esperado:

1

Esta salida confirma que el soporte de claves externas está habilitado.

Crear tablas con relación de clave externa (Foreign Key Relationship)

Ahora que el soporte de claves externas (foreign key support) está habilitado, creemos dos tablas con una relación de clave externa para demostrar su funcionalidad.

Ejecute los siguientes comandos SQL para crear una tabla users y una tabla orders:

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Estos comandos crean dos tablas:

• users: Almacena información del usuario con las columnas id (clave primaria - primary key) y name.
• orders: Almacena información del pedido con las columnas id (clave primaria), user_id (clave externa - foreign key que hace referencia a users.id) y amount.

La cláusula FOREIGN KEY (user_id) REFERENCES users(id) establece una relación entre la tabla orders y la tabla users. Asegura que el user_id en la tabla orders debe existir en la columna id de la tabla users.

Probar la restricción de clave externa (Foreign Key Constraint)

En este paso, probará la restricción de clave externa (foreign key constraint) para ver cómo evita que se inserten datos no válidos en la base de datos.

Primero, inserte un usuario en la tabla users:

INSERT INTO users (id, name) VALUES (1, 'Alice');

Este comando inserta un nuevo usuario con id 1 y name 'Alice' en la tabla users.

Ahora, intente insertar un pedido en la tabla orders con un user_id que no existe en la tabla users:

INSERT INTO orders (user_id, amount) VALUES (999, 100.0);

Debido a que las comprobaciones de clave externa (foreign key checks) están habilitadas, este comando fallará con un mensaje de error:

Error: FOREIGN KEY constraint failed

Esto demuestra que la restricción de clave externa está funcionando correctamente, impidiendo que cree un pedido para un usuario inexistente.

A continuación, inserte un pedido válido con el user_id existente:

INSERT INTO orders (user_id, amount) VALUES (1, 100.0);

Este comando tendrá éxito porque el user_id 1 existe en la tabla users.

Realizar una comprobación de integridad (Integrity Check)

En este paso, realizará una comprobación de integridad (integrity check) en su base de datos SQLite para asegurarse de que no haya inconsistencias o errores.

Ejecute el siguiente comando dentro del shell de SQLite:

PRAGMA integrity_check;

Este comando verifica toda la base de datos en busca de inconsistencias o errores. Si la base de datos está en buen estado, la salida será ok.

Salida esperada:

ok

Si la base de datos está dañada, la salida contendrá mensajes de error que indican la naturaleza y la ubicación de la corrupción.

Ajustar el tamaño de la caché (Cache Size) y salir

En este paso final, ajustará el tamaño de la caché (cache size) utilizado por SQLite y luego saldrá del shell de SQLite.

El tamaño de la caché determina cuánta memoria utiliza SQLite para almacenar los datos a los que se ha accedido recientemente. Aumentar el tamaño de la caché puede mejorar el rendimiento, especialmente para cargas de trabajo con muchas lecturas (read-heavy workloads).

Ejecute el siguiente comando para establecer el tamaño de la caché en 4 MB (4096 KB):

PRAGMA cache_size = 4096;

Este comando establece el tamaño de la caché en 4 MB para la conexión de base de datos actual.

Para verificar el tamaño de la caché, ejecute:

PRAGMA cache_size;

Salida esperada:

4096

Finalmente, salga del shell de SQLite:

.exit

Este comando cierra la conexión de la base de datos y lo devuelve a la línea de comandos.

Resumen

En este laboratorio, ha explorado la optimización (tuning) de PRAGMA en SQLite. Aprendió a configurar el modo de diario (journal mode) para mejorar el rendimiento y la fiabilidad, habilitar el soporte de claves externas (foreign key support) para garantizar la integridad de los datos, crear tablas con relaciones de claves externas, probar las restricciones de claves externas, realizar comprobaciones de integridad (integrity checks) para identificar la corrupción de la base de datos y ajustar el tamaño de la caché (cache size) para optimizar el uso de la memoria. Estas habilidades proporcionan una base sólida para trabajar con bases de datos SQLite de manera efectiva.