logo标签列表

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

mysqlperformancesubquerycbo
4
我正在使用一个通常在一秒内执行的查询，但有时需要10-40秒才能完成。我实际上并不完全清楚子查询的工作原理，只知道它起作用，因为它为每个faverprofileid给出了15行数据。
我正在记录慢查询，并告诉我已经检查了5823244行，这很奇怪，因为涉及的任何表中都没有那么多行（最多只有50000行的收藏夹表）。
是否有人可以给我一些指针？是子查询的问题，需要使用filesort？
编辑：运行解释显示用户表没有使用索引（尽管id是主键）。在extra下显示：Using temporary; Using filesort.
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;
如果您将“select”、“from”、“where”、“and”、“in”和“order by”关键字小写书写，而将“inner”、“join”、“on”和“desc”仅以首字母大写的方式书写，则可以获得更好的语法着色。 - Hosam Aly
你是否正在尝试按创建时间对每个faver_profile_id选择前15个项目？ - Quassnoi
你对表执行过ANALYZE操作了吗？ - vladr
用户表没有使用索引，收藏夹使用的是“removed”索引而不是faver_profile_id。不知何故，删除“removed”索引（我实际上不需要它）解决了这两个问题。我还切换到了GROUP BY 和 HAVING，查询现在运行得非常快。谢谢！ - makeee
5个回答
6

所检查的行数很大，因为许多行已经被检查了多次。 这是由于错误优化的查询计划导致的，当应该进行索引查找时却进行了表扫描。在这种情况下，所检查的行数是指数级的，即与一个以上表中总行数的乘积相当数量级。

  • 确保你已经对三个表运行了ANALYZE TABLE。
  • 阅读如何避免表扫描，并确定并创建任何缺失的索引
  • 重新运行ANALYZE并重新解释你的查询
    • 所检查的行数必须急剧下降
    • 如果不是，请发布完整的解释计划
  • 使用查询提示来强制使用索引（要查看表的索引名称，请使用SHOW INDEX）：
SELECT F.id,F.created,U.username,U.fullname,U.id,I.* FROM favorites AS F FORCE INDEX (faver_profile_id_key) INNER JOIN users AS U FORCE INDEX FOR JOIN (PRIMARY) ON F.faver_profile_id = U.id INNER JOIN items AS I FORCE INDEX FOR JOIN (PRIMARY) 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 FORCE INDEX (faver_profile_id_key) 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;
你也可以将查询改为使用 GROUP BY faver_profile_id/HAVING count > 15，而不是嵌套的 SELECT COUNT(*) 子查询，正如 vartec 建议的那样。如果两者都经过适当的优化（例如使用提示），则您原始查询和 vartec 的查询的性能应该是可比较的（您的查询将使用嵌套索引查找，而 vartec 的查询将使用基于哈希的策略）。
5
我认为使用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中选择哪些字段，因此我放置了占位符。

当我读到他的问题时，首先想到的是使用group by和having... - GordonBy
2

我建议您使用 Mysql Explain Query 来查看您的 MySQL 服务器如何处理查询。我的猜测是您的索引不够优化，但是 explain 应该比我的猜测更准确。

是的，运行 explain 命令显示用户表没有使用索引（尽管 id 是主键）。在 extra 中它说：Using temporary; Using filesort。不确定为什么它不使用索引.. - makeee
0
你可以对每个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;
0

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

如果我错了，请告诉我。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接