ORDER BY y PRIMARY KEY en ClickHouse: selección de índices

ORDER BY y PRIMARY KEY en ClickHouse: Cómo Configurar el Índice Correctamente

1. Por Qué ORDER BY Es Lo Más Importante Que Especificarás en una Tabla

En las bases de datos convencionales (PostgreSQL, MySQL), existen dos conceptos: un índice agrupado (la clave primaria que ordena físicamente los datos en disco) e índices secundarios (árboles B separados). Puedes agregar o eliminar un índice en cualquier momento sin tener que recrear la tabla.

En ClickHouse es diferente. Aquí solo hay un orden físico de los datos en disco: el que especificaste en ORDER BY. Y no puedes cambiarlo sin recrear la tabla. En absoluto. Punto. Es como verter hormigón y darte cuenta de que colocaste mal las barras de refuerzo. Tienes que romper todo y empezar de nuevo.

¿Por qué tan estricto? Porque ClickHouse almacena datos en formato columnar, altamente comprimido. Para cambiar el orden de las filas, tendrías que reescribir todas las columnas desde cero. Nadie quiere esperar horas o días para reorganizar una tabla de varios terabytes.

Por lo tanto, elegir ORDER BY es una decisión estratégica. Debes predecir qué consultas serán más frecuentes y diseñar la clave para que se ejecuten a la velocidad del rayo. Un error será costoso.

Analogía de la vida real: Imagina que eres bibliotecario y necesitas organizar todos los libros en los estantes en un orden específico. Puedes elegir un orden, por ejemplo por género, y dentro de ese por apellido del autor. Entonces puedes encontrar libros rápidamente si buscas por esos criterios. Pero si decides que ordenar por fecha de publicación sería más conveniente, tendrás que mover todos los libros de los estantes nuevamente. Durante horas.

2. PRIMARY KEY ⊆ ORDER BY — Una Regla Poco Común

En ClickHouse tienes dos parámetros:

• ORDER BY — define el orden físico de las filas en disco (obligatorio).
• PRIMARY KEY — define el índice (opcional).

Y hay una regla estricta: las columnas listadas en PRIMARY KEY deben ser las primeras columnas en ORDER BY. En otras palabras, PRIMARY KEY es un prefijo de ORDER BY.

-- ✅ Correcto: PRIMARY KEY son las dos primeras columnas de ORDER BY
CREATE TABLE apuestas
(
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at, amount)    -- orden completo
PRIMARY KEY (user_id, created_at);         -- prefijo: las dos primeras

-- ❌ Error: PRIMARY KEY no es un prefijo
ORDER BY (user_id, created_at, amount)
PRIMARY KEY (created_at, user_id);   -- orden diferente — ClickHouse lanzará un error

-- ⚠️ Puedes omitir PRIMARY KEY por completo
-- Entonces coincide automáticamente con ORDER BY
CREATE TABLE apuestas
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- PRIMARY KEY = (user_id, created_at)

Entonces, ¿por qué necesitas PRIMARY KEY si es solo un prefijo? He aquí por qué: el índice de ClickHouse (índice disperso) se construye solo sobre las columnas en PRIMARY KEY. Si especificas un PRIMARY KEY más corto que ORDER BY, ahorras memoria en el índice, pero el orden de las filas sigue siendo completo (por todas las columnas de ORDER BY). Esto es útil cuando las columnas que afectan el orden físico no son necesarias en el índice.

Ejemplo: En ORDER BY (user_id, created_at, amount) — las filas se ordenan primero por user_id, luego por created_at, luego por amount. Pero no necesitas buscar por amount, así que PRIMARY KEY (user_id, created_at) es más corto, el índice es más pequeño y la disposición física ayuda con la compresión (los montos idénticos se almacenan juntos).

3. Índice Disperso: Una Entrada por Cada 8192 Filas (Gránulo)

