MySQL ¿La normalización de una base de datos reduce su velocidad o la aumenta y puede normalizarse en exceso?

La normalización no se trata del rendimiento en absoluto. Se trata de evitar anomalías en los datos.

Se produce una anomalía de datos, por ejemplo, si almacena los mismos datos en dos tablas diferentes sin algún tipo de integridad referencial que los mantenga sincronizados. Corre el riesgo de anomalías en los datos cuando tiene que almacenar datos de forma redundante. Los datos redundantes son típicos de un diseño de datos desnormalizados.

La desnormalización se optimiza para ciertas consultas, pero lo hace a expensas de otras consultas con los mismos datos.

Ejemplo: supongamos que tengo una base de datos con dos tablas, Libros y Autores. Un libro puede tener múltiples autores, y un autor puede escribir varios libros. Decido almacenar en la tabla Libros una lista de los autores del libro, para que sea más fácil y rápido consultar un libro con todos sus autores.

Pero, ¿qué sucede si a veces también quiero consultar a un autor y obtener una lista de los libros de ese autor? Hacer una unión a la tabla Libros y buscar en una lista de autores es una consulta que no se puede indexar. Entonces es probable que sea lento.

Por lo tanto, desnormalizar a los autores en la tabla de libros ayudó a la consulta de libros, pero perjudicó la consulta de autores.

Así que podría solucionar esto almacenando también la lista de los libros de cada autor en la tabla Autores. Pero ahora tengo la autoría almacenada en dos lugares, y es posible que no estén sincronizados. Si veo datos almacenados en un lado u otro, ¿cómo puedo saber cuál es el correcto? ¿Cómo podría incluso buscar en toda la base de datos tales anomalías?

¿Se puede normalizar una base de datos?

Es una compensación. Puede optar por utilizar la desnormalización de vez en cuando, para optimizar ciertas consultas. Los tres escenarios que usaría para la desnormalización son:

• Almacene una columna para los resultados de una expresión compleja, para evitar calcular esa expresión en cada consulta.
• Almacene una copia de una columna de atributos en otra tabla para evitar unirse en cada consulta donde necesite esos datos.
• Almacene el resultado de una expresión agregada (como SUM () o AVG ()) para evitar ejecutar consultas grupales.

En los tres casos, usted asume la responsabilidad de mantener esos valores redundantes sincronizados con los datos originales. Esto aumenta la complejidad de su aplicación y sus operaciones de base de datos. Aumenta el riesgo de que los datos se desincronicen y entreguen resultados incorrectos.

Por lo tanto, siempre que abandone un diseño de base de datos normalizado, tenga esto en cuenta y use la desnormalización solo cuando esté seguro de que le brinda un gran beneficio.

Esta es una pregunta genérica de diseño de base de datos y no una pregunta específica de MySQL.

En general, es un buen consejo apuntar primero a 3NF en el diseño del esquema y ver qué sucede.

Para ver qué sucede, debe probar la carga de producción real y medir. No lo adivines. Adivinar las respuestas a las preguntas de desempeño es un hábito muy malo y crea problemas en lugar de resolverlos.

Lo que generalmente sucederá es que en algunos lugares tienes relaciones 1: n donde estás calculando muchos agregados de n grandes para saber algo para mostrar a un lado el 1. Esta es una desnormalización muy común.

Un ejemplo sería un buzón con carpetas de correos. Para enumerar todas las carpetas con el número de mensajes y el tamaño de cada carpeta al lado, debe sumar el recuento y el tamaño de todos los correos en cada carpeta. Eso es pesado, y le recomendamos que calcule los valores solo una vez y los almacene en caché en columnas en la tabla de carpetas para acelerar las cosas.

Por supuesto, esto eventualmente se romperá y también se recomienda escribir un script que recalcule todos estos valores desde cero y estimar el tiempo de ejecución de ese script y hacerlo antes de que se rompa (por ejemplo, al mismo tiempo que realiza esa optimización) .

Si pasa 3NF, es probable que se una a la muerte. Específicamente, hay un esquema sobrenormalizado común que puede encontrar muy a menudo en el contexto de las tiendas web.

Las tiendas web necesitan almacenar artículos. Los artículos tienen atributos. Los atributos que tienen los artículos depende en gran medida del tipo (clase) del artículo.

Todos los artículos vendibles tienen una cantidad en existencia y un precio.

Algunos artículos son ropa. Tienen tamaños (y también todos los atributos de las ventas).

Algunas prendas son prendas exteriores femeninas. Aquí los colores son importantes (y también todos los atributos de la ropa).

Los diseñadores de tiendas web no quieren limitarse a una jerarquía de artículos específica y necesitan un esquema flexible que pueda implementar un árbol de artículos de clase arbitraria con atributos arbitrarios en todos los niveles.

Los diseñadores de tiendas web también piensan que DDL es malo y no tiene lugar en la producción.

Entonces intentan llegar con un esquema lo más genérico posible.

Este esquema siempre se ve así: (article_id, attr_name, attr_type, bool, int, real, string)

Dependiendo de attr_type, bool, int, real o string tienen un valor. Los otros campos son NULL. Tienes una tienda de KV escrita con combinaciones.

Esto termina con una unión por atributo en muchas consultas útiles. Entonces te mueres.

Una solución 3NF crearía el árbol de clases de tipos de artículos y luego generaría DDL para generar las tablas necesarias.

Luego generaría consultas adecuadas aprovechando las relaciones 1: 1 o 1: 0 para crear los resultados requeridos con un número mínimo de uniones y relaciones entre tablas fácilmente optimizables.

No es particularmente conocedor de MYSQL, por lo que una discusión genérica:

Si tiene que hacer una unión para cada consulta, es posible que esté demasiado normalizado. Hay compensaciones entre CPU y E / S. Si el conjunto de trabajo cabe en la memoria, las uniones están vinculadas a la CPU. Si el conjunto de trabajo no encaja, estás obligado a realizar la E / S. Puede darse el caso de que la duplicación juiciosa de algunas columnas en las tablas que caben en la memoria reduzca el número de uniones necesarias, reduciendo la CPU y, por lo tanto, sea más rápido. El costo está en tener que actualizar varias tablas para las columnas duplicadas, que tiene su propio costo y complejidades. Una forma de mover el costo es crear la tabla desnormalizada como una vista materializada.

Una base de datos definitivamente puede sobre normalizarse. Por ejemplo, tuve un proyecto en el que estaba usando datos de varios archivos de datos del Censo que se habían agregado para varios factores demográficos. Así que al final tenía una tabla para los valores reales y luego una tabla separada para cada uno de los factores que conducen a ese valor, por ejemplo, año, raza, etnia, educación, etc. Sin embargo, en la práctica esto conduce a múltiples uniones en el mismo mesa. Esto tuvo el efecto de aumentar el tiempo de consulta a medida que aumentó el número de factores que resultaron inaceptables en el extremo superior. En nuestro caso, no había una gran manera de desnormalizar selectivamente los datos, por lo que terminamos yendo a una base de datos no relacional y nuestras consultas se convirtieron en un tiempo constante independientemente del número de factores.