5 minutes to learn about mysql5.7union all usage of black technology

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/88/96/wKiom1f8bNajxqWNAAA4eVx2Dz8965.jpg "title=" icon 2. JPG "alt=" Wkiom1f8bnajxqwnaaa4evx2dz8965.jpg "/>

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/88/92/wKioL1f8bNbCZ-bgAAA4pG6yXEQ597.jpg "title=" icon 2. JPG "alt=" Wkiol1f8bnbcz-bgaaa4pg6yxeq597.jpg "/>

Mysql5.7union all usage of black technology

The performance of union all under MySQL5.6

Part1 : mysql5.6.25

[[email protected] ~]# 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&nbSP;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&NBSP;SEC)

As you can see, in the MySQL5.6 version, the execution results are as follows:

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/88/92/wKioL1f8bZvhzEMaAAFulp6pefo997.jpg "title=" 1.jpg " alt= "Wkiol1f8bzvhzemaaafulp6pefo997.jpg"/>

From 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.

The performance of union all under mysql5.7/mariadb10.1

part1: mysql5.7.15

[[email protected] ~]# 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&nbSP;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 the MySQL5.7 version, the execution results are as follows:

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/88/96/wKiom1f8bijj3fJiAAF48HG3WPQ918.jpg "title=" 2.jpg " alt= "Wkiom1f8bijj3fjiaaf48hg3wpq918.jpg"/>

Part2 : mariadb10.1.16

[[Email protected] ~]# /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)

As you can see in MariaDB10.1, the execution results are as follows:

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/88/92/wKioL1f8bmmwi9GLAAFbMJCN0uU554.jpg "title=" 3.jpg " alt= "Wkiol1f8bmmwi9glaafbmjcn0uu554.jpg"/>

From the execution results, whether it is MySQL5.7 or MariaDB10.1, there is no temporary table created, in order, Helei table query results first output to the client, and then the query results of the T table output to the client.

The optimizations in this article are for union all only, and the order by is not valid for union and outermost. As shown:

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/88/96/wKiom1f8boazPx35AAKnKQS1Ig4776.jpg "title=" 4.jpg " alt= "Wkiom1f8boazpx35aaknkqs1ig4776.jpg"/>

-- summary --

In mysql5.7/mariadb10.1, union all no longer creates temporary tables, which reduces I/O overhead when federated queries, and does not have this feature in mysql5.5/5.6. Due to the author's limited level, writing time is also very hasty, the text will inevitably appear some errors or inaccurate places, inappropriate to ask readers to criticize correct.

This article is from the "He Lei" blog, make sure to keep this source http://suifu.blog.51cto.com/9167728/1860575

5 minutes to learn about mysql5.7union all usage of black technology