High Performance MySQL [Cap. 4: Optimización del esquema y los tipos de datos]

Tres reglas para elegir un tipo

• Más pequeño suele ser mejor. Elige el tipo más pequeño que admita todos los valores que de verdad vas a guardar. Cada byte se repite por fila, por índice y en memoria. Deja margen para el crecimiento que realmente puedes prever, pero no para fantasías, porque ensanchar una columna en una tabla grande es caro.
• Lo simple es bueno. Los enteros se comparan más rápido que las cadenas, que arrastran un juego de caracteres y una collation. Una dirección IPv4 es un número de 32 bits disfrazado: INET_ATON() y INET_NTOA() convierten entre ambas formas.
• Evita NULL cuando puedas. Las columnas que admiten NULL complican los índices, las estadísticas y las comparaciones. Cambiar un esquema existente solo por esto rara vez compensa; declarar las columnas nuevas NOT NULL por defecto, sí.

Elige primero la clase general (número, cadena, fecha/hora) y después el tipo concreto dentro de ella, comparando rango, precisión y tamaño.

Números

Los números enteros usan TINYINT, SMALLINT, MEDIUMINT, INT o BIGINT: 1, 2, 3, 4 y 8 bytes. UNSIGNED elimina los negativos y duplica el rango positivo con el mismo tamaño. El ancho de visualización de INT(11) nunca cambió el almacenamiento y está obsoleto en MySQL 8.

Para fracciones, FLOAT (4 bytes) y DOUBLE (8 bytes) son aproximados; DECIMAL es exacto y cuesta más espacio y CPU. Usa tipos exactos para el dinero. Un truco habitual para importes es guardar céntimos enteros en un BIGINT.

Cadenas

Las longitudes máximas generosas no salen gratis: cuando MySQL crea tablas temporales en memoria o buffers de ordenación, puede reservar espacio para el tamaño declarado, no para el real.

Fechas y horas

DATETIME cubre los años 1000 a 9999 y guarda el valor tal como se escribe. TIMESTAMP guarda los segundos desde la época Unix en UTC, convierte desde y hacia la zona horaria de la sesión y se acaba en 2038. Ambos aceptan fracciones de segundo, por ejemplo DATETIME(3) para milisegundos. Una convención simple y habitual es guardar UTC en DATETIME y resolver la lógica de zonas horarias en la aplicación.

Para flags y conjuntos de bits, BIT y SET empaquetan booleanos de forma compacta, a cambio de ser incómodos de consultar.

Elegir identificadores

• El mismo tipo a ambos lados de un join. Mezclar INT y BIGINT UNSIGNED, o números y cadenas, obliga a hacer conversiones y puede impedir el uso de índices.
• Los enteros con AUTO_INCREMENT son la opción por defecto: pequeños, rápidos de comparar y añadidos en orden al índice agrupado.
• Evita ENUM y SET como claves, y evita las cadenas largas, que son lentas de comparar y engordan todos los índices secundarios.
• Las claves aleatorias, como los UUID, se insertan en lugares aleatorios del B-Tree: divisiones de página, fragmentación y un buffer pool frío. Si las necesitas, guárdalas como BINARY(16) (UUID_TO_BIN(uuid, 1) de MySQL 8 además reordena los bits de tiempo para que las inserciones sigan siendo más o menos secuenciales).

Trampas de esquema propias de MySQL

• Tablas muy anchas. El servidor e InnoDB intercambian filas en un buffer que hay que decodificar columna a columna. Con cientos de columnas, esa conversión aparece como consumo de CPU.
• Demasiados joins. Los diseños entidad-atributo-valor convierten cada lectura en un montón de self-joins. MySQL limita un join a 61 tablas, y el rendimiento se degrada mucho antes.
• ENUM como objetivo cambiante. Si la lista de valores cambia a menudo, una tabla de búsqueda es más fácil de evolucionar.
• NULL frente a valores mágicos. Evita los NULL innecesarios, pero no sustituyas un "desconocido" genuino por valores centinela como 0 o '1970-01-01' que luego las consultas tienen que tratar como casos especiales.

Normalizado o desnormalizado

Un esquema normalizado guarda cada dato una sola vez. Las actualizaciones tocan una fila, las tablas son estrechas y caben en memoria, y no hace falta DISTINCT para deshacer duplicados. El precio son joins en casi todas las lecturas.

Un esquema desnormalizado copia los datos allí donde se leen. Las lecturas se ahorran los joins y, lo que a menudo importa más, los filtros y las columnas de orden pueden vivir en una misma tabla y compartir un índice. Imagina un gestor de incidencias que lista los bugs más recientes de clientes de pago. Si plan solo está en la tabla de clientes, MySQL tiene que recorrer los bugs por fecha y comprobar cada cliente, o bien traer todos los bugs de clientes de pago y ordenarlos. Copia plan en la tabla de bugs y un índice sobre (plan, created_at) responde la consulta directamente.

La respuesta habitual es una mezcla: normalizar, luego duplicar las pocas columnas que necesitan las lecturas más frecuentes y mantenerlas sincronizadas desde la aplicación o con triggers. Duplicar sale más barato con valores que rara vez cambian.

Tablas de caché y de resumen

Cuando se acepta cierto desfase, crea tablas separadas pensadas para la lectura. Una tabla de caché guarda copias desnormalizadas de datos de otras tablas; una tabla de resumen guarda agregados precalculados, como bugs por proyecto y por día, que se refrescan de forma programada. Las lecturas se convierten en búsquedas baratas y el coste pasa a un proceso en segundo plano. Las tablas de contadores, a veces repartidas en varias filas para evitar contención de bloqueos sobre una única fila muy disputada, son un pariente cercano.

Nada es definitivo

Los esquemas cambian, y en tablas grandes cambiarlos es la parte cara. Muchas operaciones ALTER TABLE de MySQL 8 se ejecutan in situ o al instante (añadir una columna suele ser ALGORITHM=INSTANT), pero otras todavía reconstruyen la tabla copiando cada fila, lo que exige hasta el doble de disco y horas de trabajo. Para esos casos, los equipos usan herramientas de cambio de esquema en línea (pt-online-schema-change, gh-ost) que construyen una tabla sombra y la intercambian, o aplican el cambio primero en las réplicas y luego hacen failover.

Preguntas frecuentes