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版本中,执行结果如下图所示:
从执行计划来看,是把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用法的黑科技,希望对大家有所帮助
上一篇: MySQL5.5.27安装图文教程