MySQL查询优化UNION操作一例

有朋友遇到问题:

1 建表如下

CREATE TABLE t92 (
  a1 int(10) unsigned NOT NULL ,
  b1 int(10) DEFAULT NULL,
  UNIQUE KEY (a1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE t93 (
  a2 int(10) unsigned NOT NULL,
  b2 int(10) DEFAULT NULL,
  UNIQUE KEY (a2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t92 (a1, b1) VALUES (1, 11);
INSERT INTO t92 (a1, b1) VALUES (2, 12);
INSERT INTO t92 (a1, b1) VALUES (3, 13);
INSERT INTO t92 (a1, b1) VALUES (4, 14);
INSERT INTO t92 (a1, b1) VALUES (5, 15);
INSERT INTO t92 (a1, b1) VALUES (6, 16);
INSERT INTO t92 (a1, b1) VALUES (7, 17);
INSERT INTO t92 (a1, b1) VALUES (8, 18);
INSERT INTO t92 (a1, b1) VALUES (9, 19);
INSERT INTO t92 (a1, b1) VALUES (10, 20);

INSERT INTO t93 (a2, b2) VALUES (1, 21);
INSERT INTO t93 (a2, b2) VALUES (2, 22);
INSERT INTO t93 (a2, b2) VALUES (3, 23);
INSERT INTO t93 (a2, b2) VALUES (4, 24);
INSERT INTO t93 (a2, b2) VALUES (5, 25);
INSERT INTO t93 (a2, b2) VALUES (6, 26);
INSERT INTO t93 (a2, b2) VALUES (7, 27);
INSERT INTO t93 (a2, b2) VALUES (8, 28);
INSERT INTO t93 (a2, b2) VALUES (9, 29);
INSERT INTO t93 (a2, b2) VALUES (10, 30);

 

2 查询执行计划如下

mysql> EXPLAIN EXTENDED (SELECT a1 FROM t92 WHERE a1>=1 ORDER BY a1) UNION ALL (SELECT a2 FROM t93 WHERE a2>=1 ORDER BY a2);
+------+--------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id   | select_type  | table      | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+------+--------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|    1 | PRIMARY      | t92        | index | a1            | a1   | 4       | NULL |   10 |      100 | Using where; Using index |
|    2 | UNION        | t93        | index | a2            | a2   | 4       | NULL |   10 |      100 | Using where; Using index |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary          |
+------+--------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
3 rows in set

 

3 问题

??? 疑问：这里我使用的是 UNION ALL 怎么还会出现 Using temporary 呢？ 哦，是因为最终的结果集没有地方放？

 

回答:

1 在MySQL V5.7.6得到的执行计划如下:

mysql> EXPLAIN EXTENDED (SELECT a1 FROM t92 WHERE a1>=1 ORDER BY a1) UNION ALL (SELECT a2 FROM t93 WHERE a2>=1 ORDER BY a2);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY     | t92   | NULL       | index | a1            | a1   | 4       | NULL |   10 |   100.00 | Using where; Using index |
|  2 | UNION       | t93   | NULL       | index | a2            | a2   | 4       | NULL |   10 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.01 sec)

这与这位朋友得到的执行计划不同。他使用的应该是MySQL V5.6.x版本

 

2 在MySQL的官方手册上，有这么一句话：

As of MySQL 5.7.3, the server does not use a temporary table for UNION statements that meet certain qualifications. Instead, it retains from temporary table creation only the data structures necessary to perform result column typecasting. The table is not fully instantiated and no rows are written to or read from it; rows are sent directly to the client. As a result, The result is reduced memory and disk requirements, and smaller delay before the first row is sent to the client because the server need not wait until the last query block is executed.
EXPLAIN  and optimizer trace output will change: The UNION RESULT query block will not be present because that block is the part that reads from the temporary table.

The conditions that qualify a UNION for evaluation without a temporary table are:

 The union is UNION ALL , not UNION or UNION DISTINCT.

There is no global ORDER BY clause.

The union is not the top-level query block of an {INSERT | REPLACE} ... SELECT ...statement.

这表明，5.7.3之后，对于UNION有了新的改进。