[Commits] ec398a2: MDEV-7811: EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache

sanja at mariadb.com sanja at mariadb.com
Wed Mar 25 19:27:13 EET 2015


revision-id: ec398a2ee84be0af3970d6247296fe1077b2f449
parent(s): 3578419b778876fc1e7ec2a6994b9273b5d2b166
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-03-25 18:27:10 +0100
message:

MDEV-7811: EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache

---
 mysql-test/r/subselect_cache.result | 211 ++++++++++++++++++++++++++++++++++++
 mysql-test/t/subselect_cache.test   |   8 ++
 sql/item.cc                         |  13 +++
 sql/item.h                          |   2 +
 sql/item_subselect.cc               |  19 ++++
 sql/item_subselect.h                |   5 +-
 sql/sql_explain.cc                  |  24 +++-
 sql/sql_explain.h                   |  12 +-
 sql/sql_expression_cache.cc         |  15 ++-
 sql/sql_expression_cache.h          |  48 +++++++-
 sql/sql_lex.cc                      |  17 +++
 11 files changed, 367 insertions(+), 7 deletions(-)

diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result
index e5a2fe1..fa4ad21 100644
--- a/mysql-test/r/subselect_cache.result
+++ b/mysql-test/r/subselect_cache.result
@@ -39,6 +39,217 @@ Handler_read_prev	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	31
+analyze format=json
+select a, (select d from t2 where b=c) from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 10,
+      "r_rows": 10,
+      "filtered": 100,
+      "r_filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "expression_cache": {
+            "state": "ENABLED",
+            "hit": 6,
+            "miss": 4
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "r_loops": 4,
+            "rows": 4,
+            "r_rows": 4,
+            "filtered": 100,
+            "r_filtered": 18.75,
+            "attached_condition": "(t1.b = t2.c)"
+          }
+        }
+      }
+    ]
+  }
+}
+analyze format=json
+select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 10,
+      "r_rows": 10,
+      "filtered": 100,
+      "r_filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union3,4>",
+            "access_type": "ALL",
+            "expression_cache": {
+              "state": "ENABLED",
+              "hit": 6,
+              "miss": 4
+            },
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t2",
+                    "access_type": "ALL",
+                    "r_loops": 4,
+                    "rows": 4,
+                    "r_rows": 4,
+                    "filtered": 100,
+                    "r_filtered": 18.75,
+                    "attached_condition": "(t1.b = t2.c)"
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 4,
+                  "table": {
+                    "message": "No tables used"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      },
+      {
+        "query_block": {
+          "select_id": 2,
+          "expression_cache": {
+            "state": "ENABLED",
+            "hit": 6,
+            "miss": 4
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "r_loops": 4,
+            "rows": 4,
+            "r_rows": 4,
+            "filtered": 100,
+            "r_filtered": 18.75,
+            "attached_condition": "(t1.b = t2.c)"
+          }
+        }
+      }
+    ]
+  }
+}
+explain format=json
+select a, (select d from t2 where b=c) from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 10,
+      "filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "expression_cache": {
+            "state": "UNINITIZED"
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "rows": 4,
+            "filtered": 100,
+            "attached_condition": "(t1.b = t2.c)"
+          }
+        }
+      }
+    ]
+  }
+}
+explain format=json
+select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 10,
+      "filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union3,4>",
+            "access_type": "ALL",
+            "expression_cache": {
+              "state": "UNINITIZED"
+            },
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t2",
+                    "access_type": "ALL",
+                    "rows": 4,
+                    "filtered": 100,
+                    "attached_condition": "(t1.b = t2.c)"
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 4,
+                  "table": {
+                    "message": "No tables used"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      },
+      {
+        "query_block": {
+          "select_id": 2,
+          "expression_cache": {
+            "state": "UNINITIZED"
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "rows": 4,
+            "filtered": 100,
+            "attached_condition": "(t1.b = t2.c)"
+          }
+        }
+      }
+    ]
+  }
+}
 set optimizer_switch='subquery_cache=off';
 flush status;
 select a, (select d from t2 where b=c) from t1;
diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test
index 1276e54..ab15b35 100644
--- a/mysql-test/t/subselect_cache.test
+++ b/mysql-test/t/subselect_cache.test
@@ -24,6 +24,14 @@ select a, (select d from t2 where b=c) from t1;
 show status like "subquery_cache%";
 show status like '%Handler_read%';
 
