Optimización de Índices en SQLite

Introducción

En este laboratorio, aprenderás cómo optimizar el rendimiento de la base de datos SQLite utilizando índices (indexes). Crearás índices de una sola columna para mejorar la velocidad de las consultas (queries), centrándote en la aplicación práctica y el análisis. También aprenderás a analizar los planes de consulta (query plans) y a eliminar índices redundantes.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") sqlite/SQLiteGroup -.-> sqlite/build_index("Create Single Index") sqlite/SQLiteGroup -.-> sqlite/clear_index("Drop Single Index") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") subgraph Lab Skills sqlite/query_where -.-> lab-552552{{"Optimización de Índices en SQLite"}} sqlite/sort_data -.-> lab-552552{{"Optimización de Índices en SQLite"}} sqlite/build_index -.-> lab-552552{{"Optimización de Índices en SQLite"}} sqlite/clear_index -.-> lab-552552{{"Optimización de Índices en SQLite"}} sqlite/verify_table -.-> lab-552552{{"Optimización de Índices en SQLite"}} end

Crear una base de datos y una tabla

En este paso, crearás una base de datos SQLite y una tabla employees (empleados). Luego, insertarás algunos datos de ejemplo en la tabla.

Primero, abre tu terminal en la VM de LabEx. Tu ruta predeterminada es /home/labex/project.

Para crear una base de datos SQLite llamada my_database.db, ejecuta el siguiente comando:

sqlite3 my_database.db

Este comando crea un nuevo archivo de base de datos SQLite llamado my_database.db en tu directorio de proyecto y abre el shell de SQLite.

A continuación, crea la tabla employees con la siguiente estructura:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    department TEXT
);

Esta sentencia SQL crea una tabla llamada employees con cinco columnas: id, first_name (nombre), last_name (apellido), email y department (departamento). La columna id se establece como la clave primaria (primary key), lo que significa que debe contener valores únicos.

Ahora, inserta algunos datos de ejemplo en la tabla employees:

INSERT INTO employees (first_name, last_name, email, department) VALUES
('John', 'Doe', '[email protected]', 'Sales'),
('Jane', 'Smith', '[email protected]', 'Marketing'),
('Robert', 'Jones', '[email protected]', 'Engineering'),
('Emily', 'Brown', '[email protected]', 'Sales'),
('Michael', 'Davis', '[email protected]', 'Marketing');

Esto insertará cinco filas de datos en la tabla employees.

Para verificar que los datos se han insertado correctamente, ejecuta el siguiente comando:

SELECT * FROM employees;

Deberías ver la siguiente salida:

1|John|Doe|[email protected]|Sales
2|Jane|Smith|[email protected]|Marketing
3|Robert|Jones|[email protected]|Engineering
4|Emily|Brown|[email protected]|Sales
5|Michael|Davis|[email protected]|Marketing

Crear un índice

En este paso, crearás un índice (index) en la columna last_name (apellido) de la tabla employees (empleados).

Los índices son tablas de búsqueda especiales que el motor de búsqueda de la base de datos puede utilizar para acelerar la recuperación de datos.

Para crear un índice llamado idx_lastname en la columna last_name, ejecuta el siguiente comando:

CREATE INDEX idx_lastname ON employees (last_name);

Esta sentencia SQL crea un índice llamado idx_lastname en la columna last_name de la tabla employees.

Para verificar que el índice se ha creado, puedes utilizar el siguiente comando:

PRAGMA index_list(employees);

Este comando mostrará una lista de índices en la tabla employees, incluyendo el índice idx_lastname que acabas de crear. Deberías ver una salida similar a esta:

0|idx_lastname|0|c|0

Esta salida confirma que el índice idx_lastname existe en la tabla employees.

Analizar consultas con EXPLAIN QUERY PLAN

En este paso, aprenderás a usar el comando EXPLAIN QUERY PLAN para analizar cómo SQLite ejecuta una consulta (query). Esta es una herramienta poderosa para comprender el rendimiento de las consultas e identificar posibles cuellos de botella (bottlenecks).

Para analizar una consulta, prefíjala con EXPLAIN QUERY PLAN. Por ejemplo, para analizar la siguiente consulta:

SELECT * FROM employees WHERE last_name = 'Smith';

Ejecuta el siguiente comando:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Smith';

La salida se verá así:

QUERY PLAN
`--SEARCH employees USING INDEX idx_lastname (last_name=?)

Esta salida te indica que SQLite está utilizando el índice idx_lastname para encontrar los empleados con el apellido 'Smith'. La palabra clave SEARCH indica que SQLite está utilizando un índice para realizar la búsqueda.

Si el índice no se utilizara, la salida sería diferente. Por ejemplo, si consultas los empleados con el nombre 'John' (y no has creado un índice en la columna first_name (nombre)), la salida sería:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE first_name = 'John';

La salida se verá así:

QUERY PLAN
`--SCAN employees

