Motores: InnoDB

MySQL tiene una característica poco común: el código que guarda físicamente los datos es un motor de almacenamiento intercambiable, y cada tabla puede usar uno distinto. Elegir mal el motor puede significar perder transacciones, claves foráneas o incluso datos tras un apagón. En esta lección entenderás por qué InnoDB es la respuesta correcta en casi todos los casos y cómo funciona por dentro.

Concepto

Imagina que MySQL es una empresa de mensajería. La oficina (el servidor) recibe tus pedidos (SQL), pero los paquetes se guardan en bodegas distintas según el contrato. Cada bodega (motor) tiene sus reglas: una tiene seguro y cámaras (InnoDB), otra es más simple y barata pero sin seguro (MyISAM), otra guarda todo en una mesa que se vacía al cerrar (MEMORY).

            ┌───────────────────────────────────────┐
  SQL ────► │ Capa SQL: parser, optimizador, caché  │
            ├───────────────────────────────────────┤
            │      API de motores de almacenamiento  │
            ├──────────┬──────────┬────────┬────────┤
            │  InnoDB  │  MyISAM  │ MEMORY │  CSV   │  ← cada tabla elige uno
            └──────────┴──────────┴────────┴────────┘

-- Motores disponibles en tu servidor
SHOW ENGINES;

Engine	Support	Transactions	XA	Savepoints
InnoDB	DEFAULT	YES	YES	YES
MyISAM	YES	NO	NO	NO
MEMORY	YES	NO	NO	NO
CSV	YES	NO	NO	NO
ARCHIVE	YES	NO	NO	NO

InnoDB vs MyISAM

Característica	InnoDB	MyISAM
Por defecto	Sí (desde MySQL 5.5)	Antes de 5.5
Transacciones (COMMIT/ROLLBACK)	Sí	No
Claves foráneas	Sí	No (las ignora)
Bloqueo	Por fila	Por tabla completa
Recuperación tras caída	Automática (redo log)	Puede corromperse; requiere REPAIR TABLE
COUNT(*) sin WHERE	Recorre un índice	Instantáneo (guarda el número)
Índices FULLTEXT	Sí (desde 5.6)	Sí
Uso recomendado	Todo	Solo tablas heredadas

El bloqueo por fila, en la práctica

MyISAM (bloqueo de tabla)                InnoDB (bloqueo de fila)
─────────────────────────                ─────────────────────────
Usuario A: UPDATE pedido 1  → bloquea    Usuario A: UPDATE pedido 1 → bloquea fila 1
           TODA la tabla                 Usuario B: UPDATE pedido 2 → ✔ sigue, otra fila
Usuario B: UPDATE pedido 2  → espera...  Usuario C: SELECT pedidos  → ✔ lee sin esperar
Usuario C: SELECT pedidos   → espera...

En una tienda con muchos clientes comprando al mismo tiempo, MyISAM se convierte en una fila de espera.

Prueba: MyISAM ignora el ROLLBACK

CREATE TABLE prueba_myisam (id INT PRIMARY KEY, valor INT) ENGINE=MyISAM;
CREATE TABLE prueba_innodb (id INT PRIMARY KEY, valor INT) ENGINE=InnoDB;

START TRANSACTION;
INSERT INTO prueba_myisam VALUES (1, 100);
INSERT INTO prueba_innodb VALUES (1, 100);
ROLLBACK;

SELECT COUNT(*) FROM prueba_myisam;   -- 1  ← ¡el INSERT se quedó!
SELECT COUNT(*) FROM prueba_innodb;   -- 0  ← deshecho correctamente
-- MySQL incluso avisa: "Some non-transactional changed tables couldn't be rolled back"

Tablas viejas en MyISAM

Es muy común heredar sitios (WordPress antiguos, sistemas de hace 10 años) con tablas MyISAM. Síntomas: claves foráneas que "no hacen nada", tablas que se marcan como crashed tras un apagón, sitio lento con mucho tráfico. Conviértelas a InnoDB (abajo verás cómo).

Otros motores

Motor	Para qué sirve
MEMORY	Tablas en RAM, se vacían al reiniciar. Datos temporales muy rápidos (hoy suele ser mejor Redis)
CSV	Guarda la tabla como archivo .csv. Intercambio de datos simple
ARCHIVE	Comprime filas, solo INSERT y SELECT. Logs históricos
BLACKHOLE	Acepta datos y los descarta. Pruebas de replicación

En MariaDB existen además Aria (MyISAM mejorado, usado en tablas del sistema) y ColumnStore (analítica).

Ver y cambiar el motor de una tabla

-- Motor de cada tabla de la base tienda
SELECT table_name, engine, table_rows, 
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS tamano_mb
FROM information_schema.tables
WHERE table_schema = 'tienda';

-- Detalle de una tabla
SHOW TABLE STATUS LIKE 'productos'\G

-- Crear indicando el motor (InnoDB es el valor por defecto; ser explícito no hace daño)
CREATE TABLE logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  mensaje TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Convertir una tabla existente
ALTER TABLE tabla_vieja ENGINE=InnoDB;

Convertir todas las tablas MyISAM de una base

Este SQL genera los ALTER TABLE necesarios; revísalos y ejecútalos:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS sentencia
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

Antes de convertir

1. Haz un respaldo completo (Respaldos).
2. ALTER TABLE ... ENGINE=InnoDB reconstruye la tabla: en tablas grandes tarda y bloquea escrituras. Hazlo en horario de poco tráfico.
3. Revisa si alguna tabla tiene índices FULLTEXT o depende de COUNT(*) instantáneo.

