Índices de omisión de ClickHouse: bloom, set, minmax

Índices Secundarios (de Salto) en ClickHouse: Cuando el Índice ORDER BY No es Suficiente

1. Cómo Funcionan los Índices de Salto — Omitir Bloques de Datos

En las bases de datos tradicionales (PostgreSQL, MySQL), un índice es una estructura que apunta exactamente a las filas que cumplen una condición. Un B-tree dice: "El valor user_id = 123 está en la fila #45678".

En ClickHouse, el índice primario (índice disperso por ORDER BY) funciona de manera diferente. Almacena valores solo para cada 8192.ª fila (gránulo) y puede podar eficientemente bloques enteros de datos para columnas que aparecen temprano en el ORDER BY.

Pero, ¿qué pasa si necesitas buscar por una columna que no está en ORDER BY? Por ejemplo, quieres encontrar todas las apuestas con una dirección IP específica, pero tu ORDER BY es (user_id, created_at). ClickHouse tendrá que leer todos los gránulos y filtrar IP después de leer. Esto se llama escaneo completo.

Los índices secundarios (de salto) resuelven este problema. No apuntan a filas específicas, sino que dicen: "En este bloque de N gránulos, definitivamente no existe ese valor — puedes omitirlo." Si el índice dice "tal vez" — ClickHouse aún lee el bloque.

Analogía del mundo real: Imagina que buscas un libro con cubierta verde en una biblioteca. El índice principal (catálogo por apellido del autor) no ayuda. Pero caminas entre los estantes y echas un vistazo rápido: "Todos los libros en este estante son azules — salta. Este estante tiene verdes — lo revisaré." Un índice de salto es como estantes codificados por color, no un puntero preciso.

¿Por qué se llaman de salto? Porque la función principal del índice es omitir bloques que definitivamente no se necesitan. Cuantos más bloques se omitan, más rápida será la consulta.

Limitación importante: Los índices de salto funcionan solo a nivel de gránulo. No pueden encontrar la posición exacta de la fila dentro de un gránulo. Por lo tanto, el índice es útil cuando el valor deseado es raro (baja selectividad). Si el 80% de las filas coinciden con la condición — aún tendrás que leer todo.

2. INDEX ... TYPE minmax — Para Consultas de Rango

El índice de salto más simple es minmax. Almacena el valor mínimo y máximo de una columna para cada grupo de gránulos.

CREATE TABLE player_events
(
    user_id     UInt64,
    event_time  DateTime,
    amount      Decimal(18,2),
    outcome     String      -- 'win', 'loss', 'push'
)
ENGINE = MergeTree()
ORDER BY (user_id, event_time)              -- orden primario
INDEX idx_outcome_minmax outcome TYPE minmax GRANULARITY 4;

Desglose de los parámetros:

• INDEX idx_outcome_minmax — nombre del índice (elige el tuyo, pero que sea significativo).
• outcome — columna sobre la que se construye el índice.
• TYPE minmax — tipo de índice: almacena valores mínimos y máximos en un grupo de gránulos.
• GRANULARITY 4 — cuántos gránulos (cada uno de 8192 filas) se combinan en un grupo para el índice. Aquí 4 × 8192 = 32768 filas por entrada de índice.

Cómo funciona en una consulta:

-- Encontrar eventos con un resultado específico
SELECT * FROM player_events 
WHERE outcome = 'win' AND event_time >= '2025-06-01';

ClickHouse lee el índice idx_outcome_minmax:

• Grupo 1: min='loss', max='push' → no hay 'win' → omitir 32768 filas.
• Grupo 2: min='loss', max='win' → contiene 'win' → leer este grupo.
• Grupo 3: min='win', max='win' → solo 'win' → leer.

Cuándo es efectivo minmax:

• Columnas con cambios monótonos (tiempo, ID, temperatura).
• Columnas con pocos valores únicos pero distribuidos de manera desigual.
• Consultas de rango (BETWEEN, >=, <=).

Cuándo es inútil:

• Valores aleatorios (por ejemplo, hash, UUID). El mínimo y máximo cubrirán todo el rango, el índice no omitirá nada.

3. INDEX ... TYPE set — Para Igualdad en Columnas de Baja Cardinalidad

Un índice set almacena valores únicos para un grupo de gránulos. Si el valor buscado no está en este conjunto — el grupo se omite.

CREATE TABLE bets
(
    user_id     UInt64,
    sport_id    UInt8,      -- solo 20 deportes
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, user_id)
INDEX idx_sport sport_id TYPE set(10) GRANULARITY 2;

