Hoja 2: diseño de clave de ordenación (ORDER BY)

Tiempo estimado: 20–25 minutos Referencia: Motores MergeTree, sección de diseño ORDER BY

Concepto

La cláusula ORDER BY de ClickHouse no es decorativa. Define el índice primario, un índice disperso por bloques que permite a ClickHouse omitir bloques de datos irrelevantes al evaluar las cláusulas WHERE. También determina el orden físico de los datos dentro de cada parte, lo que influye en la compresión.

Un ORDER BY incorrecto implica consultas lentas y almacenamiento desperdiciado. Si la primera columna es un UUID, ninguna consulta analítica podrá omitir bloques porque los UUID son aleatorios y no ofrecen un prefijo ordenable. Si se comienza por una fecha, las consultas que filtran por ella pueden omitir la mayor parte de la tabla.

Tres reglas para diseñar ORDER BY

Regla 1: deriva la clave de los filtros de las consultas, no del esquema de origen. Examina las columnas de WHERE, GROUP BY y JOIN en las consultas más frecuentes. Las columnas por las que más se filtra probablemente deban formar parte de ORDER BY, siempre que su cardinalidad no sea demasiado elevada. La clave primaria de la tabla de origen, si existe, suele ser irrelevante.

Regla 2: baja cardinalidad primero, alta al final. El índice tiene una entrada por unas 8192 filas, un gránulo. Columnas de baja cardinalidad, como toStartOfMonth(date) con unos 48 valores distintos en cuatro años, agrupan muchas filas, de modo que el índice puede omitir gránulos completos. Las de alta cardinalidad, como trip_id con 50 millones de valores distintos, son únicas por fila; si aparecen primero, el índice no puede omitir nada. Este orden es el criterio predeterminado, no una forma de ignorar la Regla 1: una columna que se filtra por intervalo y elimina la mayoría de las filas puede merecer la primera posición por delante de otra de menor cardinalidad que solo se filtra por igualdad.

Regla 3: en ReplacingMergeTree, termina con el identificador único. La clave de deduplicación es la tupla completa de ORDER BY. Si falta trip_id, dos viajes distintos con el mismo pickup_at y sin más columnas se considerarían duplicados. Colócalo al final para garantizar la unicidad sin perjudicar el rendimiento del índice.

Ejercicio: análisis de la carga

Antes de diseñar las claves de ordenación, averigua por qué columnas filtran realmente las consultas. El laboratorio de NYC Taxi incluye siete consultas representativas; para cada una, elige la columna de filtro que elimina el mayor número de filas.

Ejercicio: estimación de cardinalidad

Estima la cardinalidad de cada columna candidata a ORDER BY en un conjunto de datos de cuatro años y 50 millones de filas. La mayor parte de la tabla siguiente contiene datos de referencia; solo quedan abiertas las dos celdas correspondientes al número estimado de valores distintos y a la clase de cardinalidad de pickup_at. Cuatro años contienen unos 126 millones de segundos —y apenas unos 2,1 millones de minutos—, el productor asigna a cada viaje la hora del reloj y PICKUP_AT se almacena como DateTime64(3, 'UTC'). Calcula cuántos de esos espacios pueden ocupar 50 millones de viajes antes de elegir un intervalo.

Ejercicio: diseño de claves

Basándote en el análisis de la carga de consultas y en las estimaciones de cardinalidad, propón un ORDER BY para trips_raw, fact_trips y agg_hourly_zone_trips. Recuerda además que la clave completa debe conservar trip_id al final:

• Baja cardinalidad primero para omitir más bloques.
• Incluye columnas de WHERE/GROUP BY repetidas.
• En ReplacingMergeTree, termina con el identificador único.
• No incluyas columnas que nunca se filtran.

Cuando hayas rellenado todas las tablas, responde también las preguntas de razonamiento y reflexión.

Loading worksheet...

Transfiérelo a migration-plan.md

Cuando hayas completado esta hoja, copia tus decisiones de ORDER BY a la Sección 4 de migration-plan.md y marca:

- [ ] Sort key design: completed