在livemysql 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我正在共享主表的索引信息
2条答案
按热度按时间vwoqyblh1#
主要问题是，在主表的内部和外部查询中，没有一个包含主要条件的索引。可能还有一些微调，但这应该是一个良好的开端：
此外，具有
inquiry_id和/或firmstate可能有价值，但必须测试：试几个组合，看看能不能让你更进一步。
6qftjkof2#
首先，您只获得一个计数，但从未在projects表上使用内部联接，因此您不需要这样做。我会把它拿走。您的内部查询和外部查询在状态、日期/时间上都使用相同的条件。所以我会从内部删除它，只保留您的用户id/sharewith条件。然后，我将客户机where条件移动到该示例的join子句中。
将日期简化为2013-11-01，因为没有时间意味着00:00:00。同时将结束时间更改为小于2018-11-23，这意味着包括2018-11-22至晚上11:59:59。
最后，对共享上下文进行内部查询。我们知道这是一个必须通过内部加入，似乎你只关心在查询或共享的特定用户。所以我在共享上稍微改为左连接，但只用于与user的另一个共享，并转移到where子句
此外，我将确保在相应的表上存在以下索引
因为外部查询是基于状态和时间预先限制的，所以我们知道我们正在获取所有可能的用户，不需要将额外的条件添加到共享的连接中。where子句的最后一部分将适用于单个用户或作为共享找到的用户。
对查询的最后一个可能的添加是通过