改进这个慢速mysql查询的提示？

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

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

任何人都可以给我一些指示吗？这是子查询的问题,需要使用filesort吗？

编辑:运行说明显示users表没有使用索引(即使id是主键).额外的说它:使用临时; 使用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;

vladr.. 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查询将使用基于散列的策略),则原始查询和查询的性能应该是可比较的.

检查的行数表示很大,因为已经多次检查了许多行. 你得到这个是因为错误优化的查询计划导致应该执行索引查找时的表扫描.在这种情况下,检查的行数是指数的,即与一个以上表中的总行数的乘积相当的数量级.

确保在三个表上运行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查询将使用基于散列的策略),则原始查询和查询的性能应该是可比较的.

我认为有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你需要哪些字段,所以我给了占位符.