MySQL超长查询，内连接

Question

我开发了一个网站，两个团队可以在这里面对面交流。第一个团队为某个值(堆栈)创建一个匹配搜索m，其中status = search。第一队有一个 elo(级别)属性。当第二支队伍(elo 为 150)将搜索一场比赛时，我们执行以下查询:

SELECT m.id, m.value, m.teamOneScore, m.teamTwoScore, m.date, m.status, m.detail, m.team_one_id, m.team_two_id 
FROM user_match m
INNER JOIN team t ON m.team_one_id = t.id 
WHERE t.id = m.team_one_id 
    AND t.id <> 3        // You don't want to play against your own team
    AND t.elo <= 200     // 50 of elo range for example
    AND t.elo >= 100 
    AND m.value = '0.5'  
    AND m.status = 'search'
LIMIT 1

问题是即使我们只有 15000 个 user_match(只有 40 行满足 value = 0.5，status = search 的要求)和 15000 个团队(有 5570 个团队满足要求)，这个查询也很长与 elo 和 id)我尝试EXPLAIN 请求，结果如下:

如果用户搜索比赛时没有对手，请求可能需要 3 分钟左右，当同一请求多次执行时(缓存？)，这个时间会减少，如果有对手则更快.

我最后能确定的是 user_match 表包含一个 detail 属性，该属性包含一个沉重的 json_array ONLY IF the match status = ended

你知道现在是不是正常执行时间吗？我可以做些什么来改善它？

谢谢!

Answer 1

开门见山，您需要在 where 子句中使用索引列。

根据解释，好像mysql打算只用pk做索引。而您在 where 列中的 pk 仅用于排除单行。因此，mysql 将扫描除单行之外的所有表。

在这种情况下，您必须索引 elo 列以使查询计划扫描最少的行。

ALTER TABLE team ADD INDEX search(id, elo);

然后看查询时间。

+编辑上面的解决方案似乎并没有大大改善查询时间。

还有一点。您需要首先在连接子句中放置较小的范围表。使用以上索引，团队表将更小。所以查询看起来像这样。

SELECT m.id, m.value, m.teamOneScore, m.teamTwoScore, m.date, 
       m.status, m.detail, m.team_one_id, m.team_two_id
FROM team t
INNER JOIN user_match m ON m.team_one_id = t.id 
WHERE 
t.elo <= 200     // 50 of elo range for example
AND t.elo >= 100
AND t.id <> 3        // You don't want to play against your own team
AND m.value = '0.5'  
AND m.status = 'search'
LIMIT 1

让我看看这是否适合你