MySQL查询在删除一堆记录后仍然很慢

Question

我需要一些帮助来解决一些mysql问题。自上周以来，我的网站一直运行缓慢，在联系我的主机后，我发现一些查询花费了太长时间，主要是因为表锁定。我是一名开发人员，但不是mysql/数据库专家。我的主持人建议我删除和/或将有问题的两个表更改为innoDB。由于这些表有很多垃圾数据，我决定删除一堆记录。我想说，这两张表的大小大约是这个问题开始时的25%。问题是，它仍然没有产生任何影响。所以我的问题是：

1. 是否需要清除缓存或优化表才能查看效果？我的主机仍然建议我将这些表更改为innoDB这很好，但我不知道为什么删除这么多记录没有什么不同
2. 我还读到重新创建表而不仅仅是优化？如果需要，我可以雇佣数据库管理员来帮助我，但我想至少尝试一些如果这是一件简单的事情。有人能指引我吗通过这个

需要添加的一件更重要的事情是，它是一个运行在php5.4和mysql5.6 上的遗留网站

以下是锁定表的示例查询之一。

SELECT `m`.`message_id`, COUNT(`m`.`message_id`) AS `mails_count`, `m`.`sender_id`, `m`.`recipient_id`, 
`m`.`text`, `m`.`is_readable`, `m`.`time_stamp` AS `last_message_ts`, `c`.`conversation_id`, `c`.*, `ms`.`is_replied`
FROM `mailbox_conversation` AS `c` 
INNER JOIN (
SELECT * FROM `mailbox_message` 
WHERE `recipient_id`=67404  AND IF (`sender_id`!=67404, `status`='a', 1) ORDER BY `time_stamp` DESC  
) AS `m` ON(`m`.`conversation_id`=`c`.`conversation_id`) 
INNER JOIN (
SELECT `conversation_id`, IF(`sender_id`=67404,'yes','no') AS `is_replied` FROM `mailbox_message` 
WHERE (`recipient_id`=67404 OR `sender_id`=67404) ORDER BY `time_stamp` DESC 
) AS `ms` ON(`ms`.`conversation_id`=`c`.`conversation_id`)
WHERE (`c`.`initiator_id`=67404 OR `interlocutor_id`=67404)
AND `c`.`bm_deleted` NOT IN (IF(`c`.`initiator_id`=67404, '1, 3','2, 3'))           
AND IF (`sender_id`!=67404, `status`='a', 1)
GROUP BY `c`.`conversation_id`
ORDER BY `m`.`time_stamp` DESC  LIMIT 0,15;

谢谢！

Answer 1

以下是降低复杂查询速度的因素：

(1(

GROUP BY  `c`.`conversation_id`
ORDER BY  `m`.`time_stamp` DESC
LIMIT  0,15;

如果它只能读取15行，那就太好了。但这是不可能的，因为GROUP BY和ORDER BY不匹配。此外，它们涉及多个表，从而消除了使用索引的情况。(INDEX只能引用一个表。(

(2(

AND  IF (`sender_id`!=67404, `status`='a', 1)
(`recipient_id`=67404  OR  `sender_id`=67404)
(`c`.`initiator_id`=67404  OR  `interlocutor_id`=67404)

这两者都过于复杂，无法使用任何索引。OR是性能杀手；它有时可以变成CCD_ 5。(但你需要嵌套的UNIONs来处理这里的所有内容。(如果可以写sender_id = 67404 or status='a'，它不会更快，但(对我来说(会更清晰。

一种情况：

( SELECT ... from mailbox_message WHERE `recipient_id`=67404 )
UNION ALL
( SELECT ... from mailbox_message WHERE `sender_id`=67404 )

此UNION需要：INDEX(recipient_id)和INDEX(sender_id)。

(3(

JOIN ( SELECT ... )

这通常是不可优化的，尤其是当存在多个这样的情况时。

(4(

JOIN ( SELECT ... ORDER BY ... )

ORDER BY将被忽略。参见ONLY_FULL_GROUP_BY。

(5(

SELECT *

如果您最终不需要任何庞大的列，*可能会降低一些性能。拼写出所需的列。