jmaxzh/sdlc-mysql-design

Diseño de Tablas y SQL en MySQL

Aplicabilidad

• Aplica cuando la tarea o el proyecto establezcan MySQL como base de datos destino e impliquen modelado de datos, diseño de tablas, SQL, índices, transacciones o control de concurrencia; no es necesaria una invocación explícita.
• Úsala independientemente del flujo de trabajo SDLC. No selecciones MySQL para trabajo genérico de bases de datos ni lo apliques a tareas que solo conciernen a otras bases de datos.

Uso

• Usa la semántica oficial de MySQL 8.4 como línea base de validación sin restringir la versión desplegada. Verifica la compatibilidad de características, la aplicación de restricciones y las deprecaciones en la versión destino real cuando las diferencias importen.
• Establece el significado del negocio, los requisitos de integridad, los patrones de acceso principales, la escala y el crecimiento de datos, la concurrencia y los requisitos de consistencia relevantes para la tarea. Usa diseños existentes y evidencia de carga de trabajo cuando estén disponibles; indica suposiciones y su impacto cuando falte información clave.
• Aplica las reglas de transacción y concurrencia a transacciones locales de InnoDB. Identifica explícitamente las prohibiciones de consultas dentro de transacciones, restricciones CHECK, restricciones de clave foránea y procedimientos almacenados como convenciones del proyecto, no como limitaciones de MySQL.
• Asume que un registro se modifica como máximo una vez por milisegundo. Esta suposición no garantiza que updated_at sea un identificador de versión no reutilizado; aplica las condiciones bajo comprobaciones de timestamp antes de usarlo para control de concurrencia.
• Aplica solo las reglas relevantes a la tarea. Lee el tema correspondiente en ejemplos de diseño MySQL cuando una regla necesite aclaración; no cargues todos los ejemplos por defecto.

Modelado de datos

Entidades, relaciones y propiedad de campos

• Granularidad de fila: define el objeto de negocio, relación o evento representado por una fila; mantén la granularidad consistente dentro de una tabla. Consulta ejemplos.

• Relaciones y unicidad: define la cardinalidad mínima y máxima en ambos lados. Almacena referencias uno-a-muchos en el lado muchos, usa referencias únicas para limitar relaciones uno-a-uno a como máximo una coincidencia, y usa tablas de unión para relaciones muchos-a-muchos. Para asociaciones repetidas de los mismos objetos, define el significado comercial, la identidad y el alcance de unicidad de cada asociación. Consulta ejemplos.

• Propiedad de campos: coloca un campo con el objeto, relación o evento que determina su valor; evita duplicar hechos actuales. Mantén instantáneas históricas en valores de tiempo de evento, sin cambios por actualizaciones de valor actual. Consulta ejemplos.

• Integridad referencial: define si un destino referenciado debe existir y qué estados debe satisfacer al crear o cambiar una referencia. Define cómo se manejan las referencias existentes cuando el destino se elimina o se vuelve inválido. Aplica estos requisitos a través de flujos de escritura de negocio bajo claves y restricciones y control de concurrencia. Consulta ejemplos.

Diseño de tablas

Nombres y comentarios

• Nombra bases de datos, tablas y columnas usando letras minúsculas, dígitos y guiones bajos; no comiences con dígitos, uses palabras reservadas o abreviaturas poco claras.

• Nombra índices por sus columnas o propósito. uniq_ e idx_ pueden identificar índices únicos y ordinarios; el nombre del índice primario es PRIMARY.

• Actualiza los comentarios de columna y las definiciones relacionadas cuando cambien los significados, valores de estado o restricciones.

Tipos de datos

• Mantén las columnas usadas para referenciar o comparar los mismos valores compatibles en tipo de dato y signo; usa definiciones consistentes cuando sea posible.

• Representa montos monetarios exactos con DECIMAL de precisión y escala explícitas, o enteros en una unidad mínima definida; define la moneda y las reglas de redondeo. Preserva la representación exacta a través del almacenamiento, parámetros y cálculos; no calcules en punto flotante antes de la conversión.

• No establezcas UNSIGNED deprecado en FLOAT, DOUBLE o DECIMAL.

• Usa is_xxx y TINYINT UNSIGNED para booleanos, con validación de aplicación que restrinja los valores a 0 o 1.

