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

概述介绍《Mysql应用5分钟了解MySQL5.7中union all用法的黑科技》开发教程，希望对您有用。

《MysqL应用5分钟了解MysqL5.7中union all用法的黑科技》要点：
本文介绍了MysqL应用5分钟了解MysqL5.7中union all用法的黑科技，希望对您有用。如果有疑问，可以联系我们。

union all在MysqL5.6下的表现MysqL学习

Part1:MysqL5.6.25MysqL学习

@H_502_16@

[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)

可以看出,在MysqL5.6版本中,执行结果如下图所示：MysqL学习

MysqL学习

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

union all在MysqL5.7/MariaDB10.1下的表现
MysqL学习

Part1:MysqL5.7.15
MysqL学习

@H_502_16@

[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)

可以看出,在MysqL5.7版本中,执行结果如下图所示：
MysqL学习

MysqL学习

Part2:MariaDB10.1.16MysqL学习

@H_502_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,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中,执行结果如下图所示：MysqL学习

MysqL学习

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

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

MysqL学习

――总结――MysqL学习

在MysqL5.7/MariaDB10.1中,union all不再创建临时表,这样在联合查询时会减少I/O开销,在MysqL5.5/5.6中则不具备这一特性.MysqL学习

以上所述是小编给大家介绍的5分钟了解MysqL5.7中union all用法的黑科技,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的.在此也非常感谢大家对内存溢出PHP网站的支持!MysqL学习

总结

以上是内存溢出为你收集整理的Mysql应用5分钟了解MySQL5.7中union all用法的黑科技全部内容，希望文章能够帮你解决Mysql应用5分钟了解MySQL5.7中union all用法的黑科技所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。