Desnormalización de tablas en la base de datos.

8.1 ¿Por qué es necesaria la desnormalización?

La operación más costosa desde el punto de vista computacional entre tablas grandes es la unión. En consecuencia, si en una consulta es necesario "ventilar" varias tablas que constan de muchos millones de filas, el DBMS dedicará mucho tiempo a dicho procesamiento.

El usuario en este momento puede alejarse para tomar café. La interactividad del procesamiento prácticamente desaparece y se acerca a la del procesamiento por lotes. Peor aún, en el modo por lotes, el usuario recibe todos los datos solicitados el día anterior por la mañana y trabaja tranquilamente con ellos, preparando nuevas solicitudes para la noche.

Para evitar la situación de uniones pesadas, las tablas se desnormalizan. Pero no de todos modos. Hay algunas reglas que le permiten considerar las tablas desnormalizadas transaccionalmente como "normalizadas" de acuerdo con las reglas para construir tablas para almacenes de datos.

Hay dos esquemas principales que se consideran "normales" en el procesamiento analítico: "copo de nieve" y "estrella". Los nombres reflejan bien la esencia y se derivan directamente de la imagen de las tablas relacionadas.

En ambos casos, las llamadas tablas de hechos son el elemento central del esquema, que contiene los eventos, transacciones, documentos y otras cosas interesantes de interés para el analista. Pero si en una base de datos transaccional un documento está "difundido" en varias tablas (al menos dos: encabezados y filas de contenido), entonces en la tabla de hechos corresponde un documento, más precisamente, cada una de sus filas o un conjunto de filas agrupadas. a un registro.

Esto se puede hacer desnormalizando las dos tablas anteriores.

8.2 Ejemplo de desnormalización

Ahora puede evaluar cuánto más fácil será para el DBMS ejecutar una consulta, por ejemplo, del siguiente tipo: para determinar el volumen de ventas de harina a los clientes de Pirozhki LLC y Vatrushki CJSC para el período.

En una base de datos transaccional normalizada:

SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

En la base de datos analítica:

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

En lugar de una unión pesada entre dos tablas de documentos y su composición con millones de filas, el DBMS trabaja directamente con la tabla de hechos y uniones ligeras con pequeñas tablas auxiliares, de las que también puede prescindir conociendo los identificadores.

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Volvamos a los esquemas de "estrella" y "copo de nieve". Detrás de escena de la primera imagen había mesas de clientes, sus grupos, tiendas, vendedores y, de hecho, mercancías. Cuando se desnormaliza, estas tablas, denominadas dimensiones, también se unen a la tabla de hechos. Si la tabla de hechos hace referencia a tablas de dimensiones que tienen enlaces a otras dimensiones (dimensiones del segundo nivel y superiores), dicho esquema se denomina "copo de nieve".

Como puede ver, para consultas que incluyen filtrado por grupos de clientes, debe realizar una conexión adicional.

SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

En este caso, la desnormalización puede continuar y colocar la dimensión de segundo nivel en la primera, lo que facilita la consulta de la tabla de hechos.

Un esquema en el que una tabla de hechos solo hace referencia a dimensiones que no tienen un segundo nivel se denomina esquema en estrella. El número de tablas de medidas corresponde al número de "rayos" en la estrella.

El esquema en estrella elimina por completo la jerarquía de dimensiones y la necesidad de unir las tablas correspondientes en una sola consulta.

SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

La desventaja de la desnormalización siempre es la redundancia , lo que provoca un aumento en el tamaño de la base de datos tanto en aplicaciones transaccionales como analíticas. Calculemos un delta aproximado en el ejemplo anterior de convertir "copo de nieve" en "estrella".

En algunos DBMS, como Oracle, no hay tipos enteros especiales a nivel de definiciones de esquema de base de datos, debe usar el tipo booleano genérico numeric(N), donde N es el número de bits almacenados. El tamaño de almacenamiento de dicho número se calcula utilizando una fórmula especial proporcionada en la documentación para el almacenamiento de datos físicos y, como regla, supera el de los tipos de bajo nivel como "entero de 16 bits" en 1-3 bytes.

Supongamos que la tabla de ventas no usa compresión de datos y contiene alrededor de 500 millones de filas, y el número de grupos de clientes es de alrededor de 1000. En este caso, podemos usar un número entero corto (shortint, smallint) que ocupa 2 bytes como tipo de identificador id_customer_group.

