patrones-mysql

Patrones de MySQL

Use este skill cuando trabaje en diseño de esquemas MySQL o MariaDB, migraciones, investigación de consultas lentas, transacciones tipo cola, pools de conexión o configuración de bases de datos en producción. Prefiera verificar la versión exacta antes de aplicar un patrón específico de una característica, ya que MySQL y MariaDB han divergido en varios detalles de SQL.

Activación

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

Verificación de Versión

Comience identificando el motor y la versión:

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

Mantenga separadas las guías de MySQL y MariaDB cuando la sintaxis difiera:

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

Valores por Defecto del Esquema

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Opciones predeterminadas:

Caso de Uso	Preferir	Evitar
Claves primarias sustitutas	BIGINT UNSIGNED AUTO_INCREMENT	INT para tablas que puedan crecer más de 2B filas
Claves de búsqueda UUID	BINARY(16) con funciones de conversión	VARCHAR(36) como clave primaria en tablas calientes
Dinero y cantidades exactas	DECIMAL(p, s)	FLOAT o DOUBLE
Texto visible para el usuario	tablas e índices utf8mb4	Valores predeterminados utf8 / utf8mb3 de MySQL
Marcas de tiempo de aplicación	DATETIME con UTC gestionado por la app	Asumir que DATETIME almacena metadatos de zona horaria
Borrado suave	deleted_at DATETIME NULL más índices con ámbito	Filtrar filas borradas suavemente sin índice
Valores de estado extensibles	tabla de referencia o VARCHAR restringido	ENUM cuando los valores cambian a menudo

Indexación

El orden del índice compuesto suele seguir primero los predicados de igualdad, luego las columnas de rango u orden:

CREATE INDEX idx_orders_account_status_created
    ON orders (account_id, status, created_at);

SELECT id, total
FROM orders
WHERE account_id = ?
  AND status = 'pending'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

Use EXPLAIN antes de agregar o cambiar un índice:

EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Señales a 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

Evite 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 buffer pool.

Patrones de Consulta

Upsert

Forma compatible entre motores:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = CURRENT_TIMESTAMP;

Forma con alias de fila de MySQL:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at = CURRENT_TIMESTAMP;

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

Paginación con Keyset

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

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

CREATE INDEX idx_products_created_id ON products (created_at, id);

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

Campos JSON

Use columnas JSON para datos de extensión, no para campos que requieran filtrado relacional intensivo o restricciones.

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

Para rutas JSON consultadas con frecuencia, exponga una columna generada e indexe esa columna. Mantenga las claves foráneas, la propiedad, la tenencia y los campos de ciclo de vida como relacionales.

Búsqueda de Texto Completo

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);

SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

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

Transacciones

Mantenga las transacciones cortas y bloquee las filas en un orden consistente:

START TRANSACTION;

SELECT id, balance
FROM accounts
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

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

• Bloquee filas en un orden determinista en todas las rutas de código.
• Realice llamadas a API externas antes de abrir la transacción, no dentro de ella.
• Agregue índices para los predicados usados en UPDATE, DELETE y lecturas con bloqueo.
• Ante un interbloqueo, revierta y reintente toda la transacción con un presupuesto de reintentos limitado.
• Capture 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 jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'processing', started_at = CURRENT_TIMESTAMP
WHERE id = ?;

COMMIT;

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

Pools de Conexión

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 orders WHERE account_id = ? LIMIT 50',
  [accountId],
);

Mantenga 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 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 útiles de primera pasada:

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

Active el registro lento en un entorno controlado:

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

Use 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 lectura pueden tener retraso. No enrute rutas de lectura-después-de-escritura, flujos de pago, comprobaciones 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;

Verifique el motor/versión antes de estandarizar en un comando. Monitoree 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 'use-a-secret-manager';
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 conceda ALL PRIVILEGES o *.* a usuarios de aplicación.
• Exija TLS para usuarios de aplicación cuando el tráfico cruce hosts o redes.
• Almacene credenciales en el gestor de secretos de la plataforma, no en ejemplos, scripts o archivos de repositorio.
• Separe los usuarios de migración/administración de los usuarios de aplicación en tiempo de ejecución.
• Audite la exposición de 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

Trate los valores de configuración como una sugerencia para revisión, no como un valor preestablecido universal. Ajuste 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 críticas	Sobrerecuperación y clientes frágiles	Seleccionar columnas explícitas
Paginación profunda con OFFSET	Escaneos lineales y páginas lentas	Paginación con keyset
Sin índice en joins de clave foránea	Joins lentos y eliminaciones con bloqueo intensivo	Indexar columnas FK intencionalmente
Transacciones largas	Esperas de bloqueo e historial de deshacer grande	Confirmar unidades pequeñas de trabajo
DML directo contra mysql.user	Riesgo de corrupción de tablas de concesión	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 obsoletas en el pool	Reciclar por debajo del tiempo de espera y verificar antes de usar
Lecturas en réplica después de escrituras	Estado obsoleto visible para el usuario	Dirigir flujos de lectura-después-de-escritura al primario

Resultados Esperados

Cuando este skill se use para una revisión, devuelva:

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

Relacionados

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