Optimización y buenas prácticas — Conceptos
Una base de datos puede contener los datos correctos y aun así ser lenta o inconsistente si no está bien estructurada. Los índices aceleran las búsquedas evitando que MySQL recorra toda la tabla fila a fila. La normalización organiza el esquema para eliminar redundancias y garantizar que cada dato esté en un único lugar. Juntas, estas dos técnicas definen la diferencia entre una base de datos de juguete y una base de datos lista para producción.
Resumen rápido
Una base de datos puede contener los datos correctos y aun así ser lenta o inconsistente si no está bien estructurada. Los índices aceleran las búsquedas evitando que MySQL recorra toda la tabla fila a fila. La normalización organiza el esquema para eliminar redundancias y garantizar que cada dato esté en un único lugar. Juntas, estas dos técnicas definen la diferencia entre una base de datos de juguete y una base de datos lista para producción.
Conceptos clave
Estructura de datos auxiliar que MySQL mantiene separada de la tabla y que permite localizar registros sin escanearla entera. Funciona como el índice de un libro: en lugar de leer todas las páginas, se consulta el índice y se salta directamente a la posición correcta.
Tipo de índice predeterminado en MySQL. Organiza los datos en un árbol balanceado y ordenado, lo que lo hace eficiente tanto para búsquedas exactas como para rangos (BETWEEN, >, <) y ordenaciones (ORDER BY). Es el índice de uso general.
Índice que convierte cada valor en una posición fija mediante una función hash. Extremadamente rápido para igualdades exactas (=), pero no sirve para rangos ni ordenaciones. MySQL 8 no soporta HASH en tablas InnoDB; está disponible en PostgreSQL y motores alternativos.
Índice especializado para búsquedas en texto libre. Indexa palabras clave ignorando artículos y preposiciones comunes. Permite usar MATCH...AGAINST en lugar de LIKE, con una velocidad radicalmente superior en tablas grandes.
Índice creado sobre dos o más columnas simultáneamente. MySQL lo usa cuando la consulta filtra u ordena por esas columnas en el mismo orden en que fueron declaradas en el índice. Útil cuando se combinan condiciones habituales en los WHERE.
Comando que ejecuta la consulta y devuelve un análisis detallado de cómo MySQL la resolvió: qué índices usó, cuántas filas escaneó, el coste estimado y el tiempo real. Es el principal instrumento de diagnóstico para medir el impacto de un índice.
Sintaxis para búsquedas Full Text en MySQL. MATCH especifica la columna indexada y AGAINST la cadena de búsqueda. Con IN BOOLEAN MODE permite usar operadores: + (debe contener), - (no debe contener), * (comodín de sufijo).
Proceso de organizar el esquema de una base de datos relacional para eliminar redundancias y garantizar que cada dato dependa lógicamente del lugar donde está almacenado. Se aplica progresivamente mediante formas normales (1FN, 2FN, 3FN).
Regla de normalización que exige que cada columna contenga valores atómicos (indivisibles) y que no existan grupos repetidos dentro de una fila. Una columna con "Matemáticas, Física, Química" en un mismo campo viola la 1FN.
Regla que exige estar en 1FN y que todos los atributos no clave dependan completamente de la clave primaria, no de una parte de ella. Relevante cuando la clave primaria es compuesta (formada por más de una columna).
Regla que exige estar en 2FN y que no existan dependencias transitivas: ningún atributo no clave puede depender de otro atributo no clave. Todo atributo no clave debe depender directamente de la clave primaria.
Situación en la que un atributo no clave depende de otro atributo no clave en lugar de depender directamente de la clave primaria. Ejemplo: en una tabla de empleados, si "ciudad_sede" depende de "departamento" (no clave) y no de "empleado_id" (clave), hay una dependencia transitiva.
Contenido de la clase
Por qué la velocidad importa en producción
En un entorno de práctica con cientos o miles de filas, cualquier consulta responde en milisegundos independientemente de cómo esté escrita. En producción, las tablas pueden tener millones de registros, y una consulta mal optimizada que tarda 5 segundos en encontrar un registro puede hacer inutilizable una aplicación que recibe cientos de peticiones simultáneas.
Los índices y la normalización son las dos herramientas principales para abordar este problema. Los índices mejoran la velocidad de las consultas. La normalización mejora la estructura del esquema, eliminando duplicados y garantizando consistencia. Las dos son complementarias: una base de datos puede tener índices perfectos pero un esquema caótico, o un esquema limpio pero sin ningún índice.
Índices: cómo MySQL encuentra datos sin leer todo
Sin índices, cada consulta con WHERE obliga a MySQL a leer todas las filas de la tabla de arriba a abajo buscando las que cumplen la condición. Es el equivalente a buscar una palabra en un diccionario leyendo todas las páginas en orden.
Un índice es una estructura aparte, mantenida automáticamente por MySQL, que almacena los valores de una o varias columnas en un orden que permite localizar registros de forma directa. La consulta consulta el índice primero, obtiene la posición exacta del registro y salta directamente a él.
Los tres tipos de índice
| Tipo | Cómo funciona | Cuándo usarlo | Limitación |
|---|---|---|---|
| B-Tree | Árbol ordenado y balanceado. Permite recorrer rangos de forma eficiente. | Rangos (BETWEEN, >, <), igualdades, ORDER BY | Menos eficiente que HASH para igualdades puras en tablas muy grandes |
| HASH | Función matemática que convierte el valor en una posición fija. | Igualdades exactas (=), como búsquedas por email o DNI | No soporta rangos ni ORDER BY. No disponible en MySQL 8 con InnoDB |
| Full Text | Índice de palabras clave que ignora términos comunes. | Búsquedas en texto libre con MATCH...AGAINST | Solo para columnas de texto (VARCHAR, TEXT). No para números o fechas |
Índices compuestos: combinar columnas
Un índice compuesto cubre dos o más columnas y es útil cuando las consultas filtran habitualmente por esa combinación. MySQL lo usa cuando la condición WHERE incluye las columnas en el mismo orden en que fueron declaradas en el índice.
Por ejemplo, si las consultas frecuentes filtran por customer_id y luego ordenan por rental_date, un índice compuesto sobre (customer_id, rental_date) permite ir directamente al cliente y encontrar sus alquileres ya ordenados por fecha.
Normalización: construir esquemas que no mienten
La normalización resuelve tres problemas que aparecen en bases de datos mal diseñadas: anomalías de inserción (hay que duplicar datos para insertar un registro nuevo), anomalías de actualización (cambiar un dato requiere actualizarlo en múltiples filas), y anomalías de eliminación (borrar un registro destruye información que no debería perderse).
El proceso se aplica progresivamente mediante formas normales. Cada forma normal elimina un tipo concreto de redundancia o inconsistencia. No es necesario llegar siempre a la 3FN: en algunos casos, una desnormalización controlada puede mejorar el rendimiento a costa de algo de redundancia.
Primera Forma Normal (1FN): valores atómicos
Un valor es atómico cuando no puede dividirse más. Una columna que contiene "Matemáticas, Física, Química" en un mismo campo almacena tres valores en uno, lo que viola la 1FN. Cuando se necesita extraer uno de esos valores, hay que procesar texto, lo que es lento e impreciso.
- Cada columna debe contener un único valor por fila.
- No puede haber grupos repetidos: si un estudiante puede cursar N asignaturas, no van en una sola columna separadas por comas. Cada asignatura ocupa su propia fila.
- La solución habitual es crear filas adicionales para cada valor, usando la misma clave primaria si el registro pertenece al mismo sujeto.
Segunda Forma Normal (2FN): dependencia completa de la clave
La 2FN entra en juego cuando la clave primaria es compuesta (formada por dos o más columnas). La regla: todos los atributos no clave deben depender de la clave completa, no solo de una parte de ella.
En una tabla de detalles de pedido con clave compuesta (pedido_id, producto_id), el campo "nombre_producto" viola la 2FN porque depende solo de producto_id, no de la combinación pedido_id + producto_id. La solución es extraer los datos del producto a su propia tabla.
Tercera Forma Normal (3FN): sin dependencias entre no-claves
La 3FN añade una regla sobre los atributos no clave: ninguno puede depender de otro atributo no clave. Todo atributo no clave debe depender directamente de la clave primaria.
El ejemplo clásico: en una tabla de empleados con columnas empleado_id (clave), nombre, departamento y ciudad_sede, el campo ciudad_sede depende de departamento (no clave), no directamente de empleado_id. Eso es una dependencia transitiva. La solución es separar los departamentos a su propia tabla con departamento_id como clave.
| Forma Normal | Regla que añade | Problema que elimina |
|---|---|---|
| 1FN | Valores atómicos, sin grupos repetidos | Datos múltiples en un campo, columnas que no pueden indexarse |
| 2FN | Atributos no clave dependen de toda la clave primaria | Duplicación de datos en claves compuestas |
| 3FN | Atributos no clave no dependen de otros no clave | Dependencias transitivas, datos que cambian en cascada incorrectamente |
Código y ejemplos
Crear los tres tipos de índice
-- B-Tree por defecto: ideal para rangos y ordenaciones
CREATE INDEX idx_film_year_length ON film (release_year, length);
-- B-Tree para igualdad exacta (MySQL 8 no soporta HASH en InnoDB)
CREATE INDEX idx_customer_email ON customer (email);
-- Full Text: para búsquedas en texto libre
CREATE FULLTEXT INDEX idx_film_description ON film (description);
-- Ver todos los índices de una tabla
SHOW INDEX FROM film;
-- Eliminar un índice
DROP INDEX idx_film_year_length ON film;CREATE INDEX sin especificar tipo crea un B-Tree. El índice Full Text requiere que la columna sea de tipo CHAR, VARCHAR o TEXT. SHOW INDEX muestra los índices existentes, incluyendo el PRIMARY KEY generado automáticamente.
EXPLAIN ANALYZE: medir el impacto del índice
-- Antes del índice: MySQL escanea todas las filas (tabla completa)
EXPLAIN ANALYZE
SELECT title, release_year, length
FROM film
WHERE release_year = 2006
AND length BETWEEN 120 AND 180;
-- Crear el índice compuesto
CREATE INDEX idx_film_year_length ON film (release_year, length);
-- Después del índice: MySQL salta directamente al rango indexado
EXPLAIN ANALYZE
SELECT title, release_year, length
FROM film
WHERE release_year = 2006
AND length BETWEEN 120 AND 180;EXPLAIN ANALYZE ejecuta la consulta y devuelve el plan real de ejecución con tiempos medidos. Busca en la salida "rows" (filas escaneadas) y "actual time": un buen índice reduce ambos drásticamente.
Full Text: MATCH...AGAINST con operadores booleanos
-- Sin Full Text index la búsqueda usa LIKE (lenta, no usa índice)
SELECT title FROM film
WHERE description LIKE '%drama%' AND description LIKE '%feminist%';
-- Crear el índice Full Text
CREATE FULLTEXT INDEX idx_film_desc ON film (description);
-- Con Full Text: + = debe contener, - = no debe contener
SELECT title, description
FROM film
WHERE MATCH(description) AGAINST('+drama +feminist' IN BOOLEAN MODE);
-- Búsqueda con exclusión: drama pero no acción
SELECT title
FROM film
WHERE MATCH(description) AGAINST('+drama -action' IN BOOLEAN MODE);IN BOOLEAN MODE permite usar operadores: + (obligatorio), - (excluido), * (prefijo). Sin el modo booleano, MySQL usa relevancia natural y puede ignorar palabras que no superen cierto umbral de frecuencia.
Normalización: de violación a 3FN
-- VIOLACIÓN de 1FN: cursos en una sola columna (no atómico)
-- ❌ Mal diseño
CREATE TABLE inscripciones_mal (
alumno_id INT,
nombre VARCHAR(100),
cursos VARCHAR(200) -- "Matemáticas, Física, Química" ← no atómico
);
-- ✅ En 1FN: un valor por columna y por fila
CREATE TABLE inscripciones (
alumno_id INT,
nombre VARCHAR(100),
curso VARCHAR(100) -- cada fila = un curso
);
-- VIOLACIÓN de 3FN: ciudad_sede depende de departamento, no de empleado_id
-- ❌ Mal diseño
CREATE TABLE empleados_mal (
empleado_id INT PRIMARY KEY,
nombre VARCHAR(100),
departamento VARCHAR(50),
ciudad_sede VARCHAR(50) -- depende de departamento, no de empleado_id
);
-- ✅ En 3FN: separar departamentos a su propia tabla
CREATE TABLE departamentos (
departamento_id INT PRIMARY KEY,
nombre VARCHAR(50),
ciudad_sede VARCHAR(50)
);
CREATE TABLE empleados (
empleado_id INT PRIMARY KEY,
nombre VARCHAR(100),
departamento_id INT,
FOREIGN KEY (departamento_id) REFERENCES departamentos(departamento_id)
);Normalizar no siempre significa añadir más tablas: a veces basta con cambiar el tipo de dato o descomponer una columna. El criterio clave es que cada dato viva en un único lugar y que su lugar sea lógicamente coherente con la clave primaria de su tabla.
Errores comunes
Buenas prácticas
Ejercicios
- Identifica en la tabla de Sakila film las columnas que se usan más habitualmente en WHERE. ¿Cuáles tienen ya un índice? Usa SHOW INDEX FROM film para comprobarlo.
- Diseña en papel una tabla de pedidos con columnas pedido_id, cliente_nombre, cliente_email, producto_nombre, producto_precio, cantidad. Identifica qué forma normal viola y cómo la corregirías.
- Explica por qué la tabla film_actor de Sakila está en 3FN. ¿Qué columnas tiene? ¿De qué dependen?
- Describe la diferencia entre un índice B-Tree y un índice HASH en términos de casos de uso. ¿Para qué tipo de consulta elegirías cada uno?
- Diseña el esquema normalizado (hasta 3FN) para una tabla que almacene: empleado, departamento, ciudad de la sede del departamento y salario del empleado.
Checklist final
- Entiendo qué es un índice y por qué evita que MySQL escanee toda la tabla.
- Conozco los tres tipos de índice (B-Tree, HASH, Full Text) y sé cuándo aplicar cada uno.
- Sé crear un índice simple y un índice compuesto con CREATE INDEX.
- Sé usar EXPLAIN ANALYZE para medir el impacto de un índice en el tiempo de consulta.
- Entiendo qué es la normalización y cuáles son los tres problemas que resuelve (inserción, actualización, eliminación).
- Puedo identificar si una tabla viola la 1FN (valores no atómicos o grupos repetidos).
- Puedo identificar si una tabla viola la 2FN (dependencia parcial de clave compuesta).
- Puedo identificar si una tabla viola la 3FN (dependencia transitiva entre no-claves).
¿Has terminado la clase?
Márcala como repasada y vuelve cuando quieras revisarla.