联表操作Join在MySQL和ClickHouse中的异同
MySQL Join
mysql只支持一种join算法:Nested-Loop Join(嵌套循环连接),但Nested-Loop Join有三种变种:Simple Nested-Loop Join,Index Nested-Loop Join,Block Nested-Loop Join。
# MySQL Join 语法
SELECT *
FROM table1
join_type table2
[ON (join_condition)]
ClickHouse Join
# ClickHouse Join 语法
SELECT <expr_list>
FROM <left_subquery>
[GLOBAL] [ANY|ALL] INNER|LEFT|RIGHT|FULL|CROSS [OUTER] JOIN <right_subquery>
(ON <expr_list>)|(USING <column_list>) ...
使用建议:
从子查询中删除所有JOIN不需要的列。
当执行JOIN查询时,因为与其他阶段相比没有进行执行顺序的优化:JOIN优先于WHERE与聚合执行。因此,为了显示的指定执行顺序,我们推荐你使用子查询的方式执行JOIN。
在使用ALL修饰符对JOIN进行修饰时,如果右表中存在多个与左表关联的数据,那么系统则将右表中所有可以与左表关联的数据全部返回在结果中。这与SQL标准的JOIN行为相同。 在使用ANY修饰符对JOIN进行修饰时,如果右表中存在多个与左表关联的数据,那么系统仅返回第一个与左表匹配的结果。如果左表与右表一一对应,不存在多余的行时,ANY与ALL的结果相同。
默认是ALL,如果不需要匹配右表所有可以与左表关联的数据,建议使用ANY,这对执行速度有很大的提升。
示例:
SELECT
CounterID,
hits,
visits
FROM
(
SELECT
CounterID,
count() AS hits
FROM test.hits
GROUP BY CounterID
) ANY LEFT JOIN
(
SELECT
CounterID,
sum(Sign) AS visits
FROM test.visits
GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10
参考:
- https://www.jianshu.com/p/16ad9669d8a9 【MySQL Join的底层实现原理】
- http://www.jasongj.com/2015/03/07/Join1/ 【SQL优化 Merge Join vs. Hash Join vs. Nested Loop】
- https://www.cnblogs.com/JohnABC/p/7150921.html 【MySQL-join的实现原理、优化及NLJ算法】
- https://clickhouse.yandex/docs/zh/query_language/select/ 【ClickHouse文档中的Join使用说明】