MySQL Order by on计算列慢

Question

我正在根据输入参数计算分数，并对它们进行排序，以获得前10名运动员的列表。查询分数很快(大约<100 ms)。但当我开始排序时查询需要>1.5秒(对我来说太慢了)。

所以我得到了一个乒乓球运动员

athlete
idAthlete | name
1         | foo
2         | bar

和表结果

result
idResult | idAthlete | place | type
1        | 1         | 2     | World championship
2        | 3         | 1     | European championship

在我的数据库中，我有~40000个条目表结果

和查询

SELECT athlete.idAthlete,
SUM(IF((FIND_IN_SET(result.type, @compTypes)), GETSCORE(result.place), 0)) AS score

FROM
athlete
LEFT JOIN result ON result.idPerson = athlete.idAthlete
GROUP BY athlete.idAthlete
ORDER BY score desc
LIMIT 10;

@compTypes是"World championship,European championship，…"形式的字符串

我尝试在临时表中存储没有order by子句的结果，然后对其进行排序。这种方法有效，但速度并不快。

我将非常感谢任何建议。我在这个问题上坐了一段时间，可能忘记了一些明显的东西。谢谢你:)

Answer 1

有多少运动员?这是必须排序的行数。这个数越小，排序越快。所以，如果你能限制潜在的前10名，排序可能会快得多。

这是我如何写你的查询。不同之处在于我只连接我们想要考虑的类型的结果。(然而，这不会加快排序速度。)

select a.idathlete, coalesce(sum(getscore(r.place)), 0) as score
from athlete a
left join result r on r.idathlete = a.idathlete and find_in_set(r.type, @comptypes)
group by a.idathlete
order by score desc
limit 10;

如果可以只显示有结果的运动员，则从查询中删除athletes表:

select idperson, sum(getscore(place)) as score
from result 
where find_in_set(type, @comptypes)
group by idperson
order by score desc
limit 10;

如果可以进一步将此限制为得分至少为n的运动员，则添加HAVING子句。下面是n= 100的示例:

group by idperson
having score >= 100
order by score desc
limit 10;

值n越高，要排序的行越少。

为了有效的数据访问，我将提供这些索引:

create index idx1 on athlete (idathlete);
create index idx2 on result (idperson, type, place);
create index idx3 on result (type, idperson, place);

由于idathlete显然是表athlete的主键，您应该已经有索引idx1了。

根据查询和数据，将使用idx2或idx3，或者两者都不使用。用EXPLAIN检查这一点，并删除未使用的一个(s)。