+analyze format=json
+select a, (select d from t2 where b=c) from t1;
+analyze format=json
+select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
+explain format=json
+select a, (select d from t2 where b=c) from t1;
+explain format=json
+select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
 set optimizer_switch='subquery_cache=off';
 flush status;
 
diff --git a/sql/item.cc b/sql/item.cc
index c8a9164..7f2f8a8 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -7542,6 +7542,19 @@ bool Item_cache_wrapper::set_cache(THD *thd)
   DBUG_RETURN(expr_cache == NULL);
 }
 
+Expression_cache_stat* Item_cache_wrapper::set_stat(MEM_ROOT *mem_root)
+{
+  if (expr_cache)
+  {
+    Expression_cache_stat* stat=
+      new(mem_root) Expression_cache_stat(expr_cache);
+    if (stat)
+      ((Expression_cache_tmptable *)expr_cache)->set_stat(stat);
+    return stat;
+  }
+  return NULL;
+}
+
 
 /**
   Check if the current values of the parameters are in the expression cache
diff --git a/sql/item.h b/sql/item.h
index 4246859..4e756f2 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -3551,6 +3551,7 @@ class Item_direct_ref_to_ident :public Item_direct_ref
 
 class Item_cache;
 class Expression_cache;
+class Expression_cache_stat;
 
 /**
   The objects of this class can store its values in an expression cache.
@@ -3586,6 +3587,7 @@ class Item_cache_wrapper :public Item_result_field
   enum Type real_type() const { return orig_item->type(); }
 
   bool set_cache(THD *thd);
+  Expression_cache_stat* set_stat(MEM_ROOT *mem_root);
 
   bool fix_fields(THD *thd, Item **it);
   void fix_length_and_dec() {}
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 32d6362..9b9ed8e 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1200,7 +1200,10 @@ Item* Item_singlerow_subselect::expr_cache_insert_transformer(uchar *thd_arg)
 
   if (expr_cache_is_needed(thd) &&
       (expr_cache= set_expr_cache(thd)))
+  {
+    set_expr_cache_stat(thd);
     DBUG_RETURN(expr_cache);
+  }
   DBUG_RETURN(this);
 }
 
@@ -1493,7 +1496,10 @@ Item* Item_exists_subselect::expr_cache_insert_transformer(uchar *thd_arg)
 
   if (substype() == EXISTS_SUBS && expr_cache_is_needed(thd) &&
       (expr_cache= set_expr_cache(thd)))
+  {
+    set_expr_cache_stat(thd);
     DBUG_RETURN(expr_cache);
+  }
   DBUG_RETURN(this);
 }
 
@@ -6547,3 +6553,16 @@ void subselect_table_scan_engine::cleanup()
 {
 }
 
+void Item_subselect::set_expr_cache_stat(THD *thd)
+{
+  if(!expr_cache)
+    return;
+
+  Explain_query *qw= thd->lex->explain;
+  DBUG_ASSERT(qw);
+  Explain_node *node= qw->get_node(unit->first_select()->select_number);
+  if (!node)
+    return;
+  DBUG_ASSERT(expr_cache->type() == Item::EXPR_CACHE_ITEM);
+  node->cache_stat= ((Item_cache_wrapper *)expr_cache)->set_stat(qw->mem_root);
+}
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 92b269d..9f12b3f 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -248,7 +248,10 @@ class Item_subselect :public Item_result_field
   bool limit_index_condition_pushdown_processor(uchar *opt_arg) 
   {
     return TRUE;
-   }
+  }
+
+  void set_expr_cache_stat(THD *thd);
+
 
   friend class select_result_interceptor;
   friend class Item_in_optimizer;
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index aa16a74..69ad41b 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -22,6 +22,7 @@
 #include "sql_priv.h"
 #include "sql_select.h"
 #include "my_json_writer.h"
+#include "sql_expression_cache.h"
 
 const char * STR_DELETING_ALL_ROWS= "Deleting all rows";
 const char * STR_IMPOSSIBLE_WHERE= "Impossible WHERE";
@@ -513,6 +514,7 @@ void Explain_union::print_explain_json(Explain_query *query,
   make_union_table_name(table_name_buffer);
   writer->add_member("table_name").add_str(table_name_buffer);
   writer->add_member("access_type").add_str("ALL"); // not very useful
+  print_explain_json_cache(writer, is_analyze);
   writer->add_member("query_specifications").start_array();
 
   for (int i= 0; i < (int) union_members.elements(); i++)
@@ -610,6 +612,25 @@ void Explain_node::print_explain_json_for_children(Explain_query *query,
 }
 
 
+void Explain_node::print_explain_json_cache(Json_writer *writer,
+                                            bool is_analyze)
+{
+    if (cache_stat)
+    {
+      cache_stat->flush_stat();
+      writer->add_member("expression_cache").start_object();
+      writer->add_member("state").
+        add_str(Expression_cache_stat::state_str[cache_stat->state]);
+      if (is_analyze)
+      {
+        writer->add_member("hit").add_ll(cache_stat->hit);
+        writer->add_member("miss").add_ll(cache_stat->miss);
+      }
+      writer->end_object();
+    }
+}
+
+
 void Explain_select::replace_table(uint idx, Explain_table_access *new_tab)
 {
   delete join_tabs[idx];
@@ -732,12 +753,13 @@ void Explain_select::print_explain_json(Explain_query *query,
     */
     writer->add_member("query_block").start_object();
     writer->add_member("select_id").add_ll(select_id);
