MySQL5.7 union的一个限制

     实验说明MySQL的这个版本无法将外层条件推到内层里面。在Oracle中，有时候能推，有时候不能推。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.00 sec)
制造数据
DROP TABLE IF EXISTS test; 
CREATE TABLE test( 
    ID INT(10) NOT NULL, 
    `Name` VARCHAR(20) DEFAULT '' NOT NULL, 
    PRIMARY KEY( ID ) 
)ENGINE=INNODB DEFAULT CHARSET utf8; 
#创建生成测试数据的存储过程
DROP PROCEDURE IF EXISTS pre_test; 
DELIMITER //
CREATE PROCEDURE pre_test() 
BEGIN 
DECLARE i INT DEFAULT 0; 
SET autocommit = 0; 
WHILE i<4000000 DO 
INSERT INTO test ( ID,`Name` ) VALUES( i, CONCAT( 'Carl', i ) ); 
SET i = i+1; 
IF i%10000 = 0 THEN 
COMMIT; 
END IF; 
END WHILE; 
END; //
DELIMITER ;

CREATE TABLE test_bak AS SELECT * FROM test LIMIT 1000;
CREATE INDEX ind_t_id ON test(id);
SHOW CREATE TABLE test;
CREATE INDEX ind_t1_id ON test_bak(id);

--23s
(SELECT * FROM test ORDER BY NAME)
UNION
(SELECT * FROM test_bak ORDER BY NAME) 
LIMIT 20;
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
| id | select_type  | TABLE      | PARTITIONS | TYPE | possible_keys | KEY  | key_len | ref  | ROWS    | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
|  1 | PRIMARY      | test       | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3992817 |   100.00 | NULL            |
|  2 | UNION        | test_bak   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    NULL |     NULL | USING TEMPORARY |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+

--5s
(SELECT * FROM test ORDER BY NAME LIMIT 20)
UNION
(SELECT * FROM test_bak ORDER BY NAME LIMIT 20) 
LIMIT 20;
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
| id | select_type  | TABLE      | PARTITIONS | TYPE | possible_keys | KEY  | key_len | ref  | ROWS    | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
|  1 | PRIMARY      | test       | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3992817 |   100.00 | USING filesort  |
|  2 | UNION        | test_bak   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1000 |   100.00 | USING filesort  |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    NULL |     NULL | USING TEMPORARY |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+

--3s
(SELECT * FROM test ORDER BY NAME)
UNION ALL
(SELECT * FROM test_bak ORDER BY NAME) 
LIMIT 20;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | TABLE    | PARTITIONS | TYPE | possible_keys | KEY  | key_len | ref  | ROWS    | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | PRIMARY     | test     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3992817 |   100.00 | NULL  |
|  2 | UNION       | test_bak | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1000 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+

--4s
(SELECT * FROM test ORDER BY NAME LIMIT 20)
UNION ALL
(SELECT * FROM test_bak ORDER BY NAME LIMIT 20) 
LIMIT 20;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | TABLE    | PARTITIONS | TYPE | possible_keys | KEY  | key_len | ref  | ROWS    | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | PRIMARY     | test     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3992817 |   100.00 | USING filesort |
|  2 | UNION       | test_bak | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1000 |   100.00 | USING filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+----------------+

--3s
(SELECT * FROM test )
UNION ALL
(SELECT * FROM test_bak) 
LIMIT 20;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | TABLE    | PARTITIONS | TYPE | possible_keys | KEY  | key_len | ref  | ROWS    | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | PRIMARY     | test     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3992817 |   100.00 | NULL  |
|  2 | UNION       | test_bak | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1000 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+

--0.004s
(SELECT * FROM test  LIMIT 20)
UNION ALL
(SELECT * FROM test_bak LIMIT 20) 
LIMIT 20;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | TABLE    | PARTITIONS | TYPE | possible_keys | KEY  | key_len | ref  | ROWS    | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | PRIMARY     | test     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3992817 |   100.00 | NULL  |
|  2 | UNION       | test_bak | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1000 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+