• Al usar CHAR, verifica que su manejo de espacios finales coincida con el significado comercial del campo.

• TIMESTAMP convierte entre la zona horaria de sesión y UTC; DATETIME no. Almacena nombres de zona originales o desplazamientos por separado cuando sea necesario.

• Usa created_at para la creación y updated_at para la última modificación. Los registros comerciales mutables necesitan ambos; los registros de solo anexión necesitan solo la hora de creación. Agréguelos a tablas de unión u otras según las necesidades de auditoría.

• Usa DATETIME(3), precisión de milisegundos y hora de Pekín para marcas de tiempo de auditoría, con zona horaria de conexión +08:00.

• Establece las marcas de tiempo de auditoría a NOT NULL DEFAULT CURRENT_TIMESTAMP(3) y agrega ON UPDATE CURRENT_TIMESTAMP(3) para updated_at. Deja que la base de datos las mantenga; no las asignes redundantemente en la aplicación.

Claves y restricciones

• Define explícitamente claves primarias estables y cortas para tablas InnoDB. Los registros de índices secundarios contienen columnas de clave primaria, por lo que las claves primarias anchas aumentan el costo del índice secundario.

• Marca los campos requeridos como NOT NULL.

• No uses CHECK; valida rangos, enumeraciones y combinaciones de campos en cada punto de entrada de escritura de aplicación, rechazando escrituras inválidas. Consulta ejemplos.

• Aplica unicidad de negocio con índices únicos, no con consulta-antes-de-insertar. Los índices únicos permiten múltiples valores NULL.

• Aplica unicidad para identificadores de idempotencia dentro del ámbito de operación de negocio e inquilino aplicable, con todas las columnas participantes NOT NULL.

• No uses restricciones de clave foránea ni cascadas; preserva la integridad referencial a través de flujos de escritura de negocio.

Conjuntos de caracteres y cotejamiento

• Usa utf8mb4 para texto.

• Mantén las columnas de texto usadas para referenciar o comparar los mismos valores compatibles en conjunto de caracteres y cotejamiento; usa definiciones consistentes cuando sea posible.

• Antes de cambios de cotejamiento, verifica cambios de igualdad y nuevos valores duplicados que violen la unicidad.

Programación SQL

• No uses procedimientos almacenados. El código de aplicación controla la bifurcación, las confirmaciones y las reversiones.

Consultas

• Selecciona explícitamente las columnas requeridas en consultas comerciales estables; evita SELECT *.

• Cuando la paginación o la selección de una sola fila requieran orden determinista, usa una combinación de claves de ordenación que ordenen las filas de forma única.

• Incluye todas las claves de ordenación en cursores. Los cambios concurrentes en las claves de ordenación pueden causar omisiones/duplicados entre páginas; la paginación por cursor no garantiza una instantánea consistente entre páginas.

Modificación de datos

• No ocultes errores que deberían fallar usando INSERT IGNORE o UPDATE IGNORE.

• Evita que UPDATE/DELETE degeneren en operaciones de tabla completa debido a parámetros vacíos o predicados dinámicos faltantes. LIMIT no reemplaza las condiciones de ámbito de negocio.

• Bajo la definición de marca de tiempo de auditoría anterior, updated_at cambia automáticamente cuando el contenido de la fila cambia; no registra la hora de ejecución de cada sentencia de actualización.

• No reemplaces actualizaciones ordinarias con REPLACE INTO: en conflicto de clave primaria/única, su semántica de reemplazo puede restablecer campos no especificados a valores predeterminados y desencadenar comportamiento de eliminación/inserción.

Expresiones y funciones

• Iguala los tipos de comparación; no dejes que la conversión implícita determine comparaciones comerciales críticas.

• Distingue bytes, caracteres y grafemas visibles por el usuario en comprobaciones de longitud. LENGTH cuenta bytes y CHAR_LENGTH caracteres, no grafemas visibles.

• Si el conjunto utilizado por NOT IN contiene NULL, un valor que no coincida con ningún miembro puede producir UNKNOWN; no interpretes esto como no pertenencia confirmada.

• COUNT(DISTINCT col) excluye NULL; su forma de múltiples columnas cuenta combinaciones distintas solo cuando todas las columnas participantes son no NULL.

