改善这个慢mysql查询的技巧？

Question

我使用的查询通常在一秒内执行，但有时需要 10-40 秒才能完成。 实际上我并不完全清楚子查询是如何工作的，我只知道它是有效的，因为它为每个 faverprofileid 提供了 15 行。

我正在记录缓慢的查询，它告诉我检查了 5823244 行，这很奇怪，因为在涉及的任何表中都没有接近这么多行（收藏夹表最多有 50,000 行）。

有人能给我一些指点吗？ 子查询有问题并且需要使用文件排序吗？

编辑：运行解释显示用户表没有使用索引（即使 id 是主键）。 在额外的下面写着：使用临时的； 使用文件排序。

SELECT F.id,F.created,U.username,U.fullname,U.id,I.*   
FROM favorites AS F  
INNER JOIN users AS U ON F.faver_profile_id = U.id  
INNER JOIN items AS I ON F.notice_id = I.id  
WHERE faver_profile_id IN (360,379,95,315,278,1)  
AND F.removed = 0  
AND I.removed = 0   
AND F.collection_id is null   
AND I.nudity = 0  
AND (SELECT COUNT(*) FROM favorites WHERE faver_profile_id = F.faver_profile_id  
AND created > F.created AND removed = 0 AND collection_id is null) < 15 
ORDER BY F.faver_profile_id, F.created DESC;

Answer 1

检查的行数表示很大，因为许多行已被检查多次。 您出现此问题的原因是查询计划优化不正确，这会导致本应执行索引查找时进行表扫描。 在这种情况下，检查的行数是指数的，即与多个表中的总行数的乘积相当的数量级。

• 确保您已运行ANALYZE TABLE 在您的三张桌子上。
• 阅读如何避免表扫描< /a>，然后识别然后创建任何丢失的索引
• 重新运行分析并重新解释您的查询
  • 检查的行数必须大幅下降
  • 如果没有，发布完整的解释计划
• 使用查询提示强制使用索引（要查看表的索引名称，请使用 显示索引）：

<代码>选择
F.id,F.创建,U.用户名,U.全名,U.id,I.*
FROM 收藏夹 AS F FORCE INDEX (faver_profile_id_key)
INNER JOIN 用户 AS U FORCE INDEX FOR JOIN（主） ON F.faver_profile_id = U.id
INNER JOIN items AS I FORCE INDEX FOR JOIN (PRIMARY) ON F.notice_id = I.id
其中 faver_profile_id IN (360,379,95,315,278,1)
AND F.removed = 0
AND I.removed = 0
并且 F.collection_id 为空
AND I.nudity = 0
AND (从收藏夹中选择 COUNT(*) 个 强制索引 (faver_profile_id_key) WHERE faver_profile_id = F.faver_profile_id
并创建了> F.创建 AND 删除 = 0 AND collection_id 为空) < 15
ORDER BY F.faver_profile_id, F.created DESC;

您还可以更改查询以使用 GROUP BY faver_profile_id/HAVING count >; 15 而不是嵌套的 SELECT COUNT(*) 子查询，如 vartec 建议的那样。 如果您的原始查询和 vartec 的查询都经过适当优化，例如使用提示（您的查询将使用嵌套索引查找，而 vartec 的查询），则它们的性能应该具有可比性将使用基于哈希的策略。）

Answer 2

我认为使用 GROUP BY 和 HAVING 应该会更快。
那是你要的吗？

SELECT F.id,F.created,U.username,U.fullname,U.id, I.field1, I.field2, count(*) as CNT
FROM favorites AS F  
INNER JOIN users AS U ON F.faver_profile_id = U.id  
INNER JOIN items AS I ON F.notice_id = I.id  
WHERE faver_profile_id IN (360,379,95,315,278,1)  
AND F.removed = 0  
AND I.removed = 0   
AND F.collection_id is null   
AND I.nudity = 0  
GROUP BY F.id,F.created,U.username,U.fullname,U.id,I.field1, I.field2
HAVING CNT < 15
ORDER BY F.faver_profile_id, F.created DESC;

不知道您需要 items 中的哪些字段，因此我添加了占位符。

Answer 3

我建议你使用Mysql解释查询来看看你的mysql如何服务器处理查询。 我的赌注是你的指数不是最佳的，但解释应该比我的赌注做得更好。

Answer 4

您可以对每个 id 执行循环并使用 limit 而不是 count(*) 子查询：

foreach $id in [123,456,789]:
    SELECT
     F.id,
     F.created,
     U.username,
     U.fullname,
     U.id,
     I.*
    FROM
     favorites AS F INNER JOIN
     users AS U ON F.faver_profile_id = U.id INNER JOIN
     items AS I ON F.notice_id = I.id
    WHERE
     F.faver_profile_id = {$id} AND
     I.removed = 0 AND
     I.nudity = 0 AND
     F.removed = 0 AND
     F.collection_id is null
    ORDER BY
     F.faver_profile_id,
     F.created DESC
    LIMIT
     15;

Answer 5

我假设该查询的结果旨在显示为分页列表。 在这种情况下，也许您可​​以考虑执行更简单的“未联接查询”，并对每行执行第二次查询以仅读取显示的 15、20 或 30 个元素。 JOIN 不是一个繁重的操作吗？ 这将简化查询，并且当连接的表增长时它不会变得更慢。

如果我错了请告诉我。