Five minutes to learn about the use of union all in MySQL5.7, mysql5.7union

Five minutes to learn about the use of union all in MySQL5.7, mysql5.7union

Performance of union all in MySQL5.6

Part1: MySQL5.6.25

[root@HE1 ~]# MySQL -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.25-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();+------------+| version() |+------------+| 5.6.25-log |+------------+1 row in set (0.26 sec)  mysql> explain (select id from helei order by id) union all (select id from t where id=0 order by id);+----+--------------+------------+-------+---------------+--------+---------+------+------+-----------------+| id | select_type | table   | type | possible_keys | key  | key_len | ref | rows | Extra      |+----+--------------+------------+-------+---------------+--------+---------+------+------+-----------------+| 1 | PRIMARY   | helei   | index | NULL     | idx_c1 | 4    | NULL | 5219 | Using index   || 2 | UNION    | t     | ALL  | NULL     | NULL  | NULL  | NULL |  1 | Using where   || NULL | UNION RESULT | <union1,2> | ALL  | NULL     | NULL  | NULL  | NULL | NULL | Using temporary |+----+--------------+------------+-------+---------------+--------+---------+------+------+-----------------+3 rows in set (0.00 sec)

As you can see, in MySQL, the execution result is shown in:

According to the execution plan, the query results of the helei table and the query results of the t table are merged into a temporary table and then output to the client.

Performance of union all in MySQL5.7/MariaDB10.1

Part1: MySQL5.7.15

[root@HE1 ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.7.15-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();+------------+| version() |+------------+| 5.7.15-log |+------------+1 row in set (0.00 sec)、mysql> explain (select id from helei order by id) union all (select id from t where id=0 order by id);+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref | rows | filtered | Extra    |+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+| 1 | PRIMARY   | helei | NULL    | index | NULL     | idx_c1 | 4    | NULL | 5212 |  100.00 | Using index || 2 | UNION    | t   | NULL    | ALL  | NULL     | NULL  | NULL  | NULL |  1 |  100.00 | Using where |+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

As you can see, in MySQL, the execution result is shown in:

Part2: MariaDB10.1.16

[root@HE3 ~]# /usr/local/mariadb/bin/mysql -uroot -S /tmp/mariadb.sock Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 7Server version: 10.1.16-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>MariaDB [helei]> explain (select id from helei order by id) union all (select id from t where id=0 order by id);+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+| id  | select_type | table | type | possible_keys | key  | key_len | ref | rows | Extra    |+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+|  1 | PRIMARY   | helei | index | NULL     | idx_c1 | 4    | NULL | 5198 | Using index ||  2 | UNION    | t   | ALL  | NULL     | NULL  | NULL  | NULL |  1 | Using where |+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+2 rows in set (0.00 sec)

The execution result in MariaDB10.1 is shown in:

According to the execution results, no temporary table is created for both MySQL5.7 and MariaDB10.1. in order, the query results of the helei table are first output to the client, then the query results of table t are output to the client.

The Optimization in this article only applies to union all, which is invalid for union and order by in the outermost layer. As shown in the following figure:

-- Conclusion --

In MySQL5.7/MariaDB10.1, union all does not create temporary tables, which reduces I/O overhead during joint queries. In MySQL5.5/5.6, union all does not.

The above section describes the five-minute introduction of the Black technology used by union all in MySQL5.7. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!