欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-09 08:54:17
union all在mysql5.6下的表现 part1:mysql5.6.25 [root@he1 ~]# mysql -uroot -p enter pa...

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版本中,执行结果如下图所示:

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

从执行计划来看,是把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版本中,执行结果如下图所示:

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

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中,执行结果如下图所示:

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

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

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

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

——总结——

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

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