MySQL 查询陷入状态 "Sending Data"

Question

我有以下 MySQL 查询，该查询卡在“正在发送数据”状态并在过去 14 小时内运行。查询有 3 部分。第一部分是表 AGG_EI 上的自联接，它获取最大日期的 uuid、cmp_id、lTypeId 的不同组合。第二部分从表 CUL 中获取 uuid、cmp_id、lType_id、date。第一部分和第二部分连接并存储在临时表中。第三部分从临时表中选择数据并存储在CUL表中。AGG_EI 表有 450 万条记录(重复的 uuid、cmp_id、lTypeId、date 与查询中未使用的其他列的组合)，CUL 有 70 万条记录(uuid、cmp_id、lTypeId、date 的唯一组合)

  CREATE TEMPORARY TABLE IF NOT EXISTS temp_lt 
  SELECT cLType.uuid AS uuid, 
    cLType.cmp_id AS cmp_id, 
    cLType.lTypeId AS lTypeId, 
    (CASE WHEN cLType.lTypeId = eLType.lTypeId THEN eLType.lFrom 
             WHEN eLType.lFrom IS NULL THEN DATE_FORMAT(now(),'%Y%m%d') ELSE cLType.lFrom END) AS lFrom 
    FROM
         (
          SELECT DISTINCT d1.ei_uuid AS uuid, 
                          d1.cmp_id AS cmp_id,
                          d1.ei_type AS lTypeId,
                          d1.datedm_id AS lFrom
                          FROM AGG_EI d1 
                              LEFT OUTER JOIN AGG_EI d2 
                                              ON (d1.ei_uuid = d2.ei_uuid AND d1.cmp_id = d2.cmp_id AND d1.datedm_id < d2.datedm_id ) 
                          WHERE d2.ei_uuid IS NULL AND d2.cmp_id IS NULL
          ) AS cLType
          LEFT OUTER JOIN 
          (
            SELECT uuid AS uuid, 
                   cmp_id AS cmp_id,
                   ei_type AS lTypeId,
                   lFrom AS lFrom
                   FROM 
                       CUL
           ) AS eLType
           ON cLType.uuid = eLType.uuid AND cLType.cmp_id = eLType.cmp_id;



    INSERT INTO `CUL` (`uuid`,`cmp_id`,`ei_type`,`lFrom`)
    SELECT uuid,cmp_id,lTypeId,lFrom FROM temp_lt;

为什么此查询会卡在“正在发送数据”状态

解释:

1   PRIMARY <derived2>  ALL                 2420567 
1   PRIMARY <derived3>  ALL                 687777  
3   DERIVED CUL index       comp_uuid   468     688032  Using index
2   DERIVED d1  index       datedm_id_UNIQUE    492     4046120 Using index; Using temporary
2   DERIVED d2  ref datedm_id_UNIQUE,agg_ei_comdm_fk_idx,agg_ei_datedm_fk_idx,comp_uuid comp_uuid   460 ol.d1.com_id,ol.d1.ei_uuid  1   Using where; Using index; Not exists

Answer 1

使用 DESCRIBE 来解释你的查询并查看它在哪里变慢。有时发送数据意味着 Mysql 有数据存储在磁盘上尚未读取。