Mysql 存储过程及函数优化

 1、尽量不使用自关联查询，尤其是在大数据量的时候耗时体现的非常突出。

这是原查询函数:

CREATE FUNCTION get_channel_activation_count(p_channel_id  BIGINT,
                                                         p_date_string VARCHAR(255))
  RETURNS int(11)
  SQL SECURITY INVOKER
  COMMENT '******'
BEGIN
  -- 开始时间戳、结束时间戳
  DECLARE var_timestamp_begin, var_timestamp_end  TIMESTAMP;
  DECLARE result                                  BIGINT DEFAULT 0;

  -- 检查输入参数
  IF (ifnull(DATE_FORMAT(p_date_string, '%Y-%m-%d'), NULL) IS NULL) THEN
    CALL pro_protreat_errorinfo(DATE_FORMAT(now(), '%Y%m%d'), '******，日期格式错误');
    RETURN 0;
  END IF;

  SET var_timestamp_begin = str_to_date(concat(p_date_string, ' 00:00:00'), '%Y%m%d %k:%i:%s');
  SET var_timestamp_end = str_to_date(concat(p_date_string, ' 23:59:59'), '%Y%m%d %k:%i:%s');

  SELECT count(*)
  INTO
    result
  FROM
    (
    SELECT T1.CHANNEL_ID
    FROM
      (
      SELECT t.CHANNEL_ID
           , t.APP_PAG_ID
           , t.IMEI
           , max(t.ACTIVATION_TIME) AS ACTIVATION_TIME
           , ifnull(AI.ACTIVE_HOUR, 1) AS ACTIVE_HOUR
           , ifnull(AI.ACTIVE_DAY, 30) AS ACTIVE_DAY
      FROM
        YG_PRETREAT_ACTIVATION_RECORD t
      INNER JOIN YG_CHANNEL_APP_GD CH_APP
      ON CH_APP.APP_BAG_ID = t.APP_PAG_ID
      AND CH_APP.CHL_ID = t.CHANNEL_ID AND CH_APP.STATE = 1
      INNER JOIN APP_PAG_INFO I
      ON I.ID = t.APP_PAG_ID AND I.STATE = 1  AND I.APP_FROM IN(4,5,12)
      INNER JOIN APP_INFO AI
      ON AI.ID = I.APP_ID
      WHERE
        t.ACTIVATION_COUNT = 1
        AND t.CHANNEL_ID = p_channel_id
      GROUP BY
        t.APP_PAG_ID
      , t.IMEI
      , t.CHANNEL_ID
      ) T1
    INNER JOIN (
      SELECT t.CHANNEL_ID
           , t.APP_PAG_ID
           , t.IMEI
           , t.ACTIVATION_TIME
           , t.ACTIVATION_COUNT
      FROM
        YG_PRETREAT_ACTIVATION_RECORD t
      INNER JOIN YG_CHANNEL_APP_GD CH_APP
      ON CH_APP.APP_BAG_ID = t.APP_PAG_ID
      AND CH_APP.CHL_ID = t.CHANNEL_ID AND CH_APP.STATE = 1
      INNER JOIN APP_PAG_INFO APP_PAG
      ON APP_PAG.ID = t.APP_PAG_ID
      AND APP_PAG.STATE = 1 AND APP_PAG.APP_FROM IN(4,5,12)
      WHERE
        t.CREATE_TIME >= var_timestamp_begin
        AND t.CREATE_TIME <= var_timestamp_end
      ) T2
    ON T2.APP_PAG_ID = T1.APP_PAG_ID
    AND T2.IMEI = T1.IMEI
    AND T2.CHANNEL_ID = T1.CHANNEL_ID
    WHERE
      T2.ACTIVATION_TIME BETWEEN date_add(T1.ACTIVATION_TIME, INTERVAL T1.ACTIVE_HOUR DAY)
      AND date_add(T1.ACTIVATION_TIME, INTERVAL T1.ACTIVE_DAY DAY)
    GROUP BY
      T1.CHANNEL_ID
    , T1.APP_PAG_ID
    , T1.IMEI
    ) t_ac;
  RETURN result;
END

该计算函数在整个预处理过程中耗时相当久,在目前的数据量之下耗时超过5个小时...

用游标修改过后耗时直接下降到12分钟

CREATE FUNCTION get_channel_activation_count(p_channel_id  BIGINT,
                                                         p_date_string VARCHAR(255))
  RETURNS int(11)
  SQL SECURITY INVOKER
  COMMENT '******'
