Prefacio

Este documento trata sobre la creación y mantenimiento de “Tablas de resumen”. Es un complemento del documento sobre técnicas de almacenamiento de datos.

La terminología básica (“Tabla de hechos”, “Normalización”, etc.) se cubre en ese documento.

Tablas de resumen para “informes” de almacenamiento de datos

Las tablas de resumen son una necesidad de rendimiento para tablas grandes. MariaDB y MySQL no proporcionan ninguna forma automatizada de crearlo, por lo que proporciono técnicas aquí.

(Otros proveedores ofrecen algo similar con “vistas materializadas”).

Cuando tiene millones o miles de millones de filas, lleva mucho tiempo resumir los datos para presentar recuentos, totales, promedios, etc., en un tamaño que los humanos puedan digerir fácilmente. Al calcular y guardar subtotales a medida que ingresan los datos, se puede hacer que los “informes” se ejecuten mucho más rápido. (He visto aumentos de velocidad de 10x a 1000x). Los subtotales van en una “tabla de resumen”. Este documento lo guía sobre la eficiencia tanto en la creación como en el uso de dichas tablas.

Estructura general de una tabla resumen

Una tabla de resumen incluye dos conjuntos de columnas:

  • CLAVE principal: fecha + algunas dimensiones
  • Subtotales: COUNT

, SUM (…), …; pero no AVG ()

   FLOOR(UNIX_TIMESTAMP(dt)/3600)
   FROM_UNIXTIME(hour*3600)

La “fecha” puede ser una FECHA (un tipo de datos nativo de 3 bytes), una hora o algún otro intervalo de tiempo. Una ‘hora’ MEDIUMINT SIN FIRMAR de 3 bytes se puede derivar de un DATETIME o TIMESTAMP a través de

Las “dimensiones” (un término DW) son algunas de las columnas de la tabla “Hecho”. Ejemplos: País, Marca, Producto, Categoría, Host Ejemplos sin dimensión: Ventas, Cantidad, Tiempo invertido

Habría uno o más índices, generalmente comenzando con algunas dimensiones y terminando con el campo de fecha. Al terminar con la fecha, se puede obtener de manera eficiente un rango de días / semanas / etc. incluso cuando cada fila resume solo un día.

Normalmente habrá unas “pocas” tablas de resumen. A menudo, una tabla de resumen puede servir para múltiples propósitos de manera suficientemente eficiente.

Como regla general, una tabla de resumen tendrá una décima parte del número de filas que la tabla de hechos. (Este número está muy suelto).

Ejemplo

PRIMARYKEY(city,datetime),
   Aggregations: ct, sum_price
   
   # Core of INSERT..SELECT:DATE(datetime)ASdate, city,COUNT(*)AS ct,SUM(price)AS sum_price
   
   # Reporting average price for last month, broken down by city:SELECT city,SUM(sum_price)/SUM(ct)AS'AveragePrice'FROM SalesSummary
      WHEREdatetimeBETWEEN...GROUPBY city;# Monthly sales, nationwide, from same summary table:SELECTMONTH(datetime)AS'Month',SUM(ct)AS'TotalSalesCount'SUM(sum_price)AS'TotalDollars'FROM SalesSummary
      WHEREdatetimeBETWEEN...GROUPBYMONTH(datetime);# This might benefit from a secondary INDEX(datetime)

Hablemos de una gran cadena de concesionarios de automóviles. La tabla de hechos tiene todas las ventas con columnas como fecha y hora, salesman_id, ciudad, precio, customer_id, marca, modelo, modelo_año. Una tabla de resumen podría centrarse en las ventas:

¿Cuándo aumentar la (s) tabla (s) resumen?

“Aumentar” en esta sección significa agregar nuevas filas a la tabla de resumen o incrementar los recuentos en las filas existentes.

Plan A: “Mientras inserta” filas en la tabla de hechos, aumente la (s) tabla (s) de resumen. Esto es simple y viable para una base de datos DW más pequeña (menos de 10 filas de la tabla de hechos por segundo). Para bases de datos DW más grandes, es probable que el Plan A sea demasiado costoso para ser práctico.