El índice en ClickHouse se llama índice disperso. No almacena un puntero a cada fila como un árbol B en PostgreSQL. En su lugar, almacena una entrada por cada 8192 filas (este grupo se llama gránulo).

Cómo se ve internamente:

Gránulo (filas 1–8192)	Valor de PRIMARY KEY para la primera fila del gránulo
Gránulo 1	user_id=100, created_at=2025-01-01 00:00:01
Gránulo 2	user_id=100, created_at=2025-01-01 10:15:23
Gránulo 3	user_id=200, created_at=2025-01-01 00:00:05
...	...

Cómo busca datos ClickHouse:

1. Tienes una consulta WHERE user_id = 100 AND created_at >= '2025-01-01'.
2. ClickHouse mira el índice disperso y ve los gránulos.
3. Encuentra que user_id=100 aparece en los gránulos 1, 2, posiblemente 3 y siguientes.
4. Pero no sabe exactamente dónde dentro de un gránulo está la fila deseada — porque el índice solo apunta al inicio del gránulo.
5. Por lo tanto, ClickHouse lee todos los gránulos que podrían contener las filas necesarias (a veces más de lo necesario — esto se llama filtrado por índice).

Analogía: Un índice disperso es como un índice de contenidos en un libro donde cada capítulo tiene 100 páginas. El índice dice: "El capítulo 3 comienza en la página 201." Si necesitas una frase específica en la página 210, aún tienes que leer las páginas 201–300 por completo porque no sabes la ubicación exacta. En PostgreSQL, un índice de árbol B te daría la página 210.

¿Por qué es rápido en ClickHouse? Porque:

• ClickHouse lee columnas selectivamente — si WHERE necesita user_id y SELECT necesita amount, lee solo esas dos columnas.
• Los datos dentro de un gránulo están comprimidos, y leer 8192 filas a la vez es muy eficiente (volumen mínimo ~64 KB, el tamaño del gránulo es configurable mediante index_granularity).
• Para consultas analíticas (que leen millones de filas), esa granularidad es suficiente.

4. Regla de Cardinalidad: Primero Baja, Luego Alta

Cardinalidad es el número de valores únicos en una columna. Por ejemplo:

• sport_id (tipo de deporte: fútbol, hockey, tenis) — cardinalidad 20 (baja)
• market_id (mercado de apuesta: resultado, total, hándicap) — cardinalidad 1000 (media)
• created_at (tiempo al segundo) — cardinalidad miles de millones (alta)

La regla de oro de ClickHouse: en ORDER BY, las columnas con cardinalidad baja deben ir antes que las columnas con cardinalidad alta.

¿Por qué? Porque el índice disperso será más efectivo para podar gránulos.

Clave mala: ORDER BY (created_at, sport_id)

• Los datos se ordenan primero por tiempo. sport_id para filas adyacentes saltará: fútbol, hockey, tenis, luego fútbol otra vez...
• La consulta WHERE sport_id = 1 obliga a ClickHouse a leer todos los gránulos porque sport_id=1 está disperso por toda la tabla.

Clave buena: ORDER BY (sport_id, created_at)

• Primero, todas las filas de fútbol (sport_id=1) ordenadas por tiempo. Luego todas las filas de hockey (sport_id=2) — compacto.
• La consulta WHERE sport_id = 1 poda todos los gránulos no relacionados con fútbol a nivel de índice. ClickHouse lee solo los gránulos con sport_id=1.

Analogía: Imagina ordenar una baraja de cartas. Si ordenas primero por palo (cardinalidad baja — 4 valores) y luego por rango (alta — 13 valores), todos los picas estarán juntos. Si lo haces al revés — primero por rango, luego los ases de todos los palos están dispersos por toda la baraja. Encontrar todos los picas se vuelve difícil.

5. Ejemplo para Apuestas: Cómo Elegir el ORDER BY Correcto

Comparemos dos opciones para una tabla de apuestas en una casa de apuestas.

Opción A (mala): ORDER BY (created_at, sport_id)