BEGIN -- 开始时间戳、结束时间戳
  DECLARE var_timestamp_begin, var_timestamp_end                             TIMESTAMP;
  DECLARE the_last                                                           INT DEFAULT 0;
  DECLARE result, result_tmp                                                 BIGINT DEFAULT 0;
  DECLARE var_active_time                                                    TIMESTAMP;
  DECLARE var_channel_id, var_app_pag_id, var_active_hour, var_active_day    BIGINT;

  DECLARE var_imei                                                           VARCHAR(255);

  DECLARE cur_pre_activation CURSOR FOR SELECT t.CHANNEL_ID
                                             , t.APP_PAG_ID
                                             , t.IMEI
                                             , max(t.ACTIVATION_TIME) AS ACTIVATION_TIME
                                             , ifnull(AI.ACTIVE_HOUR, 1) AS ACTIVE_HOUR
                                             , ifnull(AI.ACTIVE_DAY, 30) AS ACTIVE_DAY
                                        FROM
                                          YG_PRETREAT_ACTIVATION_RECORD t
                                        INNER JOIN YG_CHANNEL_APP_GD CH_APP
                                        ON CH_APP.APP_BAG_ID = t.APP_PAG_ID
                                        AND CH_APP.CHL_ID = t.CHANNEL_ID AND CH_APP.STATE = 1
                                        INNER JOIN APP_PAG_INFO I
                                        ON I.ID = t.APP_PAG_ID AND I.STATE = 1
                                        INNER JOIN APP_INFO AI
                                        ON AI.ID = I.APP_ID
                                        WHERE
                                          t.ACTIVATION_COUNT = 1
                                          AND t.CHANNEL_ID = p_channel_id
                                        GROUP BY
                                          t.APP_PAG_ID
                                        , t.IMEI
                                        , t.CHANNEL_ID;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET the_last = 1;

  -- 检查输入参数
  IF (ifnull(DATE_FORMAT(p_date_string, '%Y-%m-%d'), NULL) IS NULL) THEN
    CALL pro_protreat_errorinfo(DATE_FORMAT(now(), '%Y%m%d'), '******，日期格式错误');
    RETURN 0;
  END IF;

  SET var_timestamp_begin = str_to_date(concat(p_date_string, ' 00:00:00'), '%Y%m%d %k:%i:%s');
  SET var_timestamp_end = str_to_date(concat(p_date_string, ' 23:59:59'), '%Y%m%d %k:%i:%s');

  OPEN cur_pre_activation;

loop_ac:
  LOOP
    FETCH cur_pre_activation INTO var_channel_id, var_app_pag_id, var_imei, var_active_time, var_active_hour, var_active_day;
    IF (the_last = 1) THEN
      LEAVE loop_ac;
    END IF;
    SET result_tmp = 0;

    -- CALL pro_protreat_errorinfo(DATE_FORMAT(now(), '%Y%m%d'), concat('s', result_tmp, 'var_channel_id=', var_channel_id, ' var_app_pag_id', var_app_pag_id, 'var_imei', var_imei));

    SELECT count(1)
    INTO
      result_tmp
    FROM
      (
      SELECT t.CHANNEL_ID
           , t.APP_PAG_ID
           , t.IMEI
      FROM
        YG_PRETREAT_ACTIVATION_RECORD t
      INNER JOIN YG_CHANNEL_APP_GD CH_APP
      ON CH_APP.APP_BAG_ID = t.APP_PAG_ID
      AND CH_APP.CHL_ID = t.CHANNEL_ID AND CH_APP.STATE = 1
      INNER JOIN APP_PAG_INFO APP_PAG
      ON APP_PAG.ID = t.APP_PAG_ID
      AND APP_PAG.STATE = 1
      WHERE
        t.CREATE_TIME >= var_timestamp_begin
        AND t.CREATE_TIME <= var_timestamp_end
        AND t.APP_PAG_ID = var_app_pag_id
        AND t.IMEI = var_imei
        AND t.CHANNEL_ID = var_channel_id
        AND t.ACTIVATION_TIME BETWEEN date_add(var_active_time, INTERVAL var_active_hour DAY)
        AND date_add(var_active_time, INTERVAL var_active_day DAY)
      GROUP BY
        t.CHANNEL_ID
      , t.APP_PAG_ID
      , t.IMEI) a;

    SET result = result + result_tmp;

  END LOOP;
  CLOSE cur_pre_activation;

  RETURN result;
END

在此做个mark