mysql-patterns

Patrones MySQL

Usa esta habilidad cuando trabajes en diseño de esquemas MySQL o MariaDB, migraciones, investigación de consultas lentas, transacciones tipo cola, pools de conexiones o configuración de bases de datos de producción. Prefiere verificaciones exactas de versión antes de aplicar un patrón específico de una característica porque MySQL y MariaDB han divergido en varios detalles SQL.

Activación

• Diseñar tablas, índices y restricciones en MySQL o MariaDB
• Revisar migraciones antes de que se ejecuten en tablas grandes de producción
• Depurar consultas lentas, esperas de bloqueos, interbloqueos o agotamiento de conexiones
• Agregar paginación por keyset, upserts, búsqueda de texto completo, columnas JSON o colas
• Configurar pools de conexiones de aplicaciones, réplicas de solo lectura, TLS o registros de consultas lentas

Verificación de Versión

Comienza identificando el motor y la versión:

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

Mantén las guías de MySQL y MariaDB separadas cuando la sintaxis difiera:

• Los documentos de MySQL usan alias de fila como reemplazo de VALUES(col) en ON DUPLICATE KEY UPDATE; VALUES(col) está obsoleto allí.
• Los documentos de MariaDB usan VALUES(col) como la forma compatible de referenciar valores insertados en ON DUPLICATE KEY UPDATE; úsalo para compatibilidad entre motores.
• SKIP LOCKED es apropiado solo para trabajo tipo cola. Omite filas bloqueadas y puede devolver una vista inconsistente, así que no lo uses para contabilidad general o lecturas sensibles a la integridad.

Valores Predeterminados de Esquema

