MySQL Optimizer join order

The previous article introduces the calculation method for cost, and tests the query associated with the two tables below:

Test Cases

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE TABLE `xpchild` (   `id` int(11) NOT NULL,   `name` varchar(100) DEFAULT NULL,   `c1` int(11) DEFAULT NULL,   `c2` int(11) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `xpchild_name` (`name`),   KEY `xpchild_id_c1` (`id`,`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `xpchild_1` (   `xxid` bigint(20) DEFAULT NULL,   `name` varchar(100) DEFAULT NULL,   KEY `xpchild_1_id` (`xxid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Test SQL

SELECT * from Xpchild, xpchild_1 where xpchild.id=100 and Xpchild.id=xpchild_1.xxid;

function Call Stack:
Join::optimize
Make_join_statistics
Update_ref_and_keys
Get_quick_record_count
Choose_plan

The above omitted join: The process of:p Repare, prepare mainly perform some level changes, the above SQL is a relatively simple two-table association, and do not make too many transformations.

Step1: Initialization

Make_join_statistics:
Initializes each Join_tab object according to Select_lex->leaf_tables, at which point a SQL corresponds to two join_tab for a join.
Initializes the number of record records in the stat statistics in quick_condition_rows to InnoDB.

Step 2: Querying available indexes

Update_ref_and_keys: Depending on where condition, select the index that can be used, add to possible keys, the keys in this example include:
Xpchild:primary,xpchild_id_c1
Xpchild_1:xxid

?

1 2 3 4 5 6 7

(gdb) p *keyuse_array $67 = {   buffer = 0x8ca1fb58 "@24214",   elements = 3,   max_element = 20,   alloc_increment = 64,   size_of_element = 48

Step 3: Calculate cost

Get_quick_record_count: Calculates the cost according to the selectable Possible_keys.

1. Xpchild table

Because there are primary,xpchild tables S->type = = Jt_const available, the cost is calculated as:

S->found_records=s->records=s->read_time=1.
Therefore, MySQL has a tendency to use Primary,unique key, and can save a lot of time to calculate the cost.

2. Xpchild_1 table:

Records && read_time for full table scan are:
S->found_records= 1215
S->read_time= 5

Calculate the cost of the XXID index:
Get_quick_record_count
Test_quick_select:
Cost of final calculation:
Estimated_records=1
best_read_time=2.21
Specific calculation method, you can refer to the previous blog

To this: Xpchild in the JOIN_TAB structure, relatively simple, const table type, cost=1;
In the JOIN_TAB structure of Xpchild_1, Found_records=1, read_time=2.21;
For a single-table query, access path is already optimal.

Order of Step 4:join:

Choose_join:

1. If it is a const table and no longer evaluates the join sequence, select Use current positions directly.
memcpy ((uchar*) join->best_positions, (uchar*) join->positions,sizeof (POSITION) *join->const_tables);
join->best_read=1.0;

2. For non-const table, select the optimal access order
Optimizer_search_depth: Optimizes recursive calculation depth for accessing table join order.
Straight_join: In the order of SQL, or by adding SQL hint to determine the order, default does not use
Greedy_search: Greedy algorithm, for the current query, in the candidate table, select a minimum cost to add to the current plan, recursive completion.
Best_extension_by_limited_search: According to Current_record_count, compare the Best_record_count with the call Best_access_path and select the optimal path.
Best_access_path:table->quick_rows according to the previous calculation of the records, to obtain the cost, get JOIN->POSITIONS[IDX] the optimal path.

Join sequence selection steps:

1. In this example, the cost of Xpchild is: Records=1, read_time=0, according to the Best_extension_by_limited_search in remaining table, which selects the lowest cost. So choose the first table.

2. Then select one from the candidate table (only the Xpchild_1 table) to join the join order, and select a cost-lowest execution plan based on Best_access_path to join the plan, where the xpchild_1_id index is selected.

Finally get best plan, and assign value to Last_query_cost;
join->thd->status_var.last_query_cost= join->best_read;

The last best plan to be obtained:
(GDB) P Tab->join->best_read
$73 = 1.1990000000000001

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

(gdb) p tab->join->best_positions $72 = {{     records_read = 1,     read_time = 0,     table = 0x8ca06078, ‘xpchild‘     key = 0x8ca1fb58,   ‘primary‘     ref_depend_map = 0   }, {     records_read = 1,     read_time = 1,     table = 0x8ca0620c, ‘xpchild_1‘     key = 0x8ca1fbb8,    ‘xpchild_1_id‘     ref_depend_map = 0   }

Not to be continued: