摘要:
记录对exists子句进行剔除遍历的解决, 比照优化前后子查问耗时
执行的SQL语句:
/stonedb57/install/bin/mysql -D tpch -e " explain selecto_orderpriority,count(*) as order_countfromorderswhereo_orderdate >= date '1993-07-01'and o_orderdate < date '1993-07-01' + interval '3' monthand exists (select*fromlineitemwherel_orderkey = o_orderkeyand l_commitdate < l_receiptdate)group byo_orderpriorityorder by o_orderpriority ;"外围函数解决:
ParameterizedFilter::UpdateMultiIndex
void ParameterizedFilter::UpdateMultiIndex(bool count_only, int64_t limit) { MEASURE_FET("ParameterizedFilter::UpdateMultiIndex(...)"); thd_proc_info(mind->ConnInfo().Thd(), "update multi-index"); if (descriptors.Size() < 1) { PrepareRoughMultiIndex(); rough_mind->ClearLocalDescFilters(); return; } SyntacticalDescriptorListPreprocessing(); bool empty_cannot_grow = true; // if false (e.g. outer joins), then do not // optimize empty multiindex as empty result for (uint i = 0; i < descriptors.Size(); i++) if (descriptors[i].IsOuter()) empty_cannot_grow = false; // special cases bool nonempty = true; DescriptorListOrdering(); // descriptor display if (rccontrol.isOn()) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Initial execution plan (non-join):" << system::unlock; for (uint i = 0; i < descriptors.Size(); i++) if (!descriptors[i].done && !descriptors[i].IsType_Join() && descriptors[i].IsInner()) { char buf[1000]; std::strcpy(buf, " "); descriptors[i].ToString(buf, 1000); if (descriptors[i].IsDelayed()) rccontrol.lock(mind->m_conn->GetThreadID()) << "Delayed: " << buf << " \t(" << int(descriptors[i].evaluation * 100) / 100.0 << ")" << system::unlock; else rccontrol.lock(mind->m_conn->GetThreadID()) << "Cnd(" << i << "): " << buf << " \t(" << int(descriptors[i].evaluation * 100) / 100.0 << ")" << system::unlock; } } // end now if the multiindex is empty if (mind->ZeroTuples() && empty_cannot_grow) { mind->Empty(); PrepareRoughMultiIndex(); rough_mind->ClearLocalDescFilters(); return; } // Prepare execution - rough set part for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done && descriptors[i].IsInner()) { if (descriptors[i].IsTrue()) { descriptors[i].done = true; continue; } else if (descriptors[i].IsFalse()) { if (descriptors[i].attr.vc) { descriptors[i].done = true; if (empty_cannot_grow) { mind->Empty(); PrepareRoughMultiIndex(); rough_mind->ClearLocalDescFilters(); return; } else { DimensionVector dims(mind->NoDimensions()); descriptors[i].attr.vc->MarkUsedDims(dims); mind->MakeCountOnly(0, dims); } } } } } if (rough_mind) rough_mind->ClearLocalDescFilters(); PrepareRoughMultiIndex(); nonempty = RoughUpdateMultiIndex(); // calculate all rough conditions, if ((!nonempty && empty_cannot_grow) || mind->m_conn->Explain()) { mind->Empty(); // nonempty==false if the whole result is empty (outer joins // considered) rough_mind->ClearLocalDescFilters(); return; } PropagateRoughToMind(); // exclude common::RSValue::RS_NONE from mind // count other types of conditions, e.g. joins (i.e. conditions using // attributes from two // dimensions) int no_of_join_conditions = 0; // count also one-dimensional outer join conditions int no_of_delayed_conditions = 0; for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done) if (descriptors[i].IsType_Join() || descriptors[i].IsDelayed() || descriptors[i].IsOuter()) { if (!descriptors[i].IsDelayed()) no_of_join_conditions++; else no_of_delayed_conditions++; } } // Apply all one-dimensional filters (after where, i.e. without // outer joins) int last_desc_dim = -1; int cur_dim = -1; int no_desc = 0; for (uint i = 0; i < descriptors.Size(); i++) if (!descriptors[i].done && descriptors[i].IsInner() && !descriptors[i].IsType_Join() && !descriptors[i].IsDelayed()) ++no_desc; int ApplyDescriptorNum = 0; std::chrono::high_resolution_clock::time_point start = std::chrono::high_resolution_clock::now(); int descriptorsNum = descriptors.Size(); if (descriptorsNum > 1) { STONEDB_LOG(LogCtl_Level::INFO, "UpdateMultiIndex: descriptorsNum : %d", descriptorsNum); } int desc_no = 0; for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done && descriptors[i].IsInner() && !descriptors[i].IsType_Join() && !descriptors[i].IsDelayed() && !descriptors[i].IsExists()) { ++desc_no; if (descriptors[i].attr.vc) { cur_dim = descriptors[i].attr.vc->GetDim(); } if (last_desc_dim != -1 && cur_dim != -1 && last_desc_dim != cur_dim) { // Make all possible projections to other dimensions RoughMakeProjections(cur_dim, false); } ++ApplyDescriptorNum; // limit should be applied only for the last descriptor ApplyDescriptor(i, (desc_no != no_desc || no_of_delayed_conditions > 0 || no_of_join_conditions) ? -1 : limit); if (!descriptors[i].attr.vc) continue; // probably desc got simplified and is true or false if (cur_dim >= 0 && mind->GetFilter(cur_dim) && mind->GetFilter(cur_dim)->IsEmpty() && empty_cannot_grow) { mind->Empty(); if (rccontrol.isOn()) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Empty result set after non-join condition evaluation (WHERE)" << system::unlock; } rough_mind->ClearLocalDescFilters(); return; } last_desc_dim = cur_dim; } } if (ApplyDescriptorNum > 1) { auto diff = std::chrono::duration_cast<std::chrono::duration<float>>(std::chrono::high_resolution_clock::now() - start); STONEDB_LOG(LogCtl_Level::INFO, "Timer %f : UpdateMultiIndex: ApplyDescriptorNum : %d", diff.count(), ApplyDescriptorNum); } rough_mind->UpdateReducedDimension(); mind->UpdateNoTuples(); for (int i = 0; i < mind->NoDimensions(); i++) if (mind->GetFilter(i)) table->SetVCDistinctVals(i, mind->GetFilter(i)->NoOnes()); // distinct values - not more than the // number of rows after WHERE rough_mind->ClearLocalDescFilters(); // Some displays if (rccontrol.isOn()) { int pack_full = 0, pack_some = 0, pack_all = 0; rccontrol.lock(mind->m_conn->GetThreadID()) << "Packrows after exact evaluation (execute WHERE end):" << system::unlock; for (uint i = 0; i < (uint)mind->NoDimensions(); i++) if (mind->GetFilter(i)) { Filter *f = mind->GetFilter(i); pack_full = 0; pack_some = 0; pack_all = (int)((mind->OrigSize(i) + ((1 << mind->NoPower()) - 1)) >> mind->NoPower()); for (int b = 0; b < pack_all; b++) { if (f->IsFull(b)) pack_full++; else if (!f->IsEmpty(b)) pack_some++; } rccontrol.lock(mind->m_conn->GetThreadID()) << "(t" << i << "): " << pack_all << " all packrows, " << pack_full + pack_some << " to open (including " << pack_full << " full)" << system::unlock; } } DescriptorJoinOrdering(); // descriptor display for joins if (rccontrol.isOn()) { bool first_time = true; for (uint i = 0; i < descriptors.Size(); i++) if (!descriptors[i].done && (descriptors[i].IsType_Join() || descriptors[i].IsOuter())) { if (first_time) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Join execution plan:" << system::unlock; first_time = false; } char buf[1000]; std::strcpy(buf, " "); descriptors[i].ToString(buf, 1000); if (descriptors[i].IsDelayed()) rccontrol.lock(mind->m_conn->GetThreadID()) << "Delayed: " << buf << " \t(" << int(descriptors[i].evaluation * 100) / 100.0 << ")" << system::unlock; else rccontrol.lock(mind->m_conn->GetThreadID()) << "Cnd(" << i << "): " << buf << " \t(" << int(descriptors[i].evaluation * 100) / 100.0 << ")" << system::unlock; } } bool join_or_delayed_present = false; for (uint i = 0; i < descriptors.Size(); i++) { if (mind->ZeroTuples() && empty_cannot_grow) { // set all following descriptors to done for (uint j = i; j < descriptors.Size(); j++) descriptors[j].done = true; break; } if (!descriptors[i].done && !descriptors[i].IsDelayed()) { // Merging join conditions Condition join_desc; PrepareJoiningStep(join_desc, descriptors, i, *mind); // group together all join conditions for one step no_of_join_conditions -= join_desc.Size(); JoinTips join_tips(*mind); // Optimization: Check whether there exists "a is null" delayed condition // for an outer join if (join_desc[0].IsOuter()) { for (uint i = 0; i < descriptors.Size(); i++) { if (descriptors[i].IsDelayed() && !descriptors[i].done && descriptors[i].op == common::Operator::O_IS_NULL && join_desc[0].right_dims.Get(descriptors[i].attr.vc->GetDim()) && !descriptors[i].attr.vc->IsNullsPossible()) { for (int j = 0; j < join_desc[0].right_dims.Size(); j++) { if (join_desc[0].right_dims[j] == true) join_tips.null_only[j] = true; } descriptors[i].done = true; // completed inside joining algorithms no_of_delayed_conditions--; } } } if (no_of_join_conditions == 0 && // optimizations used only for the last group of conditions no_of_delayed_conditions == 0 && parametrized_desc.Size() == 0) { // Optimization: count_only is true => do not materialize multiindex // (just counts tuples). WARNING: in this case cannot use multiindex for // any operations other than NoTuples(). if (count_only) join_tips.count_only = true; join_tips.limit = limit; // only one dim used in distinct context? int distinct_dim = table->DimInDistinctContext(); int dims_in_output = 0; for (int dim = 0; dim < mind->NoDimensions(); dim++) if (mind->IsUsedInOutput(dim)) dims_in_output++; if (distinct_dim != -1 && dims_in_output == 1) join_tips.distinct_only[distinct_dim] = true; } // Optimization: Check whether all dimensions are really used DimensionVector dims_used(mind->NoDimensions()); for (uint jj = 0; jj < descriptors.Size(); jj++) { if (jj != i && !descriptors[jj].done) descriptors[jj].DimensionUsed(dims_used); } // can't utilize not_used_dims in case there are parameterized descs left if (parametrized_desc.Size() == 0) { for (int dim = 0; dim < mind->NoDimensions(); dim++) if (!mind->IsUsedInOutput(dim) && dims_used[dim] == false) join_tips.forget_now[dim] = true; } // Joining itself UpdateJoinCondition(join_desc, join_tips); } } // Execute all delayed conditions for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Executing delayed Cnd(" << i << ")" << system::unlock; descriptors[i].CoerceColumnTypes(); descriptors[i].Simplify(); ApplyDescriptor(i); join_or_delayed_present = true; } } if (join_or_delayed_present) rough_mind->MakeDimensionSuspect(); // no common::RSValue::RS_ALL packs mind->UpdateNoTuples();}导致exists子查问遍历解决的逻辑:
for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done && descriptors[i].IsInner() && !descriptors[i].IsType_Join() && !descriptors[i].IsDelayed()) { ++desc_no; if (descriptors[i].attr.vc) { cur_dim = descriptors[i].attr.vc->GetDim(); } if (last_desc_dim != -1 && cur_dim != -1 && last_desc_dim != cur_dim) { // Make all possible projections to other dimensions RoughMakeProjections(cur_dim, false); } ++ApplyDescriptorNum; // limit should be applied only for the last descriptor ApplyDescriptor(i, (desc_no != no_desc || no_of_delayed_conditions > 0 || no_of_join_conditions) ? -1 : limit); if (!descriptors[i].attr.vc) continue; // probably desc got simplified and is true or false if (cur_dim >= 0 && mind->GetFilter(cur_dim) && mind->GetFilter(cur_dim)->IsEmpty() && empty_cannot_grow) { mind->Empty(); if (rccontrol.isOn()) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Empty result set after non-join condition evaluation (WHERE)" << system::unlock; } rough_mind->ClearLocalDescFilters(); return; } last_desc_dim = cur_dim; } }优化策略:
思路:
- exists子句与join都波及到内表与表面, 实质上能够了解为要解决同样的数据量
- mysql能够将in查问转换为exists, 两者在语义上能够对等 https://www.jb51.net/article/236338.htm#_label3_2_0_1
- 在呈现join判断的中央，将exists条件做雷同看待
优化后的遍历外围代码:
for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done && descriptors[i].IsInner() && !descriptors[i].IsType_Join() && !descriptors[i].IsDelayed() && !descriptors[i].IsExists()) { ++desc_no; if (descriptors[i].attr.vc) { cur_dim = descriptors[i].attr.vc->GetDim(); } if (last_desc_dim != -1 && cur_dim != -1 && last_desc_dim != cur_dim) { // Make all possible projections to other dimensions RoughMakeProjections(cur_dim, false); } ++ApplyDescriptorNum; // limit should be applied only for the last descriptor ApplyDescriptor(i, (desc_no != no_desc || no_of_delayed_conditions > 0 || no_of_join_conditions) ? -1 : limit); if (!descriptors[i].attr.vc) continue; // probably desc got simplified and is true or false if (cur_dim >= 0 && mind->GetFilter(cur_dim) && mind->GetFilter(cur_dim)->IsEmpty() && empty_cannot_grow) { mind->Empty(); if (rccontrol.isOn()) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Empty result set after non-join condition evaluation (WHERE)" << system::unlock; } rough_mind->ClearLocalDescFilters(); return; } last_desc_dim = cur_dim; } }整个函数:
void ParameterizedFilter::UpdateMultiIndex(bool count_only, int64_t limit) { MEASURE_FET("ParameterizedFilter::UpdateMultiIndex(...)"); thd_proc_info(mind->ConnInfo().Thd(), "update multi-index"); if (descriptors.Size() < 1) { PrepareRoughMultiIndex(); rough_mind->ClearLocalDescFilters(); return; } SyntacticalDescriptorListPreprocessing(); bool empty_cannot_grow = true; // if false (e.g. outer joins), then do not // optimize empty multiindex as empty result for (uint i = 0; i < descriptors.Size(); i++) if (descriptors[i].IsOuter()) empty_cannot_grow = false; // special cases bool nonempty = true; DescriptorListOrdering(); // descriptor display if (rccontrol.isOn()) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Initial execution plan (non-join):" << system::unlock; for (uint i = 0; i < descriptors.Size(); i++) if (!descriptors[i].done && !descriptors[i].IsType_Join() && descriptors[i].IsInner()) { char buf[1000]; std::strcpy(buf, " "); descriptors[i].ToString(buf, 1000); if (descriptors[i].IsDelayed()) rccontrol.lock(mind->m_conn->GetThreadID()) << "Delayed: " << buf << " \t(" << int(descriptors[i].evaluation * 100) / 100.0 << ")" << system::unlock; else rccontrol.lock(mind->m_conn->GetThreadID()) << "Cnd(" << i << "): " << buf << " \t(" << int(descriptors[i].evaluation * 100) / 100.0 << ")" << system::unlock; } } // end now if the multiindex is empty if (mind->ZeroTuples() && empty_cannot_grow) { mind->Empty(); PrepareRoughMultiIndex(); rough_mind->ClearLocalDescFilters(); return; } // Prepare execution - rough set part for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done && descriptors[i].IsInner()) { if (descriptors[i].IsTrue()) { descriptors[i].done = true; continue; } else if (descriptors[i].IsFalse()) { if (descriptors[i].attr.vc) { descriptors[i].done = true; if (empty_cannot_grow) { mind->Empty(); PrepareRoughMultiIndex(); rough_mind->ClearLocalDescFilters(); return; } else { DimensionVector dims(mind->NoDimensions()); descriptors[i].attr.vc->MarkUsedDims(dims); mind->MakeCountOnly(0, dims); } } } } } if (rough_mind) rough_mind->ClearLocalDescFilters(); PrepareRoughMultiIndex(); nonempty = RoughUpdateMultiIndex(); // calculate all rough conditions, if ((!nonempty && empty_cannot_grow) || mind->m_conn->Explain()) { mind->Empty(); // nonempty==false if the whole result is empty (outer joins // considered) rough_mind->ClearLocalDescFilters(); return; } PropagateRoughToMind(); // exclude common::RSValue::RS_NONE from mind // count other types of conditions, e.g. joins (i.e. conditions using // attributes from two // dimensions) int no_of_join_conditions = 0; // count also one-dimensional outer join conditions int no_of_delayed_conditions = 0; for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done) if (descriptors[i].IsType_Join() || descriptors[i].IsDelayed() || descriptors[i].IsOuter()) { if (!descriptors[i].IsDelayed()) no_of_join_conditions++; else no_of_delayed_conditions++; } } // Apply all one-dimensional filters (after where, i.e. without // outer joins) int last_desc_dim = -1; int cur_dim = -1; int no_desc = 0; for (uint i = 0; i < descriptors.Size(); i++) if (!descriptors[i].done && descriptors[i].IsInner() && !descriptors[i].IsType_Join() && !descriptors[i].IsDelayed() && !descriptors[i].IsExists()) ++no_desc; int ApplyDescriptorNum = 0; std::chrono::high_resolution_clock::time_point start = std::chrono::high_resolution_clock::now(); int descriptorsNum = descriptors.Size(); if (descriptorsNum > 1) { STONEDB_LOG(LogCtl_Level::INFO, "UpdateMultiIndex: descriptorsNum : %d", descriptorsNum); } int desc_no = 0; for (uint i = 0; i < descriptors.Size(); i++) { if (!descriptors[i].done && descriptors[i].IsInner() && !descriptors[i].IsType_Join() && !descriptors[i].IsDelayed() && !descriptors[i].IsExists()) { ++desc_no; if (descriptors[i].attr.vc) { cur_dim = descriptors[i].attr.vc->GetDim(); } if (last_desc_dim != -1 && cur_dim != -1 && last_desc_dim != cur_dim) { // Make all possible projections to other dimensions RoughMakeProjections(cur_dim, false); } ++ApplyDescriptorNum; // limit should be applied only for the last descriptor ApplyDescriptor(i, (desc_no != no_desc || no_of_delayed_conditions > 0 || no_of_join_conditions) ? -1 : limit); if (!descriptors[i].attr.vc) continue; // probably desc got simplified and is true or false if (cur_dim >= 0 && mind->GetFilter(cur_dim) && mind->GetFilter(cur_dim)->IsEmpty() && empty_cannot_grow) { mind->Empty(); if (rccontrol.isOn()) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Empty result set after non-join condition evaluation (WHERE)" << system::unlock; } rough_mind->ClearLocalDescFilters(); return; } last_desc_dim = cur_dim; } } if (ApplyDescriptorNum > 1) { auto diff = std::chrono::duration_cast<std::chrono::duration<float>>(std::chrono::high_resolution_clock::now() - start); STONEDB_LOG(LogCtl_Level::INFO, "Timer %f : UpdateMultiIndex: ApplyDescriptorNum : %d", diff.count(), ApplyDescriptorNum); } rough_mind->UpdateReducedDimension(); mind->UpdateNoTuples(); for (int i = 0; i < mind->NoDimensions(); i++) if (mind->GetFilter(i)) table->SetVCDistinctVals(i, mind->GetFilter(i)->NoOnes()); // distinct values - not more than the // number of rows after WHERE rough_mind->ClearLocalDescFilters(); // Some displays if (rccontrol.isOn()) { int pack_full = 0, pack_some = 0, pack_all = 0; rccontrol.lock(mind->m_conn->GetThreadID()) << "Packrows after exact evaluation (execute WHERE end):" << system::unlock; for (uint i = 0; i < (uint)mind->NoDimensions(); i++) if (mind->GetFilter(i)) { Filter *f = mind->GetFilter(i); pack_full = 0; pack_some = 0; pack_all = (int)((mind->OrigSize(i) + ((1 << mind->NoPower()) - 1)) >> mind->NoPower()); for (int b = 0; b < pack_all; b++) { if (f->IsFull(b)) pack_full++; else if (!f->IsEmpty(b)) pack_some++; } rccontrol.lock(mind->m_conn->GetThreadID()) << "(t" << i << "): " << pack_all << " all packrows, " << pack_full + pack_some << " to open (including " << pack_full << " full)" << system::unlock; } } DescriptorJoinOrdering(); // descriptor display for joins if (rccontrol.isOn()) { bool first_time = true; for (uint i = 0; i < descriptors.Size(); i++) if (!descriptors[i].done && (descriptors[i].IsType_Join() || descriptors[i].IsExists() || descriptors[i].IsOuter())) { if (first_time) { rccontrol.lock(mind->m_conn->GetThreadID()) << "Join execution plan:" << system::unlock; first_time = false; } char buf[1000]; std::strcpy(buf, " "); descriptors[i].ToString(buf, 1000); if (descriptors[i].IsDelayed()) rccontrol.lock(mind->m_conn->GetThreadID()) << "Delayed: " << buf << " \t(" << int(descriptors[i].evaluation * 100) / 100.0 << ")" << system::unlock; else rccontrol.lock(mind->m_conn->GetThreadID()) << "Cnd(" << i << "): " << buf << " \t(" << int(descriptors[i].evaluation * 100) / 100.0 << ")" << system::unlock; } } bool join_or_delayed_present = false; for (uint i = 0; i < descriptors.Size(); i++) { if (mind->ZeroTuples() && empty_cannot_grow) { // set all following descriptors to done for (uint j = i; j < descriptors.Size(); j++) descriptors[j].done = true; break; } if (!descriptors[i].done && !descriptors[i].IsDelayed()) { // Merging join conditions Condition join_desc; PrepareJoiningStep(join_desc, descriptors, i, *mind); // group together all join conditions for one step no_of_join_conditions -= join_desc.Size(); JoinTips join_tips(*mind); // Optimization: Check whether there exists "a is null" delayed condition // for an outer join if (join_desc[0].IsOuter()) { for (uint i = 0; i < descriptors.Size(); i++) { if (descriptors[i].IsDelayed() && !descriptors[i].done && descriptors[i].op == common::Operator::O_IS_NULL && join_desc[0].right_dims.Get(descriptors[i].attr.vc->GetDim()) && !descriptors[i].attr.vc->IsNullsPossible()) { for (int j = 0; j < join_desc[0].right_dims.Size(); j++) { if (join_desc[0].right_dims[j] == true) join_tips.null_only[j] = true; } descriptors[i].done = true; // completed inside joining algorithms no_of_delayed_conditions--; } } } if (no_of_join_conditions == 0 && // optimizations used only for the last group of conditions no_of_delayed_conditions == 0 && parametrized_desc.Size() == 0) { // Optimization: count_only is true => do not materialize multiindex // (just counts tuples). WARNING: in this case cannot use multiindex for // any operations other than NoTuples(). if (count_only) join_tips.count_only = true; join_tips.limit = limit; // only one dim used in distinct context? int distinct_dim = table->DimInDistinctContext(); int dims_in_output = 0; for (int dim = 0; dim < mind->NoDimensions(); dim++) if (mind->IsUsedInOutput(dim)) dims_in_output++; if (distinct_dim != -1 && dims_in_output == 1) join_tips.distinct_only[distinct_dim] = true; } // Optimization: Check whether all dimensions are really used DimensionVector dims_used(mind->NoDimensions()); for (uint jj = 0; jj < descriptors.Size(); jj++) { if (jj != i && !descriptors[jj].done) descriptors[jj].DimensionUsed(dims_used); } // can't utilize not_used_dims in case there are parameterized descs left if (parametrized_desc.Size() == 0) { for (int dim = 0; dim < mind->NoDimensions(); dim++) if (!mind->IsUsedInOutput(dim) && dims_used[dim] == false) join_tips.forget_now[dim] = true; } // Joining itself UpdateJoinCondition(join_desc, join_tips); } } 测试数据:
优化exists判断后的子查问耗时:
mysql> select -> o_orderpriority, -> count(*) as order_count -> from -> orders -> where -> o_orderdate >= date '1993-07-01' -> and o_orderdate < date '1993-07-01' + interval '3' month -> and exists ( -> select -> * -> from -> lineitem -> where -> l_orderkey = o_orderkey -> and l_commitdate < l_receiptdate -> ) -> group by -> o_orderpriority -> order by -> o_orderpriority ;+-----------------+-------------+| o_orderpriority | order_count |+-----------------+-------------+| 1-URGENT | 114839 || 2-HIGH | 114276 || 3-MEDIUM | 114716 || 4-NOT SPECIFIED | 114913 || 5-LOW | 114927 |+-----------------+-------------+5 rows in set (4.04 sec)比照未优化前的子查问耗时:
优化后的火焰图:
当初工夫损耗曾经全副落在理解压缩上
优化exists判断后的解决逻辑:
(gdb) bt#0 stonedb::core::TwoDimensionalJoiner::CreateJoiner (join_alg_type=stonedb::core::JTYPE_GENERAL, mind=..., tips=..., table=0x7fc2f89fb510) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/storage/stonedb/core/joiner.cpp:79#1 0x00000000030afe84 in stonedb::core::ParameterizedFilter::UpdateJoinCondition (this=0x7fc2f89fb660, cond=..., tips=...) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/storage/stonedb/core/parameterized_filter.cpp:579#2 0x00000000030b3aec in stonedb::core::ParameterizedFilter::UpdateMultiIndex (this=0x7fc2f89fb660, count_only=false, limit=-1) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/storage/stonedb/core/parameterized_filter.cpp:1203#3 0x0000000002d73237 in stonedb::core::Query::Preexecute (this=0x7fc70415e800, qu=..., sender=0x7fc2f89d05f0, display_now=true) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/storage/stonedb/core/query.cpp:777#4 0x0000000002d44a6e in stonedb::core::Engine::Execute (this=0x75a5df0, thd=0x7fc2f8000b70, lex=0x7fc2f8002e98, result_output=0x7fc2f8010320, unit_for_union=0x0) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/storage/stonedb/core/engine_execute.cpp:421#5 0x0000000002d43d22 in stonedb::core::Engine::HandleSelect (this=0x75a5df0, thd=0x7fc2f8000b70, lex=0x7fc2f8002e98, result=@0x7fc70415ed18: 0x7fc2f8010320, setup_tables_done_option=0, res=@0x7fc70415ed14: 0, optimize_after_sdb=@0x7fc70415ed0c: 1, sdb_free_join=@0x7fc70415ed10: 1, with_insert=0) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/storage/stonedb/core/engine_execute.cpp:232#6 0x0000000002e2c501 in stonedb::dbhandler::SDB_HandleSelect (thd=0x7fc2f8000b70, lex=0x7fc2f8002e98, result=@0x7fc70415ed18: 0x7fc2f8010320, setup_tables_done_option=0, res=@0x7fc70415ed14: 0, optimize_after_sdb=@0x7fc70415ed0c: 1, sdb_free_join=@0x7fc70415ed10: 1, with_insert=0) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/storage/stonedb/handler/ha_rcengine.cpp:82#7 0x000000000246fe9a in execute_sqlcom_select (thd=0x7fc2f8000b70, all_tables=0x7fc2f801b530) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/sql/sql_parse.cc:5182#8 0x000000000246921e in mysql_execute_command (thd=0x7fc2f8000b70, first_level=true) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/sql/sql_parse.cc:2831#9 0x0000000002470e63 in mysql_parse (thd=0x7fc2f8000b70, parser_state=0x7fc70415feb0) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/sql/sql_parse.cc:5621#10 0x00000000024660fb in dispatch_command (thd=0x7fc2f8000b70, com_data=0x7fc704160650, command=COM_QUERY) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/sql/sql_parse.cc:1495#11 0x0000000002465027 in do_command (thd=0x7fc2f8000b70) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/sql/sql_parse.cc:1034#12 0x0000000002597c43 in handle_connection (arg=0x756c0b0) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/sql/conn_handler/connection_handler_per_thread.cc:313#13 0x0000000002c7b874 in pfs_spawn_thread (arg=0x758e640) at /data/jenkins/workspace/stonedb5.7-zsl-centos7.9/storage/perfschema/pfs.cc:2197#14 0x00007fc70f86fea5 in start_thread (arg=0x7fc704161700) at pthread_create.c:307#15 0x00007fc70dca6b0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111