5分钟了解MySQL5.7中union all用法的黑科技

union all在mysql5.6下的表现

part1:mysql5.6.25

[root@he1 ~]# mysql -uroot -p
enter password: 
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 2
server 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 its
affiliates. other names may be trademarks of their respective
owners.
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)

可以看出，在mysql5.6版本中，执行结果如下图所示：

从执行计划来看，是把helei表的查询结果和t表的查询结果合并在了一张临时表里，然后输出给客户端。

union all在mysql5.7/mariadb10.1下的表现

part1:mysql5.7.15

[root@he1 ~]# mysql -uroot -p
enter password: 
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 8
server 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 its
affiliates. other names may be trademarks of their respective
owners.
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)

可以看出，在mysql5.7版本中，执行结果如下图所示：

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 7
server version: 10.1.16-mariadb mariadb server
copyright (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)

可以看出在mariadb10.1中，执行结果如下图所示：

从执行结果看，无论是mysql5.7还是mariadb10.1，都没有创建临时表，按照顺序，helei表的查询结果首先输出到客户端，然后t表的查询结果再输出到客户端。

本文中的优化只针对union all，对union和在最外层使用order by无效。如下图是所示： 

——总结——

在mysql5.7/mariadb10.1中，union all不再创建临时表，这样在联合查询时会减少i/o开销，在mysql5.5/5.6中则不具备这一特性。

以上所述是小编给大家介绍的5分钟了解mysql5.7中union all用法的黑科技，希望对大家有所帮助