07b · Optimización y buenas prácticas — Ejercicios

§ 01

Resumen rápido

Cuatro ejercicios prácticos que muestran el impacto real de los índices usando EXPLAIN ANALYZE en Sakila. Cada ejercicio parte de una consulta sin optimizar, mide su coste, crea el índice adecuado y vuelve a medir para ver la mejora. Los tiempos reales ilustran por qué los índices son imprescindibles en bases de datos con volumen.

§ 02

Conceptos clave

EXPLAIN ANALYZE

Herramienta de diagnóstico que ejecuta la consulta y devuelve el plan real de ejecución con tiempos medidos. Permite ver cuántas filas escanea MySQL, qué índices usa y cuánto tarda cada paso. Es el equivalente a un profiler o debugger para consultas SQL.

Full table scan

Lectura secuencial de todas las filas de una tabla para encontrar las que cumplen la condición WHERE. Es el comportamiento por defecto cuando no existe ningún índice útil. En tablas con millones de filas, un full scan puede tardar segundos o minutos.

Index scan

Lectura del índice para localizar directamente las filas que cumplen la condición, sin recorrer la tabla entera. Es el resultado de tener un índice adecuado para la consulta. Típicamente entre 10× y 100× más rápido que un full scan en tablas grandes.

Índice compuesto

Índice que cubre más de una columna. MySQL lo aprovecha cuando la consulta filtra por las primeras columnas del índice, en el mismo orden en que fueron declaradas. Es más eficiente que varios índices separados cuando las columnas siempre se usan juntas en las consultas.

MATCH...AGAINST con IN BOOLEAN MODE

Sintaxis para consultas Full Text que permite operadores de inclusión (+), exclusión (-) y prefijo (*). Usa el índice Full Text para resolver la búsqueda en milisegundos, a diferencia de LIKE que fuerza un full scan.

Coste de la consulta (query cost)

Estimación interna de MySQL de cuánto trabajo requiere resolver una consulta, medida en unidades arbitrarias. EXPLAIN ANALYZE lo muestra junto al tiempo real. Un coste alto con LIKE puede convertirse en un coste mínimo tras crear el índice correcto.

§ 03

Contenido de la clase

El entorno de práctica: Sakila y EXPLAIN ANALYZE

Estos ejercicios usan la base de datos Sakila porque tiene más datos que World y permite apreciar mejor las diferencias de rendimiento entre consultas con y sin índice. En bases de datos pequeñas (cientos de filas), la diferencia puede ser imperceptible aunque el índice sea correcto.

El comando EXPLAIN ANALYZE es el hilo conductor de todos los ejercicios. Anteponiéndolo a cualquier consulta, MySQL ejecuta la consulta y devuelve un informe detallado: qué operaciones hizo, cuántas filas procesó y cuánto tardó cada paso. Es la herramienta que convierte la optimización de arte en ciencia.

Ejercicio 1 — B-Tree compuesto: películas por año y duración

La consulta filtra películas del año 2006 con duración entre 120 y 180 minutos. Sin índice, MySQL lee las 1000 filas de la tabla film para encontrar las que cumplen ambas condiciones. El EXPLAIN ANALYZE muestra un full scan con un coste proporcional al número de filas.

Creando un índice B-Tree compuesto sobre (release_year, length), MySQL puede saltar directamente al año 2006 dentro del árbol y luego recorrer solo el rango de duraciones entre 120 y 180. El EXPLAIN ANALYZE posterior muestra el salto al índice y un tiempo notablemente menor.

Ejercicio 2 — B-Tree para búsqueda exacta por email

La búsqueda de un cliente por su email es la operación más frecuente en sistemas de login. Sin índice, MySQL recorre toda la tabla customer buscando la fila cuyo email coincide exactamente. Con EXPLAIN ANALYZE se puede medir el tiempo antes de la optimización (en el entorno de práctica, alrededor de 0.12 segundos).

Aunque el índice ideal para igualdades exactas sería HASH, MySQL 8 con el motor InnoDB no soporta índices HASH en tablas de disco. El índice B-Tree sobre la columna email resuelve el mismo problema con una mejora significativa: el tiempo baja a 0.02 segundos, porque el árbol binario localiza el email exacto en O(log n) pasos en lugar de O(n).

Ejercicio 3 — Full Text: búsqueda semántica en descripciones

Buscar películas cuya descripción mencione ciertas palabras con LIKE fuerza un full scan y no puede aprovecharse de ningún índice estándar. Intentar usar MATCH...AGAINST sin el índice Full Text produce directamente un error de MySQL: "Can't find FULLTEXT index matching the column list".

Creando el índice Full Text sobre la columna description, la misma búsqueda se resuelve en 0.03 segundos. El modo IN BOOLEAN MODE permite combinar palabras con operadores: +drama obliga a que la descripción contenga "drama"; +feminist obliga a "feminist"; -action excluiría las que contengan "action".

El índice Full Text también es más inteligente que LIKE en otro aspecto: ignora automáticamente las palabras muy frecuentes (el, la, un, the, a, of) que no aportan valor discriminativo, y puede manejar variantes morfológicas dependiendo de la configuración.

