MySQL 查询优化器的局限性

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

1. 将 select name from u1 结果与 select name from u2 查询出的结果同时放入一张临时表中
2. 将临时表进行排序
3. 将最后结果取出前 5 条返回

SQL2

1. 将 select name from u1 order by name limit 5 结果与 select name from u2 order by name limit 5 查询出的结果同时放入一张临时表中
2. 将临时表进行排序
3. 将最后结果取出前 5 条返回

关键在于

1. SQL1 的第一步中，需要将所有数据都得放入临时表中。而对于 SQL2 而言只需要返回前五条，因为对 name 有索引是不需要排序的
2. SQL1 的第二步中，需要将临时表中的所有数据进行全量排序，此时临时表具有 200w 的数据。而对于 SQL2 而言只需要对 10 条数据排序
3. 当数据量太大时，超出 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 查询优化器的局限性