Parámetros:

• set(10) — el número máximo de valores únicos que el índice almacenará para un grupo. Si un grupo contiene más de 10 valores únicos de sport_id, el índice solo recordará 10 (y puede causar un falso positivo). Elige un número ligeramente mayor que la cardinalidad esperada de la columna.

Cómo funciona:

-- Consulta para un deporte específico
SELECT sum(amount) FROM bets WHERE sport_id = 1;

El índice idx_sport sabe para cada grupo de gránulos qué valores de sport_id ocurren. Si un grupo no contiene sport_id=1 — omitir todo el grupo. Si lo contiene — leerlo.

Cuándo es efectivo set:

• La cardinalidad de la columna es baja (hasta cientos de valores).
• Consultas de igualdad (=, IN).
• Los datos están bien agrupados dentro de los gránulos (por ejemplo, todas las apuestas de fútbol de una hora se almacenan de forma compacta).

Ejemplo de apuestas: Una tabla de apuestas con ORDER BY (created_at, user_id). La columna sport_id (20 valores) no está en ORDER BY. Un índice set en sport_id permite encontrar rápidamente todas las apuestas de hockey sin escanear todo.

4. Índice bloom_filter — Para Columnas de Cadena de Alta Cardinalidad

Bloom filter es una estructura de datos probabilística. Puede decir "el valor definitivamente no está en el grupo" o "el valor podría estar presente". Nunca dice "definitivamente presente" — solo puede errar por falsos positivos.

