关于数据库:StoneDB子查询优化subquery子查询exists子查询的剔除遍历处理

93次阅读

共计 22974 个字符，预计需要花费 58 分钟才能阅读完成。

摘要:

记录对 exists 子句进行剔除遍历的解决, 比照优化前后子查问耗时

执行的 SQL 语句:

/stonedb57/install/bin/mysql -D tpch -e " explain 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 ;"

外围函数解决:

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;
    }
  }

优化策略:

思路:

  1. exists 子句与 join 都波及到内表与表面, 实质上能够了解为要解决同样的数据量
  2. mysql 能够将 in 查问转换为 exists, 两者在语义上能够对等 https://www.jb51.net/article/236338.htm#_label3_2_0_1
  3. 在呈现 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

正文完
数据库
发表至： 数据库
2022-08-05
 0
关于数据库:如何给一个-HTAP-数据库做基准测试StoneDB学术分享会第4期
关于数据库:阿里云RDS深度定制XA-Crash-Safe
关于数据库:经验汇总基于TDengine的时序数据解决方案
关于数据库:MatrixOne-070-更稳定性能更优