mysql union，union all的优化

来源：互联网发布：赤焰狂魔莫小贝知乎编辑：程序博客网时间：2024/06/06 04:24

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 |

阅读全文

0 0