CREATE TABLE player_events
(
    user_id     UInt64,
    ip_address  String,          -- millones de IPs únicas
    event_type  String,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 3;

Parámetros:

• bloom_filter(0.01) — tasa de falsos positivos del 1%. Cuanto menor sea el número, más preciso será el índice, pero ocupará más espacio. Normalmente se usa 0.01 (1%) o 0.001 (0.1%).
• GRANULARITY 3 — 3 gránulos (3 × 8192 = 24576 filas) por entrada de índice.

Cómo funciona:

-- Encontrar todos los eventos desde una IP sospechosa
SELECT * FROM player_events WHERE ip_address = '192.168.1.100';

El índice para cada grupo de gránulos verifica mediante bloom filter: "¿Podría este grupo contener IP=192.168.1.100?" Si "no" — el grupo se omite. Si "sí" (incluyendo falsos positivos) — el grupo se lee.

Cuándo es efectivo bloom filter:

• Columnas con alta cardinalidad (direcciones IP, correo electrónico, user_agent).
• Consultas de coincidencia exacta.
• Los valores buscados son raros (por ejemplo, una IP específica entre 10 millones).

Por qué minmax no es adecuado para IP: Debido a la distribución aleatoria, el IP mínimo y máximo en un grupo cubrirán casi todo el rango, por lo que la poda no funcionará.

Ejemplo del mundo real — detección de múltiples cuentas (una IP, muchos user_ids):

-- Encontrar todos los usuarios desde una IP dada
SELECT DISTINCT user_id FROM player_events 
WHERE ip_address = '192.168.1.100';

Sin índice — escaneo completo. Con bloom_filter en ip_address — rápido, incluso si la IP aparece en el 0.1% de las filas.

5. ngrambf_v1 — Para Búsqueda LIKE/ILIKE en Cadenas

A veces necesitas buscar por una subcadena: WHERE player_name LIKE '%John%'. Los índices regulares no ayudan porque % al inicio impide el uso de B-tree.

ngrambf_v1 divide la cadena en n-gramas — subcadenas de longitud N. Por ejemplo, para N=3, 'Johny' → 'Joh', 'ohn', 'hny'. El índice construye un bloom filter sobre estos n-gramas.

CREATE TABLE players
(
    player_id   UInt64,
    player_name String,
    country     String
)
ENGINE = MergeTree()
ORDER BY player_id
INDEX idx_name player_name TYPE ngrambf_v1(3, 500000, 2, 0.01) GRANULARITY 4;

Parámetros de ngrambf_v1:

• 3 — longitud del n-grama (normalmente 2–4). Más grande significa más preciso pero más memoria.
• 500000 — tamaño del bloom filter en bytes por entrada de índice.
• 2 — número de funciones hash (normalmente 2–4).
• 0.01 — probabilidad de falso positivo.

Cómo usar en una consulta:

-- Encontrar jugadores con un nombre que contenga 'Alex'
SELECT * FROM players WHERE player_name LIKE '%Alex%';

El índice divide 'Alex' en n-gramas ('Ale', 'lex') y verifica si estos n-gramas existen en los grupos. Si un grupo no tiene ninguno de estos n-gramas — el grupo se omite.

Limitaciones:

• Funciona solo con LIKE e ILIKE (sin distinción de mayúsculas/minúsculas).
• Requiere que la cadena de búsqueda sea más larga que el n-grama (al menos 3 caracteres).
• No es adecuado para cadenas cortas (por ejemplo, 'a').

Cuándo usarlo: Búsqueda por apodos de jugadores, correo electrónico parcial, direcciones. En apuestas — encontrar un jugador por parte de su nombre para atención al cliente.

6. tokenbf_v1 — Para Búsqueda de Tokens (Palabras)

tokenbf_v1 es similar a ngrambf_v1, pero divide la cadena no en piezas superpuestas, sino en tokens — palabras separadas por espacios, puntuación, dígitos.

CREATE TABLE logs
(
    log_time    DateTime,
    message     String,
    user_agent  String
)
ENGINE = MergeTree()
ORDER BY log_time
INDEX idx_msg message TYPE tokenbf_v1(500000, 2, 0.01) GRANULARITY 2;

Parámetros de tokenbf_v1:

• 500000 — tamaño del bloom filter en bytes.
• 2 — número de funciones hash.
• 0.01 — probabilidad de falso positivo.

Cómo funciona:

Para la cadena "User 123 logged in from Ukraine" tokens: 'User', '123', 'logged', 'in', 'from', 'Ukraine'.

-- Encontrar todos los logs que mencionen un error
SELECT * FROM logs WHERE message LIKE '%error%';

El índice divide 'error' en tokens (solo 'error') y verifica la presencia de este token en los grupos.

Cuándo tokenbf_v1 es mejor que ngrambf_v1:

• Búsqueda de palabras completas (no partes).
• Textos en inglés, logs, user_agent.
• Menos falsos positivos que ngrambf_v1.

Ejemplo de apuestas: Búsqueda en logs de apuestas de mensajes que contengan 'fraud' o 'suspicious'.

7. Cómo Verificar que un Índice se Usa — EXPLAIN indexes=1

Creaste un índice, pero ¿funciona? ClickHouse proporciona el comando EXPLAIN indexes = 1.

-- Habilitar análisis de uso de índices
EXPLAIN indexes = 1
SELECT user_id, amount FROM bets 
WHERE sport_id = 1 AND created_at >= '2025-06-01';

Ejemplo de salida:

Expression
  ...
  ReadFromMergeTree
    Indexes:
      PrimaryKey
        Condition: (created_at >= '2025-06-01')
        Used keys: (created_at)
        Granules: 150 / 12000
      Skip
        Name: idx_sport
        Type: set
        Condition: sport_id = 1
        Granules: 80 / 12000

Qué significan los números:

• Granules: 150 / 12000 — la clave primaria podó 11850 gránulos, quedan 150.
• Skip ... Granules: 80 / 150 — el índice de salto podó otros 70 gránulos, quedan 80.
• Ganancia final: 12000 → 80 gránulos leídos.

Si el índice no se usa:

• No aparece en la sección Skip → o no se creó, o la consulta no coincide con el tipo de índice.
• Granules: 12000 / 12000 — leyendo todo, el índice no ayudó.

Por qué un índice podría no usarse:

• El tipo de índice no coincide con el operador (minmax para = es ineficaz).
• La granularidad es demasiado grande (índice grueso).
• El valor buscado aparece en casi todas partes (el índice no puede omitir bloques).

8. Cuándo los Índices de Salto NO Ayudan

Escenario 1: Alta cardinalidad + distribución aleatoria

Si la columna user_id (millones de valores) y ORDER BY no comienza con user_id, un índice de salto (incluso bloom_filter) podará mal los bloques. Porque el valor user_id=123 puede estar disperso por toda la tabla.

Escenario 2: Consulta sin filtrado en columnas "buenas"

Los índices en sport_id no ayudarán si WHERE solo tiene amount > 1000 y no hay índice en amount.

Escenario 3: GRANULARITY demasiado grande

Si GRANULARITY = 64 (524k filas por grupo), y tu tabla tiene 10 millones de filas, solo habrá ~20 grupos. Solo puedes omitir 20 bloques, lo cual es insignificante.

Escenario 4: El valor buscado aparece en el 50%+ de las filas

Los índices de salto son buenos para valores raros. Si la mitad de las filas coinciden con la condición, los índices dirán "tal vez" para casi todos los bloques, y leerás todo.

Escenario 5: El índice es demasiado pequeño

-- Malo: bloom filter demasiado pequeño (10000 bytes)
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 4;

Un bloom filter pequeño da muchos falsos positivos (a menudo dice "tal vez" cuando no lo es). El índice deja de omitir bloques.

9. Costo de los Índices de Salto — Memoria y Velocidad de Inserción

Cada índice tiene un costo. No crees índices "por si acaso".

Costo #1: Espacio en disco adicional

• minmax — muy barato (8 bytes por grupo por columna).
• set(100) — más caro, pero dentro de miles de bytes por grupo.
• bloom_filter — caro: con 500k bytes y GRANULARITY=1, para una tabla con 10k grupos = 5 GB solo para el índice.

Costo #2: INSERT más lento

En cada inserción, ClickHouse actualiza todos los índices para cada gránulo. 5 índices en una tabla pueden ralentizar las inserciones entre 2 y 3 veces.

Regla general:

• No más de 2-3 índices de salto en una tabla grande (miles de millones de filas).
• Índices solo en columnas que se filtran con frecuencia.
• Para cargas de trabajo de prueba — experimenta. Para producción — mide.

Cómo estimar el costo del índice:

-- Verificar tamaños de índices en una tabla
SELECT 
    table,
    index_name,
    formatReadableSize(index_size) AS size
FROM system.indexes
WHERE table = 'bets';

Si el tamaño del índice se acerca al tamaño de los datos — puede que te hayas pasado.

10. Ejemplo Real: Detección de Fraude por Dirección IP

Imagina que en tu casino un grupo de jugadores usa una dirección IP para múltiples cuentas (contra las reglas). Necesitas encontrar a todos los que iniciaron sesión desde una IP sospechosa.

Tabla de eventos:

• 500 millones de filas.
• ORDER BY = (user_id, event_time) — consultas rápidas por usuario.
• Consulta frecuente: SELECT user_id FROM events WHERE ip_address = 'x.x.x.x'.

Solución — índice bloom_filter:

CREATE TABLE player_events
(
    user_id     UInt64,
    event_time  DateTime,
    ip_address  String,
    event_type  String,   -- 'login', 'bet', 'withdraw'
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time)
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 4;

Comparación de rendimiento:

Escenario	Sin índice	Con bloom_filter (0.01)
Tiempo de consulta para IP rara (0.001% filas)	60 segundos (escaneo completo 500M)	0.3 segundos
Tiempo de consulta para IP frecuente (5% filas)	60 segundos	45 segundos (el índice ayuda poco)
Tamaño de tabla (comprimido)	100 GB	108 GB (+8%)
Tiempo de INSERT (10k filas/seg)	0.5 ms por lote	0.7 ms por lote (+40%)

Cómo escribir una consulta antifraude:

-- Encontrar todos los usuarios que alguna vez usaron una IP sospechosa
SELECT DISTINCT user_id 
FROM player_events 
WHERE ip_address = '192.168.1.100'   -- bloom_filter ayuda
AND event_time >= today() - 30;      -- las particiones podan datos antiguos

-- Luego verificar cuántas cuentas diferentes usan esta IP
SELECT count(DISTINCT user_id) AS suspicious_accounts
FROM player_events 
WHERE ip_address = '192.168.1.100';

Por qué bloom_filter, no minmax:

• Las direcciones IP se distribuyen aleatoriamente; el mínimo/máximo en un grupo casi siempre cubrirá todo el rango.
• Bloom filter es ideal para comprobaciones de pertenencia a conjuntos.

Qué Sigue

Ahora conoces todos los tipos de índices secundarios de ClickHouse. Próximos temas:

• Combinación de índices — cómo funcionan juntos varios índices de salto.
• Ajuste de granularidad — cómo elegir el tamaño de gránulo óptimo para diferentes tipos de datos.
• Índices en tablas distribuidas — cómo funcionan los índices de salto en un clúster.

Conclusión: Los índices de salto en ClickHouse no son una bala de plata. No funcionan como los B-tree en PostgreSQL. Pero para los escenarios adecuados (valores raros, bloom filter, n-gramas) convierten escaneos completos en consultas ultrarrápidas. Reglas clave:

• No crees índices hasta que veas un problema (escaneo completo).
• Empieza con bloom_filter para columnas de alta cardinalidad, set para las de baja cardinalidad.
• Siempre verifica con EXPLAIN indexes = 1.
• Recuerda el costo: espacio en disco + ralentización de INSERT.

---

← Anterior: Vistas Materializadas en ClickHouse: El Poder del Procesamiento Incremental

— Editorial Team