Asumiremos que nuestro DBMS admite un tipo entero de dos bytes (por ejemplo, PostgreSQL, SQL Server, Sybase y otros). Luego, agregar la columna correspondiente id_customer_groupa la tabla de ventas aumentará su tamaño en al menos 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 ¿Cuándo es necesaria la desnormalización?

Veamos algunas situaciones comunes donde la desnormalización puede ser útil.

Gran número de combinaciones de tablas

En consultas a una base de datos completamente normalizada, a menudo tiene que unir hasta una docena o incluso más tablas. Y cada conexión es una operación que consume muchos recursos. Como resultado, dichas solicitudes consumen recursos del servidor y se realizan con lentitud.

En tal situación, puede ayudar:

• desnormalización al reducir el número de tablas. Es mejor combinar en una varias tablas que sean de tamaño pequeño, que contengan información que rara vez cambia (como suele decirse, condicionalmente constante o de referencia) e información que está estrechamente relacionada en significado.
• En general, si necesita unir más de cinco o seis tablas en una gran cantidad de consultas, debe considerar desnormalizar la base de datos.
• Desnormalización agregando un campo adicional a una de las tablas. En este caso, aparece la redundancia de datos, se requieren acciones adicionales para mantener la integridad de la base de datos.

Valores estimados

A menudo, las consultas son lentas y consumen muchos recursos, en las que se realizan algunos cálculos complejos, especialmente cuando se utilizan agrupaciones y funciones agregadas (Sum, Max, etc.). A veces tiene sentido agregar 1 o 2 columnas adicionales a la tabla que contienen datos calculados que se usan con frecuencia (y son difíciles de calcular).

Suponga que desea determinar el costo total de cada pedido. Para ello, primero debe determinar el costo de cada producto (según la fórmula "número de unidades del producto" * "precio unitario del producto" - descuento). Después de eso, debe agrupar los costos por pedidos.

Ejecutar esta consulta es bastante complejo y, si la base de datos almacena información sobre una gran cantidad de pedidos, puede llevar mucho tiempo. En lugar de ejecutar dicha consulta, puede determinar su costo en la etapa de realizar un pedido y almacenarlo en una columna separada de la tabla de pedidos. En este caso, para obtener el resultado deseado, basta con extraer los valores precalculados de esta columna.

Crear una columna que contenga valores precalculados ahorra mucho tiempo al ejecutar una consulta, pero requiere que actualice los datos en esa columna de manera oportuna.

ala larga

Si tenemos tablas grandes en la base de datos que contienen campos largos (Blob, Long, etc.), podemos acelerar seriamente la ejecución de consultas a dicha tabla si movemos los campos largos a una tabla separada. Queremos, por ejemplo, crear un catálogo de fotos en la base de datos, incluido el almacenamiento de las fotos en campos blob (calidad profesional, alta resolución y tamaño adecuado). Desde el punto de vista de la normalización, la siguiente estructura de tabla sería absolutamente correcta:

• identificación fotográfica
• Identificación del autor
• Identificación del modelo de cámara
• la foto en sí (campo blob)

Y ahora imaginemos cuánto tiempo se ejecutará la consulta, contando la cantidad de fotos tomadas por cualquier autor ...

La solución correcta (aunque violando los principios de normalización) en tal situación sería crear otra tabla que constara de solo dos campos: la identificación con foto y un campo de blob con la foto en sí. Luego, las selecciones de la tabla principal (en la que ya no hay un campo de blob enorme) irán al instante, pero cuando queramos ver la foto en sí, bueno, esperemos ...

¿Cómo determinar cuándo se justifica la desnormalización?

8.4 Pros y contras de la desnormalización

Una forma de determinar si ciertos pasos están justificados es realizar un análisis en términos de costos y posibles beneficios. ¿Cuánto costará un modelo de datos desnormalizados?

Determinar los requisitos (lo que queremos lograr) → determinar los requisitos de datos (lo que debemos seguir) → encontrar el paso mínimo que satisfaga estos requisitos → calcular los costos de implementación → implementar.

Los costos incluyen aspectos físicos como el espacio en disco, los recursos necesarios para administrar esta estructura y las oportunidades perdidas debido a los retrasos asociados con el mantenimiento de este proceso. Tienes que pagar por la desnormalización. Una base de datos desnormalizada aumenta la redundancia de datos, lo que puede mejorar el rendimiento pero requiere más esfuerzo para controlar los datos relacionados. El proceso de creación de aplicaciones será más difícil, ya que los datos se repetirán y serán más difíciles de rastrear. Además, la implementación de la integridad referencial no es fácil: los datos relacionados se dividen en diferentes tablas.