Cómo funciona InnoDB por dentro

No necesitas ser experto, pero entender estas piezas te ayudará a configurar y diagnosticar:

                    ┌──────────────── RAM ────────────────┐
  SELECT / UPDATE ► │  Buffer Pool (caché de páginas)     │
                    │  ┌──────┬──────┬──────┬──────┐      │
                    │  │página│página│página│ ...  │      │
                    │  └──────┴──────┴──────┴──────┘      │
                    │  Log buffer                          │
                    └──────┬───────────────────┬──────────┘
                           │ COMMIT            │ en segundo plano
                    ┌──────▼──────┐     ┌──────▼──────────────┐
                    │  Redo log   │     │ Archivos .ibd        │
                    │ (garantiza  │     │ (tablas e índices)   │
                    │ durabilidad)│     └──────────────────────┘
                    └─────────────┘

Pieza	Qué hace
Buffer pool	Caché en RAM de datos e índices. Es el parámetro más importante de rendimiento (innodb_buffer_pool_size)
Redo log	Registro secuencial de cambios. Tras una caída, InnoDB lo "reproduce" para no perder transacciones confirmadas
Undo log	Versiones anteriores de las filas: permite ROLLBACK y lecturas consistentes (MVCC)
Archivos .ibd	Con innodb_file_per_table=ON (por defecto), cada tabla tiene su archivo
Clustered index	Las filas se guardan ordenadas por la PRIMARY KEY

La PRIMARY KEY es especial en InnoDB

En InnoDB la tabla es el índice de la clave primaria (índice agrupado o clustered). Consecuencias prácticas:

• Buscar por PK es lo más rápido posible.
• Cada índice secundario guarda también el valor de la PK: una PK enorme (por ejemplo un VARCHAR(255)) agranda todos los índices.
• Insertar PK secuenciales (AUTO_INCREMENT) es eficiente; PK aleatorias (UUID v4 en texto) fragmentan la tabla.
• Si no defines PK, InnoDB crea una oculta. Define siempre una.

Buena práctica

Usa INT UNSIGNED o BIGINT UNSIGNED AUTO_INCREMENT como PK. Si necesitas UUID (para no exponer ids secuenciales), guárdalo como BINARY(16) con UUID_TO_BIN(UUID(), 1) (ordenado por tiempo) o usa UUID v7, y mantenlo como columna UNIQUE adicional.

AUTO_INCREMENT

CREATE TABLE productos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

INSERT INTO productos (nombre) VALUES ('Taza');
SELECT LAST_INSERT_ID();          -- id generado en ESTA conexión

-- Ver el siguiente valor
SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_schema = 'tienda' AND table_name = 'productos';

-- Reiniciar (solo en tablas vacías o de pruebas)
ALTER TABLE productos AUTO_INCREMENT = 1;

Los huecos en los ids (1, 2, 5, 6...) son normales: aparecen por ROLLBACK, DELETE o inserciones fallidas. Nunca uses el id como "número consecutivo de factura"; para eso crea una columna propia.

MVCC: leer sin bloquear

InnoDB usa MVCC (control de concurrencia multiversión): cuando alguien modifica una fila, los demás siguen leyendo la versión anterior (desde el undo log) hasta el COMMIT. Por eso los SELECT normales no esperan a los UPDATE. Es lo que hace posible el nivel de aislamiento REPEATABLE READ por defecto (ver Transacciones y ACID).

Ver el estado de InnoDB

-- Tamaño del buffer pool (en MB)
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;

-- ¿Qué tanto se encuentran los datos en RAM? (cuanto más cerca de 100 %, mejor)
SELECT ROUND(100 * (1 - 
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
), 2) AS hit_ratio_pct;

-- Informe completo (transacciones, bloqueos, último deadlock)
SHOW ENGINE INNODB STATUS\G

La sección LATEST DETECTED DEADLOCK de SHOW ENGINE INNODB STATUS es oro cuando tu app reporta errores de deadlock.

Ejercicios

1. Ejecuta SHOW ENGINES; y confirma cuál es el motor por defecto de tu servidor.
2. Escribe la consulta que lista todas las tablas que no son InnoDB en tu servidor (excluyendo las bases del sistema).
3. Reproduce la prueba de ROLLBACK con una tabla MyISAM y una InnoDB.
4. ¿Por qué no conviene usar un VARCHAR(36) con UUID aleatorio como PRIMARY KEY en InnoDB?

Ver solución

SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine IS NOT NULL
  AND engine <> 'InnoDB'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

(engine IS NOT NULL excluye las vistas.)

4. Porque InnoDB ordena físicamente la tabla por la PK: valores aleatorios obligan a insertar en medio de páginas ya llenas (divisiones de página y fragmentación), y como cada índice secundario copia la PK, 36 bytes por fila inflan todos los índices. Mejor BIGINT AUTO_INCREMENT como PK y, si hace falta, el UUID en BINARY(16) como columna única.

Resumen

• MySQL usa motores de almacenamiento intercambiables; InnoDB es el predeterminado y el correcto.
• InnoDB ofrece transacciones, FK, bloqueo por fila, recuperación automática y MVCC; MyISAM no.
• Convierte tablas heredadas con ALTER TABLE ... ENGINE=InnoDB (con respaldo previo).
• El buffer pool es la caché clave de rendimiento; el redo log garantiza durabilidad.
• En InnoDB la tabla está ordenada por la PK: usa PK pequeñas y secuenciales.