diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 484 |
1 files changed, 307 insertions, 177 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index debd1b07fe5..3544c26a5d6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -59,9 +59,9 @@ static bool make_join_statistics(JOIN *join, TABLE_LIST *leaves, COND *conds, static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse, JOIN_TAB *join_tab, uint tables, COND *conds, - COND_EQUAL *cond_equal, table_map table_map, SELECT_LEX *select_lex, st_sargable_param **sargables); +static bool sort_and_filter_keyuse(DYNAMIC_ARRAY *keyuse); static int sort_keyuse(KEYUSE *a,KEYUSE *b); static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, table_map used_tables); @@ -236,8 +236,6 @@ static bool update_sum_func(Item_sum **func); static void select_describe(JOIN *join, bool need_tmp_table,bool need_order, bool distinct, const char *message=NullS); static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab); -void get_partial_join_cost(JOIN *join, uint idx, double *read_time_arg, - double *record_count_arg); static uint make_join_orderinfo(JOIN *join); static int join_read_record_no_init(JOIN_TAB *tab); @@ -843,6 +841,7 @@ JOIN::optimize() "Impossible HAVING" : "Impossible WHERE"; tables= 0; error= 0; + choose_tableless_subquery_plan(); goto setup_subq_exit; } } @@ -887,12 +886,13 @@ JOIN::optimize() */ if ((res=opt_sum_query(select_lex->leaf_tables, all_fields, conds))) { - if (res == HA_ERR_KEY_NOT_FOUND) + if (res == HA_ERR_KEY_NOT_FOUND || res < 0) { DBUG_PRINT("info",("No matching min/max row")); zero_result_cause= "No matching min/max row"; tables= 0; error=0; + choose_tableless_subquery_plan(); goto setup_subq_exit; } if (res > 1) @@ -901,14 +901,7 @@ JOIN::optimize() DBUG_PRINT("error",("Error from opt_sum_query")); DBUG_RETURN(1); } - if (res < 0) - { - DBUG_PRINT("info",("No matching min/max row")); - zero_result_cause= "No matching min/max row"; - tables= 0; - error=0; - goto setup_subq_exit; - } + DBUG_PRINT("info",("Select tables optimized away")); zero_result_cause= "Select tables optimized away"; tables_list= 0; // All tables resolved @@ -933,17 +926,14 @@ JOIN::optimize() QT_ORDINARY);); conds= table_independent_conds; } - goto setup_subq_exit; } } if (!tables_list) { DBUG_PRINT("info",("No tables")); error= 0; - /* Create all structures needed for materialized subquery execution. */ - if (setup_subquery_materialization()) - DBUG_RETURN(1); - DBUG_RETURN(0); + choose_tableless_subquery_plan(); + goto setup_subq_exit; } error= -1; // Error is sent to client sort_by_table= get_sort_by_table(order, group_list, select_lex->leaf_tables); @@ -1300,8 +1290,7 @@ JOIN::optimize() if (!(select_options & SELECT_DESCRIBE)) init_ftfuncs(thd, select_lex, test(order)); - /* Create all structures needed for materialized subquery execution. */ - if (setup_subquery_materialization()) + if (optimize_unflattened_subqueries()) DBUG_RETURN(1); int res; @@ -1396,6 +1385,32 @@ JOIN::optimize() if (join_tab->is_using_loose_index_scan()) tmp_table_param.precomputed_group_by= TRUE; + error= 0; + DBUG_RETURN(0); + +setup_subq_exit: + /* + Even with zero matching rows, subqueries in the HAVING clause may + need to be evaluated if there are aggregate functions in the query. + */ + if (optimize_unflattened_subqueries()) + DBUG_RETURN(1); + error= 0; + DBUG_RETURN(0); +} + + +/** + Create and initialize objects neeed for the execution of a query plan. +*/ + +int JOIN::init_execution() +{ + DBUG_ENTER("JOIN::init_execution"); + + DBUG_ASSERT(optimized); + initialized= true; + /* Create a tmp table if distinct or if the sort is too complicated */ if (need_tmp) { @@ -1428,7 +1443,7 @@ JOIN::optimize() select_options, tmp_rows_limit, (char *) ""))) - { + { DBUG_RETURN(1); } @@ -1514,19 +1529,6 @@ JOIN::optimize() DBUG_RETURN(-1); /* purecov: inspected */ } - error= 0; - DBUG_RETURN(0); - -setup_subq_exit: - /* - Even with zero matching rows, subqueries in the HAVING clause may - need to be evaluated if there are aggregate functions in the - query. If we have planned to materialize the subquery, we need to - set it up properly before prematurely leaving optimize(). - */ - if (setup_subquery_materialization()) - DBUG_RETURN(1); - error= 0; DBUG_RETURN(0); } @@ -1745,6 +1747,9 @@ JOIN::exec() int tmp_error; DBUG_ENTER("JOIN::exec"); + if (!initialized && init_execution()) + DBUG_VOID_RETURN; + thd_proc_info(thd, "executing"); error= 0; if (procedure) @@ -2561,51 +2566,6 @@ err: } -/** - Setup for execution all subqueries of a query, for which the optimizer - chose hash semi-join. - - @details Iterate over all subqueries of the query, and if they are under an - IN predicate, and the optimizer chose to compute it via hash semi-join: - - try to initialize all data structures needed for the materialized execution - of the IN predicate, - - if this fails, then perform the IN=>EXISTS transformation which was - previously blocked during JOIN::prepare. - - This method is part of the "code generation" query processing phase. - - This phase must be called after substitute_for_best_equal_field() because - that function may replace items with other items from a multiple equality, - and we need to reference the correct items in the index access method of the - IN predicate. - - @return Operation status - @retval FALSE success. - @retval TRUE error occurred. -*/ - -bool JOIN::setup_subquery_materialization() -{ - for (SELECT_LEX_UNIT *un= select_lex->first_inner_unit(); un; - un= un->next_unit()) - { - for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select()) - { - Item_subselect *subquery_predicate= sl->master_unit()->item; - if (subquery_predicate && - subquery_predicate->substype() == Item_subselect::IN_SUBS) - { - Item_in_subselect *in_subs= (Item_in_subselect*) subquery_predicate; - if (in_subs->exec_method == Item_in_subselect::MATERIALIZATION && - in_subs->setup_engine()) - return TRUE; - } - } - } - return FALSE; -} - - /***************************************************************************** Create JOIN_TABS, make a guess about the table types, Approximate how many records will be used in each table @@ -2830,10 +2790,14 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, } if (conds || outer_join) + { if (update_ref_and_keys(join->thd, keyuse_array, stat, join->tables, - conds, join->cond_equal, - ~outer_join, join->select_lex, &sargables)) + conds, ~outer_join, join->select_lex, &sargables)) + goto error; + if (keyuse_array->elements && sort_and_filter_keyuse(keyuse_array)) goto error; + DBUG_EXECUTE("opt", print_keyuse_array(keyuse_array);); + } join->const_table_map= no_rows_const_tables; join->const_tables= const_count; @@ -3136,6 +3100,9 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, sizeof(POSITION)*join->const_tables); join->best_read=1.0; } + if (join->choose_subquery_plan(all_table_map & ~join->const_table_map)) + goto error; + /* Generate an execution plan from the found optimal join order. */ DBUG_RETURN(join->thd->killed || get_best_combination(join)); @@ -4086,11 +4053,10 @@ static void add_key_fields_for_nj(JOIN *join, TABLE_LIST *nested_join_table, static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, - uint tables, COND *cond, COND_EQUAL *cond_equal, - table_map normal_tables, SELECT_LEX *select_lex, - SARGABLE_PARAM **sargables) + uint tables, COND *cond, table_map normal_tables, + SELECT_LEX *select_lex, SARGABLE_PARAM **sargables) { - uint and_level,i,found_eq_constant; + uint and_level,i; KEY_FIELD *key_fields, *end, *field; uint sz; uint m= max(select_lex->max_equal_elems,1); @@ -4186,67 +4152,76 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, return TRUE; } - /* - Sort the array of possible keys and remove the following key parts: - - ref if there is a keypart which is a ref and a const. - (e.g. if there is a key(a,b) and the clause is a=3 and b=7 and b=t2.d, - then we skip the key part corresponding to b=t2.d) - - keyparts without previous keyparts - (e.g. if there is a key(a,b,c) but only b < 5 (or a=2 and c < 3) is - used in the query, we drop the partial key parts from consideration). - Special treatment for ft-keys. - */ - if (keyuse->elements) - { - KEYUSE key_end,*prev,*save_pos,*use; + return FALSE; +} - my_qsort(keyuse->buffer,keyuse->elements,sizeof(KEYUSE), - (qsort_cmp) sort_keyuse); - bzero((char*) &key_end,sizeof(key_end)); /* Add for easy testing */ - if (insert_dynamic(keyuse,(uchar*) &key_end)) - return TRUE; +/** + Sort the array of possible keys and remove the following key parts: + - ref if there is a keypart which is a ref and a const. + (e.g. if there is a key(a,b) and the clause is a=3 and b=7 and b=t2.d, + then we skip the key part corresponding to b=t2.d) + - keyparts without previous keyparts + (e.g. if there is a key(a,b,c) but only b < 5 (or a=2 and c < 3) is + used in the query, we drop the partial key parts from consideration). + Special treatment for ft-keys. +*/ - use=save_pos=dynamic_element(keyuse,0,KEYUSE*); - prev= &key_end; - found_eq_constant=0; - for (i=0 ; i < keyuse->elements-1 ; i++,use++) +static bool sort_and_filter_keyuse(DYNAMIC_ARRAY *keyuse) +{ + KEYUSE key_end, *prev, *save_pos, *use; + uint found_eq_constant, i; + + DBUG_ASSERT(keyuse->elements); + + my_qsort(keyuse->buffer, keyuse->elements, sizeof(KEYUSE), + (qsort_cmp) sort_keyuse); + + bzero((char*) &key_end, sizeof(key_end)); /* Add for easy testing */ + if (insert_dynamic(keyuse, (uchar*) &key_end)) + return TRUE; + + use= save_pos= dynamic_element(keyuse,0,KEYUSE*); + prev= &key_end; + found_eq_constant= 0; + + for (i=0 ; i < keyuse->elements-1 ; i++,use++) + { + if (!use->used_tables && use->optimize != KEY_OPTIMIZE_REF_OR_NULL) + use->table->const_key_parts[use->key]|= use->keypart_map; + if (use->keypart != FT_KEYPART) { - if (!use->used_tables && use->optimize != KEY_OPTIMIZE_REF_OR_NULL) - use->table->const_key_parts[use->key]|= use->keypart_map; - if (use->keypart != FT_KEYPART) + if (use->key == prev->key && use->table == prev->table) { - if (use->key == prev->key && use->table == prev->table) - { - if (prev->keypart+1 < use->keypart || - (prev->keypart == use->keypart && found_eq_constant)) - continue; /* remove */ - } - else if (use->keypart != 0) // First found must be 0 - continue; + if (prev->keypart+1 < use->keypart || + (prev->keypart == use->keypart && found_eq_constant)) + continue; /* remove */ } + else if (use->keypart != 0) // First found must be 0 + continue; + } #ifdef HAVE_valgrind - /* Valgrind complains about overlapped memcpy when save_pos==use. */ - if (save_pos != use) + /* Valgrind complains about overlapped memcpy when save_pos==use. */ + if (save_pos != use) #endif - *save_pos= *use; - prev=use; - found_eq_constant= !use->used_tables; - /* Save ptr to first use */ - if (!use->table->reginfo.join_tab->keyuse) - use->table->reginfo.join_tab->keyuse=save_pos; - use->table->reginfo.join_tab->checked_keys.set_bit(use->key); - save_pos++; - } - i=(uint) (save_pos-(KEYUSE*) keyuse->buffer); - VOID(set_dynamic(keyuse,(uchar*) &key_end,i)); - keyuse->elements=i; - } - DBUG_EXECUTE("opt", print_keyuse_array(keyuse);); + *save_pos= *use; + prev= use; + found_eq_constant= !use->used_tables; + /* Save ptr to first use */ + if (!use->table->reginfo.join_tab->keyuse) + use->table->reginfo.join_tab->keyuse=save_pos; + use->table->reginfo.join_tab->checked_keys.set_bit(use->key); + save_pos++; + } + i= (uint) (save_pos-(KEYUSE*) keyuse->buffer); + VOID(set_dynamic(keyuse,(uchar*) &key_end,i)); + keyuse->elements= i; + return FALSE; } + /** Update some values in keyuse for faster choose_plan() loop. */ @@ -5433,40 +5408,43 @@ greedy_search(JOIN *join, } -/* - Calculate a cost of given partial join order +/** + Calculate a cost of given partial join order in join->positions. - SYNOPSIS - get_partial_join_cost() - join IN Join to use. join->positions holds the - partial join order - idx IN # tables in the partial join order - read_time_arg OUT Store read time here - record_count_arg OUT Store record count here - - DESCRIPTION + @param n_tables[in] # tables in the partial join order after the last + constant table + @param read_time_arg[out] store read time here + @param record_count_arg[out] store record count here - This is needed for semi-join materialization code. The idea is that - we detect sj-materialization after we've put all sj-inner tables into - the join prefix + @note + When used by semi-join materialization code the idea is that we + detect sj-materialization after we've put all sj-inner tables into + the join prefix. prefix-tables semi-join-inner-tables tN ^--we're here and we'll need to get the cost of prefix-tables prefix again. + + When used with non-flattened subqueries, the method computes the + total cost of query plan. + + @returns + read_time_arg and record_count_arg contain the computed cost. */ -void get_partial_join_cost(JOIN *join, uint n_tables, double *read_time_arg, - double *record_count_arg) +void JOIN::get_partial_join_cost(uint n_tables, + double *read_time_arg, double *record_count_arg) { double record_count= 1; double read_time= 0.0; - for (uint i= join->const_tables; i < n_tables + join->const_tables ; i++) + + for (uint i= const_tables; i < n_tables; i++) { - if (join->best_positions[i].records_read) + if (best_positions[i].records_read) { - record_count *= join->best_positions[i].records_read; - read_time += join->best_positions[i].read_time; + record_count *= best_positions[i].records_read; + read_time += best_positions[i].read_time; } } *read_time_arg= read_time;// + record_count / TIME_FOR_COMPARE; @@ -11428,10 +11406,30 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, { if (thd->is_fatal_error) goto err; // Got OOM - continue; // Some kindf of const item + continue; // Some kind of const item } if (type == Item::SUM_FUNC_ITEM) - ((Item_sum *) item)->result_field= new_field; + { + Item_sum *agg_item= (Item_sum *) item; + /* + Update the result field only if it has never been set, or if the + created temporary table is not to be used for subquery + materialization. + + The reason is that for subqueries that require materialization as part + of their plan, we create the 'external' temporary table needed for IN + execution, after the 'internal' temporary table needed for grouping. + Since both the external and the internal temporary tables are created + for the same list of SELECT fields of the subquery, setting + 'result_field' for each invocation of create_tmp_table overrides the + previous value of 'result_field'. + + The condition below prevents the creation of the external temp table + to override the 'result_field' that was set for the internal temp table. + */ + if (!agg_item->result_field || !param->materialized_subquery) + agg_item->result_field= new_field; + } tmp_from_field++; reclength+=new_field->pack_length(); if (!(new_field->flags & NOT_NULL_FLAG)) @@ -18895,28 +18893,9 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) bool res= 0; SELECT_LEX *first= unit->first_select(); - for (SELECT_LEX *sl= first; - sl; - sl= sl->next_select()) - { - // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only - uint8 uncacheable= (sl->uncacheable & ~UNCACHEABLE_EXPLAIN); - sl->type= (((&thd->lex->select_lex)==sl)? - (sl->first_inner_unit() || sl->next_select() ? - "PRIMARY" : "SIMPLE"): - ((sl == first)? - ((sl->linkage == DERIVED_TABLE_TYPE) ? - "DERIVED": - ((uncacheable & UNCACHEABLE_DEPENDENT) ? - "DEPENDENT SUBQUERY": - (uncacheable?"UNCACHEABLE SUBQUERY": - "SUBQUERY"))): - ((uncacheable & UNCACHEABLE_DEPENDENT) ? - "DEPENDENT UNION": - uncacheable?"UNCACHEABLE UNION": - "UNION"))); - sl->options|= SELECT_DESCRIBE; - } + for (SELECT_LEX *sl= first; sl; sl= sl->next_select()) + sl->set_explain_type(); + if (unit->is_union()) { unit->fake_select_lex->select_number= UINT_MAX; // jost for initialization @@ -19334,6 +19313,8 @@ bool JOIN::change_result(select_result *res) { DBUG_ENTER("JOIN::change_result"); result= res; + if (tmp_join) + tmp_join->result= res; if (!procedure && (result->prepare(fields_list, select_lex->master_unit()) || result->prepare2())) { @@ -19342,6 +19323,155 @@ bool JOIN::change_result(select_result *res) DBUG_RETURN(FALSE); } + +/** + Save a query execution plan so that the caller can revert to it if needed, + and reset the current query plan so that it can be reoptimized. + + @param save_keyuse[out] a KEYUSE array to save JOIN::keyuse + @param save_best_positions[out] array to save JOIN::best_positions + @param save_join_tab_keyuse[out] array of KEYUSE pointers to save each + JOIN_TAB::keyuse pointer + @param save_join_tab_checked_keys[out] an array of bitmaps to save + each JOIN_TAB::checked_keys + + @retval 0 OK + @retval 1 memory allocation error +*/ +int JOIN::save_query_plan(DYNAMIC_ARRAY *save_keyuse, + POSITION *save_best_positions, + KEYUSE **save_join_tab_keyuse, + key_map *save_join_tab_checked_keys) +{ + if (keyuse.elements) + { + DYNAMIC_ARRAY tmp_keyuse; + if (my_init_dynamic_array(save_keyuse, sizeof(KEYUSE), 20, 64)) + return 1; + /* Swap the current and the backup keyuse arrays. */ + tmp_keyuse= keyuse; + keyuse= (*save_keyuse); + (*save_keyuse)= tmp_keyuse; + + for (uint i= 0; i < tables; i++) + { + save_join_tab_keyuse[i]= join_tab[i].keyuse; + join_tab[i].keyuse= NULL; + save_join_tab_checked_keys[i]= join_tab[i].checked_keys; + join_tab[i].checked_keys.clear_all(); + } + } + memcpy((uchar*) save_best_positions, (uchar*) best_positions, + sizeof(POSITION) * (tables + 1)); + memset(best_positions, 0, sizeof(POSITION) * (tables + 1)); + return 0; +} + + +/** + Restore a query plan previously saved by the caller. + + @param save_keyuse a KEYUSE array to restore into JOIN::keyuse + @param save_best_positions array to restore into JOIN::best_positions + @param save_join_tab_keyuse array of KEYUSE pointers to restore each + JOIN_TAB::keyuse pointer + @param save_join_tab_checked_keys an array of bitmaps to restore + each JOIN_TAB::checked_keys +*/ + +void JOIN::restore_query_plan(DYNAMIC_ARRAY *save_keyuse, + POSITION *save_best_positions, + KEYUSE **save_join_tab_keyuse, + key_map *save_join_tab_checked_keys) +{ + if (save_keyuse->elements) + { + DYNAMIC_ARRAY tmp_keyuse; + tmp_keyuse= keyuse; + keyuse= (*save_keyuse); + (*save_keyuse)= tmp_keyuse; + delete_dynamic(save_keyuse); + + for (uint i= 0; i < tables; i++) + { + join_tab[i].keyuse= save_join_tab_keyuse[i]; + join_tab[i].checked_keys= save_join_tab_checked_keys[i]; + } + + } + memcpy((uchar*) best_positions, (uchar*) save_best_positions, + sizeof(POSITION) * (tables + 1)); +} + + +/** + Reoptimize a query plan taking into account an additional conjunct to the + WHERE clause. + + @param added_where An extra conjunct to the WHERE clause to reoptimize with + @param join_tables The set of tables to reoptimize + @param save_best_positions The join order of the original plan to restore to + if needed. + + @notes + Given a query plan that already optimized taking into account some WHERE clause + 'C', reoptimize this plan with a new WHERE clause 'C AND added_where'. The + reoptimization works as follows: + + 1. Call update_ref_and_keys *only* for the new conditions 'added_where' + that are about to be injected into the query. + 2. Expand if necessary the original KEYUSE array JOIN::keyuse to + accommodate the new REF accesses computed for the 'added_where' condition. + 3. Add the new KEYUSEs into JOIN::keyuse. + 4. Re-sort and re-filter the JOIN::keyuse array with the newly added + KEYUSE elements. + + @retval REOPT_NEW_PLAN there is a new plan. + @retval REOPT_OLD_PLAN no new improved plan was produced, use the old one. + @retval REOPT_ERROR an irrecovarable error occured during reoptimization. +*/ + +JOIN::enum_reopt_result JOIN::reoptimize(Item *added_where, table_map join_tables) +{ + DYNAMIC_ARRAY added_keyuse; + SARGABLE_PARAM *sargables= 0; /* Used only as a dummy parameter. */ + + /* Re-run the REF optimizer to take into account the new conditions. */ + if (update_ref_and_keys(thd, &added_keyuse, join_tab, tables, added_where, + ~outer_join, select_lex, &sargables)) + { + delete_dynamic(&added_keyuse); + return REOPT_ERROR; + } + + if (!added_keyuse.elements) + return REOPT_OLD_PLAN; + + /* Add the new access methods to the keyuse array. */ + if (!keyuse.buffer && + my_init_dynamic_array(&keyuse, sizeof(KEYUSE), 20, 64)) + { + delete_dynamic(&added_keyuse); + return REOPT_ERROR; + } + allocate_dynamic(&keyuse, keyuse.elements + added_keyuse.elements); + memcpy(keyuse.buffer + keyuse.elements * keyuse.size_of_element, + added_keyuse.buffer, + (size_t) added_keyuse.elements * added_keyuse.size_of_element); + keyuse.elements+= added_keyuse.elements; + delete_dynamic(&added_keyuse); + + if (sort_and_filter_keyuse(&keyuse)) + return REOPT_ERROR; + optimize_keyuse(this, &keyuse); + + /* Re-run the join optimizer to compute a new query plan. */ + if (choose_plan(this, join_tables)) + return REOPT_ERROR; + + return REOPT_NEW_PLAN; +} + /** @} (end of group Query_Optimizer) */ |