Ejercicio 4 — Índice compuesto: alquileres activos por cliente

La consulta busca los alquileres activos (return_date IS NULL) de un cliente específico, ordenados por fecha. Sin índice, MySQL recorre toda la tabla rental (que puede tener miles de filas), filtra los del cliente, filtra los no devueltos y luego ordena el resultado. El EXPLAIN ANALYZE muestra un coste de aproximadamente 5 segundos en este entorno.

El índice compuesto (customer_id, rental_date) permite a MySQL saltar directamente a los alquileres de ese cliente y encontrarlos ya ordenados por fecha, sin necesidad de ordenarlos en una segunda pasada. El tiempo baja a 0.04 segundos: una mejora de más de 100 veces.

Normalización en Sakila: un esquema ya optimizado

Sakila ya está normalizada hasta 3FN, lo que la hace un buen ejemplo de referencia. En la tabla actor, first_name y last_name dependen directamente de actor_id (clave primaria). En la tabla category, last_update depende de category_id, no de name. No hay dependencias transitivas ni grupos repetidos.

La normalización en la práctica no es algo que se aplica sobre una base de datos existente: se diseña desde el principio al modelar el esquema. Por eso, la parte más importante de la 3FN no es corregirla en Sakila, sino identificar estas violaciones antes de crear las tablas en proyectos propios.

§ 04

Código y ejemplos

Ejercicio 1: B-Tree compuesto por año y duración

SQL

USE sakila;

-- Paso 1: medir el rendimiento SIN índice
EXPLAIN ANALYZE
SELECT title, release_year, length
FROM film
WHERE release_year = 2006
  AND length BETWEEN 120 AND 180;
-- Resultado: full scan de las 1000 filas de film

-- Paso 2: crear el índice B-Tree compuesto
CREATE INDEX idx_film_year_length ON film (release_year, length);

-- Paso 3: medir el rendimiento CON índice
EXPLAIN ANALYZE
SELECT title, release_year, length
FROM film
WHERE release_year = 2006
  AND length BETWEEN 120 AND 180;
-- Resultado: index scan directo al rango, mucho menos filas procesadas

Compara el campo "rows" en ambas salidas de EXPLAIN ANALYZE: la diferencia muestra cuántas filas dejó de procesar MySQL gracias al índice. En Sakila con 1000 películas la mejora es perceptible; en una tabla de millones de registros sería espectacular.

Ejercicio 2: índice para búsqueda exacta de email

SQL

-- Paso 1: consulta de login SIN índice
EXPLAIN ANALYZE
SELECT *
FROM customer
WHERE email = 'KATHLEEN.ADAMS@sakilacustomer.org';
-- Tiempo aproximado: ~0.12 segundos (full scan de customer)

-- Paso 2: crear el índice (MySQL 8 InnoDB usa B-Tree aunque especifiques HASH)
CREATE INDEX idx_customer_email ON customer (email);

-- Paso 3: misma consulta CON índice
EXPLAIN ANALYZE
SELECT *
FROM customer
WHERE email = 'KATHLEEN.ADAMS@sakilacustomer.org';
-- Tiempo aproximado: ~0.02 segundos (salto directo por índice)

-- Ver todos los índices de customer
SHOW INDEX FROM customer;

La reducción de 0.12 a 0.02 segundos puede parecer pequeña, pero en una aplicación con 1000 logins simultáneos la diferencia entre 0.12s y 0.02s por petición es la diferencia entre un servidor saturado y uno con margen.

Ejercicio 3: Full Text para búsqueda semántica

SQL

-- Sin índice Full Text, MATCH...AGAINST falla con error
-- ERROR: Can't find FULLTEXT index matching the column list
SELECT title FROM film
WHERE MATCH(description) AGAINST('+drama +feminist' IN BOOLEAN MODE);

-- Crear el índice Full Text
CREATE FULLTEXT INDEX idx_film_description ON film (description);

-- Ahora funciona: películas de drama con temática feminista
EXPLAIN ANALYZE
SELECT title, description
FROM film
WHERE MATCH(description) AGAINST('+drama +feminist' IN BOOLEAN MODE);
-- Tiempo: ~0.03 segundos usando el índice Full Text

-- Drama pero sin acción
SELECT title
FROM film
WHERE MATCH(description) AGAINST('+drama -action' IN BOOLEAN MODE);

El operador + hace que la palabra sea obligatoria. El operador - la excluye. Sin operadores, MySQL usa relevancia natural y puede ignorar palabras que aparecen en demasiados documentos. IN BOOLEAN MODE da control explícito sobre cada término.

Ejercicio 4: índice compuesto para alquileres activos

SQL

-- Paso 1: consulta SIN índice (~5 segundos, full scan de rental)
EXPLAIN ANALYZE
SELECT rental_id, rental_date, inventory_id
FROM rental
WHERE customer_id = 459
  AND return_date IS NULL
ORDER BY rental_date DESC;

