MySQL - 哪一个性能更好;"嵌套选择"还是"排序依据"？

Question

我必须从数据库中获得客户数量最多的国家。我在MySQL上使用嵌套选择的代码如下:

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

代码工作正常。但是我想知道如果我使用ORDER BY而不是ORDER BY，在时间性能方面是否会更好，例如:

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

谁能解释一下它们之间是否有重要的区别，两者中是否有一个更好，或者是否真的无关紧要。谢谢。

Answer 1

过早优化是万恶之源。如果查询很少使用并且工作很快，则不应该优化它。

我个人会使用第二种选择。子查询更难阅读，应该避免使用，以方便下一个开发人员(很可能就是您)。如果操作不当，它们往往会出现糟糕的性能问题。

只要注意"limit"是mysql特有的，如果你想在另一个SQL服务器上运行，查询必须重写。

Answer 2

根据Polygorial's Comment…

你的第一个问题是错误的。

你的第二个查询是错误的。(可能有3个错误)

修改后的第二个查询:

SELECT co.name AS Country,
COUNT(*) AS 'Number of clients'
FROM countries AS co
LEFT JOIN clients AS cl  ON cl.country = co.id
GROUP BY co.name
ORDER BY COUNT(*) DESC
LIMIT 1;

在某些情况下，此公式可能更快:

SELECT  ( SELECT name FROM Country AS co
WHERE cl.country = co.id
) AS Country,
COUNT(*) AS 'Number of clients'
FROM clients AS cl
GROUP BY cl.country
ORDER BY COUNT(*) DESC
LIMIT 1;

可能更快，尽管更复杂。注意它是如何将ORDER BY ... LIMIT推入子查询的:

SELECT  co.name
cl.nc AS 'Number of clients'
FROM (
SELECT country,
COUNT(*) AS nc
FROM clients
GROUP BY country
ORDER BY COUNT(*) DESC
LIMIT 1
) AS cl
JOIN Country AS co  ON cl.country = co.id

为了提高性能，我们需要查看您拥有的索引。请提供SHOW CREATE TABLE