CREATE TABLE pedidos (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    cuenta_id BIGINT UNSIGNED NOT NULL,
    estado VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    eliminado_en DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_pedidos_cuenta_estado_creado (cuenta_id, estado, creado_en),
    KEY idx_pedidos_activos (cuenta_id, eliminado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Elecciones predeterminadas:

Caso de Uso	Preferir	Evitar
Claves primarias sustitutas	BIGINT UNSIGNED AUTO_INCREMENT	INT para tablas que pueden crecer más allá de 2B filas
Claves de búsqueda UUID	BINARY(16) con ayudantes de conversión	VARCHAR(36) claves primarias en tablas calientes
Dinero y cantidades exactas	DECIMAL(p, s)	FLOAT o DOUBLE
Texto orientado al usuario	Tablas e índices utf8mb4	Valores predeterminados MySQL utf8 / utf8mb3
Marcas de tiempo de aplicación	DATETIME con UTC gestionado por la aplicación	Asumir que DATETIME almacena metadatos de zona horaria
Eliminaciones suaves	eliminado_en DATETIME NULL más índices con ámbito	Filtrar filas eliminadas suavemente sin un índice
Valores de estado extensibles	Tabla de búsqueda o VARCHAR restringido	ENUM cuando los valores cambian a menudo

Indexación

El orden de los índices compuestos generalmente sigue primero los predicados de igualdad, luego las columnas de rango u ordenación:

CREATE INDEX idx_pedidos_cuenta_estado_creado
    ON pedidos (cuenta_id, estado, creado_en);

SELECT id, total
FROM pedidos
WHERE cuenta_id = ?
  AND estado = 'pendiente'
  AND creado_en >= ?
ORDER BY creado_en DESC
LIMIT 50;

Usa EXPLAIN antes de agregar o cambiar un índice:

EXPLAIN
SELECT id, total
FROM pedidos
WHERE cuenta_id = 123 AND estado = 'pendiente'
ORDER BY creado_en DESC
LIMIT 50;

Señales para investigar:

Campo	Señal de Riesgo
type	ALL en una tabla grande
key	NULL cuando existe un predicado selectivo
rows	Estimación de filas muy alta para una ruta interactiva
Extra	Using temporary, Using filesort o Using where amplio

Evita agregar índices a ciegas. Cada índice aumenta el costo de escritura, el tiempo de migración, el tamaño de la copia de seguridad y la presión sobre el grupo de búferes.

Patrones de Consulta

Upsert

Forma compatible entre motores:

INSERT INTO configuraciones_usuario (usuario_id, clave_configuracion, valor_configuracion)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    valor_configuracion = VALUES(valor_configuracion),
    actualizado_en = CURRENT_TIMESTAMP;

Forma de alias de fila MySQL:

INSERT INTO configuraciones_usuario (usuario_id, clave_configuracion, valor_configuracion)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    valor_configuracion = new.valor_configuracion,
    actualizado_en = CURRENT_TIMESTAMP;

Usa la forma de alias de fila solo después de confirmar que el destino es MySQL. Usa VALUES(col) para MariaDB o flotas mixtas MySQL/MariaDB.

Paginación por Keyset

SELECT id, nombre, creado_en
FROM productos
WHERE (creado_en, id) < (?, ?)
ORDER BY creado_en DESC, id DESC
LIMIT 50;

Respáldalo con un índice que coincida con el cursor:

CREATE INDEX idx_productos_creado_id ON productos (creado_en, id);

No uses paginación profunda con OFFSET en tablas grandes; obliga al servidor a escanear y descartar filas antes de devolver la página.

Campos JSON

Usa columnas JSON para datos de extensión, no para campos que necesiten filtrado relacional intenso o restricciones.

CREATE TABLE eventos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    tipo_evento VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_eventos_tipo (tipo_evento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Para rutas JSON consultadas con frecuencia, expón una columna generada e indexa esa columna. Mantén las claves foráneas, la propiedad, la tenencia y los campos de ciclo de vida como relacionales.

Búsqueda de Texto Completo

ALTER TABLE articulos ADD FULLTEXT KEY ft_articulos_titulo_cuerpo (titulo, cuerpo);

SELECT id, titulo, MATCH(titulo, cuerpo) AGAINST (? IN NATURAL LANGUAGE MODE) AS puntuacion
FROM articulos
WHERE MATCH(titulo, cuerpo) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY puntuacion DESC
LIMIT 20;

Usa búsqueda externa cuando necesites tolerancia a errores tipográficos, clasificación compleja, facetas entre tablas o análisis específico de idioma más allá del comportamiento integrado de texto completo.

Transacciones

Mantén las transacciones cortas y bloquea las filas en un orden consistente:

START TRANSACTION;

SELECT id, saldo
FROM cuentas
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE cuentas SET saldo = saldo - ? WHERE id = ?;
UPDATE cuentas SET saldo = saldo + ? WHERE id = ?;

COMMIT;

Lista de verificación de interbloqueo y espera de bloqueo:

• Bloquea filas en un orden determinista en todas las rutas de código.
• Realiza llamadas API externas antes de abrir la transacción, no dentro de ella.
• Agrega índices para predicados usados en UPDATE, DELETE y lecturas con bloqueo.
• En caso de interbloqueo, retrocede y reintenta toda la transacción con un presupuesto de reintento limitado.
• Captura SHOW ENGINE INNODB STATUS\G poco después de un interbloqueo; se sobrescribe por eventos posteriores.

Reclamación de trabajador tipo cola:

START TRANSACTION;

SELECT id
FROM trabajos
WHERE estado = 'pendiente'
ORDER BY creado_en
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE trabajos
SET estado = 'procesando', iniciado_en = CURRENT_TIMESTAMP
WHERE id = ?;

COMMIT;

Usa SKIP LOCKED solo para cargas de trabajo tipo cola donde sea aceptable omitir una fila bloqueada. No es un reemplazo para la consistencia transaccional normal.

Pools de Conexiones

Ejemplo con SQLAlchemy:

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+mysqlconnector://app:[email protected]/app",
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=240,
    pool_pre_ping=True,
    connect_args={"connect_timeout": 5},
)

Ejemplo con Node.js mysql2:

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000,
});

const [rows] = await pool.execute(
  'SELECT id, total FROM pedidos WHERE cuenta_id = ? LIMIT 50',
  [accountId],
);

Mantén el reciclaje del pool de la aplicación por debajo del wait_timeout del servidor. Si el servidor usa wait_timeout = 300, un pool_recycle de alrededor de 240 segundos es coherente; pool_pre_ping aún ayuda a recuperarse de eventos de red y conmutación por error.

Diagnóstico

Comandos de primera pasada útiles:

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

