Shulou(Shulou.com)06/01 Report--

1. Introduction to Derived_merge

MySQL Reference manual describes it as follows:

The derived_merge flag controls whether the optimizer attempts to merge subqueries and views in the FROM clause into the outer query block, assuming that no other rule prevents merging. By default, the flag is on to enable merging. Setting the flag to off prevents merging.

In fact, derived_merge is one of the many parameters of the system variable optimizer_switch, which is supported from version 5.7.6 (including 5.7.6). The default value is derived_merge=on, which is used to control whether the optimizer merges derived tables or views.

Be careful

All the environments in this experiment are MySQL8.0.13.

Derived_merge is an important difference between MySQL5,6 and MySQL5.7, and it is very important for SQL optimization. The author has encountered relevant cases, similar to the case in part 4 of this article.

2.Derived_merge example

Select * from (select * from t_group) as T1

The merged subquery is equivalent to select * from t_group

Set derived_merge=on, and you can see from the execution plan and warnings that the subqueries after from have been merged

Mysql > set optimizer_switch='derived_merge=on';mysql > desc select * from (select * from t_group) as T1 +-+ | id | select_ Type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | t_group | NULL | ALL | NULL | 10 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.00 sec) mysql > show warnings\ Group`1. Row * * Level: Note Code: 1003Message: / * select#1 * / select `employees`.`t _ group`.`emp _ no`AS `employees no`, `employees`.`t _ group`.`dept _ no`AS `dept_ no`, `employees`.`t _ group`.from _ date`AS `from_ date` `employees`.`t _ group`.`to _ date``to_ date`from `employees`.`t _ group`1 row in set (0.00 sec)

Setting derived_merge=off, you can see from the execution plan and warnings that the subquery after from is still a separate subquery, and the parentheses are not removed and merged

Mysql > set optimizer_switch='derived_merge=off';mysql > desc select * from (select * from t_group) as T1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -- + | 1 | PRIMARY | | NULL | ALL | NULL | 10 | 100.00 | NULL | 2 | DERIVED | t_group | NULL | ALL | NULL | 10 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.00 sec) mysql > desc select * from (select * from t_group) as T1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -- + | 1 | PRIMARY | | NULL | ALL | NULL | 10 | 100.00 | NULL | 2 | DERIVED | t_group | NULL | ALL | NULL | 10 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.00 sec) mysql > show warnings\ Gmail * 1. Row * * Level: Note Code: 1003Message: / * select#1 * / select `t1`.`emp _ no`AS `emp_ no`, `t1`.`dept _ no`AS `dept_ no`, `t1`.`from _ date`AS `from_ date` `t1`.`to _ date``to_ date`from (/ * select#2 * / select `employees`.`t _ group`.`emp _ no`AS `dept_ no`, `employees`.`t _ group`.dept _ no`AS `dept_ no`, `employees`.`t _ group`.`from _ date`AS `from_ date`, `employees`.`t _ group`.`to _ date`date``to_ date`from `employees`.`t _ group`) `t1`1 row in set

3. Some skills to prevent Derived_merge

Because derived_merge defaults to on, but sometimes we don't want to change the configuration parameters to prevent subqueries from merging, is there any other way? Of course, you can add keywords to the subquery:

Aggregate function (SUM (), MIN (), MAX (), COUNT (), and so forth)

Distinct

Group by

Having

Limit

Union or union all

Use the variable symbol @

For example:

Mysql > desc select * from (select count (*) from t_group) as T1 +- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- +-+ | 1 | PRIMARY | | NULL | system | NULL | 1 | 100.00 | NULL | 2 | DERIVED | t_group | NULL | ALL | NULL | 10 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.00 sec) mysql > desc select * from (select distinct * from t_group) as T1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- +-+ | 1 | PRIMARY | | NULL | ALL | NULL | 10 | 100.00 | NULL | | 2 | DERIVED | t_group | NULL | ALL | NULL | NULL | 10 | 100.00 | Using temporary | +- -+-+ 2 rows in set 1 warning (0.00 sec) mysql > desc select * from (select dept_no from t_group group by dept_no) as T1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- +-+ | 1 | PRIMARY | | NULL | ALL | NULL | 10 | 100.00 | NULL | | 2 | DERIVED | t_group | NULL | ALL | NULL | NULL | 10 | 100.00 | Using temporary | +- -+-+ 2 rows in set 1 warning (0.00 sec) mysql > desc select * from (select * from t_group having emp_no > 15000) as T1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -- + | 1 | PRIMARY | | NULL | ALL | NULL | 10 | 100.00 | NULL | 2 | DERIVED | t_group | NULL | ALL | NULL | 10 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.00 sec) mysql > desc select * from (select * from t_group union select * from t_order) as T1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- +-+ | 1 | PRIMARY | | NULL | ALL | NULL | 20 | 100.00 | NULL | | 2 | DERIVED | t_group | NULL | ALL | NULL | | NULL | 10 | 100.00 | NULL | | 3 | UNION | t_order | NULL | ALL | NULL | 10 | 100.00 | NULL | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | +-| -+ 4 rows in set 1 warning (0.00 sec) mysql > desc select * from t_group t join (select @ rn:=10001 emp_no) e on t.emp_no=e.emp_no +- -+ | id | select_type | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- +-+ | 1 | PRIMARY | | NULL | system | NULL | 1 | 100.00 | NULL | | 1 | PRIMARY | t | NULL | ALL | NULL | | NULL | 10 | 10.00 | Using where | | 2 | DERIVED | NULL | No tables used | + -+ 3 rows in set 2 warnings (0.00 sec)