-    
+
     if (exec_const_cond)
     {
       writer->add_member("const_condition");
       write_item(writer, exec_const_cond);
     }
+    print_explain_json_cache(writer, is_analyze);
 
     Explain_basic_join::print_explain_json_interns(query, writer, is_analyze);
     writer->end_object();
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 970edf7..549211f 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -91,7 +91,7 @@ class Explain_node : public Sql_alloc
 {
 public:
   Explain_node(MEM_ROOT *root)
-    :children(root)
+    :cache_stat(0), children(root)
   {}
   /* A type specifying what kind of node this is */
   enum explain_node_type 
@@ -111,11 +111,16 @@ class Explain_node : public Sql_alloc
     EXPLAIN_NODE_NON_MERGED_SJ /* aka JTBM semi-join */
   };
 
-  Explain_node() : connection_type(EXPLAIN_NODE_OTHER) {}
+  Explain_node() : cache_stat(0), connection_type(EXPLAIN_NODE_OTHER) {}
 
   virtual enum explain_node_type get_type()= 0;
   virtual int get_select_id()= 0;
 
+  /**
+    expression cache statistics
+  */
+  Expression_cache_stat* cache_stat;
+
   /*
     How this node is connected to its parent.
     (NOTE: EXPLAIN_NODE_NON_MERGED_SJ is set very late currently)
@@ -141,6 +146,7 @@ class Explain_node : public Sql_alloc
                                  uint8 explain_flags, bool is_analyze);
   void print_explain_json_for_children(Explain_query *query,
                                        Json_writer *writer, bool is_analyze);
+  void print_explain_json_cache(Json_writer *writer, bool is_analyze);
   virtual ~Explain_node(){}
 };
 
@@ -224,7 +230,7 @@ class Explain_select : public Explain_basic_join
     members have no info 
   */
   const char *message;
-  
+
   /* Expensive constant condition */
   Item *exec_const_cond;
   
diff --git a/sql/sql_expression_cache.cc b/sql/sql_expression_cache.cc
index 824d21e..8737c34 100644
--- a/sql/sql_expression_cache.cc
+++ b/sql/sql_expression_cache.cc
@@ -43,7 +43,7 @@
 Expression_cache_tmptable::Expression_cache_tmptable(THD *thd,
                                                      List<Item> &dependants,
                                                      Item *value)
-  :cache_table(NULL), table_thd(thd), items(dependants), val(value),
+  :cache_table(NULL), table_thd(thd), stat(0), items(dependants), val(value),
    hit(0), miss(0), inited (0)
 {
   DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable");
@@ -61,6 +61,9 @@ void Expression_cache_tmptable::disable_cache()
     cache_table->file->ha_index_end();
   free_tmp_table(table_thd, cache_table);
   cache_table= NULL;
+  flush_stat();
+  if (stat)
+    stat->cache= NULL;
 }
 
 
@@ -164,6 +167,7 @@ void Expression_cache_tmptable::init()
     goto error;
   }
 
+  flush_stat();
   DBUG_VOID_RETURN;
 
 error:
@@ -180,6 +184,11 @@ void Expression_cache_tmptable::init()
 
   if (cache_table)
     disable_cache();
