为什么STRAIGHT_JOIN消耗更多的CPU？

Question

为什么STRAIGHT_JOIN比普通连接消耗更多的CPU？你有什么想法吗？

当我在一个查询中使用straight_join时，它会将查询速度从12秒提高到3秒。但它消耗了那么多CPU？可能是关于服务器配置还是其他什么的？

在此注释/主题Ids确定后，您可能需要检查代码，获取设备./

在这行之前，有一些关于将topic_ids填充到临时表的代码。

以下是查询：

CREATE PROCEDURE `DevicesByTopic`(IN platform TINYINT, IN application TINYINT, IN topicList TEXT, IN page_no MEDIUMINT UNSIGNED)
BEGIN

    DECLARE m_index INT DEFAULT 0;
    DECLARE m_topic VARCHAR(255);
    DECLARE m_topic_id BIGINT UNSIGNED DEFAULT NULL;
    DECLARE m_session_id VARCHAR(40) CHARSET utf8 COLLATE utf8_turkish_ci;

    -- Session Id 
    SET m_session_id = replace(uuid(), '-', '');    

    -- Temp table
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_topics(
        topic_slug VARCHAR(100) COLLATE utf8_turkish_ci
        ,topic_id BIGINT UNSIGNED
        ,session_id VARCHAR(40) COLLATE utf8_turkish_ci
        ,INDEX idx_tmp_topic_session_id (session_id)
        ,INDEX idx_tmp_topic_id (topic_id)
    ) CHARSET=utf8 COLLATE=utf8_turkish_ci; 

    -- Filling topics in a loop
    loop_topics: LOOP
        SET m_index = m_index + 1;      

        SET m_topic_id = NULL;      
        SET m_topic= SPLIT_STR(topicList,',', m_index);                     

        IF m_topic = '' THEN
            LEAVE loop_topics;      
        END IF;         

        SELECT t.topic_id INTO m_topic_id FROM topic AS t WHERE t.application = application AND (t.slug_hashed = UNHEX(MD5(m_topic)) AND t.slug = m_topic) LIMIT 1;

        -- Fill temp table
        IF m_topic_id IS NOT NULL AND m_topic_id > 0 THEN           
            INSERT INTO tmp_topics 
                (topic_slug, topic_id, session_id)           
            VALUES
                (m_topic, m_topic_id, m_session_id);            
        END IF;

    END LOOP loop_topics;       

    /* Topic Ids are OK, Getting Devices... */

  SELECT 
    dr.device_id, dr.platform, dr.application, dr.unique_device_id, dr.amazon_arn   
  FROM 
    device AS dr
  INNER JOIN (
            SELECT STRAIGHT_JOIN      
            DISTINCT
                d.device_id
            FROM
                device AS d
            INNER JOIN 
                device_user AS du ON du.device_id = d.device_id             
            INNER JOIN 
                topic_device_user AS tdu ON tdu.device_user_id = du.device_user_id
            INNER JOIN
                tmp_topics AS tmp_t ON tmp_t.topic_id = tdu.topic_id
            WHERE
                ((platform IS NULL OR d.platform = platform) AND d.application = application)
                AND d.page_no = page_no     
        AND d.status = 1
                AND du.status = 1               
                AND tmp_t.session_id = m_session_id COLLATE utf8_turkish_ci 
  ) dFiltered ON dFiltered.device_id = dr.device_id
  WHERE
        ((platform IS NULL OR dr.platform = platform) AND dr.application = application)
        AND dr.page_no = page_no        
        AND dr.status = 1;

    -- Delete rows fFill temp table
    DELETE FROM tmp_topics WHERE session_id = m_session_id;

END;

对于STRAIGHT_JOIN，这个查询大约需要3秒，但是消耗了大约90%的CPU，但是如果删除关键字"STRAIGHT_JOIN"，则需要12秒，但消耗12%的CPU。

MySQL 5.6.19a -无害b

可能是什么原因？

诚挚的问候。

Answer 1

当您需要重写MySQL的优化器时，将使用STRAIGHT_JOIN。您告诉它忽略它自己的优化执行路径，而是按照您在查询中所写的表的顺序来读取表。

99%的情况下，您不想使用straight_join。只需依靠MySQL来完成它的工作，并为您优化执行路径。毕竟，任何值得它的盐的RDBMS在优化方面都是相当不错的。

您应该使用straight_join的几次时间是在您已经测试了MySQL对给定查询的优化并发现它缺乏时。在使用此查询的情况下，使用straight_join的手动优化显然并不比MySQL的优化更好。