sql - SQL中派生表的性能问题

我在MySQL中使用派生表时遇到问题。使用派生表会固有地减慢查询的处理速度吗？

这是我要运行的查询。它不会执行，只会超时。

它确实成功了。确实，我已将问题隔离到最后一个联接。当我取出最后一个连接时，它工作正常。但是，当我在其中添加最后一个联接时，它拒绝执行。

SELECT cr.COMMUNICATIONS_ID AS ANSWER_ID,
       cr.CONSUMER_ID as VIEWER_ID,
       cr.ACTION_LOG_ID,
       nc.PARENT_COMMUNICATIONS_ID AS QUESTION_ID,
       nc.SENDER_CONSUMER_ID AS REPLIER_ID,
       ces.EXPERT_SCORE AS REPLIER_EXPERTISE,
       cim.CONSUMER_INTEREST_EXPERT_ID AS DOMAIN
    FROM (SELECT 234 AS CONSUMER_ID,
    ACTION_LOG_ID,
    COMMUNICATIONS_ID
    FROM consumer_action_log
    WHERE COMM_TYPE_ID=4) AS cr
JOIN network_communications AS nc ON
cr.COMMUNICATIONS_ID=nc.COMMUNICATIONS_ID
JOIN communication_interest_mapping AS cim ON
nc.PARENT_COMMUNICATIONS_ID=cim.COMMUNICATION_ID
JOIN consumer_expert_score AS ces ON
nc.SENDER_CONSUMER_ID=ces.CONSUMER_ID
        AND cim.CONSUMER_INTEREST_EXPERT_ID=ces.CONSUMER_EXPERT_ID;

最佳答案

希望对您有所帮助...这是一些mysql CREATE INDEX语句。基本上，如果可以添加索引，请确保有一个索引可以覆盖连接2个或更多表的每个列。

CREATE INDEX idx_nc
ON network_communications(COMMUNICATIONS_ID);

CREATE INDEX idx_cim
ON communication_interest_mapping(COMMUNICATION_ID);

CREATE INDEX idx_ces
ON consumer_expert_score(CONSUMER_ID, CONSUMER_EXPERT_ID);

派生表并不是天生就坏，但是在这种情况下（请参见下文），您将从consumer_action_log中提取了comm_type_id为4的所有记录。似乎没有与其他表的连接。这可能是sql永不返回的原因。

SELECT cr.COMMUNICATIONS_ID,
           cr.CONSUMER_ID,
           cr.ACTION_LOG_ID,
           nc.PARENT_COMMUNICATIONS_ID,
           nc.SENDER_CONSUMER_ID,
           ces.EXPERT_SCORE,
           cim.CONSUMER_INTEREST_EXPERT_ID

    FROM (SELECT 234 AS CONSUMER_ID,
             ACTION_LOG_ID,
             COMMUNICATIONS_ID
             FROM consumer_action_log
             WHERE COMM_TYPE_ID=4) AS cr

JOIN network_communications AS nc ON
         cr.COMMUNICATIONS_ID=nc.COMMUNICATIONS_ID

JOIN communication_interest_mapping AS cim ON
         nc.PARENT_COMMUNICATIONS_ID=cim.COMMUNICATION_ID

JOIN consumer_expert_score AS ces ON
         nc.SENDER_CONSUMER_ID=ces.CONSUMER_ID
        AND cim.CONSUMER_INTEREST_EXPERT_ID=ces.CONSUMER_EXPERT_ID;