clickhouse/mysql的 join/where操作先后问题
最近发现一个问题,在一个53亿条的clickhouse数据库中,在已经按时间戳进行分区的情况下,这样的查询:
select * FROM
web3.bsc_logs as l
left join web3.bsc_transactions_extra as ex on l.tx_hash = ex.tx_hash
where l.address ilike '0x2ba4f9828a14511c2e04a885ec1e04e1d42898ac' and l.timestamp > 1704038400 and l.timestamp < 1704124800
会导致64核的cpu使用了33个核:
然后换了一个语句查询就不会存在这种情况了:
select * from
(select * from web3.bsc_logs
where
address ilike '0x2ba4f9828a14511c2e04a885ec1e04e1d42898ac'
and timestamp > 1704038400
and timestamp < 1704124800 )
as a left join web3.bsc_transactions_extra as ex on a.tx_hash = ex.tx_hash
另外,如果不进行join的话速度也非常快:
select * FROM
web3.bsc_logs as l
where l.address ilike '0x2ba4f9828a14511c2e04a885ec1e04e1d42898ac' and l.timestamp > 1704038400 and l.timestamp < 1704124800
由此可见join非常慢,几乎可以理解把50亿条记录先拿来join再执行where了。
mysql的执行顺序一般是这样的:
在 MySQL 中,一般情况下,WHERE 过滤条件是在执行 JOIN 操作之后应用的,而不是在之前执行。
MySQL的查询执行顺序通常是按照以下顺序进行的:
-
FROM 子句:指定要查询的表和关联的表。
-
JOIN 子句:执行表之间的连接操作,根据连接条件将相关的行组合在一起。
-
WHERE 子句:应用过滤条件,根据条件筛选出满足条件的行。
-
GROUP BY 子句:按照指定的列对结果进行分组。
-
HAVING 子句:应用分组后的过滤条件,根据条件筛选出满足条件的组。
-
SELECT 子句:选择要返回的列。
-
ORDER BY 子句:对结果进行排序。
-
LIMIT 子句:限制结果集的行数。
需要注意的是,MySQL的查询优化器可以根据查询的复杂性、索引、统计信息和其他因素来决定执行计划。优化器可能会选择对查询进行重写或重新排序,以找到更高效的执行计划。因此,实际的执行顺序可能会有所不同。
总结起来,在大多数情况下,MySQL的查询执行顺序是先执行 FROM 和 JOIN 操作,然后再应用 WHERE 过滤条件。不过,MySQL的优化器在选择执行计划时可能会调整顺序以获得更好的性能。
gpt说的clickhouse和mysql的区别:
虽然在一般情况下,MySQL 和 ClickHouse 都会先执行 JOIN 操作,然后再应用 WHERE 过滤条件,但在某些情况下,两者的行为可能略有不同。
在 MySQL 中,优化器会根据查询的复杂性、索引和统计信息等因素来决定执行计划。优化器可能会选择重新排序操作的顺序,以获得更好的性能。这意味着在某些情况下,MySQL 可能会选择先应用 WHERE 过滤条件,然后再执行 JOIN 操作。
在 ClickHouse 中,执行顺序通常是先执行 JOIN 操作,然后再应用 WHERE 过滤条件。ClickHouse 的查询引擎会尽量优化查询计划,以提高查询性能。
总体而言,MySQL 和 ClickHouse 的执行顺序在一般情况下是相似的,即先执行 JOIN 操作,然后再应用 WHERE 过滤条件。然而,具体的执行顺序可能会受到查询优化器的决策以及查询的复杂性、索引和统计信息等因素的影响。因此,对于特定的查询,最好根据实际情况进行测试和观察执行计划,以了解其具体的执行顺序。