Analysis of mysq constant transfer

MySQL supports constant transfer for expression simplification, as shown in the following example:
Create Table 't1 '(
'Id1' int (11) not null default '0 ',
'A1' int (11) default null,
'B1 'int (11) default null,
Primary Key ('id1 ')
) Engine = InnoDB default charset = Latin1;

Create Table 't5 '(
'Id5' int (11) default null,
'A5 'int (11) default null,
'B5 'int (11) default null,
Unique key 'id5' ('id5 ')
) Engine = InnoDB default charset = Latin1;

Insert data into each table.

The condition "a1 = A5 and A5 = 1" is optimized by the MySQL optimizer and changed to "('xx '. 't5 '. 'a5 '= 1) and ('xx '. 't1 '. 'a1' = 1) ", which isConstant Transfer Technology.

Mysql> explain extended select * From T5, T1 where a1 = A5 and A5 = 1;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------- + -------------------------------------------------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------- + -------------------------------------------------- +
| 1 | simple | T5 | all | null | 10 | 100.00 | using where |
| 1 | simple | T1 | all | null | 9999 | 100.00 | using where; using JOIN Buffer (block nested loop) |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------- + -------------------------------------------------- +
2 rows in set, 1 warning (0.00 Sec)

Mysql> show warnings;
/* Select #1 */select 'xx'. 't5 '. 'id5' as 'id5', 'xx'. 't5'. 'a5 'As 'a5 ',
'Xx '. 't5 '. 'b5 'As 'b5', 'xx '. 't1 '. 'id1' as 'id1', 'xx '. 't1 '. 'a1' as 'a1', 'xx '. 't1 '. 'b1 'As 'b1'
From 'xx'. 't5 'join 'xx'. 't1'
Where ('xx'. 't5 '. 'a5' = 1) and ('xx'. 'T1 '. 'a1' = 1 ))

 

2. When the conditional expression contains a primary key, the situation may be somewhat different, for example:
Mysql> explain extended select * From T5, T1 where id1 = id5 and id5 = 1;
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------- +
| 1 | simple | T5 | const | id5 | id5 | 5 | const | 1 | 100.00 | null |
| 1 | simple | T1 | const | primary | 4 | const | 1 | 100.00 | null |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------- +
2 rows in set, 1 warning (0.00 Sec)

Mysql> show warnings;
/* Select #1 */select '1' as 'id5', '1' as 'a5 ', null as 'b5 ',
'1' as 'id1', '1' as 'a1', null as 'b1'
From 'xx'. 't5 'join 'xx'. 't1'
Where 1

Why?
This is because:
1 constant transfer technology makes MySQL optimizer think "id1 = id5 = 1"
2 while id1 and id5 are both primary keys, so T1 and T5 tables are considered as "constant tables". Therefore, the value of type in the execution plan is const. that is to say, t1-t5-table tuples can be completely determined (that is, they can be directly read during the optimization stage, rather than located at the execution stage ). therefore, the connection operation between table T1 and table T5 is simple.
3. When the execution plan is displayed, it is difficult to understand why it becomes "where 1 ". the condition does not work because the tuples have been found. the result shows that the condition exactly meets the output of the tuples of the constant table.

Therefore, the followingConstant transfer between a primary key and a non-primary keyThe situation is easy to understand.
Mysql> explain extended select * From T5, T1 where id1 = A5 and A5 = 1;
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
| 1 | simple | T1 | const | primary | 4 | const | 1 | 100.00 | null |
| 1 | simple | T5 | all | null | 10 | 100.00 | using where |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
2 rows in set, 1 warning (0.00 Sec)

Mysql> show warnings;
/* Select #1 */select 'xx'. 't5 '. 'id5' as 'id5', 'xx'. 't5'. 'a5 'As 'a5 ',
'Xx'. 't5 '. 'b5' As 'b5 ', '1' as 'id1', '1' as 'a1', null as 'b1'
From 'xx'. 't5 'join 'xx'. 't1'
Where ('xx'. 't5 '. 'a5' = 1)

This article from the "Linux O & M" blog, please be sure to keep this source http://2853725.blog.51cto.com/2843725/1546281

Analysis of mysq constant transfer