mysql中union可以怎样优化_mysql union，union all的优化

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 |