この記事は MySQL Advent Calendar 2023 3日目の記事です。
導入
EXPLAINにはtraditional（いわゆる通常のEXPLAIN）の他にtreeやjsonのフォーマットが以下のように存在する。
--traditional mysql> explain SELECT employees.employee_id, employees.first_name, employees.last_name, employees.hire_date, employees.salary, departments.department_name, departments.location FROM employees JOIN departments ON employees.department_id = departments.department_id WHERE employees.salary > 80000 AND departments.location = 'San Francisco'; +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+ | 1 | SIMPLE | departments | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 10.00 | Using where | | 1 | SIMPLE | employees | NULL | ref | department_id | department_id | 5 | test.departments.department_id | 1 | 33.33 | Using where | +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) --tree mysql> explain format=tree SELECT employees.employee_id, employees.first_name, employees.last_name, employees departments ON employees.department_id = departments.department_id WHERE employees.salary > 80000 AND departments.location = 'San Francisco'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=1.60 rows=0) -> Filter: (departments.location = 'San Francisco') (cost=1.25 rows=1) -> Table scan on departments (cost=1.25 rows=10) -> Filter: (employees.salary > 80000.00) (cost=0.28 rows=0) -> Index lookup on employees using department_id (department_id=departments.department_id) (cost=0.28 rows=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) --json mysql> explain format=json SELECT employees.employee_id, employees.first_name, employees.last_name, employees departments ON employees.department_id = departments.department_id WHERE employees.salary > 80000 AND departments.location = 'San Francisco'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1.60" }, "nested_loop": [ { "table": { "table_name": "departments", "access_type": "ALL", "possible_keys": [ "PRIMARY" ], "rows_examined_per_scan": 10, "rows_produced_per_join": 1, "filtered": "10.00", "cost_info": { "read_cost": "1.15", "eval_cost": "0.10", "prefix_cost": "1.25", "data_read_per_join": "2K" }, "used_columns": [ "department_id", "department_name", "location" ], "attached_condition": "(`test`.`departments`.`location` = 'San Francisco')" } }, { "table": { "table_name": "employees", "access_type": "ref", "possible_keys": [ "department_id" ], "key": "department_id", "used_key_parts": [ "department_id" ], "key_length": "5", "ref": [ "test.departments.department_id" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 0, "filtered": "33.33", "cost_info": { "read_cost": "0.25", "eval_cost": "0.03", "prefix_cost": "1.60", "data_read_per_join": "687" }, "used_columns": [ "employee_id", "first_name", "last_name", "hire_date", "salary", "department_id" ], "attached_condition": "(`test`.`employees`.`salary` > 80000.00)" } } ] } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
これらフォーマットとは別にkuboフォーマットを試しに作ってみたぞいというのが本記事の概要である。とはいっても、traditionalの出力フォーマットに有益でないカラムを１つ追加したものをkuboフォーマットと名付けただけである。そのため、そこまで大掛かりなことはしていない。
具体的には以下のようにEXTRAの横にkuboカラムが追加されている状態で出力される。 どんな行に対しても'kubo'と表示されるなんとも末恐ろしいカラムである。
mysql> explain format=kubo SELECT employees.employee_id, employees.first_name, employees.last_name, employees departments ON employees.department_id = departments.department_id WHERE employees.salary > 80000 AND departments.location = 'San Francisco'; +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | kubo | +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+------+ | 1 | SIMPLE | departments | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 10.00 | Using where | kubo | | 1 | SIMPLE | employees | NULL | ref | department_id | department_id | 5 | test.departments.department_id | 1 | 33.33 | Using where | kubo | +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+------+
さて、以降ではソースのどこにどのような編集を加えたかを順に説明する。
作るぞ
sql_yacc
まずはsql_yacc.yyに与えられているフォーマット指定部分の文法を編集する。以下のようにkuboフォーマット指定の場合はExplain_format_type::KUBOを指定する分岐を追加する。これによって後にlex->explain_formatに格納されるフォーマットクラスが決まる。
opt_explain_format_type: /* empty */ { $$= Explain_format_type::DEFAULT; } | FORMAT_SYM EQ ident_or_text { if (is_identifier($3, "JSON")) $$= Explain_format_type::JSON; else if (is_identifier($3, "TRADITIONAL")) $$= Explain_format_type::TRADITIONAL; else if (is_identifier($3, "TREE")) $$= Explain_format_type::TREE; else if (is_identifier($3, "KUBO")) $$= Explain_format_type::KUBO; else { my_error(ER_UNKNOWN_EXPLAIN_FORMAT, MYF(0), $3.str); MYSQL_YYABORT; } }
そしてsql_yacc.ccでは以下のような分岐が与えられる。 なお、Explain_format_type::KUBOは次に定義する。
#line 14012 "sql_yacc.yy" /* yacc.c:1646 */ { if (is_identifier((yyvsp[0].lexer.lex_str), "JSON")) (yyval.explain_format_type)= Explain_format_type::JSON; else if (is_identifier((yyvsp[0].lexer.lex_str), "TRADITIONAL")) (yyval.explain_format_type)= Explain_format_type::TRADITIONAL; else if (is_identifier((yyvsp[0].lexer.lex_str), "TREE")) (yyval.explain_format_type)= Explain_format_type::TREE; else if (is_identifier((yyvsp[0].lexer.lex_str), "KUBO")) (yyval.explain_format_type)= Explain_format_type::KUBO; else { my_error(ER_UNKNOWN_EXPLAIN_FORMAT, MYF(0), (yyvsp[0].lexer.lex_str).str); MYSQL_YYABORT; } }
parser_yystype.hのExplain_format_typeクラスの定義にKUBOを追加する。
enum class Explain_format_type { // DEFAULT will be changed during parsing to TRADITIONAL // for regular EXPLAIN, or TREE for EXPLAIN ANALYZE. DEFAULT, TRADITIONAL, JSON, TREE, TREE_WITH_EXECUTE, KUBO };
parse_tree_nodes
次に以下のようにparse_tree_nodes.ccのmake_cmd関数にkuboフォーマットの分岐を追加する。この関数はParse_tree_rootのmake_cmdを実装したものである。大雑把に言えば、ここでは前節で決定したExplain_format_typeに対応するフォーマットインスタンスを準備している。
※Explain_format_kuboクラスは後に用意する
... #include "sql/opt_explain_json.h" // Explain_format_JSON #include "sql/opt_explain_traditional.h" // Explain_format_traditional #include "sql/opt_explain_kubo.h" // Explain_format_kubo ... Sql_cmd *PT_explain::make_cmd(THD *thd) { LEX *const lex = thd->lex; switch (m_format) { case Explain_format_type::TRADITIONAL: lex->explain_format = new (thd->mem_root) Explain_format_traditional; break; case Explain_format_type::JSON: lex->explain_format = new (thd->mem_root) Explain_format_JSON; break; case Explain_format_type::TREE: lex->explain_format = new (thd->mem_root) Explain_format_tree; break; case Explain_format_type::TREE_WITH_EXECUTE: lex->explain_format = new (thd->mem_root) Explain_format_tree; lex->is_explain_analyze = true; break; //kuboフォーマットの分岐 case Explain_format_type::KUBO: lex->explain_format = new (thd->mem_root) Explain_format_kubo; break; default: assert(false); lex->explain_format = new (thd->mem_root) Explain_format_traditional; } if (lex->explain_format == nullptr) return nullptr; // OOM
sql_class
次にsql_class.ccのsend_explain_fieldsにkuboフォーマットの場合の分岐を用意する。
このメソッドはsend_headersというtraditionalなEXPLAINのカラム名に関する情報を送信するメソッドから呼び出されている。このメソッドではmem_root_dequeの末尾に各種カラム名をpushしている。
今回は EXTRAの隣に新規のカラムを追加するので、kuboフォーマットの場合にkuboカラムをpushするように分岐を加える。
※is_kubo()はExplain_format_kuboクラスを後に用意する際に実装する。
int THD::send_explain_fields(Query_result *result) { mem_root_deque<Item *> field_list(current_thd->mem_root); Item *item; CHARSET_INFO *cs = system_charset_info; field_list.push_back(new Item_return_int("id", 3, MYSQL_TYPE_LONGLONG)); field_list.push_back(new Item_empty_string("select_type", 19, cs)); field_list.push_back(item = new Item_empty_string("table", NAME_CHAR_LEN, cs)); item->set_nullable(true); /* Maximum length of string that make_used_partitions_str() can produce */ item = new Item_empty_string("partitions", MAX_PARTITIONS * (1 + FN_LEN), cs); field_list.push_back(item); item->set_nullable(true); field_list.push_back(item = new Item_empty_string("type", 10, cs)); item->set_nullable(true); field_list.push_back(item = new Item_empty_string( "possible_keys", NAME_CHAR_LEN * MAX_KEY, cs)); item->set_nullable(true); field_list.push_back(item = new Item_empty_string("key", NAME_CHAR_LEN, cs)); item->set_nullable(true); field_list.push_back( item = new Item_empty_string("key_len", NAME_CHAR_LEN * MAX_KEY)); item->set_nullable(true); field_list.push_back( item = new Item_empty_string("ref", NAME_CHAR_LEN * MAX_REF_PARTS, cs)); item->set_nullable(true); field_list.push_back( item = new Item_return_int("rows", 10, MYSQL_TYPE_LONGLONG)); item->set_nullable(true); field_list.push_back( item = new Item_float(NAME_STRING("filtered"), 0.1234, 2, 4)); item->set_nullable(true); field_list.push_back(new Item_empty_string("Extra", 255, cs)); item->set_nullable(true); //kuboフォーマットの分岐 if (current_thd->lex->explain_format->is_kubo()) { field_list.push_back(new Item_empty_string("kubo", 255, cs)); item->set_nullable(true); } return (result->send_result_set_metadata( this, field_list, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)); }
opt_expalin_kubo
最後にExplain_format_kuboクラスを実装する。実装するとは言っても、Explain_format_traditionalとほぼ同じファイルを追加するだけである。そのため、新規ファイルを作らずともsql/opt_expalin_traditional.ccに書いても良いと思う。
定義ファイルはExplain_format_traditionalの'traditional'の部分を'kubo'に置き換えて、is_kubo()メソッドを追加するだけで良い。
#include <assert.h> // assert #include "sql/opt_explain_format.h" #include "sql/parse_tree_node_base.h" class Item; class Query_result; class Query_expression; template <class T> class mem_root_deque; class Explain_format_kubo : public Explain_format { class Item_null *nil; qep_row column_buffer; ///< buffer for the current output row public: Explain_format_kubo() = default; bool is_hierarchical() const override { return false; } bool send_headers(Query_result *result) override; bool begin_context(enum_parsing_context, Query_expression *, const Explain_format_flags *) override { return false; } bool end_context(enum_parsing_context) override { return false; } bool flush_entry() override; qep_row *entry() override { return &column_buffer; } bool is_kubo() const override { return true; } private: bool push_select_type(mem_root_deque<Item *> *items); };
実装は同様にExplain_format_traditionalと同じ内容のものを用意して、'traditional'の部分を'kubo'に置き換えて、flush_entry()にkuboカラムへ'kubo'を追加するよう付け加えるだけで良い。
なお、この関数では先ほどと同様にmem_root_dequeにカラム値を順番に入れていっている。よって、EXTRAへのpushが終わった後にkuboカラムへのpushをする必要があることに注意したい。
bool Explain_format_kubo::flush_entry() { /* Buffer_cleanup will empty column_buffer upon exit. So column values start clear for the next row. */ Buffer_cleanup bc(&column_buffer); mem_root_deque<Item *> items(current_thd->mem_root); if (push(&items, column_buffer.col_id, nil) || push_select_type(&items) || push(&items, column_buffer.col_table_name, nil) || push(&items, column_buffer.col_partitions, nil) || push(&items, column_buffer.col_join_type, nil) || push(&items, column_buffer.col_possible_keys, nil) || push(&items, column_buffer.col_key, nil) || push(&items, column_buffer.col_key_len, nil) || push(&items, column_buffer.col_ref, nil) || push(&items, column_buffer.col_rows, nil) || push(&items, column_buffer.col_filtered, nil)) return true; if (column_buffer.col_message.is_empty() && column_buffer.col_extra.is_empty()) { items.push_back(nil); } else if (!column_buffer.col_extra.is_empty()) { StringBuffer<64> buff(system_charset_info); List_iterator<qep_row::extra> it(column_buffer.col_extra); qep_row::extra *e; while ((e = it++)) { assert(traditional_extra_tags[e->tag] != nullptr); if (buff.append(traditional_extra_tags[e->tag])) return true; if (e->data) { bool brackets = false; switch (e->tag) { case ET_RANGE_CHECKED_FOR_EACH_RECORD: case ET_USING_INDEX_FOR_GROUP_BY: case ET_USING_JOIN_BUFFER: case ET_FIRST_MATCH: case ET_REMATERIALIZE: brackets = true; // for backward compatibility break; default: break; } if (e->tag != ET_FIRST_MATCH && // for backward compatibility e->tag != ET_PUSHED_JOIN && buff.append(" ")) return true; if (brackets && buff.append("(")) return true; if (buff.append(e->data)) return true; if (brackets && buff.append(")")) return true; } if (buff.append("; ")) return true; } if (!buff.is_empty()) buff.length(buff.length() - 2); // remove last "; " if (push(&items, buff.dup(current_thd->mem_root), buff.length())) return true; } else { if (push(&items, column_buffer.col_message, nil)) return true; } // kuboカラムへ'kubo'を追加 const char* kubo = "kubo"; size_t length = strlen(kubo); if (push(&items, kubo, length)) return true; if (output->send_data(current_thd, items)) return true; return false;
以上の編集を以てビルドすれば目的のフォーマットが使えるようになる！
もっとマシなカラムにしたいぞ
「もっとマシなカラムにしたいぞ」ともう一人の僕が宣っているので、format=jsonでも出力されるprefix_costを表示させることにした。column_bufferにすでにcol_prefix_costが格納されているので、それを表示させれば良い。そのため、ここまでの実装ができていれば修正ポイントは少ない。
具体的にはsql_class.ccの分岐修正、opt_explain_kubo.ccpush関数にpush関数追加と分岐修正をすれば良い（col_prefix_costはdouble型であり、double型を引数に取るpush関数が存在しないので、自分で追加する必要がある）
if (current_thd->lex->explain_format->is_kubo()) { field_list.push_back(new Item_float(NAME_STRING("prefix_cost"), 0.1234, 2, 4)); item->set_nullable(true); }
static bool push(mem_root_deque<Item *> *items, const qep_row::column<double> &c, Item_null *nil) { if (c.is_empty()) { items->push_back(nil); return false; } Item_float *item = new Item_float(c.get(), 4); if (item == nullptr) { return true; } items->push_back(item); return false; } .... if (push(&items, column_buffer.col_prefix_cost, nil)) return true;
これを経てビルドすると、以下のようにEXTRAの横にprefix_costが表示されるようになる。これによって、総コストと特にどの段階でコストがかかっているかを把握できる（ここでいうコストとはread_costとeval_costの和を指す）
mysql> explain format=kubo SELECT employees.employee_id, employees.first_name, employees.last_name, employees.hire_date, employees.salary, departments.department_name, departments.location FROM employees JOIN departments ON employees.department_id = departments.department_id WHERE employees.salary > 80000 AND departments.location = 'San Francisco'; +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | prefix_cost | +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+-------------+ | 1 | SIMPLE | departments | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 10.00 | Using where | 2.0000 | | 1 | SIMPLE | employees | NULL | ref | department_id | department_id | 5 | test.departments.department_id | 1 | 33.33 | Using where | 3.2222 | +----+-------------+-------------+------------+------+---------------+---------------+---------+--------------------------------+------+----------+-------------+-------------+ 2 rows in set, 1 warning (0.01 sec)
もっとこんなカラムがあったらいいよなぁ!?みたいな話題や案がございましたら是非教えてください！
明日のイルカのお友達は
明日は@nippondanjiさんから「GIPKことはじめ」です！お楽しみに！