4. If the amount of sub-query data is very large, sub-query merging can optimize SQL, which is equivalent to reducing the number of times of sub-query execution.

For example, in the following SQL, it takes more than 9 seconds for SQL to execute when the subquery merge function is turned off. After enabling the subquery merge function, the time is 5 seconds, which doubles the efficiency.

If you are currently using the MySQL5,6 version and do not support derived_merge, we can improve the efficiency of SQL by rewriting SQL and reducing subqueries, which is also a way to optimize SQL.

There is no SQL execution of subquery merge:

Set optimizer_switch='derived_merge=off' Mysql > select-> total.emp_no,-> total.salary,-> total.to_date,-> total.last_name,-> total.hire_date-> from-> (select-> s.emp_no emp_no,-> s.salary salary,-> s.to_date to_date,-> e.last_name last_name -> e.hire_date hire_date-> from salaries s-> inner join employees e where s.emp_no=e.emp_no) total 2844047 rows in set (9.48sec) mysql > desc select-> total.emp_no,-> total.salary,-> total.to_date,-> total.last_name,-> total.hire_date-> from-> (select-> s.emp_no emp_no,-> s.salary salary,-> s.to_date to_date,-> e.last_name last_name -> e.hire_date hire_date-> from salaries s-> inner join employees e where s.emp_no=e.emp_no limit 10000000) total +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | PRIMARY | | NULL | ALL | NULL | 2995588 | 100.00 | NULL | | 2 | DERIVED | | e | NULL | ALL | PRIMARY | NULL | 299512 | 100.00 | NULL | | 2 | DERIVED | s | NULL | ref | PRIMARY | Emp_no | PRIMARY | 4 | testdb.e.emp_no | 10 | 100.00 | NULL | +-+-- -+ 3 rows in set 1 warning (0.00 sec) mysql > show warnings\ Gateway * 1. Row * * Level: Note Code: 1003Message: / * select#1 * / select `total`.`emp _ no`AS `emp_ no`, `total`.`salary`AS `salary`, `total`.`to _ date`AS `to_ date`, `total`.`last _ name`AS `last_ name` `total`.`hire _ date`AS `to_ date`from (/ * select#2 * / select `testdb`.`s`.`emp _ no` AS `salary`, `testdb`.`s`.`salary`AS `salary`, `testdb`.`s`to _ date`AS `salary`, `testdb`.`e`.`last _ name`AS `last_ name`, `testdb`.`e`.`hire _ date`AS `hire_ date`from `testdb`.`salaries``s`join `testdb`.employees`es``e`where (`testdb`.`s`s`emp _ no` = `testdb.`e.emp _ no` limit 10000000) `total1 row in set (sec 0.00)

SQL execution of subquery merge occurs:

Mysql > set optimizer_switch='derived_merge=on' Mysql > select-> total.emp_no,-> total.salary,-> total.to_date,-> total.last_name,-> total.hire_date-> from-> (select-> s.emp_no emp_no,-> s.salary salary,-> s.to_date to_date,-> e.last_name last_name -> e.hire_date hire_date-> from salaries s-> inner join employees e where s.emp_no=e.emp_no) total 2844047 rows in set (5.03sec) mysql > desc select-> total.emp_no,-> total.salary,-> total.to_date,-> total.last_name,-> total.hire_date-> from-> (select-> s.emp_no emp_no,-> s.salary salary,-> s.to_date to_date,-> e.last_name last_name -> e.hire_date hire_date-> from salaries s-> inner join employees e where s.emp_no=e.emp_no) total +- -- +-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-- -- +-+ | 1 | SIMPLE | e | NULL | ALL | PRIMARY | NULL | NULL | 299512 | 100.00 | NULL | | 1 | SIMPLE | s | NULL | ref | PRIMARY Emp_no | PRIMARY | 4 | testdb.e.emp_no | 10 | 100.00 | NULL | +-+-- -+ 2 rows in set 1 warning (0.00 sec) mysql > show warnings\ Gmail * 1. Row * * Level: Note Code: 1003Message: / * select#1 * / select `testdb`.`s`.`emp _ no`AS `emp_ no`, `testdb`.`s`.`salary`AS `salary`, `testdb`.`s`.`to _ date`AS `to_ date`, `testdb`.`e`.`last _ name`AS `last_ name` `testdb`.`e`.`hire _ date`AS `testdb`.`salaries``s`join `testdb`.`employees``e`where (`testdb`.`s`emp _ no` = `testdb`.`e`.`emp _ no`) 1 row in set (0.00 sec)

Reference link

Section 8.2.2.3, "Optimizing Derived Tables and View References".

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.