实验说明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);
(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 |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+