-- Paso 2: crear el índice compuesto
-- customer_id primero (filtro de igualdad), rental_date segundo (para el ORDER BY)
CREATE INDEX idx_rental_customer_date ON rental (customer_id, rental_date);

-- Paso 3: misma consulta CON índice (~0.04 segundos)
EXPLAIN ANALYZE
SELECT rental_id, rental_date, inventory_id
FROM rental
WHERE customer_id = 459
  AND return_date IS NULL
ORDER BY rental_date DESC;

-- Eliminar índices de práctica para no interferir con la tabla
DROP INDEX idx_film_year_length    ON film;
DROP INDEX idx_film_description    ON film;
DROP INDEX idx_rental_customer_date ON rental;

El índice (customer_id, rental_date) elimina dos costes: el filtrado fila a fila por customer_id y la ordenación posterior por fecha. MySQL aprovecha el orden ya implícito en el índice para el ORDER BY, sin necesidad de un paso de sorting adicional.

§ 05

Errores comunes

Crear el índice compuesto con las columnas en el orden equivocado. Un índice (rental_date, customer_id) no ayuda a consultas que filtran por customer_id sin rental_date, porque MySQL no puede usar la segunda columna del índice sin usar la primera.

Olvidar recrear los índices después de truncar o restaurar una tabla. Los índices se asocian a la tabla: si la tabla se vacía y se repuebla desde cero (por ejemplo, en migraciones), los índices siguen existiendo pero hay que verificar que siguen siendo relevantes.

Interpretar el tiempo de EXPLAIN ANALYZE como el tiempo de producción. El tiempo medido en un entorno local con pocos datos es diferente al tiempo en producción. EXPLAIN ANALYZE sirve para comparar antes y después del índice, no para predecir tiempos absolutos.

Usar MATCH...AGAINST sin el índice Full Text y no entender el error. "Can't find FULLTEXT index matching the column list" significa que falta el índice, no que la sintaxis esté mal.

Asumir que añadir más índices siempre mejora el rendimiento de SELECT. Cada índice adicional aumenta el tiempo de INSERT, UPDATE y DELETE porque MySQL debe mantener todos los índices actualizados.

§ 06

Buenas prácticas

Siempre mide el rendimiento antes de crear el índice con EXPLAIN ANALYZE. Sin una línea de base, no puedes saber si el índice mejoró algo.

Usa SHOW INDEX FROM tabla para revisar qué índices ya existen antes de crear nuevos. MySQL crea automáticamente un índice para PRIMARY KEY y UNIQUE; puede que el índice que necesitas ya esté ahí.

Al terminar los ejercicios de práctica, elimina los índices creados con DROP INDEX si no forman parte del esquema definitivo. Los índices de prueba acumulados pueden ralentizar las escrituras sin ningún beneficio real.

Lee el output de EXPLAIN ANALYZE de dentro hacia fuera: los pasos más internos (con más indentación) se ejecutan primero. Identifica cuál es el paso más costoso (mayor "actual time") y enfoca la optimización ahí.

Cuando una consulta usa JOINs, los índices más valiosos son los de las columnas que aparecen en las condiciones ON del JOIN. Sin ellos, MySQL puede hacer full scans de las tablas unidas.

§ 07

Ejercicios

Usa EXPLAIN ANALYZE sobre SELECT * FROM film WHERE rating = 'PG'. Anota el número de filas procesadas. Crea un índice sobre rating y repite. ¿Cuántas filas procesa ahora?
Crea un índice Full Text sobre el campo title de la tabla film. Escribe una consulta MATCH...AGAINST que encuentre películas con "love" en el título pero sin "war".
Usa EXPLAIN ANALYZE sobre una consulta con JOIN entre film y film_category usando film_id. ¿MySQL usa el índice de film_id? ¿Por qué?
Diseña y crea un índice compuesto para optimizar esta consulta: SELECT * FROM payment WHERE customer_id = 5 ORDER BY payment_date DESC LIMIT 10. Mide el tiempo antes y después con EXPLAIN ANALYZE.
Usa SHOW INDEX FROM rental para ver todos los índices existentes. Identifica cuáles fueron creados automáticamente por MySQL y cuáles por los ejercicios. Elimina los de práctica con DROP INDEX.

§ 08

Checklist final

Sé usar EXPLAIN ANALYZE para medir el rendimiento de una consulta antes y después de crear un índice.
Puedo crear índices B-Tree simples y compuestos con CREATE INDEX.
Entiendo por qué MySQL 8 con InnoDB no soporta índices HASH y cómo suplirlo con B-Tree.
Sé crear un índice Full Text y usar MATCH...AGAINST con IN BOOLEAN MODE.
Conozco los operadores de búsqueda booleana: + (incluir), - (excluir).
Entiendo que los índices mejoran las lecturas pero añaden coste a las escrituras.
Sé ver los índices de una tabla con SHOW INDEX y eliminarlos con DROP INDEX.
Puedo identificar si una tabla de Sakila cumple las tres formas normales y explicar por qué.