CREATE TABLE apuestas_mal
(
    sport_id    UInt8,       -- 1 = fútbol, 2 = hockey, 3 = tenis
    market_id   UInt32,      -- ID del mercado de apuesta
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, sport_id, market_id);

Cómo se comportan las consultas típicas:

-- Consulta: todas las apuestas de fútbol en la última hora
SELECT sum(amount) FROM apuestas_mal
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN mostrará: leyendo casi todos los gránulos porque sport_id=1 está disperso en la línea de tiempo

El índice (created_at, sport_id) ayuda poco porque sport_id es la segunda columna. ClickHouse puede usar el prefijo created_at, pero luego el filtrado por sport_id debe hacerse a nivel de gránulo, leyendo datos adicionales.

Opción B (buena): ORDER BY (sport_id, market_id, created_at)

CREATE TABLE apuestas_bien
(
    sport_id    UInt8,
    market_id   UInt32,
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (sport_id, market_id, created_at);

Mismas consultas:

-- Consulta: apuestas de fútbol en la última hora
SELECT sum(amount) FROM apuestas_bien
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN mostrará: leyendo solo los gránulos donde sport_id = 1, significativamente menos

Por qué es mejor: ClickHouse puede encontrar inmediatamente los bloques con sport_id = 1 mediante el índice, y dentro de esos bloques, los datos están ordenados por market_id y created_at. El filtro de tiempo created_at >= ... se aplica a nivel de gránulo dentro de esos bloques.

6. Igualdad vs Rango: Cuál Es Más Eficiente

Para las columnas en ORDER BY, hay una jerarquía de eficiencia:

1. Igualdad (=) — más eficiente. Si buscas un valor exacto, ClickHouse puede saltarse bloques enteros de gránulos.
2. Desigualdad (>=, <=, BETWEEN) — menos eficiente, pero puede funcionar si es la última columna en la clave.
3. LIKE u otras funciones — a menudo no usan el índice en absoluto (a menos que se transformen en un rango).

Regla: En ORDER BY, las columnas con condiciones de igualdad deben ir antes que las columnas con condiciones de rango.

Ejemplo para clave (user_id, created_at):

-- ✅ Excelente: user_id = igualdad (primera columna), created_at >= rango (segunda)
SELECT * FROM apuestas WHERE user_id = 123 AND created_at >= '2025-06-01';

-- ❌ Malo: created_at rango (primera columna), user_id = igualdad (segunda)
-- El índice solo puede podar por created_at, pero user_id debe filtrarse dentro de los gránulos
SELECT * FROM apuestas WHERE created_at >= '2025-06-01' AND user_id = 123;

¿Por qué? Porque los datos están ordenados físicamente por (user_id, created_at). Todos los registros de un user_id se almacenan de forma compacta, y dentro de ellos, ordenados por tiempo. Si buscas por rango de tiempo, es fácil. Pero si buscas primero por tiempo, los registros de un user_id están dispersos por toda la tabla — no se pueden podar con el índice.

Analogía: Imagina una guía telefónica ordenada primero por apellido, luego por nombre. Encontrar "todos los García" es fácil (el apellido es la primera columna). Encontrar "todos los nacidos después de 1990" requiere leer el libro completo.

7. Clave Compuesta de UInt8+UInt32+DateTime vs Solo DateTime

A veces parece: "¿Por qué no usar solo ORDER BY created_at — simple y claro?" Analicemos con un ejemplo de juego.

Consultas que realmente necesita un panel:

• Apuestas de un usuario específico en la última semana: WHERE user_id = 123 AND created_at >= today() - 7
• Estadísticas por deporte para un día: WHERE sport_id = 1 AND created_at = yesterday()
• Agregación por mercado para una hora: WHERE market_id = 100 AND created_at >= now() - 1 hour

Opción 1: ORDER BY (created_at)

CREATE TABLE apuestas_simple
(
    user_id     UInt64,
    sport_id    UInt8,
    market_id   UInt32,
    created_at  DateTime
)
ORDER BY created_at;

Problemas:

• Las consultas por user_id serán lentas — tendrás que escanear todo.
• Las consultas por sport_id — la misma historia.

Opción 2: ORDER BY (user_id, sport_id, market_id, created_at)

CREATE TABLE apuestas_compuesta
(
    user_id     UInt64,
    sport_id    UInt8,
    market_id   UInt32,
    created_at  DateTime
)
ORDER BY (user_id, sport_id, market_id, created_at);

Ahora:

• Consulta WHERE user_id = 123 AND created_at >= ... — excelente (usa el prefijo user_id).
• Consulta WHERE sport_id = 1 AND created_at = ... — pobre, porque sport_id no es la primera columna. ClickHouse no puede podar por sport_id en el índice.

Compromiso: Elige el patrón de filtro más frecuente y coloca sus columnas al principio de ORDER BY. Si buscas más a menudo por user_id, pon user_id primero. Si más a menudo por brand_id, pon ese primero.

Regla general: ORDER BY debe tener al menos 2–4 columnas. Una columna rara vez es óptima.

8. Cómo Verificar la Eficiencia de la Clave con EXPLAIN

ClickHouse proporciona herramientas potentes para analizar cómo se usa el índice.

EXPLAIN indexes = 1

-- Habilitar la salida de información de uso del índice
EXPLAIN indexes = 1
SELECT sum(amount) FROM apuestas
WHERE user_id = 123 AND created_at >= '2025-06-01';

El resultado mostrará algo como:

Expression
  ...
  ReadFromMergeTree
    Indexes:
      PrimaryKey
        Condition: (user_id = 123) AND (created_at >= '2025-06-01')
        Used keys: (user_id, created_at)
        Granules: 15 / 1280

Qué significan los números: 15 / 1280 — de 1280 gránulos en la tabla, solo se leyeron 15. Resultado excelente. Si muestra 1200 / 1280, el índice apenas ayudó.

system.query_log

La tabla del sistema query_log almacena estadísticas de cada consulta. Las columnas más útiles para el análisis del índice:

-- Encontrar consultas lentas y ver cuántas filas leyeron
SELECT 
    query,
    read_rows,          -- cuántas filas se leyeron
    result_rows,        -- cuántas filas se devolvieron
    read_rows / result_rows AS eficiencia,  -- cuanto más cerca de 1, mejor
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish' 
  AND query LIKE '%apuestas%'
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC;

Cómo interpretar:

• read_rows / result_rows ≈ 1..10 — el índice funciona bien
• read_rows / result_rows > 1000 — estás leyendo miles de filas por una — índice malo
• read_rows cercano al total de filas en la tabla — escaneo completo

columns_read de system.query_log

SELECT 
    query,
    read_rows,
    written_rows,
    result_rows,
    columns_read,      -- lista de columnas que se leyeron
    columns_written
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
LIMIT 10;

Si en columns_read ves columnas que no están en SELECT ni WHERE, ClickHouse está leyendo datos adicionales (posiblemente debido a un ORDER BY deficiente).

9. Patrones para la Industria del Juego

Patrón 1: Consultas por un Jugador Específico

Si la consulta más frecuente es "mostrar el historial de apuestas de un usuario", la clave (user_id, created_at) es ideal.

CREATE TABLE apuestas_por_usuario
(
    user_id     UInt64,
    created_at  DateTime,
    sport_id    UInt8,
    amount      Decimal(18,2)
)
ORDER BY (user_id, created_at);   -- Todas las apuestas de un usuario son compactas y por tiempo

La consulta WHERE user_id = 123 AND created_at BETWEEN ... leerá solo los gránulos de ese usuario, que son pocos.

Patrón 2: Plataforma Multi-Marca

Tienes varias marcas (casino_A, casino_B), y las consultas casi siempre incluyen brand_id. Entonces:

CREATE TABLE apuestas_multi_marca
(
    brand_id    UInt8,        -- Cardinalidad baja (5 marcas)
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ORDER BY (brand_id, created_at);

La consulta WHERE brand_id = 1 AND created_at >= ... poda todos los datos de otras marcas a nivel de índice.

Patrón 3: Panel por Deporte

Si los informes se agrupan por sport_id (fútbol, hockey) y filtran por tiempo:

CREATE TABLE apuestas_por_deporte
(
    sport_id    UInt8,
    created_at  DateTime,
    user_id     UInt64,
    amount      Decimal(18,2)
)
ORDER BY (sport_id, created_at);

No existe una clave universal. Debes elegir uno o dos patrones de consulta más frecuentes y optimizar para ellos. Otras consultas serán más lentas — esa es una compensación inevitable.

10. Cambiar ORDER BY Después de Crear la Tabla — No Es Posible

Este es el conocimiento más triste pero más importante. No puedes cambiar ORDER BY o PRIMARY KEY en una tabla existente con comandos como ALTER.

-- ❌ No existe nada como esto
ALTER TABLE apuestas MODIFY ORDER BY (new_column, created_at);   -- ERROR!

¿Por qué? Porque el orden físico de las filas ya está determinado. Para cambiarlo, necesitas recrear la tabla.

¿Qué hacer si te das cuenta de que cometiste un error?

Método 1: Crear una nueva tabla, migrar datos, renombrar

-- 1. Crear una nueva tabla con el ORDER BY correcto
CREATE TABLE apuestas_nueva
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- nueva clave

-- 2. Migrar datos (puede ser asíncrono si la tabla es grande)
INSERT INTO apuestas_nueva SELECT * FROM apuestas;

-- 3. Intercambiar tablas (operación atómica)
RENAME TABLE apuestas TO apuestas_vieja, apuestas_nueva TO apuestas;

-- 4. Verificar que todo funciona, luego eliminar la tabla vieja
DROP TABLE apuestas_vieja;

Método 2: Usar una vista materializada (si puedes almacenar datos en dos órdenes simultáneamente)

-- Mantener la tabla vieja para algunas consultas
-- Crear una vista materializada con un ORDER BY diferente para otras consultas
CREATE MATERIALIZED VIEW apuestas_por_deporte_mv
ENGINE = MergeTree() ORDER BY (sport_id, created_at)
AS SELECT * FROM apuestas;   -- los datos se duplicarán

Método 3: Aceptarlo y vivir con una clave mala (a veces es más barato aumentar recursos que migrar terabytes)

Consejo: Antes de crear una tabla con datos grandes (miles de millones de filas), siempre prueba ORDER BY en una muestra. Crea una copia con 10 millones de filas, ejecuta EXPLAIN indexes=1, prueba diferentes consultas. Esto te ahorrará semanas de dolor después.

Qué Sigue

Ahora entiendes que ORDER BY en ClickHouse no es solo ordenamiento, sino un índice estratégico. Próximos temas:

• Cómo configurar index_granularity — cambiar el tamaño del gránulo de 8192 a otro valor (casi nunca necesario).
• Particionamiento vs ORDER BY — cuándo ayudan las particiones y cuándo el índice.
• Índices de salto (índices de filtro bloom) — índices secundarios para columnas no incluidas en ORDER BY.
• Análisis de consultas lentas mediante system.query_log — perfilado profundo.

Conclusión: La fórmula para el ORDER BY ideal en ClickHouse: las columnas con cardinalidad baja y condiciones de igualdad van primero; luego las columnas con cardinalidad alta y rangos. No intentes cubrirlo todo — elige las consultas más frecuentes e ignora el resto. Y nunca olvides EXPLAIN indexes=1 — el mejor amigo de un desarrollador de ClickHouse.

---

← Anterior: Particionamiento en ClickHouse: Cómo gestionar datos a nivel de carpeta
→ Siguiente: TTL en ClickHouse: Gestión Automática del Ciclo de Vida de los Datos

— Editorial Team