[Commits] 461dbd8: EXPLAIN FORMAT=JSON: support join buffering

Sergei Petrunia psergey at askmonty.org
Thu Nov 27 22:10:44 EET 2014


revision-id: 461dbd80d2ea96034f330dd238282d2167ed2c4d
parent(s): 37c444e1a079b25d0a34efbbc2fadfae17999966
committer: Sergei Petrunia
branch nick: 10.1-explain-json-r5
timestamp: 2014-11-27 23:10:44 +0300
message:

EXPLAIN FORMAT=JSON: support join buffering

- Basic support for JOIN buffering
- The output is not polished but catches the main point:
  tab->select_cond and tab->cache_select->cond are printed separately.
- Hash join support is poor still.

- Also fixed identation in JOIN_TAB::save_explain_data

---
 mysql-test/r/explain_json.result |   33 ++++++++++++++++++++++++++++++++
 mysql-test/t/explain_json.test   |   12 ++++++++++++
 sql/sql_explain.cc               |   39 ++++++++++++++++++++++++++++++++++----
 sql/sql_explain.h                |   11 ++++++++++-
 sql/sql_select.cc                |   14 +++++++-------
 5 files changed, 97 insertions(+), 12 deletions(-)

diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result
index d2225c1..ec775c8 100644
--- a/mysql-test/r/explain_json.result
+++ b/mysql-test/r/explain_json.result
@@ -309,4 +309,37 @@ EXPLAIN
   }
 }
 drop table t1;
+#
+# Join buffering
+#
+create table t1 (a int, b int);
+insert into t1 select A.a+10*B.a, A.a+10*B.a from t0 A, t0 B;
+explain format=json
+select * from t1 A, t1 B where A.a=B.a and A.b < 3 and B.b < 5;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "A",
+      "access_type": "ALL",
+      "rows": 100,
+      "filtered": 100,
+      "attached_condition": "(A.b < 3)"
+    },
+    "block-nl-join": {
+      "table": {
+        "table_name": "B",
+        "access_type": "ALL",
+        "rows": 100,
+        "filtered": 100,
+        "attached_condition": "(B.b < 5)"
+      },
+      "buffer_type": "flat",
+      "join_type": "BNL",
+      "attached_condition": "(B.a = A.a)"
+    }
+  }
+}
+drop table t1;
 drop table t0;
diff --git a/mysql-test/t/explain_json.test b/mysql-test/t/explain_json.test
index 32932e0..6dc54f1 100644
--- a/mysql-test/t/explain_json.test
+++ b/mysql-test/t/explain_json.test
@@ -58,5 +58,17 @@ select * from t0 where
   a > (select max(b) from t1 where t1.b=t0.a) or a < 3 ;
 
 drop table t1;
+
+--echo #
+--echo # Join buffering
+--echo #
+create table t1 (a int, b int);
+insert into t1 select A.a+10*B.a, A.a+10*B.a from t0 A, t0 B;
+
+explain format=json
+select * from t1 A, t1 B where A.a=B.a and A.b < 3 and B.b < 5;
+
+drop table t1;
+
 drop table t0;
 
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 2e60c65..a37d324 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -903,8 +903,18 @@ void Explain_table_access::tag_to_json(Json_writer *writer, enum explain_extra_t
       write_item(writer, pushed_index_cond);
       break;
     case ET_USING_WHERE:
-      writer->add_member("attached_condition");
-      write_item(writer, where_cond);
+      if (where_cond)
+      {
+        writer->add_member("attached_condition");
+        /*
+          We are printing the condition that is checked when scanning this
+          table.
+          - when join buffer is used, it is cache_cond. 
+          - in other cases, it is where_cond.
+        */
+        Item *item= bka_type.is_using_jbuf()? cache_cond: where_cond;
+        write_item(writer, item);
+      }
       break;
     case ET_USING_INDEX:
       writer->add_member("using_index").add_bool(true);
@@ -912,8 +922,8 @@ void Explain_table_access::tag_to_json(Json_writer *writer, enum explain_extra_t
     case ET_USING:
       // index merge: case ET_USING 
       break;
-    case ET_USING_JOIN_BUFFER: 
-      // TODO TODO 
+    case ET_USING_JOIN_BUFFER:
+      /* Do nothing. Join buffer is handled differently */
       break;
     default:
       DBUG_ASSERT(0);
@@ -925,6 +935,12 @@ void Explain_table_access::print_explain_json(Json_writer *writer,
                                               bool is_analyze)
 {
   Json_writer_nesting_guard guard(writer);
+
+  if (bka_type.is_using_jbuf())
+  {
+    writer->add_member("block-nl-join").start_object();
+  }
+
   writer->add_member("table").start_object();
 
   writer->add_member("table_name").add_str(table_name);
@@ -1018,6 +1034,21 @@ void Explain_table_access::print_explain_json(Json_writer *writer,
     tag_to_json(writer, extra_tags.at(i));
   }
 
+  if (bka_type.is_using_jbuf())
+  {
+    writer->end_object();
+    writer->add_member("buffer_type").add_str(bka_type.incremental?
+                                              "incremental":"flat");
+    writer->add_member("join_type").add_str(bka_type.join_alg);
+    if (bka_type.mrr_type.length())
+      writer->add_member("mrr_type").add_str(bka_type.mrr_type);
+    if (where_cond)
+    {
+      writer->add_member("attached_condition");
+      write_item(writer, where_cond);
+    }
+  }
+
   writer->end_object();
 }
 
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 39392d3..6cc8e01 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -488,6 +488,12 @@ class Explain_quick_select : public Sql_alloc
 class Explain_table_access : public Sql_alloc
 {
 public:
+  Explain_table_access() :
+    where_cond(NULL),
+    cache_cond(NULL),
+    pushed_index_cond(NULL)
+  {}
+
   void push_extra(enum explain_extra_tag extra_tag);
 
   /* Internals */
@@ -558,9 +564,12 @@ class Explain_table_access : public Sql_alloc
   
   /*
     Note: lifespan of WHERE condition is less than lifespan of this object.
-    THe below is valid if tags include "ET_USING_WHERE".
+    The below two are valid if tags include "ET_USING_WHERE".
+    (TODO: indexsubquery may put ET_USING_WHERE without setting where_cond?)
   */
   Item *where_cond;
+  Item *cache_cond;
+
   Item *pushed_index_cond;
 
   int print_explain(select_result_sink *output, uint8 explain_flags, 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 96feb2b..185c092 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -23650,10 +23650,10 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tab
 
     if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
         table->file->pushed_idx_cond)
-        {
+    {
       eta->push_extra(ET_USING_INDEX_CONDITION);
-          eta->pushed_index_cond= table->file->pushed_idx_cond;
-        }
+      eta->pushed_index_cond= table->file->pushed_idx_cond;
+    }
     else if (tab->cache_idx_cond)
       eta->push_extra(ET_USING_INDEX_CONDITION_BKA);
 
@@ -23683,11 +23683,11 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tab
           eta->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION);
         }
         else
-            {
-              eta->where_cond= tab->select->cond? tab->select->cond:
-                               tab->cache_select->cond;
+        {
+          eta->where_cond= tab->select->cond;
+          eta->cache_cond= tab->cache_select? tab->cache_select->cond : NULL;
           eta->push_extra(ET_USING_WHERE);
-            }
+        }
       }
     }
     if (table_list /* SJM bushes don't have table_list */ &&


More information about the commits mailing list