Los beneficios incluyen un rendimiento de consulta más rápido y la capacidad de obtener una respuesta más rápida. También puede obtener otros beneficios, incluido un mayor rendimiento, satisfacción del cliente y productividad, así como un uso más eficiente de las herramientas de desarrollo externas.

Tasa de solicitud y consistencia del rendimiento

Por ejemplo, el 72% de las 1000 consultas generadas diariamente por una empresa son consultas de nivel de resumen, no consultas detalladas. Cuando se utiliza una tabla de resumen, las consultas se ejecutan en aproximadamente 6 segundos en lugar de 4 minutos, lo que resulta en 3000 minutos menos de tiempo de procesamiento. Incluso después de ajustar los 100 minutos que deben dedicarse al mantenimiento de las tablas dinámicas cada semana, se ahorran 2500 minutos por semana, lo que justifica la creación de la tabla dinámica. Con el tiempo, puede suceder que la mayoría de las consultas no se dirijan a datos resumidos, sino a datos detallados. Cuantas menos consultas utilicen la tabla de resumen, más fácil será eliminarla sin afectar a otros procesos.

Y…

Los criterios enumerados anteriormente no son los únicos que se deben tener en cuenta al decidir si se debe dar el siguiente paso en la optimización. Es necesario considerar otros factores, incluidas las prioridades comerciales y las necesidades del usuario final. Los usuarios deben comprender cómo, desde un punto de vista técnico, la arquitectura del sistema se ve afectada por el requisito de los usuarios que desean que todas las solicitudes se completen en unos segundos. La forma más fácil de lograr este entendimiento es delinear los costos asociados con la creación y administración de dichas tablas.

8.5 Cómo implementar de forma competente la desnormalización.

Guardar tablas detalladas

Para no limitar las capacidades de la base de datos que son importantes para el negocio, es necesario adoptar una estrategia de coexistencia, no de reemplazo, es decir, mantener tablas detalladas para un análisis profundo, agregándoles estructuras desnormalizadas. Por ejemplo, el contador de visitas. Para negocios, necesitas saber el número de visitas a una página web. Pero para el análisis (por época, por país...) muy probablemente necesitaremos datos detallados, una tabla con la información de cada visita.

Uso de disparadores

Es posible desnormalizar la estructura de una base de datos y seguir disfrutando de los beneficios de la normalización mediante el uso de disparadores de base de datos para preservar la integrityintegridad de los datos duplicados.

Por ejemplo, al agregar un campo calculado, cada una de las columnas de las que depende el campo calculado, se cuelga con un disparador que llama a un único procedimiento almacenado (¡esto es importante!), que escribe los datos necesarios en el campo calculado. Solo es necesario no saltarse ninguna de las columnas de las que depende el campo calculado.

Soporte de software

Si no utiliza disparadores integrados y procedimientos almacenados, los desarrolladores de aplicaciones deben asegurarse de garantizar la coherencia de los datos en una base de datos desnormalizada.

Por analogía con los disparadores, debe haber una función que actualice todos los campos que dependen del campo que se cambia.

conclusiones

Al desnormalizar, es importante mantener un equilibrio entre aumentar la velocidad de la base de datos y aumentar el riesgo de datos inconsistentes, entre facilitar la vida de los programadores que escriben Select-sy complicar la tarea de aquellos que proporcionan la población de la base de datos y las actualizaciones de datos. Por lo tanto, es necesario desnormalizar la base de datos con mucho cuidado, muy selectivamente, solo donde sea indispensable.

Si es imposible calcular los pros y los contras de la desnormalización por adelantado, inicialmente es necesario implementar un modelo con tablas normalizadas y solo entonces, para optimizar las consultas problemáticas, llevar a cabo la desnormalización.

Es importante introducir la desnormalización gradualmente y solo para aquellos casos en los que se realicen búsquedas repetidas de datos relacionados de diferentes tablas. Recuerde, al duplicar datos, la cantidad de registros aumentará, pero la cantidad de lecturas disminuirá. También es conveniente almacenar los datos calculados en columnas para evitar selecciones agregadas innecesarias.