diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2019-05-28 15:56:24 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2019-05-28 15:56:24 +0300 |
commit | d59e15bdb96bd24dadaed39bba25fcb0c0bbf8f4 (patch) | |
tree | f371cddd0134ed01964b7b3e051d890b2cec20f1 /sql | |
parent | 88157247fc5920ef77cfa96aebd33460d86b3905 (diff) | |
parent | 8358c6f03edb941be488f009c2bd0eb9df47e8c5 (diff) | |
download | mariadb-git-d59e15bdb96bd24dadaed39bba25fcb0c0bbf8f4.tar.gz |
Merge 10.1 into 10.2
Diffstat (limited to 'sql')
-rw-r--r-- | sql/mysqld.h | 8 | ||||
-rw-r--r-- | sql/opt_subselect.cc | 73 | ||||
-rw-r--r-- | sql/sql_const.h | 8 | ||||
-rw-r--r-- | sql/sql_parse.cc | 7 | ||||
-rw-r--r-- | sql/sql_select.cc | 136 |
5 files changed, 141 insertions, 91 deletions
diff --git a/sql/mysqld.h b/sql/mysqld.h index 8faac6f6f5d..26229c8cc5d 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -1,5 +1,5 @@ /* Copyright (c) 2006, 2016, Oracle and/or its affiliates. - Copyright (c) 2010, 2017, MariaDB Corporation. + Copyright (c) 2010, 2019, MariaDB Corporation. 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 @@ -566,11 +566,11 @@ extern mysql_mutex_t LOCK_item_func_sleep, LOCK_status, LOCK_show_status, LOCK_error_log, LOCK_delayed_insert, LOCK_short_uuid_generator, LOCK_delayed_status, LOCK_delayed_create, LOCK_crypt, LOCK_timezone, - LOCK_slave_list, LOCK_active_mi, LOCK_manager, - LOCK_global_system_variables, LOCK_user_conn, + LOCK_slave_list, LOCK_active_mi, LOCK_manager, LOCK_user_conn, LOCK_prepared_stmt_count, LOCK_error_messages, LOCK_connection_count, LOCK_slave_background; -extern MYSQL_PLUGIN_IMPORT mysql_mutex_t LOCK_thread_count; +extern MYSQL_PLUGIN_IMPORT mysql_mutex_t LOCK_thread_count, + LOCK_global_system_variables; extern mysql_mutex_t LOCK_start_thread; #ifdef HAVE_OPENSSL extern char* des_key_file; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 38c4d622db1..99fa1c7344f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1,5 +1,5 @@ /* - Copyright (c) 2010, 2015, MariaDB + Copyright (c) 2010, 2019, MariaDB 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 @@ -28,6 +28,7 @@ #include <my_global.h> #include "sql_base.h" +#include "sql_const.h" #include "sql_select.h" #include "filesort.h" #include "opt_subselect.h" @@ -523,7 +524,7 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, if (optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION) && // 0 !child_select->is_part_of_union() && // 1 parent_unit->first_select()->leaf_tables.elements && // 2 - child_select->outer_select()->leaf_tables.elements && // 2A + child_select->outer_select()->table_list.first && // 2A subquery_types_allow_materialization(in_subs) && (in_subs->is_top_level_item() || //3 optimizer_flag(thd, @@ -1383,8 +1384,8 @@ void get_delayed_table_estimates(TABLE *table, *startup_cost= item->jtbm_read_time; /* Calculate cost of scanning the temptable */ - double data_size= item->jtbm_record_count * - hash_sj_engine->tmp_table->s->reclength; + double data_size= COST_MULT(item->jtbm_record_count, + hash_sj_engine->tmp_table->s->reclength); /* Do like in handler::read_time */ *scan_time= data_size/IO_SIZE + 2; } @@ -2463,7 +2464,8 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map) int tableno; double rows= 1.0; while ((tableno = tm_it.next_bit()) != Table_map_iterator::BITMAP_END) - rows *= join->map2table[tableno]->table->quick_condition_rows; + rows= COST_MULT(rows, + join->map2table[tableno]->table->quick_condition_rows); sjm->rows= MY_MIN(sjm->rows, rows); } memcpy((uchar*) sjm->positions, @@ -2576,7 +2578,7 @@ static uint get_tmp_table_rec_length(Ref_ptr_array p_items, uint elements) static double get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size) { - if (row_count * row_size > thd->variables.max_heap_table_size) + if (row_count > thd->variables.max_heap_table_size / (double) row_size) return (double) DISK_TEMPTABLE_LOOKUP_COST; else return (double) HEAP_TEMPTABLE_LOOKUP_COST; @@ -2982,8 +2984,11 @@ bool Sj_materialization_picker::check_qep(JOIN *join, } double mat_read_time= prefix_cost.total_cost(); - mat_read_time += mat_info->materialization_cost.total_cost() + - prefix_rec_count * mat_info->lookup_cost.total_cost(); + mat_read_time= + COST_ADD(mat_read_time, + COST_ADD(mat_info->materialization_cost.total_cost(), + COST_MULT(prefix_rec_count, + mat_info->lookup_cost.total_cost()))); /* NOTE: When we pick to use SJM[-Scan] we don't memcpy its POSITION @@ -3023,9 +3028,12 @@ bool Sj_materialization_picker::check_qep(JOIN *join, } /* Add materialization cost */ - prefix_cost += mat_info->materialization_cost.total_cost() + - prefix_rec_count * mat_info->scan_cost.total_cost(); - prefix_rec_count *= mat_info->rows; + prefix_cost= + COST_ADD(prefix_cost, + COST_ADD(mat_info->materialization_cost.total_cost(), + COST_MULT(prefix_rec_count, + mat_info->scan_cost.total_cost()))); + prefix_rec_count= COST_MULT(prefix_rec_count, mat_info->rows); uint i; table_map rem_tables= remaining_tables; @@ -3039,8 +3047,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join, { best_access_path(join, join->positions[i].table, rem_tables, i, disable_jbuf, prefix_rec_count, &curpos, &dummy); - prefix_rec_count *= curpos.records_read; - prefix_cost += curpos.read_time; + prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read); + prefix_cost= COST_ADD(prefix_cost, curpos.read_time); } *strategy= SJ_OPT_MATERIALIZE_SCAN; @@ -3347,16 +3355,18 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join, for (uint j= first_dupsweedout_table; j <= idx; j++) { POSITION *p= join->positions + j; - current_fanout *= p->records_read; - dups_cost += p->read_time + current_fanout / TIME_FOR_COMPARE; + current_fanout= COST_MULT(current_fanout, p->records_read); + dups_cost= COST_ADD(dups_cost, + COST_ADD(p->read_time, + current_fanout / TIME_FOR_COMPARE)); if (p->table->emb_sj_nest) { - sj_inner_fanout *= p->records_read; + sj_inner_fanout= COST_MULT(sj_inner_fanout, p->records_read); dups_removed_fanout |= p->table->table->map; } else { - sj_outer_fanout *= p->records_read; + sj_outer_fanout= COST_MULT(sj_outer_fanout, p->records_read); temptable_rec_size += p->table->table->file->ref_length; } } @@ -3375,12 +3385,13 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join, sj_outer_fanout, temptable_rec_size); - double write_cost= join->positions[first_tab].prefix_record_count* - sj_outer_fanout * one_write_cost; - double full_lookup_cost= join->positions[first_tab].prefix_record_count* - sj_outer_fanout* sj_inner_fanout * - one_lookup_cost; - dups_cost += write_cost + full_lookup_cost; + double write_cost= COST_MULT(join->positions[first_tab].prefix_record_count, + sj_outer_fanout * one_write_cost); + double full_lookup_cost= + COST_MULT(join->positions[first_tab].prefix_record_count, + COST_MULT(sj_outer_fanout, + sj_inner_fanout * one_lookup_cost)); + dups_cost= COST_ADD(dups_cost, COST_ADD(write_cost, full_lookup_cost)); *read_time= dups_cost; *record_count= prefix_rec_count * sj_outer_fanout; @@ -3527,8 +3538,8 @@ static void recalculate_prefix_record_count(JOIN *join, uint start, uint end) if (j == join->const_tables) prefix_count= 1.0; else - prefix_count= join->best_positions[j-1].prefix_record_count * - join->best_positions[j-1].records_read; + prefix_count= COST_MULT(join->best_positions[j-1].prefix_record_count, + join->best_positions[j-1].records_read); join->best_positions[j].prefix_record_count= prefix_count; } @@ -5874,14 +5885,16 @@ bool JOIN::choose_subquery_plan(table_map join_tables) The cost of executing the subquery and storing its result in an indexed temporary table. */ - double materialization_cost= inner_read_time_1 + - write_cost * inner_record_count_1; + double materialization_cost= COST_ADD(inner_read_time_1, + COST_MULT(write_cost, + inner_record_count_1)); - materialize_strategy_cost= materialization_cost + - outer_lookup_keys * lookup_cost; + materialize_strategy_cost= COST_ADD(materialization_cost, + COST_MULT(outer_lookup_keys, + lookup_cost)); /* C.2 Compute the cost of the IN=>EXISTS strategy. */ - in_exists_strategy_cost= outer_lookup_keys * inner_read_time_2; + in_exists_strategy_cost= COST_MULT(outer_lookup_keys, inner_read_time_2); /* C.3 Compare the costs and choose the cheaper strategy. */ if (materialize_strategy_cost >= in_exists_strategy_cost) diff --git a/sql/sql_const.h b/sql/sql_const.h index b4fdbaafda5..65fe94135ec 100644 --- a/sql/sql_const.h +++ b/sql/sql_const.h @@ -236,6 +236,14 @@ #define HEAP_TEMPTABLE_LOOKUP_COST 0.05 #define DISK_TEMPTABLE_LOOKUP_COST 1.0 + +#define COST_MAX (DBL_MAX * (1.0 - DBL_EPSILON)) + +#define COST_ADD(c,d) (COST_MAX - (d) > (c) ? (c) + (d) : COST_MAX) + +#define COST_MULT(c,f) (COST_MAX / (f) > (c) ? (c) * (f) : COST_MAX) + + #define MY_CHARSET_BIN_MB_MAXLEN 1 /** Don't pack string keys shorter than this (if PACK_KEYS=1 isn't used). */ diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index d26a19ab4e3..6cec9902491 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4530,6 +4530,13 @@ end_with_restore_list: */ /* Skip first table, which is the table we are inserting in */ TABLE_LIST *second_table= first_table->next_local; + /* + This is a hack: this leaves select_lex->table_list in an inconsistent + state as 'elements' does not contain number of elements in the list. + Moreover, if second_table == NULL then 'next' becomes invalid. + TODO: fix it by removing the front element (restoring of it should + be done properly as well) + */ select_lex->table_list.first= second_table; select_lex->context.table_list= select_lex->context.first_name_resolution_table= second_table; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 614af3ee73c..182e913c16c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1,5 +1,5 @@ -/* Copyright (c) 2000, 2016 Oracle and/or its affiliates. - Copyright (c) 2009, 2019 MariaDB Corporation +/* Copyright (c) 2000, 2016, Oracle and/or its affiliates. + Copyright (c) 2009, 2019, MariaDB Corporation. 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 @@ -6514,7 +6514,7 @@ best_access_path(JOIN *join, else tmp= table->file->read_time(key, 1, (ha_rows) MY_MIN(tmp,s->worst_seeks)); - tmp*= record_count; + tmp= COST_MULT(tmp, record_count); } } else @@ -6679,18 +6679,18 @@ best_access_path(JOIN *join, else tmp= table->file->read_time(key, 1, (ha_rows) MY_MIN(tmp,s->worst_seeks)); - tmp*= record_count; + tmp= COST_MULT(tmp, record_count); } else - tmp= best_time; // Do nothing + tmp= best_time; // Do nothing } - tmp += s->startup_cost; + tmp= COST_ADD(tmp, s->startup_cost); loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp); } /* not ft_key */ if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE) { - best_time= tmp + records/(double) TIME_FOR_COMPARE; + best_time= COST_ADD(tmp, records/(double) TIME_FOR_COMPARE); best= tmp; best_records= records; best_key= start_key; @@ -6724,14 +6724,18 @@ best_access_path(JOIN *join, use_cond_selectivity); tmp= s->quick ? s->quick->read_time : s->scan_time(); - tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + tmp= COST_ADD(tmp, cmp_time); /* We read the table as many times as join buffer becomes full. */ - tmp*= (1.0 + floor((double) cache_record_length(join,idx) * - record_count / - (double) thd->variables.join_buff_size)); - best_time= tmp + - (record_count*join_sel) / TIME_FOR_COMPARE * rnd_records; + + double refills= (1.0 + floor((double) cache_record_length(join,idx) * + record_count / + (double) thd->variables.join_buff_size)); + tmp= COST_MULT(tmp, refills); + best_time= COST_ADD(tmp, + COST_MULT((record_count*join_sel) / TIME_FOR_COMPARE, + rnd_records)); best= tmp; records= rnd_records; best_key= hj_start_key; @@ -6757,7 +6761,8 @@ best_access_path(JOIN *join, 'range' access using index IDX, and the best way to perform 'ref' access is to use the same index IDX, with the same or more key parts. (note: it is not clear how this rule is/should be extended to - index_merge quick selects) + index_merge quick selects). Also if we have a hash join we prefer that + over a table scan (3) See above note about InnoDB. (4) NOT ("FORCE INDEX(...)" is used for table and there is 'ref' access path, but there is no quick select) @@ -6773,6 +6778,7 @@ best_access_path(JOIN *join, be used for cases with small datasets, which is annoying. */ if ((records >= s->found_records || best > s->read_time) && // (1) + !(best_key && best_key->key == MAX_KEY) && // (2) !(s->quick && best_key && s->quick->index == best_key->key && // (2) best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2) !((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3) @@ -6800,9 +6806,9 @@ best_access_path(JOIN *join, access (see first else-branch below), but we don't take it into account here for range/index_merge access. Find out why this is so. */ - tmp= record_count * - (s->quick->read_time + - (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE); + double cmp_time= (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE; + tmp= COST_MULT(record_count, + COST_ADD(s->quick->read_time, cmp_time)); loose_scan_opt.check_range_access(join, idx, s->quick); } @@ -6821,16 +6827,15 @@ best_access_path(JOIN *join, - read the whole table record - skip rows which does not satisfy join condition */ - tmp= record_count * - (tmp + - (s->records - rnd_records)/(double) TIME_FOR_COMPARE); + double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + tmp= COST_MULT(record_count, COST_ADD(tmp,cmp_time)); } else { - /* We read the table as many times as join buffer becomes full. */ - tmp*= (1.0 + floor((double) cache_record_length(join,idx) * - record_count / - (double) thd->variables.join_buff_size)); + double refills= (1.0 + floor((double) cache_record_length(join,idx) * + (record_count / + (double) thd->variables.join_buff_size))); + tmp= COST_MULT(tmp, refills); /* We don't make full cartesian product between rows in the scanned table and existing records because we skip all rows from the @@ -6838,7 +6843,8 @@ best_access_path(JOIN *join, we read the table (see flush_cached_records for details). Here we take into account cost to read and skip these records. */ - tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + tmp= COST_ADD(tmp, cmp_time); } } @@ -6849,9 +6855,9 @@ best_access_path(JOIN *join, tmp give us total cost of using TABLE SCAN */ if (best == DBL_MAX || - (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records < + COST_ADD(tmp, record_count/(double) TIME_FOR_COMPARE*rnd_records) < (best_key->is_for_hash_join() ? best_time : - best + record_count/(double) TIME_FOR_COMPARE*records))) + COST_ADD(best, record_count/(double) TIME_FOR_COMPARE*records))) { /* If the table has a range (s->quick is set) make_join_select() @@ -7362,9 +7368,10 @@ optimize_straight_join(JOIN *join, table_map join_tables) join->positions + idx, &loose_scan_pos); /* compute the cost of the new plan extended with 's' */ - record_count*= join->positions[idx].records_read; - read_time+= join->positions[idx].read_time + - record_count / (double) TIME_FOR_COMPARE; + record_count= COST_MULT(record_count, join->positions[idx].records_read); + read_time= COST_ADD(read_time, + COST_ADD(join->positions[idx].read_time, + record_count / (double) TIME_FOR_COMPARE)); advance_sj_state(join, join_tables, idx, &record_count, &read_time, &loose_scan_pos); @@ -7554,9 +7561,10 @@ greedy_search(JOIN *join, swap_variables(JOIN_TAB*, join->best_ref[idx], join->best_ref[best_idx]); /* compute the cost of the new plan extended with 'best_table' */ - record_count*= join->positions[idx].records_read; - read_time+= join->positions[idx].read_time + - record_count / (double) TIME_FOR_COMPARE; + record_count= COST_MULT(record_count, join->positions[idx].records_read); + read_time= COST_ADD(read_time, + COST_ADD(join->positions[idx].read_time, + record_count / (double) TIME_FOR_COMPARE)); remaining_tables&= ~(best_table->table->map); --size_remain; @@ -7663,11 +7671,13 @@ void JOIN::get_partial_cost_and_fanout(int end_tab_idx, } if (tab->records_read && (cur_table_map & filter_map)) { - record_count *= tab->records_read; - read_time += tab->read_time + record_count / (double) TIME_FOR_COMPARE; + record_count= COST_MULT(record_count, tab->records_read); + read_time= COST_ADD(read_time, + COST_ADD(tab->read_time, + record_count / (double) TIME_FOR_COMPARE)); if (tab->emb_sj_nest) - sj_inner_fanout *= tab->records_read; - } + sj_inner_fanout= COST_MULT(sj_inner_fanout, tab->records_read); + } if (i == last_sj_table) { @@ -7705,8 +7715,8 @@ void JOIN::get_prefix_cost_and_fanout(uint n_tables, { if (best_positions[i].records_read) { - record_count *= best_positions[i].records_read; - read_time += best_positions[i].read_time; + record_count= COST_MULT(record_count, best_positions[i].records_read); + read_time= COST_ADD(read_time, best_positions[i].read_time); } } *read_time_arg= read_time;// + record_count / TIME_FOR_COMPARE; @@ -8267,13 +8277,12 @@ best_extension_by_limited_search(JOIN *join, best_access_path(join, s, remaining_tables, idx, disable_jbuf, record_count, join->positions + idx, &loose_scan_pos); - /* Compute the cost of extending the plan with 's', avoid overflow */ - if (position->records_read < DBL_MAX / record_count) - current_record_count= record_count * position->records_read; - else - current_record_count= DBL_MAX; - current_read_time=read_time + position->read_time + - current_record_count / (double) TIME_FOR_COMPARE; + /* Compute the cost of extending the plan with 's' */ + current_record_count= COST_MULT(record_count, position->records_read); + current_read_time=COST_ADD(read_time, + COST_ADD(position->read_time, + current_record_count / + (double) TIME_FOR_COMPARE)); advance_sj_state(join, remaining_tables, idx, ¤t_record_count, ¤t_read_time, &loose_scan_pos); @@ -8355,12 +8364,12 @@ best_extension_by_limited_search(JOIN *join, if (join->sort_by_table && join->sort_by_table != join->positions[join->const_tables].table->table) - /* - We may have to make a temp table, note that this is only a - heuristic since we cannot know for sure at this point. + /* + We may have to make a temp table, note that this is only a + heuristic since we cannot know for sure at this point. Hence it may be wrong. */ - current_read_time+= current_record_count; + current_read_time= COST_ADD(current_read_time, current_record_count); if (current_read_time < join->best_read) { memcpy((uchar*) join->best_positions, (uchar*) join->positions, @@ -8678,8 +8687,8 @@ prev_record_reads(POSITION *positions, uint idx, table_map found_ref) #max_nested_outer_joins=64-1) will not make it any more precise. */ if (pos->records_read) - found*= pos->records_read; - } + found= COST_MULT(found, pos->records_read); + } } return found; } @@ -14824,8 +14833,20 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top, table->table->maybe_null= FALSE; table->outer_join= 0; if (!(straight_join || table->straight)) - table->dep_tables= table->embedding && !table->embedding->sj_subq_pred ? - table->embedding->dep_tables : 0; + { + table->dep_tables= 0; + TABLE_LIST *embedding= table->embedding; + while (embedding) + { + if (embedding->nested_join->join_list.head()->outer_join) + { + if (!embedding->sj_subq_pred) + table->dep_tables= embedding->dep_tables; + break; + } + embedding= embedding->embedding; + } + } if (table->on_expr) { /* Add ON expression to the WHERE or upper-level ON condition. */ @@ -15348,11 +15369,12 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, pos= loose_scan_pos; reopt_remaining_tables &= ~rs->table->map; - rec_count *= pos.records_read; - cost += pos.read_time; + rec_count= COST_MULT(rec_count, pos.records_read); + cost= COST_ADD(cost, pos.read_time); + if (!rs->emb_sj_nest) - *outer_rec_count *= pos.records_read; + *outer_rec_count= COST_MULT(*outer_rec_count, pos.records_read); } join->cur_sj_inner_tables= save_cur_sj_inner_tables; |