diff options
Diffstat (limited to 'sql')
-rw-r--r-- | sql/CMakeLists.txt | 1 | ||||
-rw-r--r-- | sql/handler.h | 1 | ||||
-rw-r--r-- | sql/my_json_writer.cc | 16 | ||||
-rw-r--r-- | sql/my_json_writer.h | 228 | ||||
-rw-r--r-- | sql/opt_range.cc | 845 | ||||
-rw-r--r-- | sql/opt_range.h | 2 | ||||
-rw-r--r-- | sql/opt_subselect.cc | 66 | ||||
-rw-r--r-- | sql/opt_table_elimination.cc | 30 | ||||
-rw-r--r-- | sql/opt_trace.cc | 722 | ||||
-rw-r--r-- | sql/opt_trace.h | 201 | ||||
-rw-r--r-- | sql/opt_trace_context.h | 92 | ||||
-rw-r--r-- | sql/set_var.h | 9 | ||||
-rw-r--r-- | sql/sp_head.cc | 13 | ||||
-rw-r--r-- | sql/sp_head.h | 1 | ||||
-rw-r--r-- | sql/sql_class.cc | 14 | ||||
-rw-r--r-- | sql/sql_class.h | 15 | ||||
-rw-r--r-- | sql/sql_derived.cc | 49 | ||||
-rw-r--r-- | sql/sql_explain.cc | 2 | ||||
-rw-r--r-- | sql/sql_parse.cc | 8 | ||||
-rw-r--r-- | sql/sql_prepare.cc | 12 | ||||
-rw-r--r-- | sql/sql_select.cc | 632 | ||||
-rw-r--r-- | sql/sql_show.cc | 7 | ||||
-rw-r--r-- | sql/sql_test.cc | 27 | ||||
-rw-r--r-- | sql/sql_test.h | 2 | ||||
-rw-r--r-- | sql/sql_view.cc | 10 | ||||
-rw-r--r-- | sql/sys_vars.cc | 18 | ||||
-rw-r--r-- | sql/table.cc | 2 |
27 files changed, 2744 insertions, 281 deletions
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index a22ce694805..69ca326c1fd 100644 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -139,6 +139,7 @@ SET (SQL_SOURCE sql_sequence.cc sql_sequence.h ha_sequence.h sql_tvc.cc sql_tvc.h opt_split.cc + opt_trace.cc ${WSREP_SOURCES} table_cache.cc encryption.cc temporary_tables.cc proxy_protocol.cc backup.cc diff --git a/sql/handler.h b/sql/handler.h index dfb2333b24e..afe62123170 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -993,6 +993,7 @@ enum enum_schema_tables SCH_KEY_CACHES, SCH_KEY_COLUMN_USAGE, SCH_OPEN_TABLES, + SCH_OPT_TRACE, SCH_PARAMETERS, SCH_PARTITIONS, SCH_PLUGINS, diff --git a/sql/my_json_writer.cc b/sql/my_json_writer.cc index 9c4d63f9c4a..7ae0c58bd7d 100644 --- a/sql/my_json_writer.cc +++ b/sql/my_json_writer.cc @@ -219,19 +219,20 @@ void Json_writer::add_str(const String &str) add_str(str.ptr(), str.length()); } -Json_writer_object::Json_writer_object(Json_writer *writer):Json_writer_struct(writer) +Json_writer_object::Json_writer_object(THD *thd) : + Json_writer_struct(thd) { if (my_writer) my_writer->start_object(); } -Json_writer_object::Json_writer_object(Json_writer *writer, const char *str) - :Json_writer_struct(writer) +Json_writer_object::Json_writer_object(THD* thd, const char *str) + : Json_writer_struct(thd) { if (my_writer) my_writer->add_member(str).start_object(); - } + Json_writer_object::~Json_writer_object() { if (!closed && my_writer) @@ -239,14 +240,15 @@ Json_writer_object::~Json_writer_object() closed= TRUE; } -Json_writer_array::Json_writer_array(Json_writer *writer):Json_writer_struct(writer) +Json_writer_array::Json_writer_array(THD *thd) : + Json_writer_struct(thd) { if (my_writer) my_writer->start_array(); } -Json_writer_array::Json_writer_array(Json_writer *writer, const char *str) - :Json_writer_struct(writer) +Json_writer_array::Json_writer_array(THD *thd, const char *str) + :Json_writer_struct(thd) { if (my_writer) my_writer->add_member(str).start_array(); diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index fcf853b0b62..4701cb4165b 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -101,6 +101,80 @@ public: /* + Something that looks like class String, but has an internal limit of + how many bytes one can append to it. + + Bytes that were truncated due to the size limitation are counted. +*/ + +class String_with_limit +{ +public: + + String_with_limit() : size_limit(SIZE_MAX), truncated_len(0) + { + str.length(0); + } + + size_t get_truncated_bytes() const { return truncated_len; } + size_t get_size_limit() { return size_limit; } + + void set_size_limit(size_t limit_arg) + { + // Setting size limit to be shorter than length will not have the desired + // effect + DBUG_ASSERT(str.length() < size_limit); + size_limit= limit_arg; + } + + void append(const char *s, size_t size) + { + if (str.length() + size <= size_limit) + { + // Whole string can be added, just do it + str.append(s, size); + } + else + { + // We cannot add the whole string + if (str.length() < size_limit) + { + // But we can still add something + size_t bytes_to_add = size_limit - str.length(); + str.append(s, bytes_to_add); + truncated_len += size - bytes_to_add; + } + else + truncated_len += size; + } + } + + void append(const char *s) + { + append(s, strlen(s)); + } + + void append(char c) + { + if (str.length() + 1 > size_limit) + truncated_len++; + else + str.append(c); + } + + const String *get_string() { return &str; } + size_t length() { return str.length(); } +private: + String str; + + // str must not get longer than this many bytes. + size_t size_limit; + + // How many bytes were truncated from the string + size_t truncated_len; +}; + +/* A class to write well-formed JSON documents. The documents are also formatted for human readability. */ @@ -116,7 +190,8 @@ public: void add_str(const char* val, size_t num_bytes); void add_str(const String &str); void add_str(Item *item); - void add_table_name(JOIN_TAB *tab); + void add_table_name(const JOIN_TAB *tab); + void add_table_name(const TABLE* table); void add_ll(longlong val); void add_size(longlong val); @@ -134,9 +209,18 @@ public: void end_object(); void end_array(); + /* + One can set a limit of how large a JSON document should be. + Writes beyond that size will be counted, but will not be collected. + */ + void set_size_limit(size_t mem_size) { output.set_size_limit(mem_size); } + + // psergey: return how many bytes would be required to store everything + size_t get_truncated_bytes() { return output.get_truncated_bytes(); } + Json_writer() : indent_level(0), document_start(true), element_started(false), - first_child(true) + first_child(true), allowed_mem_size(0) { fmt_helper.init(this); } @@ -151,22 +235,28 @@ private: bool element_started; bool first_child; + /* + True when we are using the optimizer trace + FALSE otherwise + */ + size_t allowed_mem_size; + Single_line_formatting_helper fmt_helper; void append_indent(); void start_element(); void start_sub_element(); - //const char *new_member_name; public: - String output; + String_with_limit output; }; /* A class to add values to Json_writer_object and Json_writer_array */ -class Json_value_context +class Json_value_helper { Json_writer* writer; - public: + +public: void init(Json_writer *my_writer) { writer= my_writer; } void add_str(const char* val) { @@ -219,11 +309,16 @@ class Json_value_context if (writer) writer->add_null(); } - void add_table_name(JOIN_TAB *tab) + void add_table_name(const JOIN_TAB *tab) { if (writer) writer->add_table_name(tab); } + void add_table_name(const TABLE* table) + { + if (writer) + writer->add_table_name(table); + } }; /* A common base for Json_writer_object and Json_writer_array */ @@ -231,17 +326,17 @@ class Json_writer_struct { protected: Json_writer* my_writer; - Json_value_context context; + Json_value_helper context; /* Tells when a json_writer_struct has been closed or not */ bool closed; public: - Json_writer_struct(Json_writer* writer) + explicit Json_writer_struct(THD *thd) { - my_writer= writer; - context.init(writer); + my_writer= thd->opt_trace.get_current_json(); + context.init(my_writer); closed= false; } }; @@ -249,9 +344,13 @@ public: /* RAII-based class to start/end writing a JSON object into the JSON document + + There is "ignore mode": one can initialize Json_writer_object with a NULL + Json_writer argument, and then all its calls will do nothing. This is used + by optimizer trace which can be enabled or disabled. */ -class Json_writer_object:public Json_writer_struct +class Json_writer_object : public Json_writer_struct { private: void add_member(const char *name) @@ -260,46 +359,49 @@ private: my_writer->add_member(name); } public: - Json_writer_object(Json_writer *w); - Json_writer_object(Json_writer *w, const char *str); + explicit Json_writer_object(THD *thd); + explicit Json_writer_object(THD *thd, const char *str); + Json_writer_object& add(const char *name, bool value) { + DBUG_ASSERT(!closed); add_member(name); context.add_bool(value); return *this; } - Json_writer_object& add(const char* name, uint value) - { - add_member(name); - context.add_ll(value); - return *this; - } - Json_writer_object& add(const char* name, ha_rows value) + Json_writer_object& add(const char *name, ulonglong value) { + DBUG_ASSERT(!closed); add_member(name); - context.add_ll(value); + context.add_ll(static_cast<longlong>(value)); return *this; } Json_writer_object& add(const char *name, longlong value) { + DBUG_ASSERT(!closed); add_member(name); context.add_ll(value); return *this; } Json_writer_object& add(const char *name, double value) { + DBUG_ASSERT(!closed); add_member(name); context.add_double(value); return *this; } + #ifndef _WIN64 Json_writer_object& add(const char *name, size_t value) { + DBUG_ASSERT(!closed); add_member(name); - context.add_ll(value); + context.add_ll(static_cast<longlong>(value)); return *this; } + #endif Json_writer_object& add(const char *name, const char *value) { + DBUG_ASSERT(!closed); add_member(name); context.add_str(value); return *this; @@ -310,38 +412,54 @@ public: context.add_str(value, num_bytes); return *this; } - Json_writer_object& add(const char *name, const String &value) - { - add_member(name); - context.add_str(value); - return *this; - } Json_writer_object& add(const char *name, LEX_CSTRING value) { + DBUG_ASSERT(!closed); add_member(name); context.add_str(value.str); return *this; } Json_writer_object& add(const char *name, Item *value) { + DBUG_ASSERT(!closed); add_member(name); context.add_str(value); return *this; } Json_writer_object& add_null(const char*name) { + DBUG_ASSERT(!closed); add_member(name); context.add_null(); return *this; } - Json_writer_object& add_table_name(JOIN_TAB *tab) + Json_writer_object& add_table_name(const JOIN_TAB *tab) { + DBUG_ASSERT(!closed); add_member("table"); context.add_table_name(tab); return *this; } + Json_writer_object& add_table_name(const TABLE *table) + { + DBUG_ASSERT(!closed); + add_member("table"); + context.add_table_name(table); + return *this; + } + Json_writer_object& add_select_number(uint select_number) + { + DBUG_ASSERT(!closed); + add_member("select_id"); + if (unlikely(select_number >= INT_MAX)) + context.add_str("fake"); + else + context.add_ll(static_cast<longlong>(select_number)); + return *this; + } void end() { + DBUG_ASSERT(!closed); if (my_writer) my_writer->end_object(); closed= TRUE; @@ -352,83 +470,99 @@ public: /* RAII-based class to start/end writing a JSON array into the JSON document + + There is "ignore mode": one can initialize Json_writer_array with a NULL + Json_writer argument, and then all its calls will do nothing. This is used + by optimizer trace which can be enabled or disabled. */ -class Json_writer_array:public Json_writer_struct + +class Json_writer_array : public Json_writer_struct { public: - Json_writer_array(Json_writer *w); - Json_writer_array(Json_writer *w, const char *str); + Json_writer_array(THD *thd); + Json_writer_array(THD *thd, const char *str); void end() { + DBUG_ASSERT(!closed); if (my_writer) my_writer->end_array(); closed= TRUE; } + Json_writer_array& add(bool value) { + DBUG_ASSERT(!closed); context.add_bool(value); return *this; } - Json_writer_array& add(uint value) + Json_writer_array& add(ulonglong value) { - context.add_ll(value); - return *this; - } - Json_writer_array& add(ha_rows value) - { - context.add_ll(value); + DBUG_ASSERT(!closed); + context.add_ll(static_cast<longlong>(value)); return *this; } Json_writer_array& add(longlong value) { + DBUG_ASSERT(!closed); context.add_ll(value); return *this; } Json_writer_array& add(double value) { + DBUG_ASSERT(!closed); context.add_double(value); return *this; } + #ifndef _WIN64 Json_writer_array& add(size_t value) { - context.add_ll(value); + DBUG_ASSERT(!closed); + context.add_ll(static_cast<longlong>(value)); return *this; } + #endif Json_writer_array& add(const char *value) { + DBUG_ASSERT(!closed); context.add_str(value); return *this; } Json_writer_array& add(const char *value, size_t num_bytes) { + DBUG_ASSERT(!closed); context.add_str(value, num_bytes); return *this; } - Json_writer_array& add(const String &value) - { - context.add_str(value); - return *this; - } Json_writer_array& add(LEX_CSTRING value) { + DBUG_ASSERT(!closed); context.add_str(value.str); return *this; } Json_writer_array& add(Item *value) { + DBUG_ASSERT(!closed); context.add_str(value); return *this; } Json_writer_array& add_null() { + DBUG_ASSERT(!closed); context.add_null(); return *this; } - Json_writer_array& add_table_name(JOIN_TAB *tab) + Json_writer_array& add_table_name(const JOIN_TAB *tab) { + DBUG_ASSERT(!closed); context.add_table_name(tab); return *this; } + Json_writer_array& add_table_name(const TABLE *table) + { + DBUG_ASSERT(!closed); + context.add_table_name(table); + return *this; + } ~Json_writer_array(); }; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index eca51b54dab..c87a0595a7d 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -119,6 +119,7 @@ #include "sql_select.h" #include "sql_statistics.h" #include "uniques.h" +#include "my_json_writer.h" #ifndef EXTRA_DEBUG #define test_rb_tree(A,B) {} @@ -429,6 +430,18 @@ static int and_range_trees(RANGE_OPT_PARAM *param, SEL_TREE *result); static bool remove_nonrange_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree); +static void print_key_value(String *out, const KEY_PART_INFO *key_part, + const uchar *key); + +static void append_range_all_keyparts(Json_writer_array *range_trace, + String *range_string, + String *range_so_far, const SEL_ARG *keypart, + const KEY_PART_INFO *key_parts); + +static +void append_range(String *out, const KEY_PART_INFO *key_parts, + const uchar *min_key, const uchar *max_key, const uint flag); + /* SEL_IMERGE is a list of possible ways to do index merge, i.e. it is @@ -2191,6 +2204,14 @@ public: static void operator delete(void *ptr,size_t size) { TRASH_FREE(ptr, size); } static void operator delete(void *ptr, MEM_ROOT *mem_root) { /* Never called */ } virtual ~TABLE_READ_PLAN() {} /* Remove gcc warning */ + /** + Add basic info for this TABLE_READ_PLAN to the optimizer trace. + + @param param Parameters for range analysis of this table + @param trace_object The optimizer trace object the info is appended to + */ + virtual void trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const = 0; }; @@ -2232,8 +2253,34 @@ public: } DBUG_RETURN(quick); } + void trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const; }; +void TRP_RANGE::trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const +{ + DBUG_ASSERT(param->using_real_indexes); + const uint keynr_in_table = param->real_keynr[key_idx]; + + const KEY &cur_key = param->table->key_info[keynr_in_table]; + const KEY_PART_INFO *key_part = cur_key.key_part; + + trace_object->add("type", "range_scan") + .add("index", cur_key.name) + .add("rows", records); + + Json_writer_array trace_range(param->thd, "ranges"); + + // TRP_RANGE should not be created if there are no range intervals + DBUG_ASSERT(key); + + String range_info; + range_info.length(0); + range_info.set_charset(system_charset_info); + append_range_all_keyparts(&trace_range, NULL, &range_info, key, key_part); +} + /* Plan for QUICK_ROR_INTERSECT_SELECT scan. */ @@ -2251,9 +2298,12 @@ public: struct st_ror_scan_info *cpk_scan; /* Clustered PK scan, if there is one */ bool is_covering; /* TRUE if no row retrieval phase is necessary */ double index_scan_costs; /* SUM(cost(index_scan)) */ + void trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const; }; + /* Plan for QUICK_ROR_UNION_SELECT scan. QUICK_ROR_UNION_SELECT always retrieves full rows, so retrieve_full_rows @@ -2269,8 +2319,22 @@ public: MEM_ROOT *parent_alloc); TABLE_READ_PLAN **first_ror; /* array of ptrs to plans for merged scans */ TABLE_READ_PLAN **last_ror; /* end of the above array */ + void trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const; }; +void TRP_ROR_UNION::trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const +{ + THD *thd= param->thd; + trace_object->add("type", "index_roworder_union"); + Json_writer_array smth_trace(thd, "union_of"); + for (TABLE_READ_PLAN **current = first_ror; current != last_ror; current++) + { + Json_writer_object trp_info(thd); + (*current)->trace_basic_info(param, &trp_info); + } +} /* Plan for QUICK_INDEX_INTERSECT_SELECT scan. @@ -2288,9 +2352,25 @@ public: TRP_RANGE **range_scans; /* array of ptrs to plans of intersected scans */ TRP_RANGE **range_scans_end; /* end of the array */ /* keys whose scans are to be filtered by cpk conditions */ - key_map filtered_scans; + key_map filtered_scans; + void trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const; + }; +void TRP_INDEX_INTERSECT::trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const +{ + THD *thd= param->thd; + trace_object->add("type", "index_sort_intersect"); + Json_writer_array smth_trace(thd, "index_sort_intersect_of"); + for (TRP_RANGE **current = range_scans; current != range_scans_end; + current++) + { + Json_writer_object trp_info(thd); + (*current)->trace_basic_info(param, &trp_info); + } +} /* Plan for QUICK_INDEX_MERGE_SELECT scan. @@ -2307,8 +2387,22 @@ public: MEM_ROOT *parent_alloc); TRP_RANGE **range_scans; /* array of ptrs to plans of merged scans */ TRP_RANGE **range_scans_end; /* end of the array */ + void trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const; }; +void TRP_INDEX_MERGE::trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const +{ + THD *thd= param->thd; + trace_object->add("type", "index_merge"); + Json_writer_array smth_trace(thd, "index_merge_of"); + for (TRP_RANGE **current= range_scans; current != range_scans_end; current++) + { + Json_writer_object trp_info(thd); + (*current)->trace_basic_info(param, &trp_info); + } +} /* Plan for a QUICK_GROUP_MIN_MAX_SELECT scan. @@ -2360,9 +2454,51 @@ public: QUICK_SELECT_I *make_quick(PARAM *param, bool retrieve_full_rows, MEM_ROOT *parent_alloc); void use_index_scan() { is_index_scan= TRUE; } + void trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const; }; +void TRP_GROUP_MIN_MAX::trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const +{ + THD *thd= param->thd; + trace_object->add("type", "index_group").add("index", index_info->name); + + if (min_max_arg_part) + trace_object->add("group_attribute", min_max_arg_part->field->field_name); + else + trace_object->add_null("group_attribute"); + + trace_object->add("min_aggregate", have_min) + .add("max_aggregate", have_max) + .add("distinct_aggregate", have_agg_distinct) + .add("rows", records) + .add("cost", read_cost); + + const KEY_PART_INFO *key_part = index_info->key_part; + { + Json_writer_array trace_keyparts(thd, "key_parts_used_for_access"); + for (uint partno = 0; partno < used_key_parts; partno++) + { + const KEY_PART_INFO *cur_key_part = key_part + partno; + trace_keyparts.add(cur_key_part->field->field_name); + } + } + + Json_writer_array trace_range(thd, "ranges"); + + // can have group quick without ranges + if (index_tree) + { + String range_info; + range_info.set_charset(system_charset_info); + append_range_all_keyparts(&trace_range, NULL, &range_info, index_tree, + key_part); + } +} + + typedef struct st_index_scan_info { uint idx; /* # of used key in param->keys */ @@ -2538,6 +2674,17 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, DBUG_PRINT("info",("Time to scan table: %g", read_time)); + Json_writer_object table_records(thd); + if (head->reginfo.join_tab) + table_records.add_table_name(head->reginfo.join_tab); + else + table_records.add_table_name(head); + Json_writer_object trace_range(thd, "range_analysis"); + { + Json_writer_object table_rec(thd, "table_scan"); + table_rec.add("rows", records).add("cost", read_time); + } + keys_to_use.intersect(head->keys_in_use_for_query); if (!keys_to_use.is_clear_all()) { @@ -2591,19 +2738,33 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, */ key_info= head->key_info; uint max_key_len= 0; + + Json_writer_array trace_idx(thd, "potential_range_indexes"); + for (idx=0 ; idx < head->s->keys ; idx++, key_info++) { + Json_writer_object trace_idx_details(thd); + trace_idx_details.add("index", key_info->name); KEY_PART_INFO *key_part_info; uint n_key_parts= head->actual_n_key_parts(key_info); if (!keys_to_use.is_set(idx)) - continue; + { + trace_idx_details.add("usable", false) + .add("cause", "not applicable"); + continue; + } if (key_info->flags & HA_FULLTEXT) - continue; // ToDo: ft-keys in non-ft ranges, if possible SerG + { + trace_idx_details.add("usable", false).add("cause", "fulltext"); + continue; // ToDo: ft-keys in non-ft ranges, if possible SerG + } + trace_idx_details.add("usable", true); param.key[param.keys]=key_parts; key_part_info= key_info->key_part; uint cur_key_len= 0; + Json_writer_array trace_keypart(thd, "key_parts"); for (uint part= 0 ; part < n_key_parts ; part++, key_parts++, key_part_info++) { @@ -2618,11 +2779,14 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, (key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW; /* Only HA_PART_KEY_SEG is used */ key_parts->flag= (uint8) key_part_info->key_part_flag; + trace_keypart.add(key_parts->field->field_name); } param.real_keynr[param.keys++]=idx; if (cur_key_len > max_key_len) max_key_len= cur_key_len; } + trace_idx.end(); + param.key_parts_end=key_parts; param.alloced_sel_args= 0; @@ -2644,8 +2808,18 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, (double) records / TIME_FOR_COMPARE; DBUG_PRINT("info", ("'all'+'using index' scan will be using key %d, " "read time %g", key_for_use, key_read_time)); + + Json_writer_object trace_cov(thd, "best_covering_index_scan"); + bool chosen= FALSE; if (key_read_time < read_time) + { read_time= key_read_time; + chosen= TRUE; + } + trace_cov.add("index", head->key_info[key_for_use].name) + .add("cost", key_read_time).add("chosen", chosen); + if (!chosen) + trace_cov.add("cause", "cost"); } TABLE_READ_PLAN *best_trp= NULL; @@ -2654,12 +2828,18 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, if (cond) { - if ((tree= cond->get_mm_tree(¶m, &cond))) + { + Json_writer_array trace_range_summary(thd, + "setup_range_conditions"); + tree= cond->get_mm_tree(¶m, &cond); + } + if (tree) { if (tree->type == SEL_TREE::IMPOSSIBLE) { records=0L; /* Return -1 from this function. */ read_time= (double) HA_POS_ERROR; + trace_range.add("impossible_range", true); goto free_mem; } /* @@ -2667,7 +2847,10 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, can construct a group-min-max quick select */ if (tree->type != SEL_TREE::KEY && tree->type != SEL_TREE::KEY_SMALLER) + { + trace_range.add("range_scan_possible", false); tree= NULL; + } } } @@ -2680,11 +2863,19 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, { param.table->quick_condition_rows= MY_MIN(group_trp->records, head->stat_records()); + Json_writer_object grp_summary(thd, "best_group_range_summary"); + + if (unlikely(thd->trace_started())) + group_trp->trace_basic_info(¶m, &grp_summary); + if (group_trp->read_cost < best_read_time) { + grp_summary.add("chosen", true); best_trp= group_trp; best_read_time= best_trp->read_cost; } + else + grp_summary.add("chosen", false).add("cause", "cost"); } if (tree) @@ -2697,7 +2888,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, TRP_ROR_INTERSECT *rori_trp; TRP_INDEX_INTERSECT *intersect_trp; bool can_build_covering= FALSE; - + Json_writer_object trace_range(thd, "analyzing_range_alternatives"); + remove_nonrange_trees(¶m, tree); /* Get best 'range' plan and prepare data for making other plans */ @@ -2764,6 +2956,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, DBUG_PRINT("info",("No range reads possible," " trying to construct index_merge")); List_iterator_fast<SEL_IMERGE> it(tree->merges); + Json_writer_array trace_idx_merge(thd, "analyzing_index_merge_union"); while ((imerge= it++)) { new_conj_trp= get_best_disjunct_quick(¶m, imerge, best_read_time); @@ -2798,6 +2991,19 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, possible_keys= param.possible_keys; free_mem: + if (unlikely(quick && best_trp && thd->trace_started())) + { + Json_writer_object trace_range_summary(thd, + "chosen_range_access_summary"); + { + Json_writer_object trace_range_plan(thd, "range_access_plan"); + best_trp->trace_basic_info(¶m, &trace_range_plan); + } + trace_range_summary.add("rows_for_plan", quick->records) + .add("cost_for_plan", quick->read_time) + .add("chosen", true); + } + free_root(&alloc,MYF(0)); // Return memory & allocator thd->mem_root= param.old_root; thd->no_errors=0; @@ -3060,6 +3266,9 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) estimate sources. */ + Json_writer_object trace_wrapper(thd); + Json_writer_array selectivity_for_indexes(thd, "selectivity_for_indexes"); + for (keynr= 0; keynr < table->s->keys; keynr++) { if (table->quick_keys.is_set(keynr)) @@ -3109,6 +3318,10 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) not yet been accounted for. */ table->cond_selectivity*= quick_cond_selectivity; + Json_writer_object selectivity_for_index(thd); + selectivity_for_index.add("index_name", key_info->name) + .add("selectivity_from_index", + quick_cond_selectivity); if (i != used_key_parts) { /* @@ -3128,7 +3341,9 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) */ selectivity_mult= ((double)(i+1)) / i; } - table->cond_selectivity*= selectivity_mult; + table->cond_selectivity*= selectivity_mult; + selectivity_for_index.add("selectivity_multiplier", + selectivity_mult); } /* We need to set selectivity for fields supported by indexes. @@ -3149,12 +3364,14 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) } } } + selectivity_for_indexes.end(); /* Second step: calculate the selectivity of the range conditions not supported by any index and selectivity of the range condition over the fields whose selectivity has not been set yet. */ + Json_writer_array selectivity_for_columns(thd, "selectivity_for_columns"); if (thd->variables.optimizer_use_condition_selectivity > 2 && !bitmap_is_clear_all(used_fields) && @@ -3214,17 +3431,25 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) SEL_ARG *key= tree->keys[idx]; if (key) { + Json_writer_object selectivity_for_column(thd); + selectivity_for_column.add("column_name", key->field->field_name); if (key->type == SEL_ARG::IMPOSSIBLE) - { + { rows= 0; table->reginfo.impossible_range= 1; + selectivity_for_column.add("selectivity_from_histograms", rows); + selectivity_for_column.add("cause", "impossible range"); goto free_alloc; } else { rows= records_in_column_ranges(¶m, idx, key); if (rows != DBL_MAX) + { key->field->cond_selectivity= rows/table_records; + selectivity_for_column.add("selectivity_from_histograms", + key->field->cond_selectivity); + } } } } @@ -4791,6 +5016,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, ha_rows roru_total_records; double roru_intersect_part= 1.0; size_t n_child_scans; + THD *thd= param->thd; DBUG_ENTER("get_best_disjunct_quick"); DBUG_PRINT("info", ("Full table scan cost: %g", read_time)); @@ -4816,6 +5042,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, sizeof(TRP_RANGE*)* n_child_scans))) DBUG_RETURN(NULL); + Json_writer_object trace_best_disjunct(thd); + Json_writer_array to_merge(thd, "indexes_to_merge"); /* Collect best 'range' scan for each of disjuncts, and, while doing so, analyze possibility of ROR scans. Also calculate some values needed by @@ -4827,6 +5055,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, { DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map, "tree in SEL_IMERGE");); + Json_writer_object trace_idx(thd); if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time))) { /* @@ -4838,8 +5067,11 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, imerge_too_expensive= TRUE; } if (imerge_too_expensive) + { + trace_idx.add("chosen", false).add("cause", "cost"); continue; - + } + const uint keynr_in_table = param->real_keynr[(*cur_child)->key_idx]; imerge_cost += (*cur_child)->read_cost; all_scans_ror_able &= ((*ptree)->n_ror_scans > 0); all_scans_rors &= (*cur_child)->is_ror; @@ -4852,9 +5084,16 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, } else non_cpk_scan_records += (*cur_child)->records; + trace_idx.add("index_to_merge", + param->table->key_info[keynr_in_table].name) + .add("cumulated_cost", imerge_cost); } + to_merge.end(); + DBUG_PRINT("info", ("index_merge scans cost %g", imerge_cost)); + trace_best_disjunct.add("cost_of_reading_ranges", imerge_cost); + if (imerge_too_expensive || (imerge_cost > read_time) || ((non_cpk_scan_records+cpk_scan_records >= param->table->stat_records()) && @@ -4866,6 +5105,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, */ DBUG_PRINT("info", ("Sum of index_merge scans is more expensive than " "full table scan, bailing out")); + trace_best_disjunct.add("chosen", false).add("cause", "cost"); DBUG_RETURN(NULL); } @@ -4878,6 +5118,9 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_UNION)) { roru_read_plans= (TABLE_READ_PLAN**)range_scans; + trace_best_disjunct.add("use_roworder_union", true) + .add("cause", + "always cheaper than non roworder retrieval"); goto skip_to_ror_scan; } @@ -4887,16 +5130,26 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, Add one ROWID comparison for each row retrieved on non-CPK scan. (it is done in QUICK_RANGE_SELECT::row_in_ranges) */ - imerge_cost += non_cpk_scan_records / TIME_FOR_COMPARE_ROWID; + double rid_comp_cost= static_cast<double>(non_cpk_scan_records) / + TIME_FOR_COMPARE_ROWID; + imerge_cost += rid_comp_cost; + trace_best_disjunct.add("cost_of_mapping_rowid_in_non_clustered_pk_scan", + rid_comp_cost); } /* Calculate cost(rowid_to_row_scan) */ - imerge_cost += get_sweep_read_cost(param, non_cpk_scan_records); + { + double sweep_cost= get_sweep_read_cost(param, non_cpk_scan_records); + imerge_cost += sweep_cost; + trace_best_disjunct.add("cost_sort_rowid_and_read_disk", sweep_cost); + } DBUG_PRINT("info",("index_merge cost with rowid-to-row scan: %g", imerge_cost)); if (imerge_cost > read_time || !optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION)) { + trace_best_disjunct.add("use_roworder_index_merge", true); + trace_best_disjunct.add("cause", "cost"); goto build_ror_index_merge; } @@ -4913,12 +5166,18 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, param->imerge_cost_buff_size= unique_calc_buff_size; } - imerge_cost += - Unique::get_use_cost(param->imerge_cost_buff, (uint)non_cpk_scan_records, - param->table->file->ref_length, - (size_t)param->thd->variables.sortbuff_size, - TIME_FOR_COMPARE_ROWID, - FALSE, NULL); + { + const double dup_removal_cost = Unique::get_use_cost( + param->imerge_cost_buff, (uint)non_cpk_scan_records, + param->table->file->ref_length, + (size_t)param->thd->variables.sortbuff_size, + TIME_FOR_COMPARE_ROWID, + FALSE, NULL); + imerge_cost+= dup_removal_cost; + trace_best_disjunct.add("cost_duplicate_removal", dup_removal_cost) + .add("total_cost", imerge_cost); + } + DBUG_PRINT("info",("index_merge total cost: %g (wanted: less then %g)", imerge_cost, read_time)); if (imerge_cost < read_time) @@ -4961,11 +5220,16 @@ skip_to_ror_scan: roru_total_records= 0; cur_roru_plan= roru_read_plans; + Json_writer_array trace_analyze_ror(thd, "analyzing_roworder_scans"); + /* Find 'best' ROR scan for each of trees in disjunction */ for (ptree= imerge->trees, cur_child= range_scans; ptree != imerge->trees_next; ptree++, cur_child++, cur_roru_plan++) { + Json_writer_object trp_info(thd); + if (unlikely(thd->trace_started())) + (*cur_child)->trace_basic_info(param, &trp_info); /* Assume the best ROR scan is the one that has cheapest full-row-retrieval scan cost. @@ -5001,7 +5265,7 @@ skip_to_ror_scan: roru_intersect_part *= (*cur_roru_plan)->records / param->table->stat_records(); } - + trace_analyze_ror.end(); /* rows to retrieve= SUM(rows_in_scan_i) - table_rows * PROD(rows_in_scan_i / table_rows). @@ -5027,11 +5291,14 @@ skip_to_ror_scan: DBUG_PRINT("info", ("ROR-union: cost %g, %zu members", roru_total_cost, n_child_scans)); + trace_best_disjunct.add("index_roworder_union_cost", roru_total_cost) + .add("members", n_child_scans); TRP_ROR_UNION* roru; if (roru_total_cost < read_time) { if ((roru= new (param->mem_root) TRP_ROR_UNION)) { + trace_best_disjunct.add("chosen", true); roru->first_ror= roru_read_plans; roru->last_ror= roru_read_plans + n_child_scans; roru->read_cost= roru_total_cost; @@ -5039,7 +5306,9 @@ skip_to_ror_scan: DBUG_RETURN(roru); } } - DBUG_RETURN(imerge_trp); + else + trace_best_disjunct.add("chosen", false); + DBUG_RETURN(imerge_trp); } @@ -5300,6 +5569,15 @@ ha_rows get_table_cardinality_for_index_intersect(TABLE *table) } } +static +void print_keyparts(THD *thd, KEY *key, uint key_parts) +{ + KEY_PART_INFO *part= key->key_part; + Json_writer_array keyparts= Json_writer_array(thd, "keyparts"); + for(uint i= 0; i < key_parts; i++, part++) + keyparts.add(part->field->field_name); +} + static ha_rows records_in_index_intersect_extension(PARTIAL_INDEX_INTERSECT_INFO *curr, @@ -5352,8 +5630,9 @@ bool prepare_search_best_index_intersect(PARAM *param, INDEX_SCAN_INFO *cpk_scan= NULL; TABLE *table= param->table; uint n_index_scans= (uint)(tree->index_scans_end - tree->index_scans); + THD *thd= param->thd; - if (!n_index_scans) + if (n_index_scans <= 1) return 1; bzero(init, sizeof(*init)); @@ -5370,9 +5649,6 @@ bool prepare_search_best_index_intersect(PARAM *param, common->table_cardinality= get_table_cardinality_for_index_intersect(table); - if (n_index_scans <= 1) - return TRUE; - if (table->file->primary_key_is_clustered()) { INDEX_SCAN_INFO **index_scan_end; @@ -5397,23 +5673,38 @@ bool prepare_search_best_index_intersect(PARAM *param, bzero(common->search_scans, sizeof(INDEX_SCAN_INFO *) * i); INDEX_SCAN_INFO **selected_index_scans= common->search_scans; - + Json_writer_array potential_idx_scans(thd, "potential_index_scans"); for (i=0, index_scan= tree->index_scans; i < n_index_scans; i++, index_scan++) { + Json_writer_object idx_scan(thd); uint used_key_parts= (*index_scan)->used_key_parts; KEY *key_info= (*index_scan)->key_info; + idx_scan.add("index", key_info->name); if (*index_scan == cpk_scan) + { + idx_scan.add("chosen", "false") + .add("cause", "clustered index used for filtering"); continue; + } if (cpk_scan && cpk_scan->used_key_parts >= used_key_parts && same_index_prefix(cpk_scan->key_info, key_info, used_key_parts)) + { + idx_scan.add("chosen", "false") + .add("cause", "clustered index used for filtering"); continue; + } cost= table->file->keyread_time((*index_scan)->keynr, (*index_scan)->range_count, (*index_scan)->records); + idx_scan.add("cost", cost); if (cost >= cutoff_cost) + { + idx_scan.add("chosen", false); + idx_scan.add("cause", "cost"); continue; + } for (scan_ptr= selected_index_scans; *scan_ptr ; scan_ptr++) { @@ -5430,10 +5721,20 @@ bool prepare_search_best_index_intersect(PARAM *param, } if (!*scan_ptr || cost < (*scan_ptr)->index_read_cost) { + idx_scan.add("chosen", true); + if (!*scan_ptr) + idx_scan.add("cause", "first occurence of index prefix"); + else + idx_scan.add("cause", "better cost for same idx prefix"); *scan_ptr= *index_scan; (*scan_ptr)->index_read_cost= cost; } - } + else + { + idx_scan.add("chosen", false).add("cause", "cost"); + } + } + potential_idx_scans.end(); ha_rows records_in_scans= 0; @@ -5443,6 +5744,7 @@ bool prepare_search_best_index_intersect(PARAM *param, return TRUE; records_in_scans+= (*scan_ptr)->records; } + n_search_scans= i; if (cpk_scan && create_fields_bitmap(param, &cpk_scan->used_fields)) @@ -5472,6 +5774,7 @@ bool prepare_search_best_index_intersect(PARAM *param, my_qsort(selected_index_scans, n_search_scans, sizeof(INDEX_SCAN_INFO *), (qsort_cmp) cmp_intersect_index_scan); + Json_writer_array selected_idx_scans(thd, "selected_index_scans"); if (cpk_scan) { PARTIAL_INDEX_INTERSECT_INFO curr; @@ -5484,16 +5787,36 @@ bool prepare_search_best_index_intersect(PARAM *param, curr.length= 1; for (scan_ptr=selected_index_scans; *scan_ptr; scan_ptr++) { + KEY *key_info= (*scan_ptr)->key_info; ha_rows scan_records= (*scan_ptr)->records; ha_rows records= records_in_index_intersect_extension(&curr, *scan_ptr); (*scan_ptr)->filtered_out= records >= scan_records ? - 0 : scan_records-records; + 0 : scan_records-records; + if (thd->trace_started()) + { + Json_writer_object selected_idx(thd); + selected_idx.add("index", key_info->name); + print_keyparts(thd, key_info, (*scan_ptr)->used_key_parts); + selected_idx.add("records", (*scan_ptr)->records) + .add("filtered_records", (*scan_ptr)->filtered_out); + } } } else { for (scan_ptr=selected_index_scans; *scan_ptr; scan_ptr++) + { + KEY *key_info= (*scan_ptr)->key_info; (*scan_ptr)->filtered_out= 0; + if (thd->trace_started()) + { + Json_writer_object selected_idx(thd); + selected_idx.add("index", key_info->name); + print_keyparts(thd, key_info, (*scan_ptr)->used_key_parts); + selected_idx.add("records", (*scan_ptr)->records) + .add("filtered_records", (*scan_ptr)->filtered_out); + } + } } return FALSE; @@ -5950,10 +6273,12 @@ TRP_INDEX_INTERSECT *get_best_index_intersect(PARAM *param, SEL_TREE *tree, PARTIAL_INDEX_INTERSECT_INFO init; TRP_INDEX_INTERSECT *intersect_trp= NULL; TABLE *table= param->table; - + THD *thd= param->thd; DBUG_ENTER("get_best_index_intersect"); + Json_writer_object trace_idx_interect(thd, "analyzing_sort_intersect"); + if (prepare_search_best_index_intersect(param, tree, &common, &init, read_time)) DBUG_RETURN(NULL); @@ -6015,11 +6340,15 @@ TRP_INDEX_INTERSECT *get_best_index_intersect(PARAM *param, SEL_TREE *tree, if ((intersect_trp= new (param->mem_root)TRP_INDEX_INTERSECT)) { + intersect_trp->read_cost= common.best_cost; intersect_trp->records= common.best_records; intersect_trp->range_scans= range_scans; intersect_trp->range_scans_end= cur_range; intersect_trp->filtered_scans= common.filtered_scans; + trace_idx_interect.add("rows", intersect_trp->records) + .add("cost", intersect_trp->read_cost) + .add("chosen",true); } DBUG_RETURN(intersect_trp); } @@ -6029,6 +6358,46 @@ typedef struct st_ror_scan_info : INDEX_SCAN_INFO { } ROR_SCAN_INFO; +void TRP_ROR_INTERSECT::trace_basic_info(const PARAM *param, + Json_writer_object *trace_object) const +{ + THD *thd= param->thd; + trace_object->add("type", "index_roworder_intersect"); + trace_object->add("rows", records); + trace_object->add("cost", read_cost); + trace_object->add("covering", is_covering); + trace_object->add("clustered_pk_scan", cpk_scan != NULL); + + Json_writer_array smth_trace(thd, "intersect_of"); + for (ROR_SCAN_INFO **cur_scan = first_scan; cur_scan != last_scan; + cur_scan++) + { + const KEY &cur_key = param->table->key_info[(*cur_scan)->keynr]; + const KEY_PART_INFO *key_part = cur_key.key_part; + + Json_writer_object trace_isect_idx(thd); + trace_isect_idx.add("type", "range_scan"); + trace_isect_idx.add("index", cur_key.name); + trace_isect_idx.add("rows", (*cur_scan)->records); + + Json_writer_array trace_range(thd, "ranges"); + for (const SEL_ARG *current = (*cur_scan)->sel_arg->first(); current; + current = current->next) + { + String range_info; + range_info.set_charset(system_charset_info); + for (const SEL_ARG *part = current; part; + part = part->next_key_part ? part->next_key_part : nullptr) + { + const KEY_PART_INFO *cur_key_part = key_part + part->part; + append_range(&range_info, cur_key_part, part->min_value, + part->max_value, part->min_flag | part->max_flag); + } + trace_range.add(range_info.ptr(), range_info.length()); + } + } +} + /* Create ROR_SCAN_INFO* structure with a single ROR scan on index idx using @@ -6414,7 +6783,9 @@ static double ror_scan_selectivity(const ROR_INTERSECT_INFO *info, */ static bool ror_intersect_add(ROR_INTERSECT_INFO *info, - ROR_SCAN_INFO* ror_scan, bool is_cpk_scan) + ROR_SCAN_INFO* ror_scan, + Json_writer_object *trace_costs, + bool is_cpk_scan) { double selectivity_mult= 1.0; @@ -6441,13 +6812,16 @@ static bool ror_intersect_add(ROR_INTERSECT_INFO *info, each record of every scan. Assuming 1/TIME_FOR_COMPARE_ROWID per check this gives us: */ - info->index_scan_costs += rows2double(info->index_records) / + const double idx_cost= rows2double(info->index_records) / TIME_FOR_COMPARE_ROWID; + info->index_scan_costs += idx_cost; + trace_costs->add("index_scan_cost", idx_cost); } else { info->index_records += info->param->quick_rows[ror_scan->keynr]; info->index_scan_costs += ror_scan->index_read_cost; + trace_costs->add("index_scan_cost", ror_scan->index_read_cost); bitmap_union(&info->covered_fields, &ror_scan->covered_fields); if (!info->is_covering && bitmap_is_subset(&info->param->needed_fields, &info->covered_fields)) @@ -6458,13 +6832,19 @@ static bool ror_intersect_add(ROR_INTERSECT_INFO *info, } info->total_cost= info->index_scan_costs; + trace_costs->add("cumulateed_index_scan_cost", info->index_scan_costs); DBUG_PRINT("info", ("info->total_cost: %g", info->total_cost)); if (!info->is_covering) { - info->total_cost += - get_sweep_read_cost(info->param, double2rows(info->out_rows)); + double sweep_cost= get_sweep_read_cost(info->param, + double2rows(info->out_rows)); + info->total_cost += sweep_cost; + trace_costs->add("disk_sweep_cost", sweep_cost); DBUG_PRINT("info", ("info->total_cost= %g", info->total_cost)); } + else + trace_costs->add("disk_sweep_cost", static_cast<longlong>(0)); + DBUG_PRINT("info", ("New out_rows: %g", info->out_rows)); DBUG_PRINT("info", ("New cost: %g, %scovering", info->total_cost, info->is_covering?"" : "non-")); @@ -6544,10 +6924,16 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree, uint idx; double min_cost= DBL_MAX; DBUG_ENTER("get_best_ror_intersect"); + THD *thd= param->thd; + Json_writer_object trace_ror(thd, "analyzing_roworder_intersect"); if ((tree->n_ror_scans < 2) || !param->table->stat_records() || !optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT)) - DBUG_RETURN(NULL); + { + if (tree->n_ror_scans < 2) + trace_ror.add("cause", "too few roworder scans"); + DBUG_RETURN(NULL); + } /* Step1: Collect ROR-able SEL_ARGs and create ROR_SCAN_INFO for each of @@ -6622,15 +7008,27 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree, ROR_SCAN_INFO **intersect_scans_best; cur_ror_scan= tree->ror_scans; intersect_scans_best= intersect_scans; + Json_writer_array trace_isect_idx(thd, "intersecting_indexes"); while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering) { + Json_writer_object trace_idx(thd); + trace_idx.add("index", + param->table->key_info[(*cur_ror_scan)->keynr].name); + /* S= S + first(R); R= R - first(R); */ - if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE)) + if (!ror_intersect_add(intersect, *cur_ror_scan, &trace_idx, FALSE)) { + trace_idx.add("usable", false) + .add("cause", "does not reduce cost of intersect"); cur_ror_scan++; continue; } + trace_idx.add("cumulative_total_cost", intersect->total_cost) + .add("usable", true) + .add("matching_rows_now", intersect->out_rows) + .add("intersect_covering_with_this_index", intersect->is_covering); + *(intersect_scans_end++)= *(cur_ror_scan++); if (intersect->total_cost < min_cost) @@ -6639,12 +7037,21 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree, ror_intersect_cpy(intersect_best, intersect); intersect_scans_best= intersect_scans_end; min_cost = intersect->total_cost; + trace_idx.add("chosen", true); + } + else + { + trace_idx.add("chosen", false) + .add("cause", "does not reduce cost"); } } + trace_isect_idx.end(); if (intersect_scans_best == intersect_scans) { DBUG_PRINT("info", ("None of scans increase selectivity")); + trace_ror.add("chosen", false) + .add("cause","does not increase selectivity"); DBUG_RETURN(NULL); } @@ -6662,16 +7069,31 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree, Check if we should add a CPK scan. If the obtained ROR-intersection is covering, it doesn't make sense to add CPK scan. */ + Json_writer_object trace_cpk(thd, "clustered_pk"); if (cpk_scan && !intersect->is_covering) { - if (ror_intersect_add(intersect, cpk_scan, TRUE) && + if (ror_intersect_add(intersect, cpk_scan, &trace_cpk, TRUE) && (intersect->total_cost < min_cost)) + { + trace_cpk.add("clustered_pk_scan_added_to_intersect", true) + .add("cumulated_cost", intersect->total_cost); intersect_best= intersect; //just set pointer here + } else + { + trace_cpk.add("clustered_pk_added_to_intersect", false) + .add("cause", "cost"); cpk_scan= 0; // Don't use cpk_scan + } } else + { + trace_cpk.add("clustered_pk_added_to_intersect", false) + .add("cause", cpk_scan ? "roworder is covering" + : "no clustered pk index"); cpk_scan= 0; // Don't use cpk_scan + } + trace_cpk.end(); /* Ok, return ROR-intersect plan if we have found one */ TRP_ROR_INTERSECT *trp= NULL; @@ -6698,6 +7120,17 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree, DBUG_PRINT("info", ("Returning non-covering ROR-intersect plan:" "cost %g, records %lu", trp->read_cost, (ulong) trp->records)); + trace_ror.add("rows", trp->records) + .add("cost", trp->read_cost) + .add("covering", trp->is_covering) + .add("chosen", true); + } + else + { + trace_ror.add("chosen", false) + .add("cause", (read_time > min_cost) + ? "too few indexes to merge" + : "cost"); } DBUG_RETURN(trp); } @@ -6885,6 +7318,7 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, UNINIT_VAR(best_buf_size); /* protected by key_to_read */ TRP_RANGE* read_plan= NULL; DBUG_ENTER("get_key_scans_params"); + THD *thd= param->thd; /* Note that there may be trees that have type SEL_TREE::KEY but contain no key reads at all, e.g. tree for expression "key1 is not null" where key1 @@ -6892,6 +7326,8 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, */ DBUG_EXECUTE("info", print_sel_tree(param, tree, &tree->keys_map, "tree scans");); + Json_writer_array range_scan_alt(thd, "range_scan_alternatives"); + tree->ror_scans_map.clear_all(); tree->n_ror_scans= 0; tree->index_scans= 0; @@ -6920,6 +7356,9 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool read_index_only= index_read_must_be_used ? TRUE : (bool) param->table->covering_keys.is_set(keynr); + Json_writer_object trace_idx(thd); + trace_idx.add("index", param->table->key_info[keynr].name); + found_records= check_quick_select(param, idx, read_index_only, key, update_tbl_stats, &mrr_flags, &buf_size, &cost); @@ -6928,6 +7367,14 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, (index_scan= (INDEX_SCAN_INFO *)alloc_root(param->mem_root, sizeof(INDEX_SCAN_INFO)))) { + Json_writer_array trace_range(thd, "ranges"); + + const KEY &cur_key = param->table->key_info[keynr]; + const KEY_PART_INFO *key_part = cur_key.key_part; + + String range_info; + range_info.set_charset(system_charset_info); + index_scan->idx= idx; index_scan->keynr= keynr; index_scan->key_info= ¶m->table->key_info[keynr]; @@ -6936,6 +7383,17 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, index_scan->records= found_records; index_scan->sel_arg= key; *tree->index_scans_end++= index_scan; + + if (unlikely(thd->trace_started())) + append_range_all_keyparts(&trace_range, NULL, &range_info, key, + key_part); + trace_range.end(); + + trace_idx.add("rowid_ordered", param->is_ror_scan) + .add("using_mrr", !(mrr_flags & HA_MRR_USE_DEFAULT_IMPL)) + .add("index_only", read_index_only) + .add("rows", found_records) + .add("cost", cost.total_cost()); } if ((found_records != HA_POS_ERROR) && param->is_ror_scan) { @@ -6951,6 +7409,18 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, best_idx= idx; best_mrr_flags= mrr_flags; best_buf_size= buf_size; + trace_idx.add("chosen", true); + } + else + { + trace_idx.add("chosen", false); + if (found_records == HA_POS_ERROR) + if (key->type == SEL_ARG::Type::MAYBE_KEY) + trace_idx.add("cause", "depends on unread values"); + else + trace_idx.add("cause", "unknown"); + else + trace_idx.add("cause", "cost"); } } } @@ -7055,10 +7525,11 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_quick(PARAM *param, "creating ROR-intersect", first_scan, last_scan);); alloc= parent_alloc? parent_alloc: &quick_intrsect->alloc; - for (; first_scan != last_scan;++first_scan) + for (ROR_SCAN_INFO **curr_scan= first_scan; curr_scan != last_scan; + ++curr_scan) { - if (!(quick= get_quick_select(param, (*first_scan)->idx, - (*first_scan)->sel_arg, + if (!(quick= get_quick_select(param, (*curr_scan)->idx, + (*curr_scan)->sel_arg, HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED, 0, alloc)) || quick_intrsect->push_quick_back(alloc, quick)) @@ -12584,16 +13055,27 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) DBUG_ENTER("get_best_group_min_max"); + Json_writer_object trace_group(thd, "group_index_range"); + const char* cause= NULL; + /* Perform few 'cheap' tests whether this access method is applicable. */ - if (!join) - DBUG_RETURN(NULL); /* This is not a select statement. */ - if ((join->table_count != 1) || /* The query must reference one table. */ - (join->select_lex->olap == ROLLUP_TYPE)) /* Check (B3) for ROLLUP */ + if (!join) /* This is not a select statement. */ + cause= "no join"; + else if (join->table_count != 1) /* The query must reference one table. */ + cause= "not single_table"; + else if (join->select_lex->olap == ROLLUP_TYPE) /* Check (B3) for ROLLUP */ + cause= "rollup"; + else if (table->s->keys == 0) /* There are no indexes to use. */ + cause= "no index"; + else if (join->conds && join->conds->used_tables() + & OUTER_REF_TABLE_BIT) /* Cannot execute with correlated conditions. */ + cause= "correlated conditions"; + + if (cause) + { + trace_group.add("chosen", false).add("cause", cause); DBUG_RETURN(NULL); - if (table->s->keys == 0) /* There are no indexes to use. */ - DBUG_RETURN(NULL); - if (join->conds && join->conds->used_tables() & OUTER_REF_TABLE_BIT) - DBUG_RETURN(NULL); /* Cannot execute with correlated conditions. */ + } /* Check (SA1,SA4) and store the only MIN/MAX argument - the C attribute.*/ List_iterator<Item> select_items_it(join->fields_list); @@ -12602,7 +13084,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) if ((!join->group_list) && /* Neither GROUP BY nor a DISTINCT query. */ (!join->select_distinct) && !is_agg_distinct) + { + trace_group.add("chosen", false).add("cause","no group by or distinct"); DBUG_RETURN(NULL); + } /* Analyze the query in more detail. */ if (join->sum_funcs[0]) @@ -12621,7 +13106,11 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) min_max_item->sum_func() == Item_sum::AVG_DISTINCT_FUNC)) continue; else + { + trace_group.add("chosen", false) + .add("cause", "not applicable aggregate function"); DBUG_RETURN(NULL); + } /* The argument of MIN/MAX. */ Item *expr= min_max_item->get_arg(0)->real_item(); @@ -12630,26 +13119,41 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) if (! min_max_arg_item) min_max_arg_item= (Item_field*) expr; else if (! min_max_arg_item->eq(expr, 1)) + { + trace_group.add("chosen", false) + .add("cause", "arguments different in min max function"); DBUG_RETURN(NULL); + } } else + { + trace_group.add("chosen", false) + .add("cause", "no field item in min max function"); DBUG_RETURN(NULL); + } } } /* Check (SA7). */ if (is_agg_distinct && (have_max || have_min)) { + trace_group.add("chosen", false) + .add("cause", "have both agg distinct and min max"); DBUG_RETURN(NULL); } /* Check (SA5). */ if (join->select_distinct) { + trace_group.add("distinct_query", true); while ((item= select_items_it++)) { if (item->real_item()->type() != Item::FIELD_ITEM) + { + trace_group.add("chosen", false) + .add("cause", "distinct field is expression"); DBUG_RETURN(NULL); + } } } @@ -12658,7 +13162,11 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) for (tmp_group= join->group_list; tmp_group; tmp_group= tmp_group->next) { if ((*tmp_group->item)->real_item()->type() != Item::FIELD_ITEM) + { + trace_group.add("chosen", false) + .add("cause", "group field is expression"); DBUG_RETURN(NULL); + } elements_in_group++; } @@ -12680,10 +13188,16 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) ha_rows cur_quick_prefix_records= 0; // We go through allowed indexes + Json_writer_array trace_indexes(thd, "potential_group_range_indexes"); + for (uint cur_param_idx= 0; cur_param_idx < param->keys ; ++cur_param_idx) { const uint cur_index= param->real_keynr[cur_param_idx]; KEY *const cur_index_info= &table->key_info[cur_index]; + + Json_writer_object trace_idx(thd); + trace_idx.add("index", cur_index_info->name); + KEY_PART_INFO *cur_part; KEY_PART_INFO *end_part; /* Last part for loops. */ /* Last index part. */ @@ -12708,7 +13222,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) */ if (!table->covering_keys.is_set(cur_index) || !table->keys_in_use_for_group_by.is_set(cur_index)) - continue; + { + cause= "not covering"; + goto next_index; + } /* This function is called on the precondition that the index is covering. @@ -12716,7 +13233,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) these are duplicates. The GROUP BY list cannot be a prefix of the index. */ if (elements_in_group > table->actual_n_key_parts(cur_index_info)) - continue; + { + cause= "group key parts greater than index key parts"; + goto next_index; + } /* Unless extended keys can be used for cur_index: @@ -12742,10 +13262,15 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) */ if (bitmap_is_set(table->read_set, cur_field->field_index) && !cur_field->part_of_key_not_clustered.is_set(cur_index)) + { + cause= "not covering"; goto next_index; // Field was not part of key + } } } + trace_idx.add("covering", true); + max_key_part= 0; used_key_parts_map.clear_all(); @@ -12776,7 +13301,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) used_key_parts_map.set_bit(max_key_part); } else + { + cause= "group attribute not prefix in index"; goto next_index; + } } } /* @@ -12805,7 +13333,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) /* not doing loose index scan for derived tables */ if (!item_field->field) + { + cause= "derived table"; goto next_index; + } /* Find the order of the key part in the index. */ key_part_nr= get_field_keypart(cur_index_info, item_field->field); @@ -12817,7 +13348,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) continue; if (key_part_nr < 1 || (!is_agg_distinct && key_part_nr > join->fields_list.elements)) + { + cause= "select attribute not prefix in index"; goto next_index; + } cur_part= cur_index_info->key_part + key_part_nr - 1; cur_group_prefix_len+= cur_part->store_length; used_key_parts_map.set_bit(key_part_nr); @@ -12842,7 +13376,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) { key_part_nr= get_field_keypart(cur_index_info, min_max_arg_item->field); if (key_part_nr <= cur_group_key_parts) + { + cause = "aggregate column not suffix in idx"; goto next_index; + } min_max_arg_part= cur_index_info->key_part + key_part_nr - 1; } @@ -12853,6 +13390,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) if (cur_index_info->flags & HA_NOSAME && cur_group_key_parts == cur_index_info->user_defined_key_parts) { + cause= "using unique index"; goto next_index; } @@ -12892,7 +13430,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) last_part, thd, cur_key_infix, &cur_key_infix_len, &first_non_infix_part)) + { + cause = "nonconst equality gap attribute"; goto next_index; + } } else if (min_max_arg_part && (min_max_arg_part - first_non_group_part > 0)) @@ -12901,6 +13442,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) There is a gap but no range tree, thus no predicates at all for the non-group keyparts. */ + cause = "no nongroup keypart predicate"; goto next_index; } else if (first_non_group_part && join->conds) @@ -12924,7 +13466,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) /* Check if cur_part is referenced in the WHERE clause. */ if (join->conds->walk(&Item::find_item_in_field_list_processor, 0, key_part_range)) + { + cause = "keypart reference from where clause"; goto next_index; + } } } @@ -12939,7 +13484,10 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) for (; cur_part != last_part; cur_part++) { if (bitmap_is_set(table->read_set, cur_part->field->field_index)) + { + cause = "keypart after infix in query"; goto next_index; + } } } @@ -12956,6 +13504,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) index_range_tree, &cur_range) || (cur_range && cur_range->type != SEL_ARG::KEY_RANGE)) { + cause = "minmax keypart in disjunctive query"; goto next_index; } } @@ -12978,6 +13527,17 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) cur_index_tree, TRUE, &mrr_flags, &mrr_bufsize, &dummy_cost); + if (unlikely(cur_index_tree && thd->trace_started())) + { + Json_writer_array trace_range(thd, "ranges"); + + const KEY_PART_INFO *key_part = cur_index_info->key_part; + + String range_info; + range_info.set_charset(system_charset_info); + append_range_all_keyparts(&trace_range, NULL, &range_info, + cur_index_tree, key_part); + } } cost_group_min_max(table, cur_index_info, cur_used_key_parts, cur_group_key_parts, tree, cur_index_tree, @@ -12988,6 +13548,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) Do not compare doubles directly because they may have different representations (64 vs. 80 bits). */ + trace_idx.add("rows", cur_records).add("cost", cur_read_cost); + if (cur_read_cost < best_read_cost - (DBL_EPSILON * cur_read_cost)) { index_info= cur_index_info; @@ -13005,8 +13567,16 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) used_key_parts= cur_used_key_parts; } - next_index:; + next_index: + if (cause) + { + trace_idx.add("usable", false).add("cause", cause); + cause = NULL; + } } + + trace_indexes.end(); + if (!index_info) /* No usable index found. */ DBUG_RETURN(NULL); @@ -13017,14 +13587,22 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) (index_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW, &has_min_max_fld, &has_other_fld)) + { + trace_group.add("usable", false) + .add("cause", "unsupported predicate on agg attribute"); DBUG_RETURN(NULL); + } /* Check (SA6) if clustered key is used */ if (is_agg_distinct && index == table->s->primary_key && table->file->primary_key_is_clustered()) + { + trace_group.add("usable", false) + .add("cause", "index is clustered"); DBUG_RETURN(NULL); + } /* The query passes all tests, so construct a new TRP object. */ read_plan= new (param->mem_root) @@ -13045,6 +13623,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) read_plan->records= best_records; if (read_time < best_read_cost && is_agg_distinct) { + trace_group.add("index_scan", true); read_plan->read_cost= 0; read_plan->use_index_scan(); } @@ -15002,7 +15581,6 @@ static void print_quick(QUICK_SELECT_I *quick, const key_map *needed_reg) DBUG_VOID_RETURN; } - void QUICK_RANGE_SELECT::dbug_dump(int indent, bool verbose) { /* purecov: begin inspected */ @@ -15130,3 +15708,174 @@ void QUICK_GROUP_MIN_MAX_SELECT::dbug_dump(int indent, bool verbose) } #endif /* !DBUG_OFF */ +static +void append_range(String *out, const KEY_PART_INFO *key_part, + const uchar *min_key, const uchar *max_key, const uint flag) +{ + if (out->length() > 0) + out->append(STRING_WITH_LEN(" AND ")); + + if (flag & GEOM_FLAG) + { + /* + The flags of GEOM ranges do not work the same way as for other + range types, so printing "col < some_geom" doesn't make sense. + Just print the column name, not operator. + */ + out->append(key_part->field->field_name); + out->append(STRING_WITH_LEN(" ")); + print_key_value(out, key_part, min_key); + return; + } + + if (!(flag & NO_MIN_RANGE)) + { + print_key_value(out, key_part, min_key); + if (flag & NEAR_MIN) + out->append(STRING_WITH_LEN(" < ")); + else + out->append(STRING_WITH_LEN(" <= ")); + } + + out->append(key_part->field->field_name); + + if (!(flag & NO_MAX_RANGE)) + { + if (flag & NEAR_MAX) + out->append(STRING_WITH_LEN(" < ")); + else + out->append(STRING_WITH_LEN(" <= ")); + print_key_value(out, key_part, max_key); + } +} + +/* + + Add ranges to the trace + For ex: + query: select * from t1 where a=2 ; + and we have an index on a , so we create a range + 2 <= a <= 2 + this is added to the trace +*/ + +static void append_range_all_keyparts(Json_writer_array *range_trace, + String *range_string, + String *range_so_far, const SEL_ARG *keypart, + const KEY_PART_INFO *key_parts) +{ + + DBUG_ASSERT(keypart); + DBUG_ASSERT(keypart && keypart != &null_element); + + // Navigate to first interval in red-black tree + const KEY_PART_INFO *cur_key_part = key_parts + keypart->part; + const SEL_ARG *keypart_range = keypart->first(); + const size_t save_range_so_far_length = range_so_far->length(); + + + while (keypart_range) + { + // Append the current range predicate to the range String + switch (keypart->type) + { + case SEL_ARG::Type::KEY_RANGE: + append_range(range_so_far, cur_key_part, keypart_range->min_value, + keypart_range->max_value, + keypart_range->min_flag | keypart_range->max_flag); + break; + case SEL_ARG::Type::MAYBE_KEY: + range_so_far->append("MAYBE_KEY"); + break; + case SEL_ARG::Type::IMPOSSIBLE: + range_so_far->append("IMPOSSIBLE"); + break; + default: + DBUG_ASSERT(false); + break; + } + + if (keypart_range->next_key_part && + keypart_range->next_key_part->part == + keypart_range->part + 1 && + keypart_range->is_singlepoint()) + { + append_range_all_keyparts(range_trace, range_string, range_so_far, + keypart_range->next_key_part, key_parts); + } + else + range_trace->add(range_so_far->c_ptr_safe(), range_so_far->length()); + keypart_range= keypart_range->next; + range_so_far->length(save_range_so_far_length); + } +} + +/** + Print a key to a string + + @param[out] out String the key is appended to + @param[in] key_part Index components description + @param[in] key Key tuple +*/ +static void print_key_value(String *out, const KEY_PART_INFO *key_part, + const uchar *key) +{ + Field *field = key_part->field; + + if (field->flags & BLOB_FLAG) { + // Byte 0 of a nullable key is the null-byte. If set, key is NULL. + if (field->real_maybe_null() && *key) + out->append(STRING_WITH_LEN("NULL")); + else + (field->type() == MYSQL_TYPE_GEOMETRY) + ? out->append(STRING_WITH_LEN("unprintable_geometry_value")) + : out->append(STRING_WITH_LEN("unprintable_blob_value")); + return; + } + + uint store_length = key_part->store_length; + + if (field->real_maybe_null()) + { + /* + Byte 0 of key is the null-byte. If set, key is NULL. + Otherwise, print the key value starting immediately after the + null-byte + */ + if (*key) { + out->append(STRING_WITH_LEN("NULL")); + return; + } + key++; // Skip null byte + store_length--; + } + + /* + Binary data cannot be converted to UTF8 which is what the + optimizer trace expects. If the column is binary, the hex + representation is printed to the trace instead. + */ + if (field->flags & BINARY_FLAG) { + out->append("0x"); + for (uint i = 0; i < store_length; i++) { + out->append(_dig_vec_lower[*(key + i) >> 4]); + out->append(_dig_vec_lower[*(key + i) & 0x0F]); + } + return; + } + + StringBuffer<128> tmp(system_charset_info); + TABLE *table = field->table; + my_bitmap_map *old_sets[2]; + + dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set); + + field->set_key_image(key, key_part->length); + if (field->type() == MYSQL_TYPE_BIT) + (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset + else + field->val_str(&tmp); // may change tmp's charset + out->append(tmp.ptr(), tmp.length(), tmp.charset()); + + dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets); +} diff --git a/sql/opt_range.h b/sql/opt_range.h index d5416988b88..7e92e1f54ee 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -566,7 +566,7 @@ public: FALSE Otherwise */ - bool is_singlepoint() + bool is_singlepoint() const { /* Check for NEAR_MIN ("strictly less") and NO_MIN_RANGE (-inf < field) diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index c4c30c9b50d..228fcd0f7e6 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -33,6 +33,7 @@ #include "opt_subselect.h" #include "sql_test.h" #include <my_bit.h> +#include "opt_trace.h" /* This file contains optimizations for semi-join subqueries. @@ -437,7 +438,7 @@ Currently, solution #2 is implemented. LEX_CSTRING weedout_key= {STRING_WITH_LEN("weedout_key")}; static -bool subquery_types_allow_materialization(Item_in_subselect *in_subs); +bool subquery_types_allow_materialization(THD *thd, Item_in_subselect *in_subs); static bool replace_where_subcondition(JOIN *, Item **, Item *, Item *, bool); static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, void *arg); @@ -521,7 +522,7 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, parent_unit->first_select()->leaf_tables.elements && // 2 child_select->outer_select() && child_select->outer_select()->leaf_tables.elements && // 2A - subquery_types_allow_materialization(in_subs) && + subquery_types_allow_materialization(thd, in_subs) && (in_subs->is_top_level_item() || //3 optimizer_flag(thd, OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE) || //3 @@ -682,7 +683,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) { DBUG_PRINT("info", ("Subquery is semi-join conversion candidate")); - (void)subquery_types_allow_materialization(in_subs); + (void)subquery_types_allow_materialization(thd, in_subs); in_subs->is_flattenable_semijoin= TRUE; @@ -696,6 +697,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join) if (arena) thd->restore_active_arena(arena, &backup); in_subs->is_registered_semijoin= TRUE; + OPT_TRACE_TRANSFORM(thd, oto0, oto1, select_lex->select_number, + "IN (SELECT)", "semijoin"); + oto1.add("chosen", true); } } else @@ -823,7 +827,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) */ static -bool subquery_types_allow_materialization(Item_in_subselect *in_subs) +bool subquery_types_allow_materialization(THD* thd, Item_in_subselect *in_subs) { DBUG_ENTER("subquery_types_allow_materialization"); @@ -831,9 +835,14 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs) List_iterator<Item> it(in_subs->unit->first_select()->item_list); uint elements= in_subs->unit->first_select()->item_list.elements; + const char* cause= NULL; in_subs->types_allow_materialization= FALSE; // Assign default values in_subs->sjm_scan_allowed= FALSE; + + OPT_TRACE_TRANSFORM(thd, oto0, oto1, + in_subs->get_select_lex()->select_number, + "IN (SELECT)", "materialization"); bool all_are_fields= TRUE; uint32 total_key_length = 0; @@ -846,7 +855,11 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs) total_key_length += inner->max_length; if (!inner->type_handler()->subquery_type_allows_materialization(inner, outer)) + { + oto1.add("possible", false); + oto1.add("cause", "types mismatch"); DBUG_RETURN(FALSE); + } } /* @@ -856,14 +869,23 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs) Make sure that the length of the key for the temp_table is atleast greater than 0. */ - if (!total_key_length || total_key_length > tmp_table_max_key_length() || - elements > tmp_table_max_key_parts()) - DBUG_RETURN(FALSE); - - in_subs->types_allow_materialization= TRUE; - in_subs->sjm_scan_allowed= all_are_fields; - DBUG_PRINT("info",("subquery_types_allow_materialization: ok, allowed")); - DBUG_RETURN(TRUE); + if (!total_key_length) + cause= "zero length key for materialized table"; + else if (total_key_length > tmp_table_max_key_length()) + cause= "length of key greater than allowed key length for materialized tables"; + else if (elements > tmp_table_max_key_parts()) + cause= "#keyparts greater than allowed key parts for materialized tables"; + else + { + in_subs->types_allow_materialization= TRUE; + in_subs->sjm_scan_allowed= all_are_fields; + oto1.add("sjm_scan_allowed", all_are_fields) + .add("possible", true); + DBUG_PRINT("info",("subquery_types_allow_materialization: ok, allowed")); + DBUG_RETURN(TRUE); + } + oto1.add("possible", false).add("cause", cause); + DBUG_RETURN(FALSE); } @@ -1213,15 +1235,30 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) /* Stop processing if we've reached a subquery that's attached to the ON clause */ if (in_subq->do_not_convert_to_sj) + { + OPT_TRACE_TRANSFORM(thd, oto0, oto1, + in_subq->get_select_lex()->select_number, + "IN (SELECT)", "semijoin"); + oto1.add("converted_to_semi_join", false) + .add("cause", "subquery attached to the ON clause"); break; + } if (in_subq->is_flattenable_semijoin) { + OPT_TRACE_TRANSFORM(thd, oto0, oto1, + in_subq->get_select_lex()->select_number, + "IN (SELECT)", "semijoin"); if (join->table_count + in_subq->unit->first_select()->join->table_count >= MAX_TABLES) + { + oto1.add("converted_to_semi_join", false); + oto1.add("cause", "table in parent join now exceeds MAX_TABLES"); break; + } if (convert_subq_to_sj(join, in_subq)) goto restore_arena_and_fail; + oto1.add("converted_to_semi_join", true); } else { @@ -2340,8 +2377,13 @@ int pull_out_semijoin_tables(JOIN *join) bool optimize_semijoin_nests(JOIN *join, table_map all_table_map) { DBUG_ENTER("optimize_semijoin_nests"); + THD *thd= join->thd; List_iterator<TABLE_LIST> sj_list_it(join->select_lex->sj_nests); TABLE_LIST *sj_nest; + Json_writer_object wrapper(thd); + Json_writer_object trace_semijoin_nest(thd, + "execution_plan_for_potential_materialization"); + Json_writer_array trace_steps_array(thd, "steps"); while ((sj_nest= sj_list_it++)) { /* semi-join nests with only constant tables are not valid */ diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc index 74d1e775c43..03516146de2 100644 --- a/sql/opt_table_elimination.cc +++ b/sql/opt_table_elimination.cc @@ -31,6 +31,8 @@ #include "mariadb.h" #include "my_bit.h" #include "sql_select.h" +#include "opt_trace.h" +#include "my_json_writer.h" /* OVERVIEW @@ -522,7 +524,8 @@ eliminate_tables_for_list(JOIN *join, List<TABLE_LIST> *join_list, table_map tables_in_list, Item *on_expr, - table_map tables_used_elsewhere); + table_map tables_used_elsewhere, + Json_writer_array* eliminate_tables); static bool check_func_dependency(JOIN *join, table_map dep_tables, @@ -541,7 +544,8 @@ static Dep_module_expr *merge_eq_mods(Dep_module_expr *start, Dep_module_expr *new_fields, Dep_module_expr *end, uint and_level); -static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl); +static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl, + Json_writer_array* eliminate_tables); static void add_module_expr(Dep_analysis_context *dac, Dep_module_expr **eq_mod, uint and_level, Dep_value_field *field_val, Item *right, @@ -608,6 +612,8 @@ void eliminate_tables(JOIN *join) if (!optimizer_flag(thd, OPTIMIZER_SWITCH_TABLE_ELIMINATION)) DBUG_VOID_RETURN; /* purecov: inspected */ + Json_writer_object trace_wrapper(thd); + /* Find the tables that are referred to from WHERE/HAVING */ used_tables= (join->conds? join->conds->used_tables() : 0) | (join->having? join->having->used_tables() : 0); @@ -663,13 +669,14 @@ void eliminate_tables(JOIN *join) } } } - + table_map all_tables= join->all_tables_map(); + Json_writer_array eliminated_tables(thd,"eliminated_tables"); if (all_tables & ~used_tables) { /* There are some tables that we probably could eliminate. Try it. */ eliminate_tables_for_list(join, join->join_list, all_tables, NULL, - used_tables); + used_tables, &eliminated_tables); } DBUG_VOID_RETURN; } @@ -712,7 +719,8 @@ void eliminate_tables(JOIN *join) static bool eliminate_tables_for_list(JOIN *join, List<TABLE_LIST> *join_list, table_map list_tables, Item *on_expr, - table_map tables_used_elsewhere) + table_map tables_used_elsewhere, + Json_writer_array *eliminate_tables) { TABLE_LIST *tbl; List_iterator<TABLE_LIST> it(*join_list); @@ -734,9 +742,9 @@ eliminate_tables_for_list(JOIN *join, List<TABLE_LIST> *join_list, &tbl->nested_join->join_list, tbl->nested_join->used_tables, tbl->on_expr, - outside_used_tables)) + outside_used_tables, eliminate_tables)) { - mark_as_eliminated(join, tbl); + mark_as_eliminated(join, tbl, eliminate_tables); } else all_eliminated= FALSE; @@ -748,7 +756,7 @@ eliminate_tables_for_list(JOIN *join, List<TABLE_LIST> *join_list, check_func_dependency(join, tbl->table->map, NULL, tbl, tbl->on_expr)) { - mark_as_eliminated(join, tbl); + mark_as_eliminated(join, tbl, eliminate_tables); } else all_eliminated= FALSE; @@ -1788,7 +1796,8 @@ Dep_module* Dep_value_field::get_next_unbound_module(Dep_analysis_context *dac, Mark one table or the whole join nest as eliminated. */ -static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl) +static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl, + Json_writer_array* eliminate_tables) { TABLE *table; /* @@ -1801,7 +1810,7 @@ static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl) TABLE_LIST *child; List_iterator<TABLE_LIST> it(tbl->nested_join->join_list); while ((child= it++)) - mark_as_eliminated(join, child); + mark_as_eliminated(join, child, eliminate_tables); } else if ((table= tbl->table)) { @@ -1812,6 +1821,7 @@ static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl) tab->type= JT_CONST; tab->table->const_table= 1; join->eliminated_tables |= table->map; + eliminate_tables->add(table->alias.c_ptr_safe()); join->const_table_map|= table->map; set_position(join, join->const_tables++, tab, (KEYUSE*)0); } diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc new file mode 100644 index 00000000000..ca05f36579a --- /dev/null +++ b/sql/opt_trace.cc @@ -0,0 +1,722 @@ +/* This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ + +#include "mariadb.h" +#include "sql_array.h" +#include "sql_string.h" +#include "sql_class.h" +#include "sql_show.h" +#include "field.h" +#include "table.h" +#include "opt_trace.h" +#include "sql_parse.h" +#include "set_var.h" +#include "my_json_writer.h" +#include "sp_head.h" + +const char I_S_table_name[] = "OPTIMIZER_TRACE"; + +/** + Whether a list of tables contains information_schema.OPTIMIZER_TRACE. + @param tbl list of tables + + Can we do better than this here?? + @note this does not catch that a stored routine or view accesses + the OPTIMIZER_TRACE table. So using a stored routine or view to read + OPTIMIZER_TRACE will overwrite OPTIMIZER_TRACE as it runs and provide + uninteresting info. +*/ +bool list_has_optimizer_trace_table(const TABLE_LIST *tbl) +{ + for (; tbl; tbl = tbl->next_global) + { + if (tbl->schema_table && + 0 == strcmp(tbl->schema_table->table_name, I_S_table_name)) + return true; + } + return false; +} + +/* + Returns if a query has a set command with optimizer_trace being switched on/off. + True: Don't trace the query(uninteresting) +*/ + +bool sets_var_optimizer_trace(enum enum_sql_command sql_command, + List<set_var_base> *set_vars) +{ + if (sql_command == SQLCOM_SET_OPTION) + { + List_iterator_fast<set_var_base> it(*set_vars); + const set_var_base *var; + while ((var = it++)) + if (var->is_var_optimizer_trace()) return true; + } + return false; +} + + +ST_FIELD_INFO optimizer_trace_info[] = { + /* name, length, type, value, maybe_null, old_name, open_method */ + {"QUERY", 65535, MYSQL_TYPE_STRING, 0, false, NULL, SKIP_OPEN_TABLE}, + {"TRACE", 65535, MYSQL_TYPE_STRING, 0, false, NULL, SKIP_OPEN_TABLE}, + {"MISSING_BYTES_BEYOND_MAX_MEM_SIZE", 20, MYSQL_TYPE_LONG, 0, false, NULL, + SKIP_OPEN_TABLE}, + {"INSUFFICIENT_PRIVILEGES", 1, MYSQL_TYPE_TINY, 0, false, NULL, + SKIP_OPEN_TABLE}, + {NULL, 0, MYSQL_TYPE_STRING, 0, true, NULL, 0}}; + +/* + TODO: one-line needs to be implemented seperately +*/ +const char *Opt_trace_context::flag_names[] = {"enabled", "default", + NullS}; + +/* + Returns if a particular command will be traced or not +*/ + +inline bool sql_command_can_be_traced(enum enum_sql_command sql_command) +{ + /* + For first iteration we are only allowing select queries. + TODO: change to allow other queries. + */ + return sql_command == SQLCOM_SELECT || + sql_command == SQLCOM_UPDATE || + sql_command == SQLCOM_DELETE || + sql_command == SQLCOM_DELETE_MULTI || + sql_command == SQLCOM_UPDATE_MULTI; +} + +void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex, + Json_writer_object *writer) + +{ + if (!thd->trace_started()) + return; + char buff[1024]; + String str(buff, sizeof(buff), system_charset_info); + str.length(0); + select_lex->print(thd, &str, + enum_query_type(QT_TO_SYSTEM_CHARSET | + QT_SHOW_SELECT_NUMBER | + QT_ITEM_IDENT_SKIP_DB_NAMES | + QT_VIEW_INTERNAL + )); + /* + The output is not very pretty lots of back-ticks, the output + is as the one in explain extended , lets try to improved it here. + */ + writer->add("expanded_query", str.c_ptr_safe(), str.length()); +} + +void opt_trace_disable_if_no_security_context_access(THD *thd) +{ + if (likely(!(thd->variables.optimizer_trace & + Opt_trace_context::FLAG_ENABLED)) || // (1) + thd->system_thread) // (2) + { + /* + (1) We know that the routine's execution starts with "enabled=off". + If it stays so until the routine ends, we needn't do security checks on + the routine. + If it does not stay so, it means the definer sets it to "on" somewhere + in the routine's body. Then it is his conscious decision to generate + traces, thus it is still correct to skip the security check. + + (2) Threads of the Events Scheduler have an unusual security context + (thd->m_main_security_ctx.priv_user==NULL, see comment in + Security_context::change_security_context()). + */ + return; + } + Opt_trace_context *const trace = &thd->opt_trace; + if (!thd->trace_started()) + { + /* + @@optimizer_trace has "enabled=on" but trace is not started. + Either Opt_trace_start ctor was not called for our statement (3), or it + was called but at that time, the variable had "enabled=off" (4). + + There are no known cases of (3). + + (4) suggests that the user managed to change the variable during + execution of the statement, and this statement is using + view/routine (note that we have not been able to provoke this, maybe + this is impossible). If it happens it is suspicious. + + We disable I_S output. And we cannot do otherwise: we have no place to + store a possible "missing privilege" information (no Opt_trace_stmt, as + is_started() is false), so cannot do security checks, so cannot safely + do tracing, so have to disable I_S output. And even then, we don't know + when to re-enable I_S output, as we have no place to store the + information "re-enable tracing at the end of this statement", and we + don't even have a notion of statement here (statements in the optimizer + trace world mean an Opt_trace_stmt object, and there is none here). So + we must disable for the session's life. + + COM_FIELD_LIST opens views, thus used to be a case of (3). To avoid + disabling I_S output for the session's life when this command is issued + (like in: "SET OPTIMIZER_TRACE='ENABLED=ON';USE somedb;" in the 'mysql' + command-line client), we have decided to create a Opt_trace_start for + this command. The command itself is not traced though + (SQLCOM_SHOW_FIELDS does not have CF_OPTIMIZER_TRACE). + */ + return; + } + /* + Note that thd->main_security_ctx.master_access is probably invariant + accross the life of THD: GRANT/REVOKE don't affect global privileges of an + existing connection, per the manual. + */ + if (!(thd->main_security_ctx.check_access(GLOBAL_ACLS & ~GRANT_ACL)) && + (0 != strcmp(thd->main_security_ctx.priv_user, + thd->security_context()->priv_user) || + 0 != my_strcasecmp(system_charset_info, + thd->main_security_ctx.priv_host, + thd->security_context()->priv_host))) + trace->missing_privilege(); + return; +} + +void opt_trace_disable_if_no_stored_proc_func_access(THD *thd, sp_head *sp) +{ + if (likely(!(thd->variables.optimizer_trace & + Opt_trace_context::FLAG_ENABLED)) || + thd->system_thread) + return; + + Opt_trace_context *const trace = &thd->opt_trace; + if (!thd->trace_started()) + return; + bool full_access; + Security_context *const backup_thd_sctx = thd->security_context(); + thd->set_security_context(&thd->main_security_ctx); + const bool rc = check_show_routine_access(thd, sp, &full_access) || !full_access; + thd->set_security_context(backup_thd_sctx); + if (rc) + trace->missing_privilege(); + return; +} + +/** + If tracing is on, checks additional privileges on a list of tables/views, + to make sure that the user has the right to do SHOW CREATE TABLE/VIEW and + "SELECT *". For that: + - this functions checks table-level SELECT + - which is sufficient for SHOW CREATE TABLE and "SELECT *", if a base table + - if a view, if the view has not been identified as such then + opt_trace_disable_if_no_view_access() will be later called and check SHOW + VIEW; other we check SHOW VIEW here; SHOW VIEW + SELECT is sufficient for + SHOW CREATE VIEW. + If a privilege is missing, notifies the trace system. + + @param thd + @param tbl list of tables to check +*/ + +void opt_trace_disable_if_no_tables_access(THD *thd, TABLE_LIST *tbl) +{ + if (likely(!(thd->variables.optimizer_trace & + Opt_trace_context::FLAG_ENABLED)) || thd->system_thread) + return; + Opt_trace_context *const trace = &thd->opt_trace; + + if (!thd->trace_started()) + return; + + Security_context *const backup_thd_sctx = thd->security_context(); + thd->set_security_context(&thd->main_security_ctx); + const TABLE_LIST *const first_not_own_table = thd->lex->first_not_own_table(); + for (TABLE_LIST *t = tbl; t != NULL && t != first_not_own_table; + t = t->next_global) + { + /* + Anonymous derived tables (as in + "SELECT ... FROM (SELECT ...)") don't have their grant.privilege set. + */ + if (!t->is_anonymous_derived_table()) + { + const GRANT_INFO backup_grant_info = t->grant; + Security_context *const backup_table_sctx = t->security_ctx; + t->security_ctx = NULL; + /* + (1) check_table_access() fills t->grant.privilege. + (2) Because SELECT privileges can be column-based, + check_table_access() will return 'false' as long as there is SELECT + privilege on one column. But we want a table-level privilege. + */ + + bool rc = + check_table_access(thd, SELECT_ACL, t, false, 1, true) || // (1) + ((t->grant.privilege & SELECT_ACL) == 0); // (2) + if (t->is_view()) + { + /* + It's a view which has already been opened: we are executing a + prepared statement. The view has been unfolded in the global list of + tables. So underlying tables will be automatically checked in the + present function, but we need an explicit check of SHOW VIEW: + */ + rc |= check_table_access(thd, SHOW_VIEW_ACL, t, false, 1, true); + } + t->security_ctx = backup_table_sctx; + t->grant = backup_grant_info; + if (rc) + { + trace->missing_privilege(); + break; + } + } + } + thd->set_security_context(backup_thd_sctx); + return; +} + +void opt_trace_disable_if_no_view_access(THD *thd, TABLE_LIST *view, + TABLE_LIST *underlying_tables) +{ + + if (likely(!(thd->variables.optimizer_trace & + Opt_trace_context::FLAG_ENABLED)) || + thd->system_thread) + return; + Opt_trace_context *const trace = &thd->opt_trace; + if (!thd->trace_started()) + return; + + Security_context *const backup_table_sctx = view->security_ctx; + Security_context *const backup_thd_sctx = thd->security_context(); + const GRANT_INFO backup_grant_info = view->grant; + + view->security_ctx = NULL; // no SUID context for view + // no SUID context for THD + thd->set_security_context(&thd->main_security_ctx); + const int rc = check_table_access(thd, SHOW_VIEW_ACL, view, false, 1, true); + + view->security_ctx = backup_table_sctx; + thd->set_security_context(backup_thd_sctx); + view->grant = backup_grant_info; + + if (rc) + { + trace->missing_privilege(); + return; + } + /* + We needn't check SELECT privilege on this view. Some + opt_trace_disable_if_no_tables_access() call has or will check it. + + Now we check underlying tables/views of our view: + */ + opt_trace_disable_if_no_tables_access(thd, underlying_tables); + return; +} + + +/** + @class Opt_trace_stmt + + The trace of one statement. +*/ + +class Opt_trace_stmt { + public: + /** + Constructor, starts a trace for information_schema and dbug. + @param ctx_arg context + */ + Opt_trace_stmt(Opt_trace_context *ctx_arg) + { + ctx= ctx_arg; + current_json= new Json_writer(); + missing_priv= false; + I_S_disabled= 0; + } + ~Opt_trace_stmt() + { + delete current_json; + missing_priv= false; + ctx= NULL; + I_S_disabled= 0; + } + void set_query(const char *query_ptr, size_t length, const CHARSET_INFO *charset); + void open_struct(const char *key, char opening_bracket); + void close_struct(const char *saved_key, char closing_bracket); + void fill_info(Opt_trace_info* info); + void add(const char *key, char *opening_bracket, size_t val_length); + Json_writer* get_current_json(){return current_json;} + void missing_privilege(); + void disable_tracing_for_children(); + void enable_tracing_for_children(); + bool is_enabled(); + + void set_allowed_mem_size(size_t mem_size); + size_t get_length() { return current_json->output.length(); } + size_t get_truncated_bytes() { return current_json->get_truncated_bytes(); } + bool get_missing_priv() { return missing_priv; } + +private: + Opt_trace_context *ctx; + String query; // store the query sent by the user + Json_writer *current_json; // stores the trace + bool missing_priv; ///< whether user lacks privilege to see this trace + uint I_S_disabled; +}; + +void Opt_trace_stmt::set_query(const char *query_ptr, size_t length, + const CHARSET_INFO *charset) +{ + query.append(query_ptr, length, charset); +} + +Json_writer* Opt_trace_context::get_current_json() +{ + if (!is_started()) + return NULL; + return current_trace->get_current_json(); +} + +void Opt_trace_context::missing_privilege() +{ + if (current_trace) + current_trace->missing_privilege(); +} + +void Opt_trace_context::set_allowed_mem_size(size_t mem_size) +{ + current_trace->set_allowed_mem_size(mem_size); +} + +/* + TODO: In future when we would be saving multiple trace, + this function would return + max_mem_size - memory_occupied_by_the_saved_traces +*/ + +size_t Opt_trace_context::remaining_mem_size() +{ + return max_mem_size; +} + +bool Opt_trace_context::disable_tracing_if_required() +{ + if (current_trace) + { + current_trace->disable_tracing_for_children(); + return true; + } + return false; +} + +bool Opt_trace_context::enable_tracing_if_required() +{ + if (current_trace) + { + current_trace->enable_tracing_for_children(); + return true; + } + return false; +} + +bool Opt_trace_context::is_enabled() +{ + if (current_trace) + return current_trace->is_enabled(); + return false; +} + +Opt_trace_context::Opt_trace_context() +{ + current_trace= NULL; + inited= FALSE; + traces= NULL; + max_mem_size= 0; +} +Opt_trace_context::~Opt_trace_context() +{ + inited= FALSE; + /* + would be nice to move this to a function + */ + if (traces) + { + while (traces->elements()) + { + Opt_trace_stmt *prev= traces->at(0); + delete prev; + traces->del(0); + } + delete traces; + traces= NULL; + } + max_mem_size= 0; +} + +void Opt_trace_context::set_query(const char *query, size_t length, const CHARSET_INFO *charset) +{ + current_trace->set_query(query, length, charset); +} + +void Opt_trace_context::start(THD *thd, TABLE_LIST *tbl, + enum enum_sql_command sql_command, + const char *query, + size_t query_length, + const CHARSET_INFO *query_charset, + ulong max_mem_size_arg) +{ + /* + This is done currently because we don't want to have multiple + traces open at the same time, so as soon as a new trace is created + we forcefully end the previous one, if it has not ended by itself. + This would mostly happen with stored functions or procedures. + + TODO: handle multiple traces + */ + DBUG_ASSERT(!current_trace); + current_trace= new Opt_trace_stmt(this); + max_mem_size= max_mem_size_arg; + if (!inited) + { + traces= new Dynamic_array<Opt_trace_stmt*>(); + inited= TRUE; + } + set_allowed_mem_size(remaining_mem_size()); +} + +void Opt_trace_context::end() +{ + if (current_trace) + traces->push(current_trace); + + if (!traces->elements()) + return; + if (traces->elements() > 1) + { + Opt_trace_stmt *prev= traces->at(0); + delete prev; + traces->del(0); + } + current_trace= NULL; +} + +Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl, + enum enum_sql_command sql_command, + List<set_var_base> *set_vars, + const char *query, + size_t query_length, + const CHARSET_INFO *query_charset):ctx(&thd->opt_trace) +{ + /* + if optimizer trace is enabled and the statment we have is traceable, + then we start the context. + */ + const ulonglong var = thd->variables.optimizer_trace; + traceable= FALSE; + if (unlikely(var & Opt_trace_context::FLAG_ENABLED) && + sql_command_can_be_traced(sql_command) && + !list_has_optimizer_trace_table(tbl) && + !sets_var_optimizer_trace(sql_command, set_vars) && + !thd->system_thread && + !ctx->disable_tracing_if_required()) + { + ctx->start(thd, tbl, sql_command, query, query_length, query_charset, + thd->variables.optimizer_trace_max_mem_size); + ctx->set_query(query, query_length, query_charset); + traceable= TRUE; + opt_trace_disable_if_no_tables_access(thd, tbl); + } +} + +Opt_trace_start::~Opt_trace_start() +{ + if (traceable) + { + ctx->end(); + traceable= FALSE; + } + else + { + ctx->enable_tracing_if_required(); + } +} + +void Opt_trace_stmt::fill_info(Opt_trace_info* info) +{ + if (unlikely(info->missing_priv = get_missing_priv())) + { + info->trace_ptr = info->query_ptr = ""; + info->trace_length = info->query_length = 0; + info->query_charset = &my_charset_bin; + info->missing_bytes = 0; + } + else + { + info->trace_ptr = current_json->output.get_string()->ptr(); + info->trace_length = get_length(); + info->query_ptr = query.ptr(); + info->query_length = query.length(); + info->query_charset = query.charset(); + info->missing_bytes = get_truncated_bytes(); + info->missing_priv= get_missing_priv(); + } +} + +void Opt_trace_stmt::missing_privilege() +{ + missing_priv= true; +} + +void Opt_trace_stmt::disable_tracing_for_children() +{ + ++I_S_disabled; +} + +void Opt_trace_stmt::enable_tracing_for_children() +{ + if (I_S_disabled) + --I_S_disabled; +} + +bool Opt_trace_stmt::is_enabled() +{ + return I_S_disabled == 0; +} + +void Opt_trace_stmt::set_allowed_mem_size(size_t mem_size) +{ + current_json->set_size_limit(mem_size); +} + +/* + Prefer this when you are iterating over JOIN_TABs +*/ + +void Json_writer::add_table_name(const JOIN_TAB *tab) +{ + if (tab != NULL) + { + char table_name_buffer[SAFE_NAME_LEN]; + if (tab->table && tab->table->derived_select_number) + { + /* Derived table name generation */ + size_t len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1, + "<derived%u>", + tab->table->derived_select_number); + add_str(table_name_buffer, len); + } + else if (tab->bush_children) + { + JOIN_TAB *ctab= tab->bush_children->start; + size_t len= my_snprintf(table_name_buffer, + sizeof(table_name_buffer)-1, + "<subquery%d>", + ctab->emb_sj_nest->sj_subq_pred->get_identifier()); + add_str(table_name_buffer, len); + } + else + { + TABLE_LIST *real_table= tab->table->pos_in_table_list; + add_str(real_table->alias.str, real_table->alias.length); + } + } + else + DBUG_ASSERT(0); +} + +void Json_writer::add_table_name(const TABLE *table) +{ + add_str(table->pos_in_table_list->alias.str); +} + + +void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab) +{ + Json_writer_object table_records(thd); + table_records.add_table_name(tab); + Json_writer_object table_rec(thd, "table_scan"); + table_rec.add("rows", tab->found_records) + .add("cost", tab->read_time); +} +/* + Introduce enum_query_type flags parameter, maybe also allow + EXPLAIN also use this function. +*/ + +void Json_writer::add_str(Item *item) +{ + if (item) + { + THD *thd= current_thd; + char buff[256]; + String str(buff, sizeof(buff), system_charset_info); + str.length(0); + + ulonglong save_option_bits= thd->variables.option_bits; + thd->variables.option_bits &= ~OPTION_QUOTE_SHOW_CREATE; + item->print(&str, + enum_query_type(QT_TO_SYSTEM_CHARSET | QT_SHOW_SELECT_NUMBER + | QT_ITEM_IDENT_SKIP_DB_NAMES)); + thd->variables.option_bits= save_option_bits; + add_str(str.c_ptr_safe()); + } + else + add_null(); +} + +void Opt_trace_context::flush_optimizer_trace() +{ + inited= false; + if (traces) + { + while (traces->elements()) + { + Opt_trace_stmt *prev= traces->at(0); + delete prev; + traces->del(0); + } + delete traces; + traces= NULL; + } +} + + +int fill_optimizer_trace_info(THD *thd, TABLE_LIST *tables, Item *) +{ + TABLE *table = tables->table; + Opt_trace_info info; + + /* get_values of trace, query , missing bytes and missing_priv + + @todo: Need an iterator here to walk over all the traces + */ + Opt_trace_context* ctx= &thd->opt_trace; + + if (thd->opt_trace.empty()) + { + Opt_trace_stmt *stmt= ctx->get_top_trace(); + stmt->fill_info(&info); + + table->field[0]->store(info.query_ptr, static_cast<uint>(info.query_length), + info.query_charset); + table->field[1]->store(info.trace_ptr, static_cast<uint>(info.trace_length), + system_charset_info); + table->field[2]->store(info.missing_bytes, true); + table->field[3]->store(info.missing_priv, true); + // Store in IS + if (schema_table_store_record(thd, table)) + return 1; + } + return 0; +} diff --git a/sql/opt_trace.h b/sql/opt_trace.h new file mode 100644 index 00000000000..0e2d0146a49 --- /dev/null +++ b/sql/opt_trace.h @@ -0,0 +1,201 @@ +#ifndef OPT_TRACE_INCLUDED +#define OPT_TRACE_INCLUDED +/* This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ + +#include "opt_trace_context.h" // Opt_trace_context +#include "sql_lex.h" +#include "my_json_writer.h" +#include "sql_select.h" +class Item; +class THD; +struct TABLE_LIST; + +class Opt_trace_stmt; + +/* + User-visible information about a trace. +*/ + +struct Opt_trace_info +{ + /** + String containing trace. + If trace has been end()ed, this is 0-terminated, which is only to aid + debugging or unit testing; this property is not relied upon in normal + server usage. + If trace has not been ended, this is not 0-terminated. That rare case can + happen when a substatement reads OPTIMIZER_TRACE (at that stage, the top + statement is still executing so its trace is not ended yet, but may still + be read by the sub-statement). + */ + const char *trace_ptr; + size_t trace_length; + //// String containing original query. + const char *query_ptr; + size_t query_length; + const CHARSET_INFO *query_charset; ///< charset of query string + /** + How many bytes this trace is missing (for traces which were truncated + because of @@@@optimizer-trace-max-mem-size). + The trace is not extended beyond trace-max-mem-size. + */ + size_t missing_bytes; + /* + Whether user lacks privilege to see this trace. + If this is set to TRUE, then we return an empty trace + */ + bool missing_priv; +}; + +/** + Instantiate this class to start tracing a THD's actions (generally at a + statement's start), and to set the "original" query (not transformed, as + sent by client) for the new trace. Destructor will end the trace. + + @param thd the THD + @param tbl list of tables read/written by the statement. + @param sql_command SQL command being prepared or executed + @param set_vars what variables are set by this command (only used if + sql_command is SQLCOM_SET_OPTION) + @param query query + @param length query's length + @param charset charset which was used to encode this query +*/ + + +class Opt_trace_start { + public: + Opt_trace_start(THD *thd_arg, TABLE_LIST *tbl, + enum enum_sql_command sql_command, + List<set_var_base> *set_vars, + const char *query, + size_t query_length, + const CHARSET_INFO *query_charset); + ~Opt_trace_start(); + + private: + Opt_trace_context *const ctx; + /* + True: the query will be traced + False: otherwise + */ + bool traceable; +}; + +/** + Prints SELECT query to optimizer trace. It is not the original query (as in + @c Opt_trace_context::set_query()) but a printout of the parse tree + (Item-s). + @param thd the THD + @param select_lex query's parse tree + @param trace_object Json_writer object to which the query will be added +*/ +void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex, + Json_writer_object *trace_object); + +void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab); + +/* + Security related (need to add a proper comment here) +*/ + +/** + If the security context is not that of the connected user, inform the trace + system that a privilege is missing. With one exception: see below. + + @param thd + + This serves to eliminate the following issue. + Any information readable by a SELECT may theoretically end up in + the trace. And a SELECT may read information from other places than tables: + - from views (reading their bodies) + - from stored routines (reading their bodies) + - from files (reading their content), with LOAD_FILE() + - from the list of connections (reading their queries...), with + I_S.PROCESSLIST. + If the connected user has EXECUTE privilege on a routine which does a + security context change, the routine can retrieve information internally + (if allowed by the SUID context's privileges), and present only a portion + of it to the connected user. But with tracing on, all information is + possibly in the trace. So the connected user receives more information than + the routine's definer intended to provide. Fixing this issue would require + adding, near many privilege checks in the server, a new + optimizer-trace-specific check done against the connected user's context, + to verify that the connected user has the right to see the retrieved + information. + + Instead, our chosen simpler solution is that if we see a security context + change where SUID user is not the connected user, we disable tracing. With + only one safe exception: if the connected user has all global privileges + (because then she/he can find any information anyway). By "all global + privileges" we mean everything but WITH GRANT OPTION (that latter one isn't + related to information gathering). + + Read access to I_S.OPTIMIZER_TRACE by another user than the connected user + is restricted: @see fill_optimizer_trace_info(). +*/ +void opt_trace_disable_if_no_security_context_access(THD *thd); + +void opt_trace_disable_if_no_tables_access(THD *thd, TABLE_LIST *tbl); + +/** + If tracing is on, checks additional privileges for a view, to make sure + that the user has the right to do SHOW CREATE VIEW. For that: + - this function checks SHOW VIEW + - SELECT is tested in opt_trace_disable_if_no_tables_access() + - SELECT + SHOW VIEW is sufficient for SHOW CREATE VIEW. + We also check underlying tables. + If a privilege is missing, notifies the trace system. + This function should be called when the view's underlying tables have not + yet been merged. + + @param thd THD context + @param view view to check + @param underlying_tables underlying tables/views of 'view' + */ + +void opt_trace_disable_if_no_view_access(THD *thd, TABLE_LIST *view, + TABLE_LIST *underlying_tables); + +/** + If tracing is on, checks additional privileges on a stored routine, to make + sure that the user has the right to do SHOW CREATE PROCEDURE/FUNCTION. For + that, we use the same checks as in those SHOW commands. + If a privilege is missing, notifies the trace system. + + This function is not redundant with + opt_trace_disable_if_no_security_context_access(). + Indeed, for a SQL SECURITY INVOKER routine, there is no context change, but + we must still verify that the invoker can do SHOW CREATE. + + For triggers, see note in sp_head::execute_trigger(). + + @param thd + @param sp routine to check + */ +void opt_trace_disable_if_no_stored_proc_func_access(THD *thd, sp_head *sp); + +/** + Fills information_schema.OPTIMIZER_TRACE with rows (one per trace) + @retval 0 ok + @retval 1 error +*/ +int fill_optimizer_trace_info(THD *thd, TABLE_LIST *tables, Item *); + +#define OPT_TRACE_TRANSFORM(writer, object_level0, object_level1, \ + select_number, from, to) \ + Json_writer_object object_level0(writer); \ + Json_writer_object object_level1(writer, "transformation"); \ + object_level1.add_select_number(select_number).add("from", from).add("to", to); +#endif
\ No newline at end of file diff --git a/sql/opt_trace_context.h b/sql/opt_trace_context.h new file mode 100644 index 00000000000..87317f67e22 --- /dev/null +++ b/sql/opt_trace_context.h @@ -0,0 +1,92 @@ +#ifndef OPT_TRACE_CONTEXT_INCLUDED +#define OPT_TRACE_CONTEXT_INCLUDED + +#include "sql_array.h" + +class Opt_trace_stmt; + +class Opt_trace_context +{ +public: + Opt_trace_context(); + ~Opt_trace_context(); + + void start(THD *thd, TABLE_LIST *tbl, + enum enum_sql_command sql_command, + const char *query, + size_t query_length, + const CHARSET_INFO *query_charset, + ulong max_mem_size_arg); + void end(); + void set_query(const char *query, size_t length, const CHARSET_INFO *charset); + void flush_optimizer_trace(); + void set_allowed_mem_size(size_t mem_size); + size_t remaining_mem_size(); + +private: + Opt_trace_stmt* top_trace() + { + return *(traces->front()); + } + +public: + + /* + This returns the top trace from the list of traces. This function + is used when we want to see the contents of the INFORMATION_SCHEMA.OPTIMIZER_TRACE + table. + */ + + Opt_trace_stmt* get_top_trace() + { + if (!traces || !traces->elements()) + return NULL; + return top_trace(); + } + + /* + This returns the current trace, to which we are still writing and has not been finished + */ + + Json_writer* get_current_json(); + + bool empty() + { + return traces && (static_cast<uint>(traces->elements()) != 0); + } + + bool is_started() + { + return current_trace && is_enabled(); + } + + bool disable_tracing_if_required(); + + bool enable_tracing_if_required(); + + bool is_enabled(); + + void missing_privilege(); + + static const char *flag_names[]; + enum + { + FLAG_DEFAULT = 0, + FLAG_ENABLED = 1 << 0 + }; + +private: + /* + List of traces (currently it stores only 1 trace) + */ + Dynamic_array<Opt_trace_stmt*> *traces; + Opt_trace_stmt *current_trace; + /* + TRUE: if we allocate memory for list of traces + FALSE: otherwise + */ + bool inited; + size_t max_mem_size; +}; + +#endif /* OPT_TRACE_CONTEXT_INCLUDED */ diff --git a/sql/set_var.h b/sql/set_var.h index 6097b28e76f..8a82e07fd6b 100644 --- a/sql/set_var.h +++ b/sql/set_var.h @@ -275,6 +275,10 @@ public: virtual int update(THD *thd)=0; /* To set the value */ virtual int light_check(THD *thd) { return check(thd); } /* for PS */ virtual bool is_system() { return FALSE; } + /** + @returns whether this variable is @@@@optimizer_trace. + */ + virtual bool is_var_optimizer_trace() const { return false; } }; @@ -306,6 +310,11 @@ public: int check(THD *thd); int update(THD *thd); int light_check(THD *thd); + virtual bool is_var_optimizer_trace() const + { + extern sys_var *Sys_optimizer_trace_ptr; + return var == Sys_optimizer_trace_ptr; + } }; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 8345a9efe61..11c1234e2db 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -29,6 +29,8 @@ #include "sql_derived.h" // mysql_handle_derived #include "sql_cte.h" #include "sql_select.h" // Virtual_tmp_table +#include "opt_trace.h" +#include "my_json_writer.h" #ifdef USE_PRAGMA_IMPLEMENTATION #pragma implementation @@ -1146,6 +1148,8 @@ sp_head::execute(THD *thd, bool merge_da_on_success) if (check_stack_overrun(thd, 7 * STACK_MIN_SIZE, (uchar*)&old_packet)) DBUG_RETURN(TRUE); + opt_trace_disable_if_no_security_context_access(thd); + /* init per-instruction memroot */ init_sql_alloc(&execute_mem_root, "per_instruction_memroot", MEM_ROOT_BLOCK_SIZE, 0, MYF(0)); @@ -1982,6 +1986,7 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, thd->variables.option_bits&= ~OPTION_BIN_LOG; } + opt_trace_disable_if_no_stored_proc_func_access(thd, this); /* Switch to call arena/mem_root so objects like sp_cursor or Item_cache holders for case expressions can be allocated on it. @@ -2272,6 +2277,7 @@ sp_head::execute_procedure(THD *thd, List<Item> *args) err_status= set_routine_security_ctx(thd, this, &save_security_ctx); #endif + opt_trace_disable_if_no_stored_proc_func_access(thd, this); if (!err_status) { err_status= execute(thd, TRUE); @@ -3297,6 +3303,13 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, thd->lex->safe_to_cache_query= 0; #endif + Opt_trace_start ots(thd, m_lex->query_tables, + SQLCOM_SELECT, &m_lex->var_list, + NULL, 0, + thd->variables.character_set_client); + + Json_writer_object trace_command(thd); + Json_writer_array trace_command_steps(thd, "steps"); if (open_tables) res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) || instr->exec_open_and_lock_tables(thd, m_lex->query_tables); diff --git a/sql/sp_head.h b/sql/sp_head.h index 8db6ecac9e7..f59da93d8aa 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -2024,6 +2024,7 @@ private: }; // class sp_instr_set_case_expr : public sp_instr_opt_meta +bool check_show_routine_access(THD *thd, sp_head *sp, bool *full_access); #ifndef NO_EMBEDDED_ACCESS_CHECKS bool diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 81db3c286ad..97e3bde97dd 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -71,6 +71,7 @@ #include "wsrep_thd.h" #include "wsrep_trans_observer.h" #endif /* WITH_WSREP */ +#include "opt_trace.h" #ifdef HAVE_SYS_SYSCALL_H #include <sys/syscall.h> @@ -1410,6 +1411,7 @@ void THD::change_user(void) sp_cache_clear(&sp_func_cache); sp_cache_clear(&sp_package_spec_cache); sp_cache_clear(&sp_package_body_cache); + opt_trace.flush_optimizer_trace(); } /** @@ -2187,6 +2189,11 @@ void THD::reset_globals() net.thd= 0; } +bool THD::trace_started() +{ + return opt_trace.is_started(); +} + /* Cleanup after query. @@ -4337,6 +4344,13 @@ bool Security_context::set_user(char *user_arg) return user == 0; } +bool Security_context::check_access(ulong want_access, bool match_any) +{ + DBUG_ENTER("Security_context::check_access"); + DBUG_RETURN((match_any ? (master_access & want_access) + : ((master_access & want_access) == want_access))); +} + #ifndef NO_EMBEDDED_ACCESS_CHECKS /** Initialize this security context from the passed in credentials diff --git a/sql/sql_class.h b/sql/sql_class.h index 402a114aadd..d6da0566bbe 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -28,6 +28,7 @@ #include "rpl_tblmap.h" #include "mdl.h" #include "field.h" // Create_field +#include "opt_trace_context.h" #include "probes_mysql.h" #include "sql_locale.h" /* my_locale_st */ #include "sql_profile.h" /* PROFILING */ @@ -569,6 +570,8 @@ typedef struct system_variables ulonglong long_query_time; ulonglong max_statement_time; ulonglong optimizer_switch; + ulonglong optimizer_trace; + ulong optimizer_trace_max_mem_size; sql_mode_t sql_mode; ///< which non-standard SQL behaviour should be enabled sql_mode_t old_behavior; ///< which old SQL behaviour should be enabled ulonglong option_bits; ///< OPTION_xxx constants, e.g. OPTION_PROFILING @@ -1350,6 +1353,14 @@ public: restore_security_context(THD *thd, Security_context *backup); #endif bool user_matches(Security_context *); + /** + Check global access + @param want_access The required privileges + @param match_any if the security context must match all or any of the req. + * privileges. + @return True if the security context fulfills the access requirements. + */ + bool check_access(ulong want_access, bool match_any = false); }; @@ -2306,6 +2317,8 @@ public: Security_context main_security_ctx; Security_context *security_ctx; + Security_context *security_context() const { return security_ctx; } + void set_security_context(Security_context *sctx) { security_ctx = sctx; } /* Points to info-string that we show in SHOW PROCESSLIST @@ -2989,6 +3002,7 @@ public: ulonglong bytes_sent_old; ulonglong affected_rows; /* Number of changed rows */ + Opt_trace_context opt_trace; pthread_t real_id; /* For debugging */ my_thread_id thread_id, thread_dbug_id; uint32 os_thread_id; @@ -3297,6 +3311,7 @@ public: void reset_for_reuse(); bool store_globals(); void reset_globals(); + bool trace_started(); #ifdef SIGNAL_WITH_VIO_CLOSE inline void set_active_vio(Vio* vio) { diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 878aa715b84..28d28ca2425 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -32,6 +32,7 @@ #include "sql_acl.h" // SELECT_ACL #include "sql_class.h" #include "sql_cte.h" +#include "my_json_writer.h" typedef bool (*dt_processor)(THD *thd, LEX *lex, TABLE_LIST *derived); @@ -198,6 +199,7 @@ mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases) if ((res= (*processors[phase])(lex->thd, lex, derived))) break; } + lex->thd->derived_tables_processing= FALSE; DBUG_RETURN(res); } @@ -368,6 +370,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_PRINT("enter", ("Alias: '%s' Unit: %p", (derived->alias.str ? derived->alias.str : "<NULL>"), derived->get_unit())); + const char *cause= NULL; if (derived->merged) { @@ -379,6 +382,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) if (dt_select->uncacheable & UNCACHEABLE_RAND) { /* There is random function => fall back to materialization. */ + cause= "Random function in the select"; derived->change_refs_to_fields(); derived->set_materialized_derived(); DBUG_RETURN(FALSE); @@ -401,15 +405,11 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) and small subqueries, and the bigger one can't be merged it wouldn't block the smaller one. */ - if (parent_lex->get_free_table_map(&map, &tablenr)) - { - /* There is no enough table bits, fall back to materialization. */ - goto unconditional_materialization; - } - - if (dt_select->leaf_tables.elements + tablenr > MAX_TABLES) + if (parent_lex->get_free_table_map(&map, &tablenr) || + dt_select->leaf_tables.elements + tablenr > MAX_TABLES) { /* There is no enough table bits, fall back to materialization. */ + cause= "Not enough table bits to merge subquery"; goto unconditional_materialization; } @@ -486,6 +486,24 @@ exit_merge: DBUG_RETURN(res); unconditional_materialization: + + if (unlikely(thd->trace_started())) + { + /* + Add to the optimizer trace the change in choice for merged + derived tables/views to materialised ones. + */ + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_derived(thd, derived->is_derived() ? + "derived" : "view"); + trace_derived.add("table", derived->alias.str ? derived->alias.str : "<NULL>") + .add_select_number(derived->get_unit()-> + first_select()->select_number) + .add("initial_choice", "merged") + .add("final_choice", "materialized") + .add("cause", cause); + } + derived->change_refs_to_fields(); derived->set_materialized_derived(); if (!derived->table || !derived->table->is_created()) @@ -654,7 +672,6 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_ENTER("mysql_derived_prepare"); DBUG_PRINT("enter", ("unit: %p table_list: %p alias: '%s'", unit, derived, derived->alias.str)); - if (!unit) DBUG_RETURN(FALSE); @@ -747,6 +764,22 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) } } + if (unlikely(thd->trace_started())) + { + /* + Add to optimizer trace whether a derived table/view + is merged into the parent select or not. + */ + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_derived(thd, derived->is_derived() ? + "derived" : "view"); + trace_derived.add("table", derived->alias.str ? derived->alias.str : "<NULL>") + .add_select_number(derived->get_unit()->first_select()->select_number); + if (derived->is_materialized_derived()) + trace_derived.add("materialized", true); + if (derived->is_merged_derived()) + trace_derived.add("merged", true); + } /* Above cascade call of prepare is important for PS protocol, but after it is called we can check if we really need prepare for this derived diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 1c45b05ccc5..022a8beb6d6 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -233,7 +233,7 @@ void Explain_query::print_explain_json(select_result_sink *output, CHARSET_INFO *cs= system_charset_info; List<Item> item_list; - String *buf= &writer.output; + const String *buf= writer.output.get_string(); item_list.push_back(new (thd->mem_root) Item_string(thd, buf->ptr(), buf->length(), cs), thd->mem_root); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 7b6029aaa12..25dfdbcbc71 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -100,6 +100,7 @@ #include "set_var.h" #include "sql_bootstrap.h" #include "sql_sequence.h" +#include "opt_trace.h" #include "my_json_writer.h" @@ -3412,6 +3413,13 @@ mysql_execute_command(THD *thd) #ifdef HAVE_REPLICATION } /* endif unlikely slave */ #endif + Opt_trace_start ots(thd, all_tables, lex->sql_command, &lex->var_list, + thd->query(), thd->query_length(), + thd->variables.character_set_client); + + Json_writer_object trace_command(thd); + Json_writer_array trace_command_steps(thd, "steps"); + #ifdef WITH_WSREP if (WSREP(thd)) { diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index c8cc64dba7e..6c3ad9c6924 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -112,6 +112,7 @@ When one supplies long data for a placeholder: #include "sp_cache.h" #include "sql_handler.h" // mysql_ha_rm_tables #include "probes_mysql.h" +#include "opt_trace.h" #ifdef EMBEDDED_LIBRARY /* include MYSQL_BIND headers */ #include <mysql.h> @@ -2273,6 +2274,17 @@ static bool check_prepared_statement(Prepared_statement *stmt) lex->first_select_lex()->context.resolve_in_table_list_only(select_lex-> get_table_list()); + /* + For the optimizer trace, this is the symmetric, for statement preparation, + of what is done at statement execution (in mysql_execute_command()). + */ + Opt_trace_start ots(thd, tables, lex->sql_command, &lex->var_list, + thd->query(), thd->query_length(), + thd->variables.character_set_client); + + Json_writer_object trace_command(thd); + Json_writer_array trace_command_steps(thd, "steps"); + /* Reset warning count for each query that uses tables */ if (tables) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1478dd93af4..06205a46f40 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -64,6 +64,8 @@ #include "sys_vars_shared.h" #include "sp_head.h" #include "sp_rcontext.h" +#include "my_json_writer.h" +#include "opt_trace.h" /* A key part number that means we're using a fulltext scan. @@ -295,6 +297,8 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab); static Item **get_sargable_cond(JOIN *join, TABLE *table); +static void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables); + #ifndef DBUG_OFF /* @@ -345,6 +349,40 @@ bool dbug_user_var_equals_int(THD *thd, const char *name, int value) } #endif +static void trace_table_dependencies(THD *thd, + JOIN_TAB *join_tabs, uint table_count) +{ + Json_writer_object trace_wrapper(thd); + Json_writer_array trace_dep(thd, "table_dependencies"); + for (uint i = 0; i < table_count; i++) + { + TABLE_LIST *table_ref = join_tabs[i].tab_list; + Json_writer_object trace_one_table(thd); + trace_one_table.add_table_name(&join_tabs[i]); + trace_one_table.add("row_may_be_null", + (bool)table_ref->table->maybe_null); + const table_map map = table_ref->get_map(); + DBUG_ASSERT(map < (1ULL << table_count)); + for (uint j = 0; j < table_count; j++) + { + if (map & (1ULL << j)) + { + trace_one_table.add("map_bit", static_cast<longlong>(j)); + break; + } + } + Json_writer_array depends_on(thd, "depends_on_map_bits"); + static_assert(sizeof(table_ref->get_map()) <= 64, + "RAND_TABLE_BIT may be in join_tabs[i].dependent, so we test " + "all 64 bits."); + for (uint j = 0; j < 64; j++) + { + if (join_tabs[i].dependent & (1ULL << j)) + depends_on.add(static_cast<longlong>(j)); + } + } +} + /** This handles SELECT with and without UNION. @@ -994,6 +1032,11 @@ JOIN::prepare(TABLE_LIST *tables_init, join_list= &select_lex->top_join_list; union_part= unit_arg->is_unit_op(); + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_prepare(thd, "join_preparation"); + trace_prepare.add_select_number(select_lex->select_number); + Json_writer_array trace_steps(thd, "steps"); + // simple check that we got usable conds dbug_print_item(conds); @@ -1336,6 +1379,11 @@ JOIN::prepare(TABLE_LIST *tables_init, } } + { + Json_writer_object trace_wrapper(thd); + opt_trace_print_expanded_query(thd, select_lex, &trace_wrapper); + } + if (!procedure && result && result->prepare(fields_list, unit_arg)) goto err; /* purecov: inspected */ @@ -1517,6 +1565,11 @@ JOIN::optimize_inner() set_allowed_join_cache_types(); need_distinct= TRUE; + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_prepare(thd, "join_optimization"); + trace_prepare.add_select_number(select_lex->select_number); + Json_writer_array trace_steps(thd, "steps"); + /* Needed in case optimizer short-cuts, set properly in make_aggr_tables_info() @@ -3914,6 +3967,12 @@ void JOIN::exec_inner() limit in order to produce the partial query result stored in the UNION temp table. */ + + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_exec(thd, "join_execution"); + trace_exec.add_select_number(select_lex->select_number); + Json_writer_array trace_steps(thd, "steps"); + if (!select_lex->outer_select() && // (1) select_lex != select_lex->master_unit()->fake_select_lex) // (2) thd->lex->set_limit_rows_examined(); @@ -4464,6 +4523,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, SARGABLE_PARAM *sargables= 0; List_iterator<TABLE_LIST> ti(tables_list); TABLE_LIST *tables; + THD *thd= join->thd; DBUG_ENTER("make_join_statistics"); table_count=join->table_count; @@ -4659,9 +4719,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, } } + if (thd->trace_started()) + trace_table_dependencies(thd, stat, join->table_count); + if (join->conds || outer_join) { - if (update_ref_and_keys(join->thd, keyuse_array, stat, join->table_count, + if (update_ref_and_keys(thd, keyuse_array, stat, join->table_count, join->conds, ~outer_join, join->select_lex, &sargables)) goto error; /* @@ -4673,10 +4736,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, ((Item_in_subselect*)join->unit->item)->test_strategy(SUBS_IN_TO_EXISTS)); if (keyuse_array->elements && - sort_and_filter_keyuse(join->thd, keyuse_array, + sort_and_filter_keyuse(thd, keyuse_array, skip_unprefixed_keyparts)) goto error; DBUG_EXECUTE("opt", print_keyuse_array(keyuse_array);); + if (thd->trace_started()) + print_keyuse_array_for_trace(thd, keyuse_array); } join->const_table_map= no_rows_const_tables; @@ -4961,143 +5026,160 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, /* Calc how many (possible) matched records in each table */ - for (s=stat ; s < stat_end ; s++) + /* + Todo: add a function so that we can add these Json_writer_objects + easily. + Another way would be to enclose them in a scope {}; + */ { - s->startup_cost= 0; - if (s->type == JT_SYSTEM || s->type == JT_CONST) - { - /* Only one matching row */ - s->found_records= s->records= 1; - s->read_time=1.0; - s->worst_seeks=1.0; - continue; - } - /* Approximate found rows and time to read them */ - if (s->table->is_filled_at_execution()) - { - get_delayed_table_estimates(s->table, &s->records, &s->read_time, - &s->startup_cost); - s->found_records= s->records; - table->quick_condition_rows=s->records; - } - else - { - s->scan_time(); - } - - if (s->table->is_splittable()) - s->add_keyuses_for_splitting(); + Json_writer_object rows_estimation_wrapper(thd); + Json_writer_array rows_estimation(thd, "rows_estimation"); + for (s=stat ; s < stat_end ; s++) + { + s->startup_cost= 0; + if (s->type == JT_SYSTEM || s->type == JT_CONST) + { + + Json_writer_object table_records(thd); + /* Only one matching row */ + s->found_records= s->records= 1; + s->read_time=1.0; + s->worst_seeks=1.0; + table_records.add_table_name(s) + .add("rows", s->found_records) + .add("cost", s->read_time) + .add("table_type", s->type == JT_CONST ? + "const" : + "system"); + continue; + } + /* Approximate found rows and time to read them */ + if (s->table->is_filled_at_execution()) + { + get_delayed_table_estimates(s->table, &s->records, &s->read_time, + &s->startup_cost); + s->found_records= s->records; + table->quick_condition_rows=s->records; + } + else + s->scan_time(); - /* - Set a max range of how many seeks we can expect when using keys - This is can't be to high as otherwise we are likely to use - table scan. - */ - s->worst_seeks= MY_MIN((double) s->found_records / 10, - (double) s->read_time*3); - if (s->worst_seeks < 2.0) // Fix for small tables - s->worst_seeks=2.0; + if (s->table->is_splittable()) + s->add_keyuses_for_splitting(); - /* - Add to stat->const_keys those indexes for which all group fields or - all select distinct fields participate in one index. - */ - add_group_and_distinct_keys(join, s); + /* + Set a max range of how many seeks we can expect when using keys + This is can't be to high as otherwise we are likely to use + table scan. + */ + s->worst_seeks= MY_MIN((double) s->found_records / 10, + (double) s->read_time*3); + if (s->worst_seeks < 2.0) // Fix for small tables + s->worst_seeks=2.0; - s->table->cond_selectivity= 1.0; - - /* - Perform range analysis if there are keys it could use (1). - Don't do range analysis for materialized subqueries (2). - Don't do range analysis for materialized derived tables (3) - */ - if ((!s->const_keys.is_clear_all() || - !bitmap_is_clear_all(&s->table->cond_set)) && // (1) - !s->table->is_filled_at_execution() && // (2) - !(s->table->pos_in_table_list->derived && // (3) - s->table->pos_in_table_list->is_materialized_derived())) // (3) - { - bool impossible_range= FALSE; - ha_rows records= HA_POS_ERROR; - SQL_SELECT *select= 0; - Item **sargable_cond= NULL; - if (!s->const_keys.is_clear_all()) - { - sargable_cond= get_sargable_cond(join, s->table); - - select= make_select(s->table, found_const_table_map, - found_const_table_map, - *sargable_cond, - (SORT_INFO*) 0, - 1, &error); - if (!select) - goto error; - records= get_quick_record_count(join->thd, select, s->table, - &s->const_keys, join->row_limit); + /* + Add to stat->const_keys those indexes for which all group fields or + all select distinct fields participate in one index. + */ + add_group_and_distinct_keys(join, s); - /* - Range analyzer might have modified the condition. Put it the new - condition to where we got it from. - */ - *sargable_cond= select->cond; + s->table->cond_selectivity= 1.0; - s->quick=select->quick; - s->needed_reg=select->needed_reg; - select->quick=0; - impossible_range= records == 0 && s->table->reginfo.impossible_range; - } - if (!impossible_range) - { - if (!sargable_cond) + /* + Perform range analysis if there are keys it could use (1). + Don't do range analysis for materialized subqueries (2). + Don't do range analysis for materialized derived tables (3) + */ + if ((!s->const_keys.is_clear_all() || + !bitmap_is_clear_all(&s->table->cond_set)) && // (1) + !s->table->is_filled_at_execution() && // (2) + !(s->table->pos_in_table_list->derived && // (3) + s->table->pos_in_table_list->is_materialized_derived())) // (3) + { + bool impossible_range= FALSE; + ha_rows records= HA_POS_ERROR; + SQL_SELECT *select= 0; + Item **sargable_cond= NULL; + if (!s->const_keys.is_clear_all()) + { sargable_cond= get_sargable_cond(join, s->table); - if (join->thd->variables.optimizer_use_condition_selectivity > 1) - calculate_cond_selectivity_for_table(join->thd, s->table, - sargable_cond); - if (s->table->reginfo.impossible_range) - { - impossible_range= TRUE; - records= 0; + + select= make_select(s->table, found_const_table_map, + found_const_table_map, + *sargable_cond, + (SORT_INFO*) 0, 1, &error); + if (!select) + goto error; + records= get_quick_record_count(join->thd, select, s->table, + &s->const_keys, join->row_limit); + + /* + Range analyzer might have modified the condition. Put it the new + condition to where we got it from. + */ + *sargable_cond= select->cond; + + s->quick=select->quick; + s->needed_reg=select->needed_reg; + select->quick=0; + impossible_range= records == 0 && s->table->reginfo.impossible_range; } - } - if (impossible_range) - { - /* - Impossible WHERE or ON expression - In case of ON, we mark that the we match one empty NULL row. - In case of WHERE, don't set found_const_table_map to get the - caller to abort with a zero row result. - */ - TABLE_LIST *emb= s->table->pos_in_table_list->embedding; - if (emb && !emb->sj_on_expr) + if (!impossible_range) { - /* Mark all tables in a multi-table join nest as const */ - mark_join_nest_as_const(join, emb, &found_const_table_map, - &const_count); + if (!sargable_cond) + sargable_cond= get_sargable_cond(join, s->table); + if (join->thd->variables.optimizer_use_condition_selectivity > 1) + calculate_cond_selectivity_for_table(join->thd, s->table, + sargable_cond); + if (s->table->reginfo.impossible_range) + { + impossible_range= TRUE; + records= 0; + } } - else + if (impossible_range) { - join->const_table_map|= s->table->map; - set_position(join,const_count++,s,(KEYUSE*) 0); - s->type= JT_CONST; - s->table->const_table= 1; - if (*s->on_expr_ref) + /* + Impossible WHERE or ON expression + In case of ON, we mark that the we match one empty NULL row. + In case of WHERE, don't set found_const_table_map to get the + caller to abort with a zero row result. + */ + TABLE_LIST *emb= s->table->pos_in_table_list->embedding; + if (emb && !emb->sj_on_expr) + { + /* Mark all tables in a multi-table join nest as const */ + mark_join_nest_as_const(join, emb, &found_const_table_map, + &const_count); + } + else { - /* Generate empty row */ - s->info= ET_IMPOSSIBLE_ON_CONDITION; - found_const_table_map|= s->table->map; - mark_as_null_row(s->table); // All fields are NULL + join->const_table_map|= s->table->map; + set_position(join,const_count++,s,(KEYUSE*) 0); + s->type= JT_CONST; + s->table->const_table= 1; + if (*s->on_expr_ref) + { + /* Generate empty row */ + s->info= ET_IMPOSSIBLE_ON_CONDITION; + found_const_table_map|= s->table->map; + mark_as_null_row(s->table); // All fields are NULL + } } } + if (records != HA_POS_ERROR) + { + s->found_records=records; + s->read_time= s->quick ? s->quick->read_time : 0.0; + } + if (select) + delete select; + else + add_table_scan_values_to_trace(thd, s); } - if (records != HA_POS_ERROR) - { - s->found_records=records; - s->read_time= s->quick ? s->quick->read_time : 0.0; - } - if (select) - delete select; + else + add_table_scan_values_to_trace(thd, s); } - } if (pull_out_semijoin_tables(join)) @@ -6856,11 +6938,15 @@ best_access_path(JOIN *join, MY_BITMAP *eq_join_set= &s->table->eq_join_set; KEYUSE *hj_start_key= 0; SplM_plan_info *spl_plan= 0; + const char* cause= NULL; disable_jbuf= disable_jbuf || idx == join->const_tables; Loose_scan_opt loose_scan_opt; DBUG_ENTER("best_access_path"); + + Json_writer_object trace_wrapper(thd, "best_access_path"); + Json_writer_array trace_paths(thd, "considered_access_paths"); bitmap_clear_all(eq_join_set); @@ -6975,6 +7061,7 @@ best_access_path(JOIN *join, if (rec < MATCHING_ROWS_IN_OTHER_TABLE) rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables + Json_writer_object trace_access_idx(thd); /* ft-keys require special treatment */ @@ -6986,6 +7073,8 @@ best_access_path(JOIN *join, */ tmp= prev_record_reads(join->positions, idx, found_ref); records= 1.0; + trace_access_idx.add("access_type", "fulltext") + .add("index", keyinfo->name); } else { @@ -7000,11 +7089,15 @@ best_access_path(JOIN *join, if ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME || MY_TEST(key_flags & HA_EXT_NOSAME)) { + trace_access_idx.add("access_type", "eq_ref") + .add("index", keyinfo->name); tmp = prev_record_reads(join->positions, idx, found_ref); records=1.0; } else { + trace_access_idx.add("access_type", "ref") + .add("index", keyinfo->name); if (!found_ref) { /* We found a const key */ /* @@ -7025,11 +7118,16 @@ best_access_path(JOIN *join, empty interval we wouldn't have got here). */ if (table->quick_keys.is_set(key)) + { records= (double) table->quick_rows[key]; + trace_access_idx.add("used_range_estimates", true); + } else { /* quick_range couldn't use key! */ records= (double) s->records/rec; + trace_access_idx.add("used_range_estimates", false) + .add("cause", "not available"); } } else @@ -7061,7 +7159,23 @@ best_access_path(JOIN *join, table->quick_n_ranges[key] == 1 && records > (double) table->quick_rows[key]) { + records= (double) table->quick_rows[key]; + trace_access_idx.add("used_range_estimates", true); + } + else + { + if (table->quick_keys.is_set(key)) + { + trace_access_idx.add("used_range_estimates",false) + .add("cause", + "not better than ref estimates"); + } + else + { + trace_access_idx.add("used_range_estimates", false) + .add("cause", "not available"); + } } } /* Limit the number of matched rows */ @@ -7077,6 +7191,9 @@ best_access_path(JOIN *join, } else { + trace_access_idx.add("access_type", + ref_or_null_part ? "ref_or_null" : "ref") + .add("index", keyinfo->name); /* Use as much key-parts as possible and a uniq key is better than a not unique key @@ -7131,6 +7248,7 @@ best_access_path(JOIN *join, table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part)) //(C3) { tmp= records= (double) table->quick_rows[key]; + trace_access_idx.add("used_range_estimates", true); } else { @@ -7156,7 +7274,19 @@ best_access_path(JOIN *join, if (!found_ref && table->quick_keys.is_set(key) && // (1) table->quick_key_parts[key] > max_key_part && // (2) records < (double)table->quick_rows[key]) // (3) + { + trace_access_idx.add("used_range_estimates", true); records= (double)table->quick_rows[key]; + } + else + { + if (table->quick_keys.is_set(key) && + table->quick_key_parts[key] < max_key_part) + { + trace_access_idx.add("chosen", false); + cause= "range uses more keyparts"; + } + } tmp= records; } @@ -7240,15 +7370,22 @@ best_access_path(JOIN *join, tmp*= record_count; } else + { + if (!(found_part & 1)) + cause= "no predicate for first keypart"; tmp= best_time; // Do nothing + } } tmp += s->startup_cost; loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp); } /* not ft_key */ + trace_access_idx.add("rows", records).add("cost", tmp); + if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE) { + trace_access_idx.add("chosen", true); best_time= tmp + records/(double) TIME_FOR_COMPARE; best= tmp; best_records= records; @@ -7256,6 +7393,12 @@ best_access_path(JOIN *join, best_max_key_part= max_key_part; best_ref_depends_map= found_ref; } + else + { + trace_access_idx.add("chosen", false) + .add("cause", cause ? cause : "cost"); + } + cause= NULL; } /* for each key */ records= best_records; } @@ -7277,6 +7420,7 @@ best_access_path(JOIN *join, (!(s->table->map & join->outer_join) || join->allowed_outer_join_with_cache)) // (2) { + Json_writer_object trace_access_hash(thd); double join_sel= 0.1; /* Estimate the cost of the hash join access to the table */ double rnd_records= matching_candidates_in_table(s, found_constraint, @@ -7296,7 +7440,12 @@ best_access_path(JOIN *join, best_key= hj_start_key; best_ref_depends_map= 0; best_uses_jbuf= TRUE; - } + trace_access_hash.add("type", "hash"); + trace_access_hash.add("index", "hj-key"); + trace_access_hash.add("cost", rnd_records); + trace_access_hash.add("cost", best); + trace_access_hash.add("chosen", true); + } /* Don't test table scan if it can't be better. @@ -7331,6 +7480,7 @@ best_access_path(JOIN *join, can be [considered to be] more expensive, which causes lookups not to be used for cases with small datasets, which is annoying. */ + Json_writer_object trace_access_scan(thd); if ((records >= s->found_records || best > s->read_time) && // (1) !(s->quick && best_key && s->quick->index == best_key->key && // (2) best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2) @@ -7350,6 +7500,10 @@ best_access_path(JOIN *join, if (s->quick) { + trace_access_scan.add("access_type", "range"); + /* + should have some info about all the different QUICK_SELECT + */ /* For each record we: - read record range through 'quick' @@ -7367,6 +7521,7 @@ best_access_path(JOIN *join, } else { + trace_access_scan.add("access_type", "scan"); /* Estimate cost of reading table. */ if (s->table->force_index && !best_key) // index scan tmp= s->table->file->read_time(s->ref.key, 1, s->records); @@ -7411,6 +7566,8 @@ best_access_path(JOIN *join, as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus tmp give us total cost of using TABLE SCAN */ + trace_access_scan.add("resulting_rows", rnd_records); + trace_access_scan.add("cost", tmp); if (best == DBL_MAX || (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records < (best_key->is_for_hash_join() ? best_time : @@ -7429,6 +7586,13 @@ best_access_path(JOIN *join, join->outer_join))); spl_plan= 0; } + trace_access_scan.add("chosen", best_key == NULL); + } + else + { + trace_access_scan.add("type", "scan"); + trace_access_scan.add("chosen", false); + trace_access_scan.add("cause", "cost"); } /* Update the cost information for the current partial plan */ @@ -7447,7 +7611,10 @@ best_access_path(JOIN *join, idx == join->const_tables && s->table == join->sort_by_table && join->unit->select_limit_cnt >= records) + { + trace_access_scan.add("use_tmp_table", true); join->sort_by_table= (TABLE*) 1; // Must use temporary table + } DBUG_VOID_RETURN; } @@ -7590,6 +7757,7 @@ choose_plan(JOIN *join, table_map join_tables) uint use_cond_selectivity= join->thd->variables.optimizer_use_condition_selectivity; bool straight_join= MY_TEST(join->select_options & SELECT_STRAIGHT_JOIN); + THD *thd= join->thd; DBUG_ENTER("choose_plan"); join->cur_embedding_map= 0; @@ -7626,6 +7794,9 @@ choose_plan(JOIN *join, table_map join_tables) join->table_count - join->const_tables, sizeof(JOIN_TAB*), jtab_sort_func, (void*)join->emb_sjm_nest); + Json_writer_object wrapper(thd); + Json_writer_array trace_plan(thd,"considered_execution_plans"); + if (!join->emb_sjm_nest) { choose_initial_table_order(join); @@ -7919,9 +8090,16 @@ optimize_straight_join(JOIN *join, table_map join_tables) uint use_cond_selectivity= join->thd->variables.optimizer_use_condition_selectivity; POSITION loose_scan_pos; + THD *thd= join->thd; for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++) { + Json_writer_object trace_one_table(thd); + if (unlikely(thd->trace_started())) + { + trace_plan_prefix(join, idx, join_tables); + trace_one_table.add_table_name(s); + } /* Find the best access method from 's' to the current partial plan */ best_access_path(join, s, join_tables, idx, disable_jbuf, record_count, join->positions + idx, &loose_scan_pos); @@ -8650,6 +8828,18 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, } +static void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables) +{ + THD *const thd = join->thd; + Json_writer_array plan_prefix(thd, "plan_prefix"); + for (uint i = 0; i < idx; i++) + { + TABLE_LIST *const tr = join->positions[i].table->tab_list; + if (!(tr->map & remaining_tables)) + plan_prefix.add_table_name(join->positions[i].table); + } +} + /** Find a good, possibly optimal, query execution plan (QEP) by a possibly exhaustive search. @@ -8827,6 +9017,13 @@ best_extension_by_limited_search(JOIN *join, double current_record_count, current_read_time; POSITION *position= join->positions + idx; + Json_writer_object trace_one_table(thd); + if (unlikely(thd->trace_started())) + { + trace_plan_prefix(join, idx, remaining_tables); + trace_one_table.add_table_name(s); + } + /* Find the best access method from 's' to the current partial plan */ POSITION loose_scan_pos; best_access_path(join, s, remaining_tables, idx, disable_jbuf, @@ -8840,6 +9037,9 @@ best_extension_by_limited_search(JOIN *join, current_read_time=read_time + position->read_time + current_record_count / (double) TIME_FOR_COMPARE; + /* + TODO add filtering estimates here + */ advance_sj_state(join, remaining_tables, idx, ¤t_record_count, ¤t_read_time, &loose_scan_pos); @@ -8851,6 +9051,7 @@ best_extension_by_limited_search(JOIN *join, read_time, current_read_time, "prune_by_cost");); + trace_one_table.add("pruned_by_cost", true); restore_prev_nj_state(s); restore_prev_sj_state(remaining_tables, s, idx); continue; @@ -8884,6 +9085,7 @@ best_extension_by_limited_search(JOIN *join, read_time, current_read_time, "pruned_by_heuristic");); + trace_one_table.add("pruned_by_heuristic", true); restore_prev_nj_state(s); restore_prev_sj_state(remaining_tables, s, idx); continue; @@ -8901,6 +9103,7 @@ best_extension_by_limited_search(JOIN *join, if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) & allowed_tables ) { /* Recursively expand the current partial plan */ swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); + Json_writer_array trace_rest(thd, "rest_of_plan"); if (best_extension_by_limited_search(join, remaining_tables & ~real_table_bit, idx + 1, @@ -10502,23 +10705,40 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) there inside the triggers. */ { // Check const tables - join->exec_const_cond= - make_cond_for_table(thd, cond, + Item* const_cond= NULL; + const_cond= make_cond_for_table(thd, cond, join->const_table_map, (table_map) 0, -1, FALSE, FALSE); /* Add conditions added by add_not_null_conds(). */ for (uint i= 0 ; i < join->const_tables ; i++) - add_cond_and_fix(thd, &join->exec_const_cond, + add_cond_and_fix(thd, &const_cond, join->join_tab[i].select_cond); - DBUG_EXECUTE("where",print_where(join->exec_const_cond,"constants", + DBUG_EXECUTE("where",print_where(const_cond,"constants", QT_ORDINARY);); - if (join->exec_const_cond && !join->exec_const_cond->is_expensive() && - !join->exec_const_cond->val_int()) + + if (const_cond) { - DBUG_PRINT("info",("Found impossible WHERE condition")); - join->exec_const_cond= NULL; - DBUG_RETURN(1); // Impossible const condition + Json_writer_object trace_const_cond(thd); + trace_const_cond.add("condition_on_constant_tables", const_cond); + if (const_cond->is_expensive()) + { + trace_const_cond.add("evalualted", "false") + .add("cause", "expensive cond"); + } + else + { + const bool const_cond_result = const_cond->val_int() != 0; + if (!const_cond_result) + { + DBUG_PRINT("info",("Found impossible WHERE condition")); + trace_const_cond.add("evalualted", "true") + .add("found", "impossible where"); + join->exec_const_cond= NULL; + DBUG_RETURN(1); + } + } + join->exec_const_cond= const_cond; } if (join->table_count != join->const_tables) @@ -10555,6 +10775,11 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) /* Step #2: Extract WHERE/ON parts */ + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_conditions(thd, "attaching_conditions_to_tables"); + trace_conditions.add("original_condition", cond); + Json_writer_array trace_attached_comp(thd, + "attached_conditions_computation"); uint i; for (i= join->top_join_tab_count - 1; i >= join->const_tables; i--) { @@ -10617,10 +10842,19 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) (!is_hash_join_key_no(tab->ref.key) && tab->table->intersect_keys.is_set(tab->ref.key)))) { - /* Range uses longer key; Use this instead of ref on key */ - tab->type=JT_ALL; - use_quick_range=1; - tab->use_quick=1; + /* Range uses longer key; Use this instead of ref on key */ + + /* + We can trace here, changing ref access to range access here + have a range that uses longer key. + Lets take @spetrunia's opinion + */ + Json_writer_object ref_to_range(thd); + ref_to_range.add("ref_to_range", true); + ref_to_range.add("cause", "range uses longer key"); + tab->type=JT_ALL; + use_quick_range=1; + tab->use_quick=1; tab->ref.key= -1; tab->ref.key_parts=0; // Don't use ref key. join->best_positions[i].records_read= rows2double(tab->quick->records); @@ -11058,6 +11292,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (!tab->bush_children) i++; } + + trace_attached_comp.end(); + Json_writer_array trace_attached_summary(thd, + "attached_conditions_summary"); + for (tab= first_depth_first_tab(join); tab; + tab= next_depth_first_tab(join, tab)) + { + if (!tab->table) + continue; + Item *const cond = tab->select_cond; + Json_writer_object trace_one_table(thd); + trace_one_table.add_table_name(tab); + trace_one_table.add("attached", cond); + } } DBUG_RETURN(0); } @@ -15983,12 +16231,24 @@ optimize_cond(JOIN *join, COND *conds, that occurs in a function set a pointer to the multiple equality predicate. Substitute a constant instead of this field if the multiple equality contains a constant. - */ + */ + + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_cond(thd, "condition_processing"); + trace_cond.add("condition", join->conds == conds ? "WHERE" : "HAVING") + .add("original_condition", conds); + + Json_writer_array trace_steps(thd, "steps"); DBUG_EXECUTE("where", print_where(conds, "original", QT_ORDINARY);); conds= build_equal_items(join, conds, NULL, join_list, ignore_on_conds, cond_equal, MY_TEST(flags & OPT_LINK_EQUAL_FIELDS)); DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY);); + { + Json_writer_object equal_prop_wrapper(thd); + equal_prop_wrapper.add("transformation", "equality_propagation") + .add("resulting_condition", conds); + } /* change field = field to field = const for each found field = const */ propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds); @@ -15997,10 +16257,21 @@ optimize_cond(JOIN *join, COND *conds, Remove all and-levels where CONST item != CONST item */ DBUG_EXECUTE("where",print_where(conds,"after const change", QT_ORDINARY);); + { + Json_writer_object const_prop_wrapper(thd); + const_prop_wrapper.add("transformation", "constant_propagation") + .add("resulting_condition", conds); + } conds= conds->remove_eq_conds(thd, cond_value, true); if (conds && conds->type() == Item::COND_ITEM && ((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC) *cond_equal= &((Item_cond_and*) conds)->m_cond_equal; + + { + Json_writer_object cond_removal_wrapper(thd); + cond_removal_wrapper.add("transformation", "trivial_condition_removal") + .add("resulting_condition", conds); + } DBUG_EXECUTE("info",print_where(conds,"after remove", QT_ORDINARY);); } DBUG_RETURN(conds); @@ -26122,7 +26393,8 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str, // A view if (!(belong_to_view && - belong_to_view->compact_view_format)) + belong_to_view->compact_view_format) && + !(query_type & QT_ITEM_IDENT_SKIP_DB_NAMES)) { append_identifier(thd, str, &view_db); str->append('.'); @@ -26151,7 +26423,8 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str, // A normal table if (!(belong_to_view && - belong_to_view->compact_view_format)) + belong_to_view->compact_view_format) && + !(query_type & QT_ITEM_IDENT_SKIP_DB_NAMES)) { append_identifier(thd, str, &db); str->append('.'); @@ -26886,6 +27159,12 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, bool group= join && join->group && order == join->group_list; ha_rows refkey_rows_estimate= table->quick_condition_rows; const bool has_limit= (select_limit_arg != HA_POS_ERROR); + THD* thd= join ? join->thd : table->in_use; + + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_cheaper_ordering( + thd, "reconsidering_access_paths_for_index_ordering"); + trace_cheaper_ordering.add("clause", group ? "GROUP BY" : "ORDER BY"); /* If not used with LIMIT, only use keys if the whole query can be @@ -26924,11 +27203,12 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, else read_time= table->file->scan_time(); + trace_cheaper_ordering.add("fanout", fanout); /* TODO: add cost of sorting here. */ read_time += COST_EPS; - + trace_cheaper_ordering.add("read_time", read_time); /* Calculate the selectivity of the ref_key for REF_ACCESS. For RANGE_ACCESS we use table->quick_condition_rows. @@ -26945,11 +27225,20 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, set_if_bigger(refkey_rows_estimate, 1); } + if (tab) + trace_cheaper_ordering.add_table_name(tab); + else + trace_cheaper_ordering.add_table_name(table); + trace_cheaper_ordering.add("rows_estimation", refkey_rows_estimate); + + Json_writer_array possible_keys(thd,"possible_keys"); for (nr=0; nr < table->s->keys ; nr++) { int direction; ha_rows select_limit= select_limit_arg; uint used_key_parts= 0; + Json_writer_object possible_key(thd); + possible_key.add("index", table->key_info[nr].name); if (keys.is_set(nr) && (direction= test_if_order_by_key(join, order, table, nr, @@ -26962,6 +27251,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, */ DBUG_ASSERT (ref_key != (int) nr); + possible_key.add("can_resolve_order", true); bool is_covering= (table->covering_keys.is_set(nr) || (table->file->index_flags(nr, 0, 1) & HA_CLUSTERED_INDEX)); @@ -27092,6 +27382,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, select_limit= (ha_rows) (select_limit * (double) table_records / refkey_rows_estimate); + possible_key.add("updated_limit", select_limit); rec_per_key= keyinfo->actual_rec_per_key(keyinfo->user_defined_key_parts-1); set_if_bigger(rec_per_key, 1); /* @@ -27111,9 +27402,11 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, if (get_range_limit_read_cost(tab, table, table_records, nr, select_limit, &range_scan_time)) { + possible_key.add("range_scan_time", range_scan_time); if (range_scan_time < index_scan_time) index_scan_time= range_scan_time; } + possible_key.add("index_scan_time", index_scan_time); if ((ref_key < 0 && (group || table->force_index || is_covering)) || index_scan_time < read_time) @@ -27124,17 +27417,29 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, table->covering_keys.is_set(ref_key)) ? refkey_rows_estimate : HA_POS_ERROR; - if ((is_best_covering && !is_covering) || - (is_covering && refkey_select_limit < select_limit)) + if (is_best_covering && !is_covering) + { + possible_key.add("chosen", false); + possible_key.add("cause", "covering index already found"); + continue; + } + + if (is_covering && refkey_select_limit < select_limit) + { + possible_key.add("chosen", false); + possible_key.add("cause", "ref estimates better"); continue; + } if (table->quick_keys.is_set(nr)) quick_records= table->quick_rows[nr]; + possible_key.add("records", quick_records); if (best_key < 0 || (select_limit <= MY_MIN(quick_records,best_records) ? keyinfo->user_defined_key_parts < best_key_parts : quick_records < best_records) || (!is_best_covering && is_covering)) { + possible_key.add("chosen", true); best_key= nr; best_key_parts= keyinfo->user_defined_key_parts; if (saved_best_key_parts) @@ -27144,8 +27449,47 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, best_key_direction= direction; best_select_limit= select_limit; } + else + { + char const *cause; + possible_key.add("chosen", false); + if (is_covering) + cause= "covering index already found"; + else + { + if (select_limit <= MY_MIN(quick_records,best_records)) + cause= "keyparts greater than the current best keyparts"; + else + cause= "rows estimation greater"; + } + possible_key.add("cause", cause); + } + } + else + { + possible_key.add("usable", false); + possible_key.add("cause", "cost"); } - } + } + else + { + possible_key.add("usable", false); + if (!group && select_limit == HA_POS_ERROR) + possible_key.add("cause", "order by without limit"); + } + } + else + { + if (keys.is_set(nr)) + { + possible_key.add("can_resolve_order", false); + possible_key.add("cause", "order can not be resolved by key"); + } + else + { + possible_key.add("can_resolve_order", false); + possible_key.add("cause", "not usable index for the query"); + } } } diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 3d2723081a3..80884d66590 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -63,6 +63,7 @@ #include "ha_partition.h" #endif #include "transaction.h" +#include "opt_trace.h" enum enum_i_s_events_fields { @@ -9764,6 +9765,10 @@ ST_FIELD_INFO check_constraints_fields_info[]= OPEN_FULL_TABLE}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} }; + +/** For creating fields of information_schema.OPTIMIZER_TRACE */ +extern ST_FIELD_INFO optimizer_trace_info[]; + /* Description of ST_FIELD_INFO in table.h @@ -9816,6 +9821,8 @@ ST_SCHEMA_TABLE schema_tables[]= OPTIMIZE_I_S_TABLE|OPEN_TABLE_ONLY}, {"OPEN_TABLES", open_tables_fields_info, 0, fill_open_tables, make_old_format, 0, -1, -1, 1, 0}, + {"OPTIMIZER_TRACE", optimizer_trace_info, 0, + fill_optimizer_trace_info, NULL, NULL, -1, -1, false, 0}, {"PARAMETERS", parameters_fields_info, 0, fill_schema_proc, 0, 0, -1, -1, 0, 0}, {"PARTITIONS", partitions_fields_info, 0, diff --git a/sql/sql_test.cc b/sql/sql_test.cc index 3d43c35177d..f247fb10f89 100644 --- a/sql/sql_test.cc +++ b/sql/sql_test.cc @@ -24,6 +24,7 @@ #include "sql_show.h" // calc_sum_of_all_status #include "sql_select.h" #include "keycaches.h" +#include "my_json_writer.h" #include <hash.h> #include <thr_alarm.h> #if defined(HAVE_MALLINFO) && defined(HAVE_MALLOC_H) @@ -36,6 +37,8 @@ #include "events.h" #endif +#define FT_KEYPART (MAX_FIELDS+10) + static const char *lock_descriptions[] = { /* TL_UNLOCK */ "No lock", @@ -225,8 +228,6 @@ TEST_join(JOIN *join) } -#define FT_KEYPART (MAX_FIELDS+10) - static void print_keyuse(KEYUSE *keyuse) { char buff[256]; @@ -263,7 +264,6 @@ void print_keyuse_array(DYNAMIC_ARRAY *keyuse_array) DBUG_UNLOCK_FILE; } - /* Print the current state during query optimization. @@ -655,3 +655,24 @@ Memory allocated by threads: %s\n", puts(""); fflush(stdout); } + +void print_keyuse_array_for_trace(THD *thd, DYNAMIC_ARRAY *keyuse_array) +{ + Json_writer_object wrapper(thd); + Json_writer_array trace_key_uses(thd, "ref_optimizer_key_uses"); + for(uint i=0; i < keyuse_array->elements; i++) + { + KEYUSE *keyuse= (KEYUSE*)dynamic_array_ptr(keyuse_array, i); + Json_writer_object keyuse_elem(thd); + keyuse_elem.add_table_name(keyuse->table->reginfo.join_tab); + keyuse_elem.add("field", (keyuse->keypart == FT_KEYPART) ? "<fulltext>" + : (keyuse->is_for_hash_join() + ? keyuse->table->field[keyuse->keypart] + ->field_name.str + : keyuse->table->key_info[keyuse->key] + .key_part[keyuse->keypart] + .field->field_name.str)); + keyuse_elem.add("equals",keyuse->val); + keyuse_elem.add("null_rejecting",keyuse->null_rejecting); + } +} diff --git a/sql/sql_test.h b/sql/sql_test.h index 867582a9569..cbef581b784 100644 --- a/sql/sql_test.h +++ b/sql/sql_test.h @@ -17,6 +17,7 @@ #define SQL_TEST_INCLUDED #include "mysqld.h" +#include "opt_trace_context.h" class JOIN; struct TABLE_LIST; @@ -34,6 +35,7 @@ void print_keyuse_array(DYNAMIC_ARRAY *keyuse_array); void print_sjm(SJ_MATERIALIZATION_INFO *sjm); void dump_TABLE_LIST_graph(SELECT_LEX *select_lex, TABLE_LIST* tl); #endif +void print_keyuse_array_for_trace(THD *thd, DYNAMIC_ARRAY *keyuse_array); void mysql_print_status(); #endif /* SQL_TEST_INCLUDED */ diff --git a/sql/sql_view.cc b/sql/sql_view.cc index e475a3d3719..31032c5cd5e 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -36,6 +36,7 @@ #include "datadict.h" // dd_frm_is_view() #include "sql_derived.h" #include "sql_cte.h" // check_dependencies_in_with_clauses() +#include "opt_trace.h" #define MD5_BUFF_LENGTH 33 @@ -1420,6 +1421,15 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, goto err; /* + Check rights to run commands which show underlying tables. + In the optimizer trace we would not like to show trace for + cases when the current user does not have rights for the + underlying tables. + */ + if (!table->prelocking_placeholder) + opt_trace_disable_if_no_view_access(thd, table, view_tables); + + /* Check rights to run commands (ANALYZE SELECT, EXPLAIN SELECT & SHOW CREATE) which show underlying tables. Skip this step if we are opening view for prelocking only. diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 7241685fb61..a8b557c90c8 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -53,6 +53,7 @@ #include <myisam.h> #include "debug_sync.h" // DEBUG_SYNC #include "sql_show.h" +#include "opt_trace_context.h" #include "log_event.h" #ifdef WITH_PERFSCHEMA_STORAGE_ENGINE @@ -2542,6 +2543,23 @@ static Sys_var_flagset Sys_optimizer_switch( NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(fix_optimizer_switch)); +static Sys_var_flagset Sys_optimizer_trace( + "optimizer_trace", + "Controls tracing of the Optimizer:" + " optimizer_trace=option=val[,option=val...], where option is one of" + " {enabled}" + " and val is one of {on, off, default}", + SESSION_VAR(optimizer_trace), CMD_LINE(REQUIRED_ARG), + Opt_trace_context::flag_names, DEFAULT(Opt_trace_context::FLAG_DEFAULT)); + // @see set_var::is_var_optimizer_trace() +export sys_var *Sys_optimizer_trace_ptr = &Sys_optimizer_trace; + +static Sys_var_ulong Sys_optimizer_trace_max_mem_size( + "optimizer_trace_max_mem_size", + "Maximum allowed size of an optimizer trace", + SESSION_VAR(optimizer_trace_max_mem_size), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, ULONG_MAX), DEFAULT(1024 * 1024), BLOCK_SIZE(1)); + static Sys_var_charptr Sys_pid_file( "pid_file", "Pid file used by safe_mysqld", READ_ONLY GLOBAL_VAR(pidfile_name_ptr), CMD_LINE(REQUIRED_ARG), diff --git a/sql/table.cc b/sql/table.cc index 375f7a3f65f..d5c88b226c7 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -44,6 +44,7 @@ #include "sql_cte.h" #include "ha_sequence.h" #include "sql_show.h" +#include "opt_trace.h" /* For MySQL 5.7 virtual fields */ #define MYSQL57_GENERATED_FIELD 128 @@ -5714,6 +5715,7 @@ bool TABLE_LIST::prepare_security(THD *thd) if (prepare_view_security_context(thd)) DBUG_RETURN(TRUE); thd->security_ctx= find_view_security_context(thd); + opt_trace_disable_if_no_security_context_access(thd); while ((tbl= tb++)) { DBUG_ASSERT(tbl->referencing_view); |