Introducción
- en qué se diferencia la indexación en ClickHouse de la de los sistemas tradicionales de gestión de bases de datos relacionales
- cómo ClickHouse construye y utiliza el índice primario disperso de una tabla
- cuáles son algunas de las buenas prácticas de indexación en ClickHouse
Esta guía se centra en los índices primarios dispersos de ClickHouse.Para obtener información sobre los índices de omisión de datos secundarios de ClickHouse, consulta el Tutorial.
Conjunto de datos
- Usaremos un subconjunto de 8,87 millones de filas (eventos) del conjunto de datos de ejemplo.
- El tamaño de los datos sin comprimir es de 8,87 millones de eventos y unos 700 MB. Esto se comprime a 200 MB cuando se almacena en ClickHouse.
- En nuestro subconjunto, cada fila contiene tres columnas que corresponden a un usuario de internet (columna
UserID) que hizo clic en una URL (columnaURL) en un momento concreto (columnaEventTime).
- “¿Cuáles son las 10 URL en las que más hizo clic un usuario concreto?”
- “¿Cuáles son los 10 usuarios que hicieron clic con más frecuencia en una URL concreta?”
- “¿Cuáles son los momentos más habituales (p. ej., los días de la semana) en los que un usuario hace clic en una URL concreta?”
Máquina de prueba
Un escaneo completo de la tabla
insert.
Para ello, se utiliza la función de tabla URL para cargar un subconjunto del conjunto de datos completo alojado de forma remota en clickhouse.com:
En general, no es necesario ni se recomienda optimizar una tabla inmediatamente
después de cargar datos en ella. Más adelante quedará claro por qué esto es necesario en este ejemplo.
Diseño de índices en ClickHouse
Un diseño de índices para datos a gran escala
B(+)-Tree tiene una complejidad temporal media de O(log n); más concretamente, log_b n = log_2 n / log_2 b, donde b es el factor de ramificación del B(+)-Tree y n es el número de filas indexadas. Como b suele estar entre varios cientos y varios miles, los B(+)-Trees son estructuras muy poco profundas, y se requieren pocas búsquedas en disco para localizar registros. Con 8,87 millones de filas y un factor de ramificación de 1000, se necesitan de media 2,3 búsquedas en disco. Esta capacidad tiene un coste: mayor consumo de disco y memoria, costes de inserción más altos al añadir nuevas filas a la tabla y nuevas entradas al índice y, en ocasiones, el reequilibrado del B-Tree.
Teniendo en cuenta los retos asociados a los índices B-Tree, los motores de tabla de ClickHouse utilizan un enfoque distinto. La familia de motores MergeTree de ClickHouse se ha diseñado y optimizado para gestionar volúmenes masivos de datos. Estas tablas están diseñadas para recibir millones de inserciones de filas por segundo y almacenar volúmenes de datos muy grandes (cientos de petabytes). Los datos se escriben rápidamente en una tabla parte por parte, aplicando reglas para fusionar las partes en segundo plano. En ClickHouse, cada parte tiene su propio índice primario. Cuando las partes se fusionan, los índices primarios de la parte resultante también se fusionan. A la escala masiva para la que está diseñado ClickHouse, la eficiencia en disco y memoria es fundamental. Por ello, en lugar de indexar cada fila, el índice primario de una parte tiene una entrada de índice (conocida como “marca”) por cada grupo de filas (llamado “gránulo”); esta técnica se denomina índice disperso.
La indexación dispersa es posible porque ClickHouse almacena en disco las filas de una parte ordenadas por las columnas de la clave primaria. En lugar de localizar directamente filas individuales (como haría un índice basado en B-Tree), el índice primario disperso permite identificar rápidamente, mediante una búsqueda binaria sobre las entradas del índice, grupos de filas que podrían coincidir con la consulta. Los grupos localizados de filas potencialmente coincidentes (gránulos) se transmiten después en paralelo al motor de ClickHouse para encontrar las coincidencias. Este diseño de índices permite que el índice primario sea pequeño (puede, y debe, caber por completo en la memoria principal) y, al mismo tiempo, acelera significativamente los tiempos de ejecución de las consultas, especialmente en las consultas de rango típicas de los casos de uso de analítica de datos.
A continuación se ilustra en detalle cómo ClickHouse construye y utiliza su índice primario disperso. Más adelante en el artículo, analizaremos algunas buenas prácticas para elegir, eliminar y ordenar las columnas de la tabla que se utilizan para construir el índice (columnas de la clave primaria).
Una tabla con clave primaria
Detalles de la sentencia DDL
Detalles de la sentencia DDL
Para simplificar las explicaciones posteriores de esta guía, así como para hacer que los diagramas y los resultados sean reproducibles, la sentencia DDL:
- Especifica una clave de ordenación compuesta para la tabla mediante una cláusula
ORDER BY. - Controla explícitamente cuántas entradas de índice tendrá el índice primario mediante las siguientes configuraciones:
index_granularity: se establece explícitamente en su valor predeterminado de 8192. Esto significa que, para cada grupo de 8192 filas, el índice primario tendrá una entrada de índice. Por ejemplo, si la tabla contiene 16384 filas, el índice tendrá dos entradas de índice.index_granularity_bytes: se establece en 0 para desactivar la granularidad adaptativa del índice. La granularidad adaptativa del índice significa que ClickHouse crea automáticamente una entrada de índice para un grupo de n filas si se cumple cualquiera de estas condiciones:- Si
nes menor que 8192 y el tamaño combinado de los datos de esasnfilas es mayor o igual que 10 MB (el valor predeterminado deindex_granularity_bytes). - Si el tamaño combinado de los datos de
nfilas es menor que 10 MB, perones 8192.
- Si
compress_primary_key: se establece en 0 para desactivar la compresión del índice primario. Esto nos permitirá inspeccionar su contenido más adelante, si es necesario.
A continuación, inserta los datos:
Y optimice la tabla:
Podemos usar la siguiente consulta para obtener metadatos de nuestra tabla:
- Los datos de la tabla se almacenan en wide format en un directorio específico del disco, lo que significa que dentro de ese directorio habrá un archivo de datos (y un archivo de marcas) por cada columna de la tabla.
- La tabla tiene 8.87 millones de filas.
- El tamaño sin comprimir de todos los datos de la tabla es de 733.28 MB.
- El tamaño comprimido en disco de todos los datos de la tabla es de 206.94 MB.
- La tabla tiene un índice primario con 1083 entradas (llamadas ‘marks’) y el tamaño del índice es de 96.93 KB.
- En total, los datos de la tabla, los archivos de marcas y el archivo del índice primario ocupan 207.07 MB en disco.
Los datos se almacenan en disco ordenados por las columnas de la clave primaria
- una clave primaria compuesta
(UserID, URL)y - una clave de ordenación compuesta
(UserID, URL, EventTime).
- Si hubiéramos especificado solo la clave de ordenación, la clave primaria se habría definido implícitamente como igual a la clave de ordenación.
- Para usar la memoria de forma eficiente, especificamos explícitamente una clave primaria que solo contiene columnas por las que filtran nuestras consultas. El índice primario basado en la clave primaria se carga por completo en la memoria principal.
- Para mantener la coherencia en los diagramas de la guía y maximizar la ratio de compresión, definimos una clave de ordenación independiente que incluye todas las columnas de nuestra tabla (si en una columna se colocan datos similares cerca unos de otros, por ejemplo mediante la ordenación, esos datos se comprimirán mejor).
- La clave primaria debe ser un prefijo de la clave de ordenación si se especifican ambas.
EventTime de la clave de ordenación).
ClickHouse permite insertar varias filas con valores idénticos en las columnas de la clave primaria. En este caso (véanse la fila 1 y la fila 2 en el diagrama de abajo), el orden final lo determina la clave de ordenación especificada y, por tanto, el valor de la columna
EventTime.- para la representación en disco, hay un único archivo de datos (*.bin) por columna de la tabla donde todos los valores de esa columna se almacenan en formato comprimido, y
- las 8,87 millones de filas se almacenan en disco en orden lexicográfico ascendente según las columnas de la clave primaria (y las columnas adicionales de la clave de ordenación), es decir, en este caso
- primero por
UserID, - luego por
URL, - y por último por
EventTime:
- primero por
UserID.bin, URL.bin y EventTime.bin son los archivos de datos en disco donde se almacenan los valores de las columnas UserID, URL y EventTime.
- Como la clave primaria define el orden lexicográfico de las filas en disco, una tabla solo puede tener una clave primaria.
- Numeramos las filas empezando por 0 para mantener la coherencia con el esquema interno de numeración de filas de ClickHouse, que también se utiliza en los mensajes de logging.
Los datos se organizan en gránulos para el procesamiento de datos en paralelo
Los valores de las columnas no se almacenan físicamente dentro de los gránulos: los gránulos son solo una organización lógica de los valores de las columnas para el procesamiento de consultas.
index_granularity (establecida en su valor predeterminado de 8192).
Las primeras 8192 filas (según el orden físico en disco) (sus valores de las columnas) pertenecen lógicamente al gránulo 0; luego, las siguientes 8192 filas (sus valores de las columnas) pertenecen al gránulo 1, y así sucesivamente.
- El último gránulo (gránulo 1082) “contiene” menos de 8192 filas.
- Mencionamos al principio de esta guía, en “Detalles de la sentencia DDL”, que desactivamos la granularidad adaptativa del índice (para simplificar la explicación de esta guía, así como para hacer reproducibles los diagramas y los resultados). Por lo tanto, todos los gránulos (excepto el último) de nuestra tabla de ejemplo tienen el mismo tamaño.
- En las tablas con granularidad adaptativa del índice (la granularidad del índice es adaptativa de forma predeterminada), el tamaño de algunos gránulos puede ser inferior a 8192 filas, dependiendo del tamaño de los datos de las filas.
-
Marcamos en naranja algunos valores de las columnas de nuestra clave primaria (
UserID,URL). Estos valores de las columnas marcados en naranja son los valores de las columnas de clave primaria de la primera fila de cada gránulo. Como veremos a continuación, estos valores de las columnas marcados en naranja serán las entradas del índice primario de la tabla. - Numeramos los gránulos comenzando por 0 para mantener la coherencia con el esquema de numeración interno de ClickHouse, que también se utiliza en los mensajes de registro.
El índice primario tiene una entrada por gránulo
- la primera entrada del índice (‘mark 0’ en el diagrama de abajo) almacena los valores de las columnas clave de la primera fila del gránulo 0 del diagrama anterior,
- la segunda entrada del índice (‘mark 1’ en el diagrama de abajo) almacena los valores de las columnas clave de la primera fila del gránulo 1 del diagrama anterior, y así sucesivamente.
- En las tablas con granularidad adaptativa del índice, también se almacena en el índice primario una marca adicional “final” que registra los valores de las columnas de la clave primaria de la última fila de la tabla. Pero, como hemos desactivado la granularidad adaptativa del índice (para simplificar las explicaciones de esta guía y hacer reproducibles los diagramas y los resultados), el índice de nuestra tabla de ejemplo no incluye esa marca final.
- El archivo del índice primario se carga por completo en la memoria principal. Si el archivo es más grande que el espacio de memoria libre disponible, ClickHouse generará un error.
Inspección del contenido del índice primario
Inspección del contenido del índice primario
En un clúster de ClickHouse autogestionado, podemos usar la función de tabla file para inspeccionar el contenido del índice primario de nuestra tabla de ejemplo.Para ello, primero debemos copiar el archivo del índice primario al user_files_path de un nodo del clúster en ejecución:
- Paso 1: Obtener la ruta de la parte que contiene el archivo del índice primario
- Paso 2: Obtener user_files_path El user_files_path predeterminado en Linux es
- Paso 3: Copiar el archivo del índice primario en user_files_path
SELECT path FROM system.parts WHERE table = 'hits_UserID_URL' AND active = 1devuelve /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4 en la máquina de prueba./var/lib/clickhouse/user_files/y en Linux puedes comprobar si se ha cambiado: $ grep user_files_path /etc/clickhouse-server/config.xmlEn la máquina de prueba, la ruta es /Users/tomschreiber/Clickhouse/user_files/cp /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4/primary.idx /Users/tomschreiber/Clickhouse/user_files/primary-hits_UserID_URL.idxAhora podemos inspeccionar el contenido del índice primario mediante SQL:
- Obtener el número de entradas
- Obtener las dos primeras marcas del índice
- Obtener la última marca del índice
SELECT count( )<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String');
devuelve 1083SELECT UserID, URL<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 0, 2;devuelve240923, http://showtopics.html%3...<br/> 4073710, http://mk.ru&pos=3_0SELECT UserID, URL FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 1082, 1;
devuelve
4292714039 │ http://sosyal-mansetleri...Esto coincide exactamente con nuestro diagrama del contenido del índice primario de nuestra tabla de ejemplo:
-
Marcas del índice de UserID:
Los valores
UserIDalmacenados en el índice primario están ordenados de forma ascendente.
Por lo tanto, la “marca 1” del diagrama anterior indica que los valoresUserIDde todas las filas de la tabla del gránulo 1, y de todos los gránulos siguientes, serán necesariamente mayores o iguales que 4.073.710.
-
Marcas del índice de URL:
La cardinalidad bastante similar de las columnas de la clave primaria
UserIDyURLsignifica que, por lo general, las marcas del índice de todas las columnas clave posteriores a la primera solo indican un rango de datos mientras el valor de la columna clave anterior siga siendo el mismo para todas las filas de la tabla, al menos dentro del gránulo actual.
Por ejemplo, como los valores de UserID de la marca 0 y la marca 1 son diferentes en el diagrama anterior, ClickHouse no puede asumir que todos los valores de URL de todas las filas de la tabla del gránulo 0 sean mayores o iguales que'http://showtopics.html%3...'. Sin embargo, si los valores de UserID de la marca 0 y la marca 1 fueran iguales en el diagrama anterior (lo que significa que el valor de UserID se mantiene igual para todas las filas de la tabla dentro del gránulo 0), ClickHouse podría asumir que todos los valores de URL de todas las filas de la tabla del gránulo 0 son mayores o iguales que'http://showtopics.html%3...'. Más adelante analizaremos con más detalle las consecuencias de esto en el rendimiento de ejecución de las consultas.
El índice primario se usa para seleccionar gránulos
749927693 en la columna UserID. Esto requiere 19 pasos, con una complejidad temporal media de O(log2 n):
Detalles del registro de trazas
Detalles del registro de trazas
Se identificó la marca 176 (la ‘marca de límite izquierdo encontrada’ es inclusiva y la ‘marca de límite derecho encontrada’ es exclusiva), por lo que las 8192 filas del gránulo 176 (que empieza en la fila 1.441.792; lo veremos más adelante en esta guía) se cargan en ClickHouse para encontrar las filas concretas en las que la columna UserID tiene el valor 749927693.
ConclusiónCuando una consulta filtra por una columna que forma parte de una clave compuesta y es la primera columna de la clave, ClickHouse ejecuta el algoritmo de búsqueda binaria sobre las marcas de índice de esa columna clave.
Como se explicó antes, ClickHouse usa su índice primario disperso para seleccionar rápidamente (mediante búsqueda binaria) los gránulos que podrían contener filas que coincidan con una consulta. Esta es la primera etapa (selección de gránulos) de la ejecución de consultas de ClickHouse. En la segunda etapa (lectura de datos), ClickHouse localiza los gránulos seleccionados para transmitir todas sus filas al motor de ClickHouse a fin de encontrar las filas que realmente coinciden con la consulta. Analizamos esta segunda etapa con más detalle en la siguiente sección.
Los archivos de marcas se usan para localizar gránulos
Detalles de la selección de gránulos
Detalles de la selección de gránulos
El diagrama anterior muestra que la marca 176 es la primera entrada del índice en la que tanto el valor mínimo de UserID del gránulo 176 asociado es menor que 749.927.693 como el valor mínimo de UserID del gránulo 177 para la siguiente marca (marca 177) es mayor que ese valor. Por lo tanto, solo el gránulo 176 correspondiente a la marca 176 podría contener filas con un valor de 749.927.693 en la columna UserID.
UserID.mrk, URL.mrk y EventTime.mrk, que almacenan las ubicaciones físicas de los gránulos para las columnas UserID, URL y EventTime de la tabla.
Ya hemos explicado que el índice primario es un archivo Array plano sin comprimir (primary.idx) que contiene marcas de índice numeradas a partir de 0.
Del mismo modo, un archivo de marcas también es un archivo Array plano sin comprimir (*.mrk) que contiene marcas numeradas a partir de 0.
Una vez que ClickHouse ha identificado y seleccionado la marca de índice de un gránulo que podría contener filas coincidentes para una consulta, se puede realizar una búsqueda posicional en Array en los archivos de marcas para obtener las ubicaciones físicas del gránulo.
Cada entrada del archivo de marcas para una columna específica almacena dos ubicaciones en forma de desplazamientos:
- El primer desplazamiento (‘block_offset’ en el diagrama anterior) localiza el bloque en el archivo de datos de la columna comprimido que contiene la versión comprimida del gránulo seleccionado. Este bloque comprimido puede contener varios gránulos comprimidos. El bloque localizado del archivo comprimido se descomprime en la memoria principal durante la lectura.
- El segundo desplazamiento (‘granule_offset’ en el diagrama anterior) del archivo de marcas proporciona la ubicación del gránulo dentro de los datos del bloque sin comprimir.
- Para tablas con wide format y sin adaptive index granularity, ClickHouse usa archivos de marcas
.mrk, como se muestra arriba, que contienen entradas con dos direcciones de 8 bytes por entrada. Estas entradas son ubicaciones físicas de gránulos que tienen todos el mismo tamaño.
-
Para tablas con wide format y con granularidad adaptativa del índice, ClickHouse usa archivos de marcas
.mrk2, que contienen entradas similares a las de los archivos de marcas.mrk, pero con un tercer valor adicional por entrada: el número de filas del gránulo al que está asociada la entrada actual. -
Para tablas con compact format, ClickHouse usa archivos de marcas
.mrk3.
Por qué se usan archivos de marcas¿Por qué el índice primario no contiene directamente las ubicaciones físicas de los gránulos correspondientes a las marcas del índice?Porque, a la escala tan grande para la que está diseñado ClickHouse, es importante hacer un uso muy eficiente del disco y la memoria.El archivo del índice primario debe caber en la memoria principal.Para nuestra consulta de ejemplo, ClickHouse usó el índice primario y seleccionó un único gránulo que podría contener filas que coincidan con nuestra consulta. Solo para ese gránulo ClickHouse necesita entonces las ubicaciones físicas para leer en streaming las filas correspondientes y seguir procesándolas.Además, esta información de desplazamiento solo es necesaria para las columnas UserID y URL.La información de desplazamiento no es necesaria para las columnas que no se usan en la consulta, por ejemplo,
EventTime.Para nuestra consulta de ejemplo, ClickHouse solo necesita los dos desplazamientos de ubicación física para el gránulo 176 en el archivo de datos UserID (UserID.bin) y los dos desplazamientos de ubicación física para el gránulo 176 en el archivo de datos URL (URL.bin).La indirección que proporcionan los archivos de marcas evita almacenar directamente en el índice primario entradas con las ubicaciones físicas de los 1083 gránulos de las tres columnas, evitando así tener datos innecesarios (y potencialmente no utilizados) en la memoria principal.Uso de múltiples índices primarios
Las columnas secundarias de la clave pueden (o no) ser ineficientes
Analizamos un escenario en el que una consulta no filtra explícitamente por la primera columna de la clave, sino por una columna secundaria de la clave.Cuando una consulta filtra tanto por la primera columna de la clave como por cualquiera de las columnas posteriores de la clave, ClickHouse ejecuta una búsqueda binaria sobre las marcas de índice de la primera columna de la clave.
Usamos una consulta que calcula los 10 usuarios que han hecho clic con mayor frecuencia en la URL “http://public_search”:
Algoritmo de búsqueda por exclusión genérica
- una consulta que busca filas con el valor de URL = “W3”.
- una versión abstracta de nuestra tabla hits con valores simplificados para UserID y URL.
- la misma clave primaria compuesta (UserID, URL) para el índice. Esto significa que las filas se ordenan primero por los valores de UserID. Las filas con el mismo valor de UserID se ordenan después por URL.
- un tamaño de gránulo de dos; es decir, cada gránulo contiene dos filas.
- La marca de índice 0, para la que el valor de URL es menor que W3 y el valor de URL de la marca de índice inmediatamente siguiente también es menor que W3, puede excluirse porque las marcas 0 y 1 tienen el mismo valor de UserID. Tenga en cuenta que esta condición previa para la exclusión garantiza que el gránulo 0 esté compuesto por completo por valores de UserID U1, de modo que ClickHouse puede asumir que el valor máximo de URL del gránulo 0 también es menor que W3 y excluir ese gránulo.
- La marca de índice 1, para la que el valor de URL es menor (o igual) que W3 y el valor de URL de la marca de índice inmediatamente siguiente es mayor (o igual) que W3, se selecciona porque eso significa que el gránulo 1 podría contener filas con URL W3.
- Las marcas de índice 2 y 3, para las que el valor de URL es mayor que W3, pueden excluirse, ya que las marcas de índice de un índice primario almacenan los valores de las columnas clave de la primera fila de la tabla de cada gránulo, y las filas de la tabla están ordenadas en disco por los valores de las columnas clave; por lo tanto, los gránulos 2 y 3 no pueden contener el valor de URL W3.
ConclusiónEl algoritmo de búsqueda por exclusión genérica que ClickHouse utiliza en lugar del algoritmo de búsqueda binaria cuando una consulta filtra por una columna que forma parte de una clave compuesta, pero no es la primera columna de la clave, resulta más eficaz cuando la columna clave precedente tiene una cardinalidad menor.
Nota sobre el índice de omisión de datos
GRANULARITY 4 en la instrucción ALTER TABLE anterior)— el valor mínimo y máximo de URL:
La primera entrada del índice (‘mark 0’ en el diagrama anterior) almacena los valores mínimo y máximo de URL para las filas que pertenecen a los primeros 4 gránulos de nuestra tabla.
La segunda entrada del índice (‘mark 1’) almacena los valores mínimo y máximo de URL para las filas que pertenecen a los 4 gránulos siguientes de nuestra tabla, y así sucesivamente.
(ClickHouse también creó un archivo de marcas especial para el índice de omisión de datos a fin de localizar los grupos de gránulos asociados a las marcas del índice).
Debido a la cardinalidad igualmente alta de UserID y URL, este índice secundario de omisión de datos no puede ayudar a excluir gránulos cuando se ejecuta nuestra consulta que filtra por URL.
Es muy probable que el valor de URL concreto que busca la consulta (es decir, ‘http://public_search') esté entre el valor mínimo y máximo almacenado por el índice para cada grupo de gránulos, lo que obliga a ClickHouse a seleccionar ese grupo de gránulos (porque podría contener filas que coincidan con la consulta).
La necesidad de usar varios índices primarios
Opciones para crear índices primarios adicionales
- Crear una segunda tabla con una clave primaria diferente.
- Crear una vista materializada sobre la tabla existente.
- Añadir una proyección a la tabla existente.
Opción 1: Tablas secundarias
UserIDs no se ejecutará de forma muy eficiente con la nueva tabla adicional, porque UserID ahora es la segunda columna clave del índice primario de esa tabla y, por lo tanto, ClickHouse utilizará búsqueda por exclusión genérica para la selección de gránulos, lo cual no es muy eficaz cuando UserID y URL tienen una cardinalidad igual de alta.
Abre el cuadro de detalles para ver más información.
La consulta que filtra por UserIDs ahora tiene un rendimiento deficiente
La consulta que filtra por UserIDs ahora tiene un rendimiento deficiente
UserIDs y las consultas que filtran por URL:
Opción 2: Vistas materializadas
- cambiamos el orden de las columnas de la clave (en comparación con nuestra tabla original) en la clave primaria de la vista
- la vista materializada está respaldada por una tabla creada implícitamente cuyo orden de filas y cuyo índice primario se basan en la definición de clave primaria proporcionada
- la tabla creada implícitamente aparece en la consulta
SHOW TABLESy tiene un nombre que comienza con.inner - también es posible crear primero explícitamente la tabla de respaldo de una vista materializada, y después la vista puede apuntar a esa tabla mediante la cláusula
TO [db].[table] - usamos la palabra clave
POPULATEpara rellenar inmediatamente la tabla creada implícitamente con los 8,87 millones de filas de la tabla de origen hits_UserID_URL - si se insertan nuevas filas en la tabla de origen hits_UserID_URL, esas filas también se insertan automáticamente en la tabla creada implícitamente
- En la práctica, la tabla creada implícitamente tiene el mismo orden de filas y el mismo índice primario que la tabla secundaria que creamos explícitamente:
Opción 3: Proyecciones
- la proyección crea una tabla oculta cuyo orden de las filas y cuyo índice primario se basan en la cláusula
ORDER BYespecificada en la proyección - la tabla oculta no aparece en la consulta
SHOW TABLES - usamos la palabra clave
MATERIALIZEpara rellenar inmediatamente la tabla oculta con los 8,87 millones de filas de la tabla de origen hits_UserID_URL - si se insertan nuevas filas en la tabla de origen hits_UserID_URL, esas filas también se insertan automáticamente en la tabla oculta
- una consulta siempre apunta (sintácticamente) a la tabla de origen hits_UserID_URL, pero si el orden de las filas y el índice primario de la tabla oculta permiten ejecutar la consulta de forma más eficiente, se usará esa tabla oculta en su lugar
- tenga en cuenta que las proyecciones no hacen más eficientes las consultas que usan ORDER BY, aunque el ORDER BY coincida con la cláusula ORDER BY de la proyección (consulte https://github.com/ClickHouse/ClickHouse/issues/47333)
- En la práctica, la tabla oculta creada implícitamente tiene el mismo orden de las filas y el mismo índice primario que la tabla secundaria que creamos explícitamente:
Resumen
Ordenar eficientemente las columnas de la clave
- la eficiencia del filtrado sobre las columnas secundarias de la clave en las consultas, y
- la relación de compresión de los archivos de datos de la tabla.
UserID) a una URL (columna URL) se marcó como tráfico de bots (columna IsRobot).
Usaremos una clave primaria compuesta que contiene las tres columnas mencionadas anteriormente y que puede utilizarse para acelerar consultas típicas de analítica web que calculan:
- qué proporción del tráfico (qué porcentaje) hacia una URL concreta proviene de bots, o
- con qué confianza podemos afirmar que un usuario concreto es (o no) un bot (qué porcentaje del tráfico de ese usuario se considera tráfico de bots o no)
clickhouse client:
URL e IsRobot, y, por lo tanto, el orden de estas columnas en una clave primaria compuesta es importante tanto para acelerar de forma eficiente las consultas que filtran por esas columnas como para lograr ratios de compresión óptimos en los archivos de datos de las columnas de la tabla.
Para demostrarlo, estamos creando dos versiones de tabla para nuestros datos de análisis de tráfico de bots:
- una tabla
hits_URL_UserID_IsRobotcon la clave primaria compuesta(URL, UserID, IsRobot), donde ordenamos las columnas clave por cardinalidad en orden descendente - una tabla
hits_IsRobot_UserID_URLcon la clave primaria compuesta(IsRobot, UserID, URL), donde ordenamos las columnas clave por cardinalidad en orden ascendente
hits_URL_UserID_IsRobot con la clave primaria compuesta (URL, UserID, IsRobot):
hits_IsRobot_UserID_URL con la clave primaria compuesta (IsRobot, UserID, URL):
Filtrado eficiente en columnas secundarias de la clave
UserID de la tabla en la que ordenamos las columnas de la clave (URL, UserID, IsRobot) por cardinalidad en orden descendente:
(IsRobot, UserID, URL) por cardinalidad de forma ascendente:
Relación de compresión óptima de los archivos de datos
UserID entre las dos tablas que creamos anteriormente:
UserID es significativamente mayor en la tabla en la que ordenamos las columnas clave (IsRobot, UserID, URL) por cardinalidad en orden ascendente.
Aunque en ambas tablas se almacenan exactamente los mismos datos (insertamos las mismas 8,87 millones de filas en ambas tablas), el orden de las columnas clave en la clave primaria compuesta influye significativamente en cuánto espacio en disco requieren los datos comprimidos en los archivos de datos de columnas de la tabla:
- en la tabla
hits_URL_UserID_IsRobot, con la clave primaria compuesta(URL, UserID, IsRobot), donde ordenamos las columnas clave por cardinalidad en orden descendente, el archivo de datosUserID.binocupa 11.24 MiB de espacio en disco - en la tabla
hits_IsRobot_UserID_URL, con la clave primaria compuesta(IsRobot, UserID, URL), donde ordenamos las columnas clave por cardinalidad en orden ascendente, el archivo de datosUserID.binocupa solo 877.47 KiB de espacio en disco
cl, y las filas que tienen el mismo valor cl se ordenan por su valor ch. Y como la primera columna clave cl tiene baja cardinalidad, es probable que haya filas con el mismo valor cl. Debido a ello, también es probable que los valores ch estén ordenados (localmente, para las filas con el mismo valor cl).
Si en una columna se colocan datos similares cerca unos de otros, por ejemplo mediante ordenación, esos datos se comprimirán mejor.
En general, un algoritmo de compresión se beneficia de la longitud de las secuencias de datos (cuantos más datos vea, mejor para la compresión)
y de la localidad (cuanto más similares sean los datos, mejor será la relación de compresión).
A diferencia del diagrama anterior, el siguiente diagrama muestra esquemáticamente el orden en disco de las filas para una clave primaria en la que las columnas clave están ordenadas por cardinalidad en orden descendente:
Ahora las filas de la tabla se ordenan primero por su valor de ch, y las filas que tienen el mismo valor de ch se ordenan por su valor de cl.
Pero, como la primera columna de la clave, ch, tiene una cardinalidad alta, es poco probable que haya filas con el mismo valor de ch. Y, debido a ello, también es poco probable que los valores de cl estén ordenados (localmente, para las filas con el mismo valor de ch).
Por lo tanto, lo más probable es que los valores de cl estén en un orden aleatorio y, en consecuencia, tengan una mala localidad y una mala relación de compresión, respectivamente.
Resumen
Identificar filas individuales de forma eficiente
Un ejemplo concreto
- el orden de inserción de las filas cuando cambia el contenido (por ejemplo, debido a las pulsaciones al escribir el texto en el área de texto) y
- el orden en disco de los datos de las filas insertadas cuando se usa
PRIMARY KEY (hash):
hash se usa como columna de clave primaria,
- se pueden recuperar filas específicas muy rápidamente, pero
- las filas de la tabla (los datos de sus columnas) se almacenan en disco ordenadas de forma ascendente por los valores hash (únicos y aleatorios). Por lo tanto, los valores de la columna de contenido también se almacenan en orden aleatorio, sin localidad de datos, lo que da como resultado una relación de compresión subóptima para el archivo de datos de la columna de contenido.
- un hash del contenido, como se comentó antes, que es distinto para datos distintos, y
- un hash sensible a la localidad (huella) que no cambia ante pequeños cambios en los datos.
- el orden de inserción de las filas cuando cambia el contenido (por ejemplo, debido a las pulsaciones al escribir el texto en el área de texto) y
- el orden en disco de los datos de las filas insertadas cuando se usa la
PRIMARY KEY (fingerprint, hash)compuesta:
fingerprint, y para las filas con el mismo valor de fingerprint, su valor de hash determina el orden final.
Como los datos que difieren solo en pequeños cambios obtienen el mismo valor de fingerprint, ahora los datos similares se almacenan en disco cerca unos de otros en la columna de contenido. Y eso es muy bueno para la relación de compresión de la columna de contenido, ya que un algoritmo de compresión, en general, se beneficia de la localidad de los datos (cuanto más similares sean los datos, mejor será la relación de compresión).
La contrapartida es que se requieren dos campos (fingerprint y hash) para recuperar una fila específica y así aprovechar de forma óptima el índice primario que resulta de la PRIMARY KEY (fingerprint, hash) compuesta.