• SUM devuelve NULL para entradas vacías o totalmente NULL. Si GROUP BY no forma ningún grupo, no se devuelve ninguna fila para él. Sustituye cero solo cuando las reglas de negocio definan la agregación vacía como cero.

• Prefiere intervalos de tiempo semiabiertos: incluye el inicio y excluye el final. Calcula los límites en la zona horaria de negocio con la misma base de tiempo que los valores almacenados.

Transacciones y consistencia

Gestión de transacciones

• Sentencias dentro de transacciones: realiza consultas, validación de negocio y preparación de escritura fuera de transacciones explícitas. Dentro de la transacción, permite solo modificaciones de datos necesarias y control de transacción. Prohíbe consultas ordinarias, consultas de bloqueo y componentes de consulta dentro de escrituras, incluidas subconsultas. Consulta ejemplos.

• Conexión de transacción: ejecuta escrituras que deban tener éxito juntas en la misma conexión dentro de la misma transacción. El cambio de conexión, la ejecución asíncrona y la configuración de propagación deben preservar ese límite. Consulta ejemplos.

• Confirmaciones implícitas: no simules transacciones anidadas con START TRANSACTION ni cambies autocommit de 0 a 1 entre escrituras de negocio; ambas confirman implícitamente. Mantén DDL como ALTER TABLE y TRUNCATE TABLE fuera de las transacciones de escritura de negocio. Consulta ejemplos.

• Manejo de fallos: en errores de escritura requerida o condiciones de negocio no cumplidas, detén escrituras posteriores y revierte toda la transacción. No asumas que una sentencia fallida deshace escrituras anteriores. Después de un punto muerto o tiempo de espera de bloqueo, asegura la reversión completa y reintenta toda la transacción solo cuando sea seguro, con un límite de reintentos finito. Para confirmaciones inciertas, verifica primero usando identificadores de idempotencia. Consulta ejemplos.

• Operaciones externas: las transacciones locales no confirman/revierten atómicamente llamadas externas o envío de mensajes. Consulta ejemplos.

Control de concurrencia

• Actualizaciones condicionales de negocio: prefiere predicados de campo de negocio en tiempo de escritura. Al calcular nuevos valores a partir de datos leídos fuera de la transacción, incluye predicados que verifiquen los valores originales relevantes o un identificador de versión válido para evitar sobrescribir cambios concurrentes. No agregues transacciones explícitas cuando una sentencia condicional sea suficiente. Consulta ejemplos.

• Comprobaciones de timestamp: si faltan campos de negocio adecuados, compara el updated_at original solo si cada modificación relevante lo cambia y el valor antiguo no puede reutilizarse durante la validación. Preserva los milisegundos en lecturas, escrituras, serialización y vinculación. De lo contrario, usa un identificador de versión no reutilizado u otro mecanismo de concurrencia. Consulta ejemplos.

• Integridad referencial: usa actualizaciones condicionales, unicidad y transacciones necesarias para coordinar la creación/cambio de referencias contra la eliminación/invalidación del destino. Consulta ejemplos.

• Resultados de actualización: determina si el controlador informa filas coincidentes o cambiadas, luego evalúa si la escritura requerida tuvo éxito. Un predicado no cumplido no es éxito; cero filas cambiadas por sí solo no prueba un predicado no cumplido. Consulta ejemplos.

• Orden de escritura: las transacciones en competencia deben usar orden consistente de tabla/registro. El orden de texto SQL no establece el orden real de bloqueo. Consulta ejemplos.

Seguridad de base de datos

• Vincula los datos de aplicación como parámetros en lugar de concatenarlos en SQL.

• Mapea nombres de tabla/columna y direcciones de ordenación dinámicos a través de listas blancas de código en estructura SQL; los marcadores de posición de valor no pueden reemplazarlos.

Optimización de rendimiento

• Viajes de ida y vuelta de solicitud: reduce las solicitudes de base de datos por operación de negocio mientras preservas la corrección y el rendimiento general.

Optimización de índices

• Reutilización y cambios de índices: reutiliza índices en consultas primarias. No agregues índices meramente para nuevo SQL/predicados cuando las rutas existentes cumplan con los requisitos de rendimiento. Verifica otras consultas y restricciones únicas antes de fusionar o reemplazar. Consulta ejemplos.

