CMU15445 (Fall 2020) 数据库系统 Project#3 - Query Execution 详解
前言
经过前两个实验的铺垫,终于到了执行 SQL 语句的时候了。这篇博客将会介绍 SQL 执行计划实验的实现过程,下面进入正题。
总体架构
一条 SQL 查询的处理流程如下为:
- SQL 被 Parser 解析为抽象语法树 AST
- Binber 将 AST转换为 Bustub 可以理解的更高级的 AST
- Tree rewriter 将语法树转换为逻辑执行计划
- Optimizer 对逻辑计划进行优化,生成最终要执行的物理执行计划
- 执行引擎执行物理执行计划,返回查询结果
物理执行计划定义了具体的执行方式,比如逻辑计划中的 Join 可以被替换为 Nest loop join、 Hash join 或者 Index join。由于 Fall 2020 版本的代码没有 Parser 和 Optimizer,所以测试用例中都是手动构造的物理执行计划。
系统目录
目录结构
数据库会维护一个内部目录,以跟踪有关数据库的元数据。目录中可以存放数据表的信息、索引信息和统计数据。Bustub 中使用 Catalog
类表示系统目录,内部存放 table_oid_t
到 TableMetadata
的映射表以及 index_oid_t
到 IndexInfo
的映射表。
TableMetadata
描述了一张表的信息,包括表名、Schema、表 id 和表的指针。代码如下所示:
struct TableMetadata {
TableMetadata(Schema schema, std::string name, std::unique_ptr<TableHeap> &&table, table_oid_t oid)
: schema_(std::move(schema)), name_(std::move(name)), table_(std::move(table)), oid_(oid) {}
Schema schema_;
std::string name_;
std::unique_ptr<TableHeap> table_;
table_oid_t oid_;
};
TableHeap
代表了一张表,实现了 tuple 的增删改查操作。它的内部存放了第一个表页 TablePage
的 id,由于每个 TablePage
都会存放前一个和下一个表页的 id,这样就将表组织为双向链表,可以通过 TableIterator
进行迭代。
TablePage
使用分槽页结构(slotted page),tuple 从后往前插入,每个 tuple 由一个 RID
标识。
class RID {
public:
RID() = default;
/**
* Creates a new Record Identifier for the given page identifier and slot number.
*/
RID(page_id_t page_id, uint32_t slot_num) : page_id_(page_id), slot_num_(slot_num) {}
explicit RID(int64_t rid) : page_id_(static_cast<page_id_t>(rid >> 32)), slot_num_(static_cast<uint32_t>(rid)) {}
inline int64_t Get() const { return (static_cast<int64_t>(page_id_)) << 32 | slot_num_; }
inline page_id_t GetPageId() const { return page_id_; }
inline uint32_t GetSlotNum() const { return slot_num_; }
bool operator==(const RID &other) const { return page_id_ == other.page_id_ && slot_num_ == other.slot_num_; }
private:
page_id_t page_id_{INVALID_PAGE_ID};
uint32_t slot_num_{0}; // logical offset from 0, 1...
};
表管理
Catalog
中有三个与表相关的方法:CreateTable
、GetTable(const std::string &table_name)
和 GetTable(table_oid_t table_oid)
,第一个方法用于创建一个新的表,后面两个方法用于获取表元数据:
/**
* Create a new table and return its metadata.
* @param txn the transaction in which the table is being created
* @param table_name the name of the new table
* @param schema the schema of the new table
* @return a pointer to the metadata of the new table
*/
TableMetadata *CreateTable(Transaction *txn, const std::string &table_name, const Schema &schema) {
BUSTUB_ASSERT(names_.count(table_name) == 0, "Table names should be unique!");
auto tid = next_table_oid_++;
auto table_heap = std::make_unique<TableHeap>(bpm_, lock_manager_, log_manager_, txn);
tables_[tid] = std::make_unique<TableMetadata>(schema, table_name, std::move(table_heap), tid);
names_[table_name] = tid;
return tables_[tid].get();
}
/** @return table metadata by name */
TableMetadata *GetTable(const std::string &table_name) {
auto it = names_.find(table_name);
if (it == names_.end()) {
throw std::out_of_range("Table is not found");
}
return tables_[it->second].get();
}
/** @return table metadata by oid */
TableMetadata *GetTable(table_oid_t table_oid) {
auto it = tables_.find(table_oid);
if (it == tables_.end()) {
throw std::out_of_range("Table is not found");
}
return it->second.get();
}
索引管理
创建索引
Catalog
使用 CreateIndex()
方法创建索引,创建的时候需要将表中的数据转换为键值对插入索引中:
/**
* Create a new index, populate existing data of the table and return its metadata.
* @param txn the transaction in which the table is being created
* @param index_name the name of the new index
* @param table_name the name of the table
* @param schema the schema of the table
* @param key_schema the schema of the key
* @param key_attrs key attributes
* @param keysize size of the key
* @return a pointer to the metadata of the new table
*/
template <class KeyType, class ValueType, class KeyComparator>
IndexInfo *CreateIndex(Transaction *txn, const std::string &index_name, const std::string &table_name,
const Schema &schema, const Schema &key_schema, const std::vector<uint32_t> &key_attrs,
size_t keysize) {
BUSTUB_ASSERT(index_names_.count(index_name) == 0, "Index names should be unique!");
auto id = next_index_oid_++;
auto meta = new IndexMetadata(index_name, table_name, &schema, key_attrs);
auto index = std::make_unique<BPLUSTREE_INDEX_TYPE>(meta, bpm_);
// 初始化索引
auto table = GetTable(table_name)->table_.get();
for (auto it = table->Begin(txn); it != table->End(); ++it) {
index->InsertEntry(it->KeyFromTuple(schema, key_schema, key_attrs), it->GetRid(), txn);
}
indexes_[id] = std::make_unique<IndexInfo>(key_schema, index_name, std::move(index), id, table_name, keysize);
index_names_[table_name][index_name] = id;
return indexes_[id].get();
}
查询索引
数据库中有多个表,一个表可以拥有多个索引,但是每个索引对应一个全局唯一的 index_oid_t
:
IndexInfo *GetIndex(const std::string &index_name, const std::string &table_name) {
auto it = index_names_.find(table_name);
if (it == index_names_.end()) {
throw std::out_of_range("Table is not found");
}
auto iit = it->second.find(index_name);
if (iit == it->second.end()) {
throw std::out_of_range("Index is not found");
}
return indexes_[iit->second].get();
}
IndexInfo *GetIndex(index_oid_t index_oid) {
auto it = indexes_.find(index_oid);
if (it == indexes_.end()) {
throw std::out_of_range("Index is not found");
}
return it->second.get();
}
std::vector<IndexInfo *> GetTableIndexes(const std::string &table_name) {
auto it = index_names_.find(table_name);
if (it == index_names_.end()) {
return {};
};
std::vector<IndexInfo *> indexes;
for (auto &[name, id] : it->second) {
indexes.push_back(GetIndex(id));
}
return indexes;
}
执行器
如下图的右下角所示,执行计划由一系列算子组合而成,每个算子可以拥有自己的子算子,数据从子算子流向父算子,最终从根节点输出执行结果。执行计划有三种执行模型:
-
迭代模型:每个算子都会实现
Next()
方法,父算子调用子算子的Next()
方法获取一条记录,外部通过不断调用根节点的Next()
方法直至没有更多数据输出。这种方法的优点就是一次只产生一条 Tuple,内存占用小 -
物化模型:每个算子一次性返回所有记录
-
向量模型:迭代模型和物化模型的折中版本,一次返回一批数据
本次实验使用迭代模型,伪代码如下图所示:
Bustub 使用执行引擎 ExecutionEngine
执行物理计划,这个类的代码很简洁,只有一个 Execute()
方法。可以看到这个方法会先将执行计划转换为对应的执行器 executor
,使用 Init()
初始化后循环调用 executor
的 Next()
方法获取查询结果:
class ExecutionEngine {
public:
ExecutionEngine(BufferPoolManager *bpm, TransactionManager *txn_mgr, Catalog *catalog)
: bpm_(bpm), txn_mgr_(txn_mgr), catalog_(catalog) {}
DISALLOW_COPY_AND_MOVE(ExecutionEngine);
bool Execute(const AbstractPlanNode *plan, std::vector<Tuple> *result_set, Transaction *txn,
ExecutorContext *exec_ctx) {
// construct executor
auto executor = ExecutorFactory::CreateExecutor(exec_ctx, plan);
// prepare
executor->Init();
// execute
try {
Tuple tuple;
RID rid;
while (executor->Next(&tuple, &rid)) {
if (result_set != nullptr) {
result_set->push_back(tuple);
}
}
} catch (Exception &e) {
// TODO(student): handle exceptions
}
return true;
}
private:
[[maybe_unused]] BufferPoolManager *bpm_;
[[maybe_unused]] TransactionManager *txn_mgr_;
[[maybe_unused]] Catalog *catalog_;
};
全表扫描
SeqScanExecutor
用于进行全表扫描操作,内部带有 SeqScanPlan
执行计划:
/**
* SeqScanExecutor executes a sequential scan over a table.
*/
class SeqScanExecutor : public AbstractExecutor {
public:
/**
* Creates a new sequential scan executor.
* @param exec_ctx the executor context
* @param plan the sequential scan plan to be executed
*/
SeqScanExecutor(ExecutorContext *exec_ctx, const SeqScanPlanNode *plan);
void Init() override;
bool Next(Tuple *tuple, RID *rid) override;
const Schema *GetOutputSchema() override { return plan_->OutputSchema(); }
private:
/** The sequential scan plan node to be executed. */
const SeqScanPlanNode *plan_;
TableMetadata *table_metadata_;
TableIterator it_;
};
SeqScanPlan
声明如下,Schema *output
指明了输出列,table_oid
代表被扫描的表,而 AbstractExpression *predicate
代表谓词算子:
/**
* SeqScanPlanNode identifies a table that should be scanned with an optional predicate.
*/
class SeqScanPlanNode : public AbstractPlanNode {
public:
/**
* Creates a new sequential scan plan node.
* @param output the output format of this scan plan node
* @param predicate the predicate to scan with, tuples are returned if predicate(tuple) = true or predicate = nullptr
* @param table_oid the identifier of table to be scanned
*/
SeqScanPlanNode(const Schema *output, const AbstractExpression *predicate, table_oid_t table_oid)
: AbstractPlanNode(output, {}), predicate_{predicate}, table_oid_(table_oid) {}
PlanType GetType() const override { return PlanType::SeqScan; }
/** @return the predicate to test tuples against; tuples should only be returned if they evaluate to true */
const AbstractExpression *GetPredicate() const { return predicate_; }
/** @return the identifier of the table that should be scanned */
table_oid_t GetTableOid() const { return table_oid_; }
private:
/** The predicate that all returned tuples must satisfy. */
const AbstractExpression *predicate_;
/** The table whose tuples should be scanned. */
table_oid_t table_oid_;
};
举个栗子,SELECT name, age FROM t_student WHERE age > 16
的 age > 16
部分就是 predicate
,实际数据类型为 ComparisonExpression
,而 predicate
又由 ColumnValueExpression
(代表 age
列的值) 和 ConstantValueExpression
(代表 16)组成。
要实现全表扫描只需在 Next
函数中判断迭代器所指的 tuple 是否满足查询条件并递增迭代器,如果满足条件就返回该 tuple,不满足就接着迭代。
SeqScanExecutor::SeqScanExecutor(ExecutorContext *exec_ctx, const SeqScanPlanNode *plan)
: AbstractExecutor(exec_ctx),
plan_(plan), table_metadata_(exec_ctx->GetCatalog()->GetTable(plan->GetTableOid())) {}
void SeqScanExecutor::Init() { it_ = table_metadata_->table_->Begin(exec_ctx_->GetTransaction()); }
bool SeqScanExecutor::Next(Tuple *tuple, RID *rid) {
auto predicate = plan_->GetPredicate();
while (it_ != table_metadata_->table_->End()) {
*tuple = *it_++;
*rid = tuple->GetRid();
if (!predicate || predicate->Evaluate(tuple, &table_metadata_->schema_).GetAs<bool>()) {
// 只保留输出列
std::vector<Value> values;
for (auto &col : GetOutputSchema()->GetColumns()) {
values.push_back(col.GetExpr()->Evaluate(tuple, &table_metadata_->schema_));
}
*tuple = {values, GetOutputSchema()};
return true;
}
}
return false;
}
测试用例中通过下述代码手动构造出 SELECT colA, colB FROM test_1 WHERE colA < 500
的全表扫描执行计划并执行:
// Construct query plan
TableMetadata *table_info = GetExecutorContext()->GetCatalog()->GetTable("test_1");
Schema &schema = table_info->schema_;
auto *colA = MakeColumnValueExpression(schema, 0, "colA");
auto *colB = MakeColumnValueExpression(schema, 0, "colB");
auto *const500 = MakeConstantValueExpression(ValueFactory::GetIntegerValue(500));
auto *predicate = MakeComparisonExpression(colA, const500, ComparisonType::LessThan);
auto *out_schema = MakeOutputSchema({{"colA", colA}, {"colB", colB}});
SeqScanPlanNode plan{out_schema, predicate, table_info->oid_};
// Execute
std::vector<Tuple> result_set;
GetExecutionEngine()->Execute(&plan, &result_set, GetTxn(), GetExecutorContext());
索引扫描
上一节中实现了 B+ 树索引,使用索引可以减小查询范围,大大加快查询速度。由于 IndexScanExecutor
不是模板类,所以这里使用的 KeyType
为 GenericKey<8>
,KeyComparator
为 GenericComparator<8>
:
#define B_PLUS_TREE_INDEX_ITERATOR_TYPE IndexIterator<GenericKey<8>, RID, GenericComparator<8>>
#define B_PLUS_TREE_INDEX_TYPE BPlusTreeIndex<GenericKey<8>, RID, GenericComparator<8>>
class IndexScanExecutor : public AbstractExecutor {
public:
/**
* Creates a new index scan executor.
* @param exec_ctx the executor context
* @param plan the index scan plan to be executed
*/
IndexScanExecutor(ExecutorContext *exec_ctx, const IndexScanPlanNode *plan);
const Schema *GetOutputSchema() override { return plan_->OutputSchema(); };
void Init() override;
bool Next(Tuple *tuple, RID *rid) override;
private:
/** The index scan plan node to be executed. */
const IndexScanPlanNode *plan_;
IndexInfo *index_info_;
B_PLUS_TREE_INDEX_TYPE *index_;
TableMetadata *table_metadata_;
B_PLUS_TREE_INDEX_ITERATOR_TYPE it_;
};
索引扫描的代码和全表扫描几乎一样,只是迭代器换成了 B+ 树的迭代器:
IndexScanExecutor::IndexScanExecutor(ExecutorContext *exec_ctx, const IndexScanPlanNode *plan)
: AbstractExecutor(exec_ctx),
plan_(plan),
index_info_(exec_ctx->GetCatalog()->GetIndex(plan->GetIndexOid())),
index_(dynamic_cast<B_PLUS_TREE_INDEX_TYPE *>(index_info_->index_.get())),
table_metadata_(exec_ctx->GetCatalog()->GetTable(index_info_->table_name_)) {}
void IndexScanExecutor::Init() { it_ = index_->GetBeginIterator(); }
bool IndexScanExecutor::Next(Tuple *tuple, RID *rid) {
auto predicate = plan_->GetPredicate();
while (it_ != index_->GetEndIterator()) {
*rid = (*it_).second;
table_metadata_->table_->GetTuple(*rid, tuple, exec_ctx_->GetTransaction());
++it_;
if (!predicate || predicate->Evaluate(tuple, &table_metadata_->schema_).GetAs<bool>()) {
// 只保留输出列
std::vector<Value> values;
for (auto &col : GetOutputSchema()->GetColumns()) {
values.push_back(col.GetExpr()->Evaluate(tuple, &table_metadata_->schema_));
}
*tuple = {values, GetOutputSchema()};
return true;
}
}
return false;
}
插入
插入操作分为两种:
- raw inserts:插入数据直接来自插入执行器本身,比如
INSERT INTO tbl_user VALUES (1, 15), (2, 16)
- not-raw inserts:插入的数据来自子执行器,比如
INSERT INTO tbl_user1 SELECT * FROM tbl_user2
可以使用插入计划的 IsRawInsert()
判断插入操作的类型,这个函数根据子查询器列表是否为空进行判断:
/** @return true if we embed insert values directly into the plan, false if we have a child plan providing tuples */
bool IsRawInsert() const { return GetChildren().empty(); }
如果是 raw inserts,我们直接根据插入执行器中的数据构造 tuple 并插入表中,否则调用子执行器的 Next
函数获取数据并插入表中。因为表中可能建了索引,所以插入数据之后需要更新索引:
class InsertExecutor : public AbstractExecutor {
public:
/**
* Creates a new insert executor.
* @param exec_ctx the executor context
* @param plan the insert plan to be executed
* @param child_executor the child executor to obtain insert values from, can be nullptr
*/
InsertExecutor(ExecutorContext *exec_ctx, const InsertPlanNode *plan,
std::unique_ptr<AbstractExecutor> &&child_executor);
const Schema *GetOutputSchema() override { return plan_->OutputSchema(); };
void Init() override;
// Note that Insert does not make use of the tuple pointer being passed in.
// We return false if the insert failed for any reason, and return true if all inserts succeeded.
bool Next([[maybe_unused]] Tuple *tuple, RID *rid) override;
void InsertTuple(Tuple *tuple, RID *rid);
private:
/** The insert plan node to be executed. */
const InsertPlanNode *plan_;
std::unique_ptr<AbstractExecutor> child_executor_;
TableMetadata *table_metadata_;
std::vector<IndexInfo *> index_infos_;
uint32_t index_{0};
};
InsertExecutor::InsertExecutor(ExecutorContext *exec_ctx, const InsertPlanNode *plan,
std::unique_ptr<AbstractExecutor> &&child_executor)
: AbstractExecutor(exec_ctx),
plan_(plan),
child_executor_(std::move(child_executor)),
table_metadata_(exec_ctx->GetCatalog()->GetTable(plan->TableOid())),
index_infos_(exec_ctx->GetCatalog()->GetTableIndexes(table_metadata_->name_)) {}
void InsertExecutor::Init() {
if (!plan_->IsRawInsert()) {
child_executor_->Init();
}
}
bool InsertExecutor::Next([[maybe_unused]] Tuple *tuple, RID *rid) {
if (plan_->IsRawInsert()) {
if (index_ >= plan_->RawValues().size()) {
return false;
}
*tuple = {plan_->RawValuesAt(index_++), &table_metadata_->schema_};
InsertTuple(tuple, rid);
return true;
} else {
auto has_data = child_executor_->Next(tuple, rid);
if (has_data) {
InsertTuple(tuple, rid);
}
return has_data;
}
}
void InsertExecutor::InsertTuple(Tuple *tuple, RID *rid) {
// 更新数据表
table_metadata_->table_->InsertTuple(*tuple, rid, exec_ctx_->GetTransaction());
// 更新索引
for (auto &index_info : index_infos_) {
index_info->index_->InsertEntry(
tuple->KeyFromTuple(table_metadata_->schema_, index_info->key_schema_, index_info->index_->GetKeyAttrs()), *rid,
exec_ctx_->GetTransaction());
}
}
更新
UpdateExecutor
从子执行器获取需要更新的 tuple,并调用 GenerateUpdatedTuple
生成更新之后的 tuple,同样也要更新索引。
class UpdateExecutor : public AbstractExecutor {
friend class UpdatePlanNode;
public:
UpdateExecutor(ExecutorContext *exec_ctx, const UpdatePlanNode *plan,
std::unique_ptr<AbstractExecutor> &&child_executor);
const Schema *GetOutputSchema() override { return plan_->OutputSchema(); };
void Init() override;
bool Next([[maybe_unused]] Tuple *tuple, RID *rid) override;
/* Given an old tuple, creates a new updated tuple based on the updateinfo given in the plan */
Tuple GenerateUpdatedTuple(const Tuple &old_tup);
private:
const UpdatePlanNode *plan_;
const TableMetadata *table_info_;
std::unique_ptr<AbstractExecutor> child_executor_;
std::vector<IndexInfo *> index_infos_;
};
bool UpdateExecutor::Next([[maybe_unused]] Tuple *tuple, RID *rid) {
if (!child_executor_->Next(tuple, rid)) {
return false;
}
// 更新数据表
auto new_tuple = GenerateUpdatedTuple(*tuple);
table_info_->table_->UpdateTuple(new_tuple, *rid, exec_ctx_->GetTransaction());
// 更新索引
for (auto &index_info : index_infos_) {
// 删除旧的 tuple
index_info->index_->DeleteEntry(
tuple->KeyFromTuple(table_info_->schema_, index_info->key_schema_, index_info->index_->GetKeyAttrs()), *rid,
exec_ctx_->GetTransaction());
// 插入新的 tuple
index_info->index_->InsertEntry(
new_tuple.KeyFromTuple(table_info_->schema_, index_info->key_schema_, index_info->index_->GetKeyAttrs()), *rid,
exec_ctx_->GetTransaction());
}
return true;
}
删除
DeleteExecutor
的数据来自于子执行器,删除之后需要更新索引。
DeleteExecutor::DeleteExecutor(ExecutorContext *exec_ctx, const DeletePlanNode *plan,
std::unique_ptr<AbstractExecutor> &&child_executor)
: AbstractExecutor(exec_ctx),
plan_(plan),
child_executor_(std::move(child_executor)),
table_metadata_(exec_ctx->GetCatalog()->GetTable(plan->TableOid())),
index_infos_(exec_ctx->GetCatalog()->GetTableIndexes(table_metadata_->name_)) {}
void DeleteExecutor::Init() { child_executor_->Init(); }
bool DeleteExecutor::Next([[maybe_unused]] Tuple *tuple, RID *rid) {
if (!child_executor_->Next(tuple, rid)) {
return false;
}
table_metadata_->table_->MarkDelete(*rid, exec_ctx_->GetTransaction());
// 更新索引
for (auto &index_info : index_infos_) {
index_info->index_->DeleteEntry(
tuple->KeyFromTuple(table_metadata_->schema_, index_info->key_schema_, index_info->index_->GetKeyAttrs()), *rid,
exec_ctx_->GetTransaction());
}
return true;
}
嵌套循环连接
要实现连接操作,最简单粗暴的方法就是开个二重循环,外层循环是小表(指的是数据页较少),内层循环是大表,小表驱动大表。但是这种连接方法效率非常低,因为完全无法利用到缓存池(分块变成四重循环之后效果会好一些):
假设一次磁盘 IO 的时间是 0.1ms,那么大表驱动小表耗时 1.3 小时,小表驱动大表耗时 1.1 小时,可见速度慢的感人。
循环嵌套连接执行器 NestLoopJoinExecutor
的声明如下,可以看到数据成员包括 left_executor_
和 right_executor
,前者代表外表执行器,后者代表内表的执行器:
class NestedLoopJoinExecutor : public AbstractExecutor {
public:
/**
* Creates a new NestedLoop join executor.
* @param exec_ctx the executor context
* @param plan the NestedLoop join plan to be executed
* @param left_executor the child executor that produces tuple for the left side of join
* @param right_executor the child executor that produces tuple for the right side of join
*
*/
NestedLoopJoinExecutor(ExecutorContext *exec_ctx, const NestedLoopJoinPlanNode *plan,
std::unique_ptr<AbstractExecutor> &&left_executor,
std::unique_ptr<AbstractExecutor> &&right_executor);
const Schema *GetOutputSchema() override { return plan_->OutputSchema(); };
void Init() override;
bool Next(Tuple *tuple, RID *rid) override;
private:
/** The NestedLoop plan node to be executed. */
const NestedLoopJoinPlanNode *plan_;
std::unique_ptr<AbstractExecutor> left_executor_;
std::unique_ptr<AbstractExecutor> right_executor_;
Tuple left_tuple_;
bool is_done_;
};
由于一次只能返回一个 tuple,所以需要先保存外表的一个 tuple,然后循环调用内表执行器的 Next()
方法直至匹配,当内表遍历完一遍之后需要更新外表的 tuple。这个部分的代码写的比较奇怪,如果有 python 的 yield
关键字可能会好写很多:
void NestedLoopJoinExecutor::Init() {
left_executor_->Init();
right_executor_->Init();
RID left_rid;
is_done_ = !left_executor_->Next(&left_tuple_, &left_rid);
}
bool NestedLoopJoinExecutor::Next(Tuple *tuple, RID *rid) {
Tuple right_tuple;
RID right_rid, left_rid;
auto predicate = plan_->Predicate();
auto left_schema = left_executor_->GetOutputSchema();
auto right_schema = right_executor_->GetOutputSchema();
while (!is_done_) {
while (right_executor_->Next(&right_tuple, &right_rid)) {
if (!predicate || predicate->EvaluateJoin(&left_tuple_, left_schema, &right_tuple, right_schema).GetAs<bool>()) {
// 拼接 tuple
std::vector<Value> values;
for (auto &col : GetOutputSchema()->GetColumns()) {
values.push_back(col.GetExpr()->EvaluateJoin(&left_tuple_, left_schema, &right_tuple, right_schema));
}
*tuple = {values, GetOutputSchema()};
return true;
}
}
is_done_ = !left_executor_->Next(&left_tuple_, &left_rid);
right_executor_->Init();
}
return false;
}
索引循环连接
索引循环连接可以减少内表的扫描范围和磁盘 IO 次数,大大提升连接效率。假设走一次索引的 IO 次数为常数 \(C \ll n\),那么总共只需 \(M+m \cdot C\) 次 IO:
嵌套循环执行器 NestIndexJoinExecutor
的声明如下,child_executor_
是外表的执行器,内表的数据由索引提供,所以不需要内表的执行器:
class NestIndexJoinExecutor : public AbstractExecutor {
public:
NestIndexJoinExecutor(ExecutorContext *exec_ctx, const NestedIndexJoinPlanNode *plan,
std::unique_ptr<AbstractExecutor> &&child_executor);
const Schema *GetOutputSchema() override { return plan_->OutputSchema(); }
void Init() override;
bool Next(Tuple *tuple, RID *rid) override;
private:
/** The nested index join plan node. */
const NestedIndexJoinPlanNode *plan_;
std::unique_ptr<AbstractExecutor> child_executor_;
TableMetadata *inner_table_info_;
IndexInfo *index_info_;
Tuple left_tuple_;
std::vector<RID> inner_result_;
};
在索引上寻找匹配值时需要将 left_tuple_
转换为内表索引的 key
:
bool NestIndexJoinExecutor::Next(Tuple *tuple, RID *rid) {
Tuple right_tuple;
RID left_rid, right_rid;
auto left_schema = plan_->OuterTableSchema();
auto right_schema = plan_->InnerTableSchema();
while (true) {
if (!inner_result_.empty()) {
right_rid = inner_result_.back();
inner_result_.pop_back();
inner_table_info_->table_->GetTuple(right_rid, &right_tuple, exec_ctx_->GetTransaction());
// 拼接 tuple
std::vector<Value> values;
for (auto &col : GetOutputSchema()->GetColumns()) {
values.push_back(col.GetExpr()->EvaluateJoin(&left_tuple_, left_schema, &right_tuple, right_schema));
}
*tuple = {values, GetOutputSchema()};
return true;
}
if (!child_executor_->Next(&left_tuple_, &left_rid)) {
return false;
}
// 在内表的索引上寻找匹配值列表
auto value = plan_->Predicate()->GetChildAt(0)->EvaluateJoin(&left_tuple_, left_schema, &right_tuple, right_schema);
auto inner_key = Tuple({value}, index_info_->index_->GetKeySchema());
index_info_->index_->ScanKey(inner_key, &inner_result_, exec_ctx_->GetTransaction());
}
return false;
}
聚合
由于 Fall2020 没有要求实现哈希索引,所以聚合执行器 AggregationExecutor
内部维护的是直接放在内存中的哈希表 SimpleAggregationHashTable
以及哈希表迭代器 aht_iterator_
。将键值对插入哈希表的时候会立刻更新哈希表中保存的聚合结果,最终的查询结果也从该哈希表获取:
void AggregationExecutor::Init() {
child_->Init();
// 构造哈希表
Tuple tuple;
RID rid;
while (child_->Next(&tuple, &rid)) {
aht_.InsertCombine(MakeKey(&tuple), MakeVal(&tuple));
}
aht_iterator_ = aht_.Begin();
}
bool AggregationExecutor::Next(Tuple *tuple, RID *rid) {
auto having = plan_->GetHaving();
while (aht_iterator_ != aht_.End()) {
auto group_bys = aht_iterator_.Key().group_bys_;
auto aggregates = aht_iterator_.Val().aggregates_;
++aht_iterator_;
if (!having || having->EvaluateAggregate(group_bys, aggregates).GetAs<bool>()) {
std::vector<Value> values;
for (auto &col : GetOutputSchema()->GetColumns()) {
values.push_back(col.GetExpr()->EvaluateAggregate(group_bys, aggregates));
}
*tuple = {values, GetOutputSchema()};
return true;
}
}
return false;
}
测试
在终端输入:
cd build
cmake ..
make
make executor_test
make grading_executor_test # 从 grade scope 扒下来的测试代码
./test/executor_test
./test/grading_executor_test
测试结果如下,成功通过了所有测试用例:
后记
通过这次实验,可以加深对目录、查询计划、迭代模型和 tuple 页布局的理解,算是收获满满的一次实验了,以上~~