2023-03-29 duckdb-查询计划-逻辑计划和物理计划_duckdb查询文本文件-优快云博客

本文链接：https://blog.youkuaiyun.com/adofsauron/article/details/129842198

本文介绍了DuckDB如何将ANSI SQL转化为逻辑查询计划，并进行基于规则的优化，然后详细阐述了逻辑计划如何转换为考虑操作代价的物理计划，分析了相关的核心函数和优化过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

摘要:

duckdb的查询计划倒是很复合教科书的定义, 先将ANSI SQL转换成查询树, 生成逻辑查询计划. 然后将逻辑查询计划的节点转换成物理计划的节点.

相比于mysql的没有清晰划分的逻辑计划和物理计划, 所有操作都是对着转换后的查询树节点处理,看起来清晰了很多.

本文对逻辑计划和物理计划做一些分析. 会涉及到基于规则的优化,但是不涉及基于代价的优化.基于代价的优化随后单独分析.

参考

https://www.youtube.com/watch?v=bXfvmeWsQ78

ANSI SQL生成逻辑计划

duckdb采用了精简的postgres的语法树构建模块
可以理解为将ANSI SQL先进行词法分析，也就是进行分词. 然后进行语法分析, 也就是构建查询树节点.
其实任何一个关系型数据库的这块处理都差不多, 包括mysql, 关键在于要理解经过语法分析后的查询树节点的数据结构, 这些数据结构是后续生成逻辑计划的输入

核心处理:

Planner::CreatePlan

#0  duckdb::Planner::CreatePlan (this=0x7ffc42d9b060, statement=...) at /root/work/duckdb-dev/trunk/duckdb-0.7.1/src/planner/planner.cpp:29
#1  0x00000000045251dc in duckdb::Planner::CreatePlan (this=0x7ffc42d9b060, statement=std::unique_ptr<duckdb::SQLStatement> = {...})
    at /root/work/duckdb-dev/trunk/duckdb-0.7.1/src/planner/planner.cpp:137
#2  0x00000000033c03d4 in duckdb::ClientContext::CreatePreparedStatement (this=0x615000011d90, lock=..., 
    query="SELECT     *     FROM     c     WHERE     EXISTS (         SELECT         1         FROM         d         WHERE         c.c1 = d.d1     ) ;", 
    statement=std::unique_ptr<duckdb::SQLStatement> = {...}, values=0x0) at /root/work/duckdb-dev/trunk/duckdb-0.7.1/src/main/client_context.cpp:325
#3  0x00000000033c81dd in operator() (__closure=0x604000109010) at /root/work/duckdb-dev/trunk/duckdb-0.7.1/src/main/client_context.cpp:526
#4  0x00000000035e9aae in std::__invoke_impl<void, duckdb::ClientContext::PrepareInternal(duckdb::ClientContextLock&, std::unique_ptr<duckdb::SQLStatement>)::<lambda()>&>(std::__invoke_other, struct {...} &) (__f=...) at /usr/include/c++/11/bits/invoke.h:61
#5  0x000000000344eeb6 in std::__invoke_r<void, duckdb::ClientContext::PrepareInternal(duckdb::ClientContextLock&, std::unique_ptr<duckdb::SQLStatement>)::<lambda()>&>(struct {...} &) (
    __fn=...) at /usr/include/c++/11/bits/invoke.h:154
#6  0x000000000344b525 in std::_Function_handler<void(), duckdb::ClientContext::PrepareInternal(duckdb::ClientContextLock&, std::unique_ptr<duckdb::SQLStatement>)::<lambda()> >::_M_invoke(const std::_Any_data &) (__functor=...) at /usr/include/c++/11/bits/std_function.h:290
#7  0x0000000003642bc4 in std::function<void ()>::operator()() const (this=0x7ffc42d9b520) at /usr/include/c++/11/bits/std_function.h:590
#8  0x00000000033d5b2b in duckdb::ClientContext::RunFunctionInTransactionInternal(duckdb::ClientContextLock&, std::function<void ()> const&, bool) (this=0x615000011d90, lock=..., fun=..., 
    requires_valid_transaction=false) at /root/work/duckdb-dev/trunk/duckdb-0.7.1/src/main/client_context.cpp:945
#9  0x00000000033c8a4d in duckdb::ClientContext::PrepareInternal (this=0x615000011d90, lock=..., statement=std::unique_ptr<duckdb::SQLStatement> = {...})
    at /root/work/duckdb-dev/trunk/duckdb-0.7.1/src/main/client_context.cpp:525
#10 0x00000000033c91bd in duckdb::ClientContext::Prepare (this=0x615000011d90, statement=std::unique_ptr<duckdb::SQLStatement> = {...})
    at /root/work/duckdb-dev/trunk/duckdb-0.7.1/src/main/client_context.cpp:537
#11 0x00000000033eb869 in duckdb::Connection::Prepare (this=0x6030000536e0, statement=std::unique_ptr<duckdb::SQLStatement> = {...})
    at /root/work/duckdb-dev/trunk/duckdb-0.7.1/src/main/connection.cpp:127
#12 0x0000000001cbbccd in duckdb_shell_sqlite3_prepare_v2 (db=0x60b000002fb0, 
    zSql=0x6110000ac5c0 "SELECT     *     FROM     c     WHERE     EXISTS (         SELECT         1         FROM         d         WHERE         c.c1 = d.d1     ) ;", nByte=-1, 
    ppStmt=0x7ffc42d9be20, pzTail=0x7ffc42d9be40) at /root/work/duckdb-dev/trunk/duckdb-0.7.1/tools/sqlite3_api_wrapper/sqlite3_api_wrapper.cpp:191
#13 0x0000000001c4ed35 in shell_exec (pArg=0x7ffc42d9c110, 
    zSql=0x6110000ac5c0 "SELECT     *     FROM     c     WHERE     EXISTS (         SELECT         1         FROM         d         WHERE         c.c1 = d.d1     ) ;", 
    pzErrMsg=0x7ffc42d9bf10) at /root/work/duckdb-dev/trunk/duckdb-0.7.1/tools/shell/shell.c:13138
#14 0x0000000001c8749d in runOneSqlLine (p=0x7ffc42d9c110, 
    zSql=0x6110000ac5c0 "SELECT     *     FROM     c     WHERE     EXISTS (         SELECT         1         FROM         d         WHERE         c.c1 = d.d1     ) ;", in=0x0, startline=79)
    at /root/work/duckdb-dev/tr