MySQL DML Operations--------Merge query Results combat

1. Background

* Full and query results merge the query results of multiple SELECT statements together.

* Result sets that participate in merging require field unification.

* fields can be replaced with an empty string '.

2. Merge query results actual combat [users1 and Users2]

* View users1 table and USERS2 table structure

mysql> desc users1;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| ID | bigint (20) | NO | PRI | NULL | auto_increment |
| name | VARCHAR (64) |     NO | |                NULL | |
| sex | Enum (' M ', ' F ') |     NO | |                NULL | |
| Age | Int (11) |     NO | |                NULL | |
+-------+---------------+------+-----+---------+----------------+
4 rows in Set (0.00 sec)

mysql> desc users2;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| ID | bigint (20) | NO | PRI | NULL | auto_increment |
| name | VARCHAR (64) |     NO | |                NULL | |
| sex | Enum (' M ', ' F ') |     NO | |                NULL | |
| Age | Int (11) |     NO | |                NULL | |
+-------+---------------+------+-----+---------+----------------+
4 rows in Set (0.01 sec)

* View users1 and USERS2 table data

Users1 and USERS2 tables have the same fields Tom

Mysql> select * from Users1;
+----+------+-----+-----+
| ID | name | sex | Age |
+----+------+-----+-----+
| 1 | Tom |  M | 25 |
| 2 | Jak |  F | 42 |
+----+------+-----+-----+
2 rows in Set (0.00 sec)

Mysql> select * from Users2;
+----+-------+-----+-----+
| ID | name | sex | Age |
+----+-------+-----+-----+
| 1 | Tom |  M | 25 |
| 2 | Lisea |  M | 42 |
+----+-------+-----+-----+
2 rows in Set (0.00 sec)

* Union Merge and go to heavy

650) this.width=650; "Style=" display:block; Float:none; Margin-left:auto; Margin-right:auto "title=" union.jpg "src=" Https://s5.51cto.com/wyfs02/M00/9A/9A/wKiom1lYV0OxySZRAAA41trCYeE559.jpg "/>

Mysql> (SELECT * from users1) union (SELECT * from users2);
+----+-------+-----+-----+
| ID | name | sex | Age |
+----+-------+-----+-----+
| 1 | Tom |  M | 25 |
| 2 | Jak |  F | 42 |
| 2 | Lisea |  M | 42 |
+----+-------+-----+-----+
3 Rows in Set (0.00 sec)

* UNION ALL does not go heavy

650) this.width=650; "Style=" display:block; Float:none; Margin-left:auto; Margin-right:auto "title=" union_all.jpg "src=" https://s3.51cto.com/wyfs02/M01/9A/9B/ Wkiol1lyv2osrumiaaa4ximrkqk585.jpg "/>

Mysql> (SELECT * from users1) UNION ALL (SELECT * from users2);
+----+-------+-----+-----+
| ID | name | sex | Age |
+----+-------+-----+-----+
| 1 | Tom |  M | 25 |
| 2 | Jak |  F | 42 |
| 1 | Tom |  M | 25 |
| 2 | Lisea |  M | 42 |
+----+-------+-----+-----+
4 rows in Set (0.01 sec)

* View Union Performance analysis

[Temporary table used]

Mysql> Explain (SELECT * from users1) union (SELECT * from users2);
+----+--------------+------------+------------+------+---------------+------+---------+------+------+---------- +-----------------+
| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+---------- +-----------------+
| 1 | PRIMARY | users1 | NULL | All | NULL | NULL | NULL |    NULL |   2 | 100.00 | NULL |
| 2 | UNION | Users2 | NULL | All | NULL | NULL | NULL |    NULL |   2 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | All | NULL | NULL | NULL | NULL |     NULL | NULL | Using Temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+---------- +-----------------+
3 rows in Set, 1 warning (0.01 sec)

* View UNION ALL performance analysis

[temporary table not used]

Mysql> Explain (SELECT * from users1) UNION ALL (SELECT * from users2);
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---- ---+
| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---- ---+
| 1 | PRIMARY | users1 | NULL | All | NULL | NULL | NULL |    NULL |   2 | 100.00 | NULL |
| 2 | UNION | Users2 | NULL | All | NULL | NULL | NULL |    NULL |   2 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---- ---+
2 rows in Set, 1 warning (0.01 sec)

3. Union and UNION ALL summary

* The union is one step away from the union all, which creates a temporary table and degrades performance.

* When both sides of the result set data are relative to each other, it is recommended to use Union ALL.

4. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

?

MySQL DML Operations--------Merge query Results combat