JOIN, con una amplia variedad de algoritmos de JOIN. Para maximizar el rendimiento, recomendamos seguir las sugerencias de optimización de JOIN que se enumeran en esta guía.
- Para un rendimiento óptimo, conviene reducir el número de
JOINen las consultas, especialmente en cargas de trabajo analíticas en tiempo real donde se requiere latencia de milisegundos. Intenta no superar los 3 o 4 joins por consulta. En la sección de modelado de datos detallamos varios cambios para minimizar los joins, como la desnormalización, los diccionarios y las vistas materializadas. - A partir de ClickHouse 24.12, el planificador de consultas reordena automáticamente los joins entre dos tablas para situar la tabla más pequeña en el lado derecho y así obtener un rendimiento óptimo. En la versión 25.9, esto se amplió para optimizar el orden de join en consultas que combinan tres o más tablas.
- Si tu consulta requiere un direct join, es decir, un
LEFT ANY JOIN, como se muestra a continuación, recomendamos usar Dictionaries siempre que sea posible.
- Si realizas inner joins, a menudo resulta más eficiente escribirlos como subconsultas con la cláusula
IN. Considera las siguientes consultas, que son funcionalmente equivalentes. Ambas encuentran el número depostsque no mencionan ClickHouse en la pregunta, pero sí en loscomments.
ANY INNER JOIN en lugar de un simple INNER JOIN, ya que no queremos el producto cartesiano; es decir, queremos solo una coincidencia por cada post.
Este join puede reescribirse mediante una subconsulta, lo que mejora significativamente el rendimiento:
JOIN. Considere el siguiente ejemplo, en el que queremos calcular el número de votos positivos de las publicaciones relacionadas con Java desde 2020.
Una consulta ingenua, con la tabla más grande en el lado izquierdo, se completa en 56 s:
INNER JOIN a una subconsulta, como se indicó anteriormente, manteniendo el filtro tanto en la consulta externa como en la interna.
Elección de un algoritmo de JOIN
Estos algoritmos determinan cómo se planifica y ejecuta una consulta con JOIN. De forma predeterminada, ClickHouse utiliza el algoritmo direct o hash join en función del tipo de JOIN y la strictness utilizados, así como del engine de las tablas involucradas. Como alternativa, ClickHouse puede configurarse para elegir de forma adaptativa y cambiar dinámicamente el algoritmo de JOIN que se usará en tiempo de ejecución, según la disponibilidad y el uso de recursos: cuando
join_algorithm=auto, ClickHouse prueba primero el algoritmo hash join y, si se supera el límite de memoria de ese algoritmo, cambia sobre la marcha a partial merge join. Puede ver qué algoritmo se eligió mediante trace logging. ClickHouse también le permite especificar directamente el algoritmo de JOIN deseado mediante la configuración join_algorithm.
A continuación se muestran los tipos de JOIN admitidos por cada algoritmo de JOIN, y deben tenerse en cuenta antes de realizar la optimización:
Puede encontrar aquí una descripción detallada de cada algoritmo
JOIN, incluidas sus ventajas, desventajas y propiedades de escalado.
La elección de los algoritmos de JOIN adecuados depende de si busca optimizar la memoria o el rendimiento.
Optimización del rendimiento de JOIN
-
(1) Si los datos de la tabla del lado derecho pueden precargarse en una estructura de datos key-value en memoria y de baja latencia, por ejemplo, un diccionario, y si la clave de join coincide con el atributo clave del almacenamiento key-value subyacente, y si la semántica de
LEFT ANY JOINes adecuada, entonces puede aplicarse el direct join, que ofrece el enfoque más rápido. - (2) Si el orden físico de las filas de su tabla coincide con el orden de clasificación de la clave de join, entonces depende. En este caso, el full sorting merge join omite la fase de ordenación, lo que reduce significativamente el uso de memoria y, según el tamaño de los datos y la distribución de valores de la clave de join, puede ofrecer tiempos de ejecución más rápidos que algunos de los algoritmos hash join.
- (3) Si la tabla derecha cabe en memoria, incluso con la sobrecarga adicional de uso de memoria del parallel hash join, entonces este algoritmo o el hash join pueden ser más rápidos. Esto depende del volumen de datos, de los tipos de datos y de la distribución de valores de las columnas de la clave de join.
- (4) Si la tabla derecha no cabe en memoria, entonces vuelve a depender. ClickHouse ofrece tres algoritmos de join que no están limitados por la memoria. Los tres hacen spill de datos a disco de forma temporal. Full sorting merge join y partial merge join requieren una ordenación previa de los datos. En cambio, grace hash join construye tablas hash a partir de los datos. Según el volumen de datos, los tipos de datos y la distribución de valores de las columnas de la clave de join, puede haber casos en los que construir tablas hash a partir de los datos sea más rápido que ordenarlos. Y viceversa.
Optimización para minimizar el uso de memoria
- (1) Si el orden físico de las filas de su tabla coincide con el orden de clasificación de la clave del join, el uso de memoria del full sorting merge join será el mínimo posible. Además, ofrece una buena velocidad de join porque la fase de ordenación está deshabilitada.
- (2) El grace hash join puede ajustarse para lograr un uso de memoria muy bajo configurando un número elevado de buckets, a costa de la velocidad del join. El partial merge join usa intencionadamente poca memoria principal. El full sorting merge join con la ordenación externa habilitada suele usar más memoria que el partial merge join (suponiendo que el orden de las filas no coincida con el orden de clasificación de la clave), con la ventaja de ofrecer un tiempo de ejecución del join significativamente mejor.