UNION 的限制
MySQL 无法将限制条件从 UNION 的外层“下推”到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上
-- SQL1
select name from u1
union all
select name from u2
order by name
limit 5;
-- SQL2
(select name from u1 order by name limit 5)
union all
(select name from u2 order by name limit 5)
order by name
limit 5;
以上两条 SQL 查询结果一致
MySQL
SQL1 与 SQL2 的查询结果一模一样,但是对于 MySQL 而言其耗时相差很大
u1 与 u2 表结构相同,只有 id
与 name
两个字段,且 id
为主键,而 name
为普通索引
两张表分别模拟百万条数据,结果如下
mysql> select name from u1
-> union all
-> select name from u2
-> order by name
-> limit 5;
+----------------------------------+
| name |
+----------------------------------+
| 00000e4f6275e52e414f98e2ef31db05 |
| 000012e70e02febb0631d70317902445 |
| 00001ac6b5808ac01a835c8cd8e5689b |
| 000023520c3064b4ab5a6acb2ee7d604 |
| 000025ae14d5047357d72ad99de7e2a5 |
+----------------------------------+
5 rows in set (2.07 sec)
mysql> (select name from u1 order by name limit 5)
-> union all
-> (select name from u2 order by name limit 5)
-> order by name
-> limit 5;
+----------------------------------+
| name |
+----------------------------------+
| 00000e4f6275e52e414f98e2ef31db05 |
| 000012e70e02febb0631d70317902445 |
| 00001ac6b5808ac01a835c8cd8e5689b |
| 000023520c3064b4ab5a6acb2ee7d604 |
| 000025ae14d5047357d72ad99de7e2a5 |
+----------------------------------+
5 rows in set (0.01 sec)
MySQL 对两者的做法如下:
SQL1
- 将
select name from u1
结果与select name from u2
查询出的结果同时放入一张临时表中 - 将临时表进行排序
- 将最后结果取出前 5 条返回
SQL2
- 将
select name from u1 order by name limit 5
结果与select name from u2 order by name limit 5
查询出的结果同时放入一张临时表中 - 将临时表进行排序
- 将最后结果取出前 5 条返回
关键在于
- SQL1 的第一步中,需要将所有数据都得放入临时表中。而对于 SQL2 而言只需要返回前五条,因为对
name
有索引是不需要排序的 - SQL1 的第二步中,需要将临时表中的所有数据进行全量排序,此时临时表具有 200w 的数据。而对于 SQL2 而言只需要对 10 条数据排序
- 当数据量太大时,超出 sort_buffer 时,会使用更慢的磁盘排序
Postgres
同样的查询语句,以及同样的表结构,结果如下
postgres=# select name from u1
union all
select name from u2
order by name
limit 5;
name
----------------------------------
000Aq77eN3gkC22GmH10wBe5CiauNNRH
000DOwoR6Ter2fsss39IKcrsseEcq2v3
000EyUs6oysIyPFgkMxEXiMfqPwnXjh1
000VNqt1qtXkytYKypET4KKFsolvBZ5j
000XEVWIL7bLuvPu5aAlF15xGFj1bWtp
(5 rows)
Time: 2.119 ms
postgres=# (select name from u1 order by name limit 5)
union all
(select name from u2 order by name limit 5)
order by name
limit 5;
name
----------------------------------
000Aq77eN3gkC22GmH10wBe5CiauNNRH
000DOwoR6Ter2fsss39IKcrsseEcq2v3
000EyUs6oysIyPFgkMxEXiMfqPwnXjh1
000VNqt1qtXkytYKypET4KKFsolvBZ5j
000XEVWIL7bLuvPu5aAlF15xGFj1bWtp
(5 rows)
Time: 2.673 ms
对 Postgres 而言,两者差距不大
在同一个表中查询和更新
MySQL不允许对一张表同时进行查询和更新
MySQL
-- SQL1
update test a
set content = (
select upper(content) from test b
where a.id = b.id
);
-- SQL2
update test a
join (
select id, lower(content) content from test b
) c using (id)
set a.content = c.content;
执行 SQL1 时,数据库会报 ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause
运行 SQL2 正常执行更新操作
因为当更新 test 时,读取的是临时表 c,当读取临时表 c 时,test 已经完成了查询操作
Posotgres
Postgres 执行 SQL1 执行成功
postgres=# update test a
set content = (
select upper(content) from test b
where a.id = b.id
);
UPDATE 3
Time: 4.674 ms
参考文献
- 《高性能MySQL(第4版)》 第 8 章 查询性能优化 - MySQL 查询优化器的局限性