为什么MAX（）比ORDER BY...限制1慢100倍？

Question

我有一个表foo，其中有（其他20个）列bar、baz和quux，以及baz和quux的索引。该表有大约500k行。

为什么以下to查询在速度上差异如此之大？查询A需要0.3秒，而查询B需要28秒。

查询A

select baz from foo
    where bar = :bar
    and quux = (select quux from foo where bar = :bar order by quux desc limit 1)

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   PRIMARY     foo     ref     quuxIdx         quuxIdx 9       const   2       "Using where"
2   SUBQUERY    foo     index   NULL            quuxIdx 9       NULL    1       "Using where"

select baz from foo
    where bar = :bar
    and quux = (select MAX(quux) from foo where bar = :bar)

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   PRIMARY     foo     ref     quuxIdx         quuxIdx 9       const   2       "Using where"
2   SUBQUERY    foo     ALL     NULL            NULL    NULL    NULL    448060  "Using where"

我使用MySQL5.1.34。

Answer 1

您应该在(bar，quux)上添加索引。

没有这个索引，MySQL就无法看到如何高效地执行查询，因此它必须从各种低效的查询计划中进行选择。

在第一个示例中，它扫描quux索引，对于找到的每一行，在原始表中查找bar的相应值。这需要两倍的时间来检查每一行，但幸运的是，具有正确值bar的行靠近扫描的开始，因此可以停止。这可能是因为您正在搜索的bar值经常出现，所以幸运的几率非常高。因此，在找到匹配项之前，它可能只需要检查少数几行，因此，即使检查每一行所需的时间是原来的两倍，但仅检查几行的事实也会带来巨大的整体节省。由于在bar上没有索引，MySQL事先不知道值:bar频繁出现，因此它无法知道该查询会很快。