优化一个复杂的查询

问题描述：

我试图优化我使用MySQL EXPLAIN写过的最长的查询，但由于这是我的第一个，我似乎无法理解结果。这里的查询，并从运行EXPLAIN命令的结果：优化一个复杂的查询

EXPLAIN SELECT pb.name, s1.MessageFrom, s1.MessageText, s1.SendTime, s1.is_unread, s1.Id, s1.autoreply_sent FROM sol_inbound s1 
    JOIN sol_contactnum c ON s1.MessageFrom = c.number 
    JOIN sol_phonebk_contactnum USING (contactnum_id) 
    JOIN sol_phonebk pb USING (phonebk_id) 
    JOIN sol_message_folder mf ON s1.Id = mf.message_id 
    WHERE (MessageFrom, SendTime) IN (SELECT MessageFrom, MAX(SendTime) FROM sol_inbound inb 
     JOIN sol_message_folder mf WHERE inb.Id = mf.message_id 
     AND mf.folder_id=1 AND mf.direction='inbound' AND mf.user_id=1 
     GROUP BY MessageFrom) 
    AND mf.folder_id=1 AND mf.direction='inbound' AND mf.user_id=1 
    UNION 
    SELECT NULL `name`, s1.MessageFrom, s1.MessageText, s1.SendTime, s1.is_unread, s1.Id, s1.autoreply_sent FROM sol_inbound s1 
    LEFT JOIN sol_contactnum c ON s1.MessageFrom = c.number 
    JOIN sol_message_folder mf ON s1.Id = mf.message_id 
    WHERE c.number IS NULL 
    AND mf.folder_id=1 AND mf.direction='inbound' AND mf.user_id=1 
    AND (MessageFrom, SendTime) IN (SELECT MessageFrom, MAX(SendTime) FROM sol_inbound inb 
     JOIN sol_message_folder mf WHERE inb.Id = mf.message_id 
     AND mf.folder_id=1 AND mf.direction='inbound' AND mf.user_id=1 
     GROUP BY MessageFrom) 
    ORDER BY SendTime DESC LIMIT 100

的EXPLAIN结果：谁的数字出现在电话簿与

id select_type   table     type possible_keys             key    key_len ref              rows Extra     
------ ------------------ ---------------------- ------ ------------------------------------------------------------- ---------------- ------- ---------------------------------------------------- ------ ------------------------ 
    1 PRIMARY    pb      ALL  PRIMARY              (NULL)   (NULL) (NULL)             303       
    1 PRIMARY    sol_phonebk_contactnum ref  PRIMARY,phonebk_id1_idx,contactnum_id1_idx,phonebk_contactnum PRIMARY   4  googlep1_solane.pb.phonebk_id        1 Using index    
    1 PRIMARY    c      eq_ref PRIMARY,number_idx            PRIMARY   4  googlep1_solane.sol_phonebk_contactnum.contactnum_id  1       
    1 PRIMARY    s1      ref  PRIMARY,message_from_idx          message_from_idx 243  googlep1_solane.c.number         1 Using where    
    1 PRIMARY    mf      eq_ref PRIMARY              PRIMARY   22  const,googlep1_solane.s1.Id,const,const     1 Using where; Using index 
    2 DEPENDENT SUBQUERY inb      index PRIMARY              message_from_idx 243  (NULL)              1       
    2 DEPENDENT SUBQUERY mf      eq_ref PRIMARY              PRIMARY   22  const,googlep1_solane.inb.Id,const,const     1 Using where; Using index 
    3 UNION    s1      ALL  PRIMARY              (NULL)   (NULL) (NULL)             877 Using where    
    3 UNION    c      ref  number_idx              number_idx  243  googlep1_solane.s1.MessageFrom        1 Using where; Using index 
    3 UNION    mf      eq_ref PRIMARY              PRIMARY   22  const,googlep1_solane.s1.Id,const,const     1 Using where; Using index 
    4 DEPENDENT SUBQUERY inb      index PRIMARY              message_from_idx 243  (NULL)              1       
    4 DEPENDENT SUBQUERY mf      eq_ref PRIMARY              PRIMARY   22  const,googlep1_solane.inb.Id,const,const     1 Using where; Using index 
