From 414d4ece7273d799e645ae7a2a60db3d1579318f Mon Sep 17 00:00:00 2001 From: hopelee Date: Wed, 17 Mar 2021 14:19:18 +0800 Subject: [PATCH] [Feature] Avoid multiple executions of subquery dependent on const tables Description ----------- Dependent subqueries are usually uncacheable and evaluated at the execution stage. But if the tables which the subqueries are dependent on are const, the results of subqueries are stable. In current server codes, the optimizer will recognize such subqueries and evaluated them in make_join_select(). But at the execution stage, these subqueries will get evaluated again, which is unnecessary. We can mark such subqueries as cacheable to avoid evaluating them for multiple times, which can bring performance improvement. --- sql/item.h | 3 +++ sql/item_subselect.cc | 16 ++++++++++++++++ sql/item_subselect.h | 1 + sql/sql_optimizer.cc | 4 ++++ 4 files changed, 24 insertions(+) diff --git a/sql/item.h b/sql/item.h index 26fdde30d30..279b2fe8059 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2611,6 +2611,9 @@ class Item : public Parse_tree_node { virtual bool collect_grouped_aggregates(uchar *) { return false; } virtual bool collect_subqueries(uchar *) { return false; } virtual bool update_depended_from(uchar *) { return false; } + virtual bool mark_subquery_dependent_const_cacheable(uchar *) { + return false; + } /** Check if an aggregate is referenced from within the GROUP BY clause of the query block in which it is aggregated. Such diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 25c49c82dc7..0ae74727e06 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2617,6 +2617,22 @@ bool Item_subselect::collect_subqueries(uchar *arg) { return false; } +bool Item_subselect::mark_subquery_dependent_const_cacheable(uchar *) { + /* + The query expression is dependent on const tables, whose results are + stable and safe to cache. Clear away its query blocks' + UNCACHEABLE_DEPENDENT mark. + */ + if (unit->uncacheable & UNCACHEABLE_DEPENDENT) { + unit->uncacheable &= ~UNCACHEABLE_DEPENDENT; + for (SELECT_LEX *select_lex = unit->first_select(); select_lex; + select_lex = select_lex->next_select()) + select_lex->uncacheable &= ~UNCACHEABLE_DEPENDENT; + } + + return false; +} + Item_subselect::trans_res Item_allany_subselect::select_transformer( THD *thd, SELECT_LEX *select) { DBUG_TRACE; diff --git a/sql/item_subselect.h b/sql/item_subselect.h index f546e0b43a6..036504f4c6f 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -252,6 +252,7 @@ class Item_subselect : public Item_result_field { Item *replace_item_field(uchar *arg) override; Item *replace_item_view_ref(uchar *arg) override; Item *replace_item(Item_transformer t, uchar *arg); + bool mark_subquery_dependent_const_cacheable(uchar *) override; friend class Query_result_interceptor; friend class Item_in_optimizer; diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc index 34c8bea1ad5..2b61b463bd9 100644 --- a/sql/sql_optimizer.cc +++ b/sql/sql_optimizer.cc @@ -9264,6 +9264,10 @@ static bool make_join_select(JOIN *join, Item *cond) { const bool const_cond_result = const_cond->val_int() != 0; if (thd->is_error()) return true; + if (const_cond->walk(&Item::mark_subquery_dependent_const_cacheable, + enum_walk::POSTFIX, nullptr)) + return true; + Opt_trace_object trace_const_cond(trace); trace_const_cond.add("condition_on_constant_tables", const_cond) .add("condition_value", const_cond_result); -- 2.19.1.6.gb485710b