Plan B: “Periódicamente”, vía cron o un EVENTO.

Plan C: “Según sea necesario”. Es decir, cuando alguien solicita un informe, el código primero actualiza las tablas de resumen que serán necesarias.

Plan D: “Híbrido” de B y C. C, por sí solo, puede provocar grandes retrasos en el informe. Al hacer también B, esos retrasos se pueden mantener bajos.

Plan E: (Esto no se recomienda). “Reconstruya” la tabla de resumen completa a partir de la tabla de hechos completa. El costo de esto es prohibitivo para mesas grandes. Sin embargo, es posible que necesite el Plan E cuando decida cambiar las columnas de una Tabla de resumen o descubra una falla en los cálculos. Para reducir el impacto de una compilación completa, adapte las técnicas de fragmentación en Eliminar en fragmentos.

Plan F: “Tabla de etapas”. Esto es principalmente para ingestión de muy alta velocidad. Se menciona brevemente en este blog y se analiza más a fondo en el blog complementario: Ingestión de alta velocidad

INSERTINTO Fact ...;INSERTINTO Summary (..., ct, foo,...)VALUES(...,1, foo,...)ONDUPLICATEKEYUPDATE ct = ct+1, sum_foo = sum_foo +VALUES(foo),...;

Resumir mientras se inserta (una fila a la vez)

IODKU (Insertar en actualización de clave duplicada) actualizará una fila existente o creará una nueva fila. Sabe qué hacer basándose en la CLAVE PRINCIPAL de la tabla Resumen.

Precaución: este enfoque es costoso y no se escalará a una tasa de ingestión de más de, digamos, 10 filas por segundo (o quizás 50 / segundo en SSD). Más discusión más tarde.

Resumir periódicamente frente a según sea necesario

Si sus informes deben estar actualizados al segundo, necesita “según sea necesario” o “híbrido”. Si sus informes tienen menos urgencia (por ejemplo, informes semanales que no incluyen “hoy”), entonces “periódicamente” podría ser lo mejor.

Para los resúmenes diarios, se pueden aumentar las tablas de resumen justo después de la medianoche. Pero tenga cuidado con los datos que llegan “tarde”.

Tanto para “periódicos” como “según sea necesario”, necesita una forma definitiva de realizar un seguimiento de dónde “lo dejó”.

Caso 1: Primero inserta en la tabla de hechos y tiene una identificación AUTO_INCREMENT: tome MAX (id) como el límite superior para resumir y colóquelo en algún otro lugar seguro (una tabla adicional), o colóquelo en la fila ( s) en la tabla Resumen a medida que los inserta. (Advertencia: los identificadores AUTO_INCREMENT no funcionan bien en configuraciones multimaestro, incluida Galera).

Caso 2: si está utilizando una tabla de ‘preparación’, no hay problema. (Más sobre las tablas de preparación más adelante).

Resumiendo mientras se inserta por lotes

Esto se aplica a INSERTAR y CARGAR DATOS de varias filas (lotes).

La tabla de hechos necesita un ID AUTO_INCREMENT, y necesita poder encontrar el rango exacto de ID insertados. (Esto puede resultar poco práctico en cualquier configuración multimaestro).

FROM Fact
   WHERE id BETWEEN min_id and max_id

Luego, realice un resumen masivo usando