(NULL) UNION RESULT  <union1,3>    ALL  (NULL)               (NULL)   (NULL) (NULL)            (NULL) Using filesort

查询中间的UNION加入那些谁不要（因此LEFT JOIN）。

编辑：

什么这个查询的作用是让每个号码最多最新的入站消息，并将其返回。我可以使用GROUP BY，因为它返回最旧的消息...我需要最新的消息。然后加入它不中这就是为什么我检查WHERE c.number IS NULL.

什么版本的MySQL？来自http://dev.mysql.com/doc/refman/5.7/en/using-explain.html：“当你在关键字EXPLAIN之前的语句前面时，MySQL显示来自优化器的关于查询执行计划的信息。，MySQL解释了它将如何处理这个声明，包括关于如何连接表以及以何种顺序的信息。“ – MichaelJCox 2013-05-06 15:42:01

版本5.0.10我假设。无论哪个版本随附最新的XAMPP。 – enchance 2013-05-06 15:48:05

基于解释计划，我猜这个查询是非常快的。它给你带来了问题吗？我想我不太确定问题是什么。 – 2013-05-06 15:51:11

答

如果可能的话，按如下方式使用一个关联子查询电话簿存在似乎你已经使用2这些数字...

FROM... x 
JOIN 
    (SELECT MessageFrom 
      , MAX(SendTime) max_sendtime 
     FROM sol_inbound inb 
     JOIN sol_message_folder mf 
     ON inb.Id = mf.message_id 
     WHERE mf.folder_id=1 
     AND mf.direction='inbound' 
     AND mf.user_id=1 
     GROUP 
     BY MessageFrom 
    ) y 
    ON y.messagefrom = x.messagefrom 
AND y.max_sendtime = x.sendtime

答

sub-query's来过滤结果，这些都不是真的需要。我假设你需要显示来自每个MessageFrom id的latest message。

试试这个更快的结果

SELECT 
    * 
FROM 
    (SELECT 
    pb.name, 
    s1.MessageFrom, 
    s1.MessageText, 
    s1.SendTime, 
    s1.is_unread, 
    s1.Id, 
    s1.autoreply_sent, 
    @row_num := IF(@prev_value=s1.MessageFrom,@row_num+1,1) AS row_num, 
    @prev_value := s1.MessageFrom 
    FROM 
    sol_inbound s1 
    JOIN sol_contactnum c ON s1.MessageFrom = c.number 
    JOIN sol_phonebk_contactnum USING (contactnum_id) 
    JOIN sol_phonebk pb USING (phonebk_id) 
    JOIN sol_message_folder mf ON s1.Id = mf.message_id 
    WHERE 
     mf.folder_id=1 
     AND mf.direction='inbound' 
     AND mf.user_id=1 
    ORDER BY 
    s1.MessageFrom, 
    s1.sendTime desc) temp WHERE temp.row_num = 1 

UNION 

SELECT 
    * 
FROM 
    (  
    SELECT 
     NULL `name`, 
     s1.MessageFrom, 
     s1.MessageText, 
     s1.SendTime, 
     s1.is_unread, 
     s1.Id, 
     s1.autoreply_sent, 
     @row_num := IF(@prev_value=s1.MessageFrom,@row_num+1,1) AS row_num, 
     @prev_value := s1.MessageFrom 
    FROM 
     sol_inbound s1 
     LEFT JOIN sol_contactnum c ON s1.MessageFrom = c.number 
     JOIN sol_message_folder mf ON s1.Id = mf.message_id 
    WHERE 
     c.number IS NULL 
     AND mf.folder_id=1 
     AND mf.direction='inbound' 
     AND mf.user_id=1 
    ORDER BY 
     s1.MessageFrom, 
     s1.sendTime desc  
     ) temp2 WHERE temp2.row_num = 1 

ORDER BY 
    SendTime DESC 
LIMIT 100

而不是使用sub-query过滤结果的，我已经利用在MySQL session vars保持秩MessageFrom的所有消息。具有latest sendtime的人有rank作为1

优化一个复杂的查询

相关推荐