• Campos de baja cardinalidad: no rechaces índices únicamente por pocos valores distintos. Considera la distribución objetivo, filtros combinados, ordenación, beneficios de cobertura, búsquedas en tabla y costos de escritura. Consulta ejemplos.

• Índices de prefijo: selecciona longitudes de prefijo de cadena a partir de datos reales. Los índices únicos de prefijo pueden rechazar valores completos distintos que compartan un prefijo y no pueden reemplazar la unicidad de valor completo. Consulta ejemplos.

• Índices compuestos: equilibra filtrado, reutilización de prefijo izquierdo, ordenación y agrupación. Las columnas de igualdad comúnmente preceden a las columnas de rango; no ordenes universalmente por selectividad. Consulta ejemplos.

• Costo de ordenación: filesort por sí solo no significa un mal índice. Compara los costos de ordenación, escaneos y mantenimiento de índices agregados. Consulta ejemplos.

• Columnas y expresiones indexadas: prefiere predicados de rango temporal directos para filtrado diario. Si son necesarias expresiones, evalúa índices funcionales o de columna generada coincidentes. Consulta ejemplos.

• Comodines iniciales: los índices B-tree ordinarios generalmente no pueden convertir búsquedas con comodín inicial en escaneos de rango efectivos. La búsqueda de texto completo o dedicada debe retener la semántica de coincidencia original. Consulta ejemplos.

Optimización de consultas

• Consultas por lotes: reemplaza consultas por fila fusionables con uniones o subconsultas. Convierte actualizaciones/eliminaciones individuales en sentencias de múltiples filas con listas fijas de valores o tablas temporales. Consulta ejemplos.

• Reutilización del mismo plan: para ejecución repetida de la misma consulta, úsalos con declaraciones preparadas. Esto no reemplaza la optimización de consultas en tiempo de ejecución o el almacenamiento en caché del plan de consultas. Consulta ejemplos.

• Desglose y combinación de intención: desglosa consultas de agregación complejas en subconsultas para reducir la magnitud de la unión, luego combina los resultados de subconsultas. En operaciones de paginación con unión, duplica el trabajo de filtrado y clasificación; primero recupera la clave primaria. Consulta ejemplos.

• Vistas materializadas y tablas de resumen: para tablas que mantienen recuentos agregados y valores de resumen, usa tablas de resumen materializadas. Mantenlas atómicamente en la misma transacción que los datos de origen; acepta la desviación de consistencia cuando sea necesario para el rendimiento. Consulta ejemplos.

Optimización de estadísticas

• Distribución de valores: para IN con longitud de lista que excede eq_range_index_dive_limit, analiza los planes de ejecución y la distribución de valores; ajusta el límite cuando sea necesario. Consulta ejemplos.

• Diferencias de cardinalidad: analiza el impacto en el plan de ejecución causado por parámetros de consulta de diferentes cardinalidades; evita planes de ejecución que se degraden para valores de baja selectividad. Consulta ejemplos.

• Histogramas de columnas: para distribuciones sesgadas o dependencias de columnas, ayuda al optimizador con histogramas o índices compuestos. Los histogramas son estáticos; recopílalos después de cambios de distribución significativos. Consulta ejemplos.

Optimización de escritura

• Inserción masiva: para cargas de datos grandes de una sola vez, usa LOAD DATA con configuración de tabla ajustada, manteniendo el comportamiento transaccional y de registro. No uses LOAD DATA para inserciones de aplicación. Evita operaciones de restauración o inserción masiva durante períodos de carga pico de negocio. Consulta ejemplos.

• Procesamiento de lotes: cuando se procesan lotes comerciales en una sola transacción, un lote grande corre el riesgo de tiempos de espera de bloqueo y desbordamiento de registro. Controla el tamaño del lote y monitorea los cuellos de botella de rendimiento. Consulta ejemplos.

• Actualizaciones masivas de tablas y cambios de esquema: para cambios de datos o esquema a gran escala, usa herramientas en línea como pt-online-schema-change. No bloquees tablas de producción con DDL directo durante el horario comercial. Consulta ejemplos.

Referencias

• Ejemplos de diseño MySQL