+  else
+  {
+    flush_stat();
+    stat= NULL;
+  }
 }
 
 
@@ -323,3 +332,7 @@ void Expression_cache_tmptable::print(String *str, enum_query_type query_type)
   }
   str->append('>');
 }
+
+
+const char *Expression_cache_stat::state_str[3]=
+{"UNINITIZED", "STOPPED", "ENABLED"};
diff --git a/sql/sql_expression_cache.h b/sql/sql_expression_cache.h
index 48a8e33..f1ceaab 100644
--- a/sql/sql_expression_cache.h
+++ b/sql/sql_expression_cache.h
@@ -19,6 +19,7 @@
 
 #include "sql_select.h"
 
+
 /**
   Interface for expression cache
 
@@ -62,6 +63,11 @@ class Expression_cache :public Sql_alloc
     Initialize this cache
   */
   virtual void init()= 0;
+
+  /**
+    Update statistics
+  */
+  virtual void flush_stat()= 0;
 };
 
 struct st_table_ref;
@@ -69,6 +75,30 @@ class Expression_cache :public Sql_alloc
 class Item_field;
 
 
+class Expression_cache_stat :public Sql_alloc
+{
+public:
+  enum expr_cache_state {UNINITED, STOPPED, OK};
+  Expression_cache_stat(Expression_cache *c) :
+    cache(c), hit(0), miss(0), state(UNINITED)
+  {}
+
+  Expression_cache *cache;
+  ulong hit, miss;
+  enum expr_cache_state state;
+
+  static const char* state_str[3];
+  void set(ulong h, ulong m, enum expr_cache_state s)
+  {hit= h; miss= m; state= s;}
+
+  void flush_stat()
+  {
+    if (cache)
+      cache->flush_stat();
+  }
+};
+
+
 /**
   Implementation of expression cache over a temporary table
 */
@@ -85,6 +115,20 @@ class Expression_cache_tmptable :public Expression_cache
   bool is_inited() { return inited; };
   void init();
 
+  void set_stat(Expression_cache_stat *st)
+  {
+    stat= st;
+    flush_stat();
+  }
+  virtual void flush_stat()
+  {
+    if (stat)
+      stat->set(hit, miss, (inited ? (cache_table ?
+                                      Expression_cache_stat::OK :
+                                      Expression_cache_stat::STOPPED) :
+                            Expression_cache_stat::UNINITED));
+  }
+
 private:
   void disable_cache();
 
@@ -94,6 +138,8 @@ class Expression_cache_tmptable :public Expression_cache
   TABLE *cache_table;
   /* Thread handle for the temporary table */
   THD *table_thd;
+  /* EXPALIN/ANALYZE statistics */
+  Expression_cache_stat *stat;
   /* TABLE_REF for index lookup */
   struct st_table_ref ref;
   /* Cached result */
@@ -103,7 +149,7 @@ class Expression_cache_tmptable :public Expression_cache
   /* Value Item example */
   Item *val;
   /* hit/miss counters */
-  uint hit, miss;
+  ulong hit, miss;
   /* Set on if the object has been succesfully initialized with init() */
   bool inited;
 };
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 4e06111..28f92a7 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3488,6 +3488,8 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only)
 
       bool empty_union_result= true;
       bool is_correlated_unit= false;
+      bool first= true;
+      bool union_plan_saved= false;
       /*
         If the subquery is a UNION, optimize all the subqueries in the UNION. If
         there is no UNION, then the loop will execute once for the subquery.
@@ -3495,6 +3497,17 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only)
       for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select())
       {
         JOIN *inner_join= sl->join;
+        if (first)
+          first= false;
+        else
+        {
+          if (!union_plan_saved)
+          {
+            union_plan_saved= true;
+            if (un->save_union_explain(un->thd->lex->explain))
+              return true; /* Failure */
+          }
+        }
         if (!inner_join)
           continue;
         SELECT_LEX *save_select= un->thd->lex->current_select;
@@ -4317,6 +4330,10 @@ void LEX::restore_set_statement_var()
 int st_select_lex_unit::save_union_explain(Explain_query *output)
 {
   SELECT_LEX *first= first_select();
+
+  if (output->get_union(first->select_number))
+    return 0; /* Already added */
+
   Explain_union *eu= new (output->mem_root) Explain_union(output->mem_root);
 
   if (derived)


More information about the commits mailing list