MySQL中嵌套select与ORDER BY查询写法哪种性能更优

首先需要先指出你贴出的两段代码都存在语法/逻辑问题，无法稳定返回正确结果：

• 第一版嵌套SELECT的子查询缺少GROUP BY countries.name分组逻辑，没有分组的情况下count(clients.id)会统计全表客户总数量，不会按国家维度拆分计数；且外层查询直接取name和max(cnt)没有对应分组规则，在开启ONLY_FULL_GROUP_BY的MySQL环境中会直接报错，关闭该配置时会返回随机的国家名和全表客户数，结果完全不符合预期。
• 第二版ORDER BY写法中order by 'Amount of clients'使用单引号包裹了别名，MySQL会将其识别为固定字符串常量而非列别名，实际不会按客户数排序，返回结果也是随机的。

修正后的正确写法 #

修正后的嵌套MAX逻辑写法 #

如果要保留嵌套子查询取最大值的逻辑，正确写法如下，该写法会返回所有客户数并列第一的国家：

select name as Country, cnt as 'Amount of clients'
from (
  select countries.name, count(clients.id) as cnt 
  from clients
  inner join countries on clients.country = countries.id
  group by countries.name
) as country_client_count
where cnt = (
  select max(cnt) 
  from (
    select count(clients.id) as cnt 
    from clients 
    inner join countries on clients.country = countries.id
    group by countries.name
  ) t
);

修正后的ORDER BY + LIMIT写法 #

修正别名引用问题、补充分组逻辑后，正确写法如下，该写法仅返回客户数最多的其中一个国家：

select countries.name as Country, count(clients.id) as 'Amount of clients'
from clients 
inner join countries on clients.country = countries.id
group by countries.name
order by `Amount of clients` desc 
limit 1;

性能对比结论 #

在countries.id为主键、clients.country建有普通索引的常规建表场景下：

• ORDER BY + LIMIT 写法性能显著优于嵌套MAX写法
• 性能差异的核心原因：
  • 嵌套MAX写法需要对两张表的关联结果做两次分组聚合：第一次统计每个国家的客户数生成临时结果集，第二次遍历整个临时结果集计算最大值，再回表匹配对应的国家名，国家数量越多，两次扫描的开销越大。
  • ORDER BY + LIMIT 写法仅需要做一次分组聚合，且MySQL优化器针对ORDER BY ... LIMIT 1有专门优化：不需要对所有聚合结果做全量排序，只需要在遍历聚合结果时持续记录当前最大值对应的行即可，遍历完成就能直接返回结果，额外排序开销几乎可以忽略。
• 补充说明：如果业务要求返回所有客户数并列第一的国家，ORDER BY + LIMIT 1无法满足需求，此时可以根据数据量选择嵌套MAX写法或者MySQL 8.0+的窗口函数写法。

内容的提问来源于stack exchange，提问作者Alonso Garita