mysql - SQL查询优化(Mysql 5.7)

Question

我有以下查询要在实时 mysql 5.7 中执行，这需要大约 7 秒，我们希望减少他们的时间，请分享您的想法

SELECT 
     COUNT(
          DISTINCT InternalInquiry.inquiry_id
     ) AS "count" 
FROM
     internal_inquiries `InternalInquiry` 
     LEFT JOIN cpml_projects `Project` 
          ON (
               Project.project_id = InternalInquiry.object_id 
               AND Project.agent_id = 159297
          ) 
     INNER JOIN clients `Client` 
          ON (
               Client.clientid = InternalInquiry.clientID
          ) 
     INNER JOIN 
          (SELECT DISTINCT 
               (InternalInquiry.inquiry_id) AS "id" 
          FROM
               internal_inquiries `InternalInquiry` 
                LEFT JOIN internal_inquiries_sharing `InternalInquirySharing` 
                    ON (
                         InternalInquiry.inquiry_id = InternalInquirySharing.inquiry_id 
                    )
          WHERE (
                    (
                      InternalInquiry.userid IN ( 399724, 420710, 438389, 535916, 535917, 541643, 541644)
                       OR InternalInquirySharing.share_with IN ( 399724, 420710, 438389, 535916, 535917, 541643, 541644)
                     ) 
               ) 
               AND  (
                    InternalInquiry.status IN (
                         1, 21, 26, 14, 4, 15, 13, 5, 16, 17, 18, 22, 25, 2, 10, 3, 19, 20, 23, 11, 12, 24, 9, 8, 28
                    )
               ) 
               AND (
                    InternalInquiry.time_added >= '2013-11-01 00:00:00' 
                    AND InternalInquiry.time_added <= '2018-11-22 23:59:59'
               )) dd 
          ON dd.id = InternalInquiry.inquiry_id 
WHERE (
          InternalInquiry.firmstate != 'deleted'
     ) 
     AND (
          InternalInquiry.status IN (
               1, 21, 26, 14, 4, 15, 13, 5, 16, 17, 18, 22, 25, 2, 10, 3, 19, 20, 23, 11, 12, 24, 9, 8, 28
          )
     ) 
     AND (
          InternalInquiry.time_added >= '2013-11-01 00:00:00' 
          AND InternalInquiry.time_added <= '2018-11-22 23:59:59'
     ) 
     AND (
           Client.client_status != 1 AND
           Client.client_agency = 159297
     )

执行计划如下:

 id  select_type  table                   partitions  type    possible_keys                                                                                                         key                               key_len  ref                                       rows  filtered  Extra                         
------  -----------  ----------------------  ----------  ------  --------------------------------------------------------------------------------------------------------------------  --------------------------------  -------  --------------------------------------  ------  --------  ------------------------------
     1  PRIMARY      <derived2>              (NULL)      ALL     (NULL)                                                                                                                (NULL)                            (NULL)   (NULL)                                  143661    100.00  (NULL)                        
     1  PRIMARY      InternalInquiry         (NULL)      eq_ref  PRIMARY,clientID,time_added,firmstate,search_inquiry_basic_index                                                      PRIMARY                           4        dd.id                                        1     12.50  Using where                   
     1  PRIMARY      Client                  (NULL)      eq_ref  PRIMARY,client_agency,client_status,com_1                                                                             PRIMARY                           4        InternalInquiry.clientID                     1     25.00  Using where                   
     1  PRIMARY      Project                 (NULL)      ref     project_id                                                                                                            project_id                        5        InternalInquiry.object_id                    1    100.00  Using where                   
     2  DERIVED      InternalInquiry         (NULL)      ALL     PRIMARY,clientID,userid,object_type,next_status_wanted,time_added,firmstate,search_inquiry_basic_index,idx_object_id  (NULL)                            (NULL)   (NULL)                                  544996     25.00  Using where; Using temporary  
     2  DERIVED      InternalInquirySharing  (NULL)      ref     internal_inquiries_sharing_UN,inquiry_id                                                                               internal_inquiries_sharing_UN    5        InternalInquiry.inquiry_id                   1    100.00  Using where; Using index      

我正在分享主表的索引信息

Answer 1

主要问题是您没有覆盖主表的内部和外部查询中主要条件的索引。可能还有更多的微调，但这应该是一个好的开始:

CREATE INDEX ix_internal_inquiries_status_timeadded
    ON internal_inquiries
    (status, time_added);

此外，带有 inquiry_id 和/或 firmstate 的版本可能有值(value)，但必须经过测试:

CREATE INDEX ix_internal_inquiries_inquiryid_status_timeadded, firmstate
    ON internal_inquiries
    (inquiry_id, status, time_added, firmstate);

尝试几种组合，看看是否能让您更进一步。