MySQL 查询在执行 ORDER BY 和执行计数时永远不会完成

Question

我正在尝试计算表中字符的排名。无论出于何种原因，每当我使用 order by 子句运行查询时，查询都会永远运行。

我有一个非常相似的查询在具有不同架构的不同服务器中运行，但它本质上在做同样的事情，并且几乎立即完成。我完全不知道为什么这个查询永远不会完成并且需要永远。

我正在索引字符表中的几乎所有内容，但仍然没有运气。

KEY `accountid` (`accountid`),
KEY `party` (`party`),
KEY `ranking1` (`level`,`exp`),
KEY `ranking2` (`gm`,`job`),
KEY `idx_characters_gm` (`gm`),
KEY `idx_characters_fame` (`fame`),
KEY `idx_characters_job` (`job`),
KEY `idx_characters_level` (`level`),
KEY `idx_characters_exp` (`exp`),

当我不包括 ORDER BY 时，它运行良好并立即完成。当我这样做时，它会永远运行。

数据库中只有 28,000 个字符，因此计算排名不会那么密集，尤其是当限制仅为 1 时。

SELECT c.name
, 1+(
SELECT COUNT(*)
FROM msd.characters as rankc
WHERE rankc.level > c.level 
LIMIT 1
) as jobRank
FROM characters as c
JOIN accounts as a
ON c.accountid = a.id
WHERE c.gm = 0 AND a.banned = 0
ORDER BY c.`level` DESC, c.exp DESC
LIMIT 1
OFFSET 0;

任何帮助将不胜感激！

编辑：

从本质上讲，每个角色都有一个独特的工作，我想得到这个角色的工作排名。排名的默认顺序是按级别。这就是为什么我要在我的jobRank SELECT中进行比较。

这是我想要的结果的例子：期望的结果

Answer 1

遇到查询性能问题时，您应该做的第一件事是对查询运行解释(只需在 select 语句前面加上"explain"，您就可以查看 https://dev.mysql.com/doc/refman/5.7/en/using-explain.html 以获取更多详细信息。 虽然您可能认为您已经准备好了所有适当的索引，但有时 DBMS 引擎做出的决策并不明显。 不要惊讶地发现一个或多个全表扫描，您认为将使用索引(这可能是"查询永不完成"问题的最常见来源(

更新：根据您的解释输出，查询正在子查询中执行多个全表扫描，这可能是您的性能问题。 您想尝试使用索引获取该子查询，因此我建议从子查询中删除 LIMIT 1(where 子句过滤掉您不想要的级别，并且没有 group by 子句，因此应该只返回 1 行(并查看这是否可以完成工作。