Resumiendo cuando se usa una tabla de preparación [[data-warehousing-high-speed-ingestion|HighSpeedIngestion

Cargue los datos (a través de INSERT o LOAD DATA) en masa en una “tabla de etapas”. Luego, realice el resumen de lotes de la tabla de etapas. Y copia por lotes de la tabla de etapas a la tabla de hechos. Tenga en cuenta que la tabla Staging es útil para la “normalización” por lotes durante la ingestión. Consulte también[data-warehouse-high-speed-ingestion|HighSpeed​​Ingestion

Tabla resumen: ¿PK o no?

Supongamos que su tabla de resumen tiene una FECHA, “dy” y una dimensión, “foo”. La pregunta es: ¿Debería (foo, dy) ser la CLAVE PRIMARIA? ¿O un índice no ÚNICO?

Caso 1: CLAVE PRIMARIA (foo, dy) y el resumen está en el paso de bloqueo con, digamos, cambios en `dy`.

Este caso es limpio y simple, hasta que llega a los casos finales. ¿Cómo manejará el caso de que los datos lleguen ‘tarde’? ¿Quizás deba volver a calcular algunos fragmentos de datos? ¿Si es así, cómo?

Caso 2: (foo, dy) es un ÍNDICE NO ÚNICO.

Este caso es limpio y simple, pero puede saturar la tabla de resumen porque pueden ocurrir múltiples filas para un par dado (foo, dy). El informe siempre tendrá que SUMAR () los valores superiores porque no puede asumir que solo hay una fila, incluso cuando informa sobre un solo “foo” para un solo “dy”. Esta suma forzada no es realmente mala, debería hacerlo de todos modos; de esa manera, todos sus informes se escriben con un patrón.

Caso 3: CLAVE PRIMARIA (foo, dy) y el resumen puede ocurrir en cualquier momento.

   id INTUNSIGNEDAUTO_INCREMENTNOTNULL,...PRIMARYKEY(foo, dy, id),-- `id` added to make uniqueINDEX(id)-- sufficient to keep AUTO_INCREMENT happy

Dado que debería usar InnoDB, debe haber una CLAVE PRIMARIA explícita. Un enfoque cuando no tiene un PK ‘natural’ es este:

Este caso empuja la complejidad al resumen haciendo un IODKU.

¿Consejo? Evite el Caso 1; muy desordenado. El caso 2 está bien si las filas adicionales no son demasiado comunes. El caso 3 puede ser el más cercano a “una talla única”.

Promedios, etc. COUNT(*) AS ct and SUM(foo) AS sum_fooAl resumir, incluya

. Al informar, el “promedio” se calcula como SUM (sum_foo) / SUM (ct). Eso es matemáticamente correcto.

Excepción … Digamos que está mirando las temperaturas meteorológicas. Y su estación de monitoreo recibe la temperatura periódicamente, pero de manera poco confiable. Es decir, varía el número de lecturas de un día. Además, decide que la forma más fácil de compensar la inconsistencia es hacer algo como: Calcule la temperatura promedio para cada día, luego promedie las del mes (u otro período de tiempo).

    SQRT(SUM(sum_foo2)/SUM(ct)- POWER(SUM(sum_foo)/SUM(ct),2))

Fórmula para la desviación estándar:

Donde sum_foo2 es SUM (foo * foo) de la tabla de resumen. sum_foo y sum_foo2 deben ser FLOAT. FLOAT le da aproximadamente 7 dígitos significativos, que es más que suficiente para cosas como la desviación estándar y promedio. FLOAT ocupa 4 bytes. DOBLE le daría más precisión, pero ocupa 8 bytes. INT y BIGINT no son prácticos porque pueden generar quejas sobre desbordamiento.

Mesa de preparación

  • La idea aquí es cargar primero un conjunto de registros de hechos en una “tabla de preparación”, con las siguientes características (al menos):
  • La tabla se llena y trunca repetidamente
  • Las inserciones pueden ser individuales o por lotes, y de uno o varios clientes.
  • Los SELECT serán escaneos de tablas, por lo que no se necesitan índices
  • La inserción será rápida (InnoDB puede ser la más rápida)
  • La normalización se puede hacer a granel, por lo tanto, de manera eficiente
  • Copiar a la tabla de hechos será rápido
  • El resumen se puede hacer a granel, por lo tanto, de manera eficiente
  • La ingestión “en ráfagas” se suaviza mediante este proceso.

Flip-flop un par de mesas de preparación

Si tiene inserciones masivas (INSERTAR por lotes o CARGAR DATOS), considere realizar la normalización y el resumen inmediatamente después de cada inserción masiva.

Más detalles: Ingestión de alta velocidad

Diseño extremo

  • Aquí hay una forma más compleja de diseñar el sistema, con el objetivo de escalar aún más.
  • Utilice la configuración maestro-esclavo: ingesta en maestro; informe de esclavo (s).
  • Ingestión de alimento a través de una tabla de clasificación (como se describe arriba)
  • Fuente única de datos: MOTOR = MEMORIA; múltiples fuentes: InnoDB
  • binlog_format = FILA
  • Utilice binlog_ignore_db para evitar replicar la puesta en escena, lo que requiere ponerlo en una base de datos separada.
  • Hacer el resumen de Staging

Cargar hecho a través de INSERT INTO Fact … SELECT FROM Staging …

  • Explicación y comentarios:
  • ROW + ignore_db evita replicar el Staging, pero replica los INSERT basados ​​en él. Por lo tanto, aligera la carga de escritura en los esclavos.
  • Si usa MEMORIA, recuerde que es volátil: recupérese de un bloqueo comenzando de nuevo la ingestión.
  • Para ayudar con la depuración, TRUNCATE o RE-CREATE Staging al comienzo del siguiente ciclo.

La puesta en escena no necesita índices: todas las operaciones leen todas sus filas.

Estadísticas sobre el sistema del que proviene este ‘diseño extremo’: Tabla de hechos: 450GB, 100M filas / día (lote de 4M / hora), retención de 60 días (60 + 24 particiones), 75B / fila, 7 tablas de resumen, menos de 10 minutos para ingerir y resumir el lote por hora. El INSERT..SELECT manejó más de 20K filas / seg entrando en la tabla de hechos. Unidades giratorias (no SSD) con RAID-10.

“Parado”

Una técnica consiste en resumir algunos de los datos y luego registrar donde “lo dejó”, para que la próxima vez pueda comenzar allí. Hay algunos problemas sutiles con “dejado fuera” de los que debe tener cuidado.

Si usa DATETIME o TIMESTAMP como “dejado”, tenga cuidado con varias filas con el mismo valor.

  • Plan A: use un compuesto “dejado fuera” (por ejemplo, TIMESTAMP + ID). Esto es complicado, propenso a errores, etc.
  • Plan B: DONDE ts> = $ left_off AND ts <$ max_ts - evita dups, pero tiene otros problemas (abajo)
  • Los subprocesos separados podrían COMMIT TIMESTAMPs fuera de servicio.

Si usa un AUTO_INCREMENT como “dejado”, tenga cuidado con:

  • En InnoDB, los subprocesos separados podrían COMMIT ID en el orden “incorrecto”.
  • Multi-master (incluidos Galera e InnoDB Cluster), podría dar lugar a problemas de pedidos.

Entonces, ¿nada funciona, al menos no en un entorno de subprocesos múltiples?

Si puede vivir con un hipo ocasional (registro omitido), entonces tal vez esto ‘no sea un problema’ para usted.

El “Flip-Flop Staging” es una alternativa segura, opcionalmente combinada con el “Diseño Extremo”.

Puesta en escena de flip-flop

Si tiene muchos subprocesos INSERTANDO simultáneamente en una tabla de preparación, entonces aquí hay una manera eficiente de manejar una carga grande: Tenga un proceso que invierta esa tabla de preparación con otra tabla de preparación idéntica y realice la normalización masiva, inserción de hechos y masiva resumen.

El paso de voltear utiliza un RENAME rápido y atómico.

Aquí hay un boceto del código:

# Prep for flip:CREATETABLE new LIKE Staging;# Swap (flip) Staging tables:RENAMETABLE Staging TO old, new TO Staging;# Normalize new `foo`s:# (autocommit = 1)INSERTIGNOREINTO Foos SELECT fpp FROM old LEFTJOIN Foos ...# Prep for possible deadlocks, etcwhile...STARTTRANSACTION;# Add to Fact:INSERTINTO Fact ...FROM old JOIN Foos ...# Summarize:INSERTINTO Summary ...FROM old ...GROUPBY...COMMIT;end-while# Cleanup:DROPTABLE old;

Mientras tanto, la ingestión puede seguir escribiendo en “Staging”. Los INSERT de ingestión entrarán en conflicto con el RENAME, pero se resolverán de forma elegante, silenciosa y rápida.

¿Qué tan rápido debería hacer flip-flop? Probablemente el mejor esquema es

  • Tener un trabajo que cambia en un bucle estrecho (sin demora, o una pequeña demora, entre iteraciones), y
  • Tenga un CRON que sirva solo como “mantener vivo” para reiniciar el trabajo si muere.

Si Staging es ‘grande’, una iteración tomará más tiempo, pero se ejecutará de manera más eficiente. Por lo tanto, se autorregula.

En un entorno Galera (¿o InnoDB Cluster?), Cada nodo podría estar recibiendo entrada. Si puede permitirse perder algunas filas, haga que `Staging` sea una tabla de MEMORIA no replicada. De lo contrario, tenga un “Staging” por nodo y sea InnoDB; será más seguro, pero más lento y no sin problemas. En particular, si un nodo muere por completo, de alguna manera necesita procesar su tabla `Staging`.

Varias tablas de resumen

  • Mire los informes que necesitará.
  • Diseñe una tabla resumen para cada uno.
  • Luego, mire las tablas de resumen; es probable que encuentre algunas similitudes.
  • Fusionar otros similares.

Para ver lo que necesita un informe, mire la cláusula WHERE que proporcionaría los datos. Algunos ejemplos, asumiendo datos sobre registros de servicio para automóviles: El GRUPO POR da una pista de lo que podría tratar el informe.

1. ¿DÓNDE hacer =? Y modelo_año =? GROUP BY service_date, service_type 2. ¿DÓNDE make =? Y modelo =? GROUP BY service_date, service_type 3. ¿DÓNDE service_type =? AGRUPAR POR marca, modelo, fecha_servicio 4. ¿DÓNDE entre fecha_servicio? y ? GRUPO POR marca, modelo, modelo_año

¿Necesita permitir consultas “ad hoc”? Bueno, mire todas las consultas ad hoc: todas tienen un rango de fechas, además de precisar una o dos cosas más. (Rara vez veo algo tan feo como ‘% CL%’ para precisar otra dimensión). Por lo tanto, comience pensando en la fecha más una o dos dimensiones más como la ‘clave’ en una nueva tabla de resumen. Luego viene la pregunta de qué datos se pueden desear: recuentos, sumas, etc. Finalmente, tiene un pequeño conjunto de tablas de resumen. Luego, construya una interfaz para permitirles elegir solo entre esas posibilidades. Debería fomentar el uso de las tablas de resumen existentes, no ser verdaderamente “abierto”.

Más tarde, puede surgir otro “requisito”. Entonces, construya otra tabla de resumen. Por supuesto, puede llevar un día poblarlo inicialmente.

Juegos en tablas de resumen

¿Alguna vez es necesario resumir una tabla de resumen? Sí, pero solo en situaciones extremas. Por lo general, un informe “semanal” puede derivarse de una tabla de resumen “diaria”; no vale la pena el esfuerzo de construir una tabla de resumen semanal separada.

¿Alguna vez se PARTICIPARÁ una Tabla de Resumen? Sí, en situaciones extremas, como que la mesa sea grande y

  • Necesita depurar datos antiguos (poco probable) o
  • Por lo general, se solicitan datos recientes y los índices no evitan los escaneos de tablas (poco común). (“Poda de partición” al rescate).

Ver también

Rick James amablemente nos permitió usar este artículo en la Base de conocimientos.

El sitio de Rick James tiene otros consejos útiles, procedimientos, optimizaciones y consejos de depuración.

Fuente original: http://mysql.rjweb.org/doc.php/summarytables

Ejemplos de

El contenido reproducido en este sitio es propiedad de sus respectivos dueños, y MariaDB no revisa este contenido con anticipación. Los puntos de vista, la información y las opiniones expresadas por este contenido no representan necesariamente las de MariaDB o de cualquier otra parte.