Habilita el registro de consultas lentas en un entorno controlado:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Usa EXPLAIN ANALYZE solo cuando sea seguro ejecutar la consulta. Ejecuta la sentencia y puede ser costoso en datos de tamaño de producción.

Replicación

Las réplicas de solo lectura pueden retrasarse. No enrutes rutas de lectura-después-de-escritura, flujos de pago, verificaciones de permisos o lecturas de clave de idempotencia a una réplica inmediatamente después de una escritura.

-- Terminología heredada de MySQL, aún común en flotas existentes
SHOW SLAVE STATUS\G;

-- Terminología más nueva donde sea compatible
SHOW REPLICA STATUS\G;

Verifica el motor/versión antes de estandarizar en un comando. Monitorea la salud del hilo SQL de la réplica, la salud del hilo IO y el retraso, no solo si la conexión TCP está activa.

Seguridad

CREATE USER 'app'@'%' IDENTIFIED BY 'usa-un-gestor-de-secretos';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

ALTER USER 'app'@'%' REQUIRE SSL;

SELECT user, host
FROM mysql.user
WHERE user = '';

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

Puntos de revisión de seguridad:

• No concedas ALL PRIVILEGES o *.* a usuarios de aplicaciones.
• Exige TLS para usuarios de aplicaciones cuando el tráfico cruce hosts o redes.
• Almacena credenciales en el gestor de secretos de la plataforma, no en ejemplos, scripts o archivos de repositorio.
• Separa los usuarios de migración/administración de los usuarios de aplicaciones en tiempo de ejecución.
• Audita la exposición de la red pública y las direcciones de enlace antes de ajustar el rendimiento.

Configuración

Ejemplo de punto de partida para un host de base de datos dedicado:

[mysqld]
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

max_connections = 300
thread_cache_size = 50

wait_timeout = 300
interactive_timeout = 300
innodb_lock_wait_timeout = 10

slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

Trata los valores de configuración como una sugerencia para revisión, no un preajuste universal. Dimensiona la memoria, las conexiones, la retención de registros y la configuración de durabilidad según la carga de trabajo, el hardware, la política de copias de seguridad y los objetivos de recuperación.

Antipatrones

Antipatrón	Riesgo	Mejor Patrón
SELECT * en rutas calientes	Sobrecarga de datos y clientes frágiles	Seleccionar columnas explícitas
Paginación profunda con OFFSET	Escaneos lineales y páginas lentas	Paginación por keyset
Sin índice en joins por clave foránea	Joins lentos y eliminaciones con bloqueo pesado	Indexar columnas FK intencionadamente
Transacciones largas	Esperas de bloqueo y gran historial de deshacer	Confirmar unidades pequeñas de trabajo
DML directo contra mysql.user	Riesgo de corrupción de la tabla de concesiones	Usar CREATE USER, ALTER USER, DROP USER
Usuario de aplicación con permisos de administrador	Alto radio de explosión	Usuario de tiempo de ejecución con mínimo privilegio
Reciclaje del pool por encima de wait_timeout	Conexiones de pool obsoletas	Reciclar por debajo del tiempo de espera y verificar previamente
Lecturas en réplicas después de escrituras	Estado obsoleto visible para el usuario	Dirigir flujos de lectura-después-de-escritura al primario

Expectativas de Salida

Cuando se use esta habilidad para revisión, devuelve:

1. Suposiciones de motor/versión.
2. Problemas de mayor riesgo en corrección, bloqueo, seguridad y migración.
3. Cambios exactos de SQL o código para la ruta segura.
4. Plan de validación: EXPLAIN, simulación de migración, verificación de bloqueo/interbloqueo y criterios de reversión.
5. Diferencias de sintaxis MySQL/MariaDB que afecten la recomendación.

Relacionado

• Habilidad: patrones-postgres - Patrones de esquema y consulta específicos de PostgreSQL
• Habilidad: migraciones-base-de-datos - Planificación de migraciones y seguridad en el despliegue
• Habilidad: patrones-backend - Patrones de API y capa de servicio
• Habilidad: revision-seguridad - Manejo de secretos, autenticación y mínimo privilegio
• Agente: revisor-base-de-datos - Flujo de trabajo de revisión de base de datos más amplio