mysql - FirstMatch 在 EXPLAIN 语句中的含义是什么？

Question

今天我注意到在语句的 EXPLAIN 中有一个“新的”Extra 类型。

这是我的查询

SELECT UrlId,'61','Australia' 
FROM dbase.phonenumbers
WHERE UrlId IN (SELECT UrlId FROM dbase.phonenumbers
                WHERE PhoneNumber LIKE '+31%')
AND PhoneNumber LIKE '+61%'

EXPLAIN 给出了这个，

phonenumbers    range   UrlId_PhoneNumber,PhoneNumber   PhoneNumber        12                               4176    Using where
phonenumbers    ref     UrlId_PhoneNumber,PhoneNumber   UrlId_PhoneNumber   4   dbase.phonenumbers.UrlId    4       Using where; Using index; FirstMatch(phonenumbers)

FirstMatch(phonenumbers) 到底是什么意思？我以前从未见过它，也找不到任何足够快的东西来解释它。

Answer 1

这是一种策略，一旦找到第一个真正的匹配项，就会通过快捷方式执行来避免产生重复。

所以 FirstMatch(phonenumbers) 意味着一旦我们产生了一个匹配的记录组合，它就会缩短执行并跳回 phonenumbers 表。

来自 FirstMatch strategy

• The FirstMatch strategy works by executing the subquery and short-cutting its execution as soon as the first match is found.
• This means, subquery tables must be after all of the parent select's tables that are referred from the subquery predicate.
• EXPLAIN shows FirstMatch as "FirstMatch(tableN)".
• The strategy can handle correlated subqueries.
• But it cannot be applied if the subquery has meaningful GROUP BY and/or aggregate functions.
• Use of the FirstMatch strategy is controlled with the firstmatch=on|off flag in the optimizer_switch variable.