La palabra clave SCAN indica que SQLite está realizando un escaneo completo de la tabla (full table scan), lo que significa que tiene que examinar cada fila de la tabla para encontrar los empleados con el nombre 'John'. Esto es menos eficiente que usar un índice.

Agregar más datos y analizar la ordenación (Sorting)

Vamos a insertar más datos para que el análisis del plan de consulta (query plan analysis) sea más significativo. Inserta los siguientes datos en la tabla employees:

INSERT INTO employees (first_name, last_name, email, department) VALUES
('Alice', 'Johnson', '[email protected]', 'HR'),
('Bob', 'Williams', '[email protected]', 'Finance'),
('Charlie', 'Brown', '[email protected]', 'IT'),
('David', 'Miller', '[email protected]', 'Sales'),
('Eve', 'Wilson', '[email protected]', 'Marketing'),
('John', 'Taylor', '[email protected]', 'Engineering'),
('Jane', 'Anderson', '[email protected]', 'HR'),
('Robert', 'Thomas', '[email protected]', 'Finance'),
('Emily', 'Jackson', '[email protected]', 'IT'),
('Michael', 'White', '[email protected]', 'Sales');

Ahora, analicemos una consulta más compleja que implica la ordenación. Supongamos que deseas encontrar a todos los empleados del departamento 'Sales' y ordenarlos por apellido. Puedes usar la siguiente consulta:

SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

Analiza el plan de consulta:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

La salida podría verse así:

QUERY PLAN
`--SCAN employees USING INDEX idx_lastname

En este caso, SQLite está realizando un escaneo completo de la tabla (full table scan) y luego ordenando los resultados.

Creemos un índice en la columna department:

CREATE INDEX idx_department ON employees (department);

Ahora, analiza el plan de consulta nuevamente:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

La salida podría cambiar a:

QUERY PLAN
|--SEARCH employees USING INDEX idx_department (department=?)
`--USE TEMP B-TREE FOR ORDER BY

Ahora SQLite está utilizando el índice idx_department para encontrar a los empleados del departamento 'Sales', pero aún necesita ordenar los resultados.

Eliminar índices redundantes (Drop Redundant Indexes)

En este paso, aprenderás a identificar y eliminar índices redundantes en SQLite. Los índices redundantes pueden afectar negativamente el rendimiento de la base de datos al aumentar la sobrecarga (overhead) de las operaciones de escritura sin proporcionar ningún beneficio a las operaciones de lectura.

Creemos un índice en las columnas department y last_name:

CREATE INDEX idx_department_lastname ON employees (department, last_name);

Ahora, enumera todos los índices en la tabla employees:

PRAGMA index_list(employees);

Deberías ver una salida similar a esta:

0|idx_lastname|0|c|0
1|idx_department|0|c|0
2|idx_department_lastname|0|c|0

Ahora, analicemos una consulta que filtra por department y last_name:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' AND last_name = 'Doe';

La salida podría verse así:

QUERY PLAN
`--SEARCH employees USING INDEX idx_department_lastname (department=? AND last_name=?)

Esta salida indica que SQLite está utilizando el índice idx_department_lastname para esta consulta.

Ahora, analicemos una consulta que filtra solo por department:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';

La salida podría verse así:

QUERY PLAN
`--SEARCH employees USING INDEX idx_department (department=?)

Esta salida indica que SQLite está utilizando el índice idx_department para esta consulta.

En este escenario, el índice idx_department_lastname es redundante porque el índice idx_department se puede utilizar para consultas que filtran solo por department. El índice idx_department_lastname solo proporciona un beneficio para las consultas que filtran por department y last_name.

Para eliminar el índice redundante idx_department, puedes usar el comando DROP INDEX:

DROP INDEX idx_department;

Ahora, enumera todos los índices en la tabla employees nuevamente:

PRAGMA index_list(employees);

Deberías ver que el índice idx_department ya no aparece en la lista.

Resumen

En este laboratorio, has aprendido cómo optimizar el rendimiento de la base de datos SQLite utilizando índices (indexes). Has creado índices de una sola columna (single-column indexes) para mejorar la velocidad de las consultas (query speed), has analizado planes de consulta (query plans) utilizando EXPLAIN QUERY PLAN, y has eliminado índices redundantes (redundant indexes). Estas habilidades te ayudarán a construir bases de datos SQLite más eficientes y con mayor capacidad de respuesta.