안녕하세요.

로그성 테이블의 속도가 너무 느려져서 index를 구성하던 중 해결이 되지 않는 쿼리가 생겨 질문드립니다.

버전 : MariaDB 10.3

테이블 데이터 수 : 약 600만건 / 일 10만건~20만건 데이터 insert

* DDL 

-- auto-generated definition
CREATE TABLE MS_SNSR_LOG
(
    NO        BIGINT AUTO_INCREMENT COMMENT 'SEQ_NO',
    CUST_CD   VARCHAR(6)       NOT NULL COMMENT '고객코드',
    FACT_CD   VARCHAR(20)      NOT NULL COMMENT '공장코드',
    SNSR_CD   VARCHAR(20)      NOT NULL COMMENT '센서코드',
    TPBT_CD   VARCHAR(20)      NULL COMMENT '이탈코드',
    SNSR_ID   VARCHAR(20)      NULL COMMENT '센서ID',
    SNLG_TP   DECIMAL(11, 3)   NULL COMMENT '측정온도',
    PRPL_CD   VARCHAR(20)      NULL COMMENT '생산계획코드',
    PRODR_CD  VARCHAR(20)      NULL COMMENT '생산지시코드',
    PRPCS_CD  VARCHAR(20)      NULL COMMENT '생산공정코드',
    PROUT_CD  VARCHAR(20)      NULL COMMENT '생산실적코드',
    BOLT_YN   CHAR             NULL COMMENT '이탈여부',
    SNLG_YN   CHAR DEFAULT 'Y' NOT NULL COMMENT '사용여부',
    SNLG_NT   VARCHAR(20)      NULL COMMENT '비고',
    SNLG_CRDT DATETIME         NOT NULL COMMENT '등록일시',
    SNLG_UPDT DATETIME         NOT NULL COMMENT '수정일시',
    SNLG_SABN VARCHAR(20)      NULL COMMENT '등록수정자',
    SNLG_DT   DATE             NULL,
    SNLG_TIME VARCHAR(20)      NULL,
    PRIMARY KEY (NO, CUST_CD, FACT_CD, SNSR_CD),
    CONSTRAINT FK_BS_SNSR_INFO_TO_MS_SNSR_LOG
        FOREIGN KEY (CUST_CD, FACT_CD, SNSR_CD) REFERENCES BS_SNSR_INFO (CUST_CD, FACT_CD, SNSR_CD)
)
    COMMENT '센서이력' CHARSET = UTF8;

CREATE INDEX MS_SNSR_LOG_CUST_CD_FACT_CD_SNSR_CD_SNLG_DT_INDEX
    ON MS_SNSR_LOG (CUST_CD, FACT_CD, SNSR_CD, SNLG_DT);

CREATE INDEX MS_SNSR_LOG_SNLG_TIME_INDEX
    ON MS_SNSR_LOG (SNLG_TIME);

CREATE INDEX MS_SNSR_LOG_SNLG_UPDT_INDEX
    ON MS_SNSR_LOG (SNLG_UPDT);

현재 index는 저렇게 잡아놓은 상태입니다.

문제가 되는 쿼리는

SELECT SNLG_TIME
    ,MAX(IF(SNSR_CD = '20200331111725205513', SNLG_TP, '')) AS SNSR_01
    ,MAX(IF(SNSR_CD = '20200331111741060253', SNLG_TP, '')) AS SNSR_02
    ,MAX(IF(SNSR_CD = '20200331111751209651', SNLG_TP, '')) AS SNSR_03
    ,MAX(IF(SNSR_CD = '20200429150000000000', SNLG_TP, '')) AS SNSR_04
    ,MAX(IF(SNSR_CD = '20200331111751209652', SNLG_TP, '')) AS SNSR_05
    ,MAX(IF(SNSR_CD = '20200331111751209653', SNLG_TP, '')) AS SNSR_06
FROM
(
    SELECT MSL.SNSR_CD
        ,SNLG_TP
        ,SNLG_TIME
    FROM MS_SNSR_LOG MSL
    WHERE MSL.CUST_CD = '123456'
    AND MSL.FACT_CD = '20181018123456781000'
    AND MSL.SNSR_CD IN ('20200331111725205513', '20200331111741060253', '20200331111751209651', '20200429150000000000', '20200331111751209652', '20200331111751209653')
    AND SNLG_DT >= '2020-12-07'
    AND SNLG_DT <= '2021-01-07'
 ) AA
GROUP BY SNLG_TIME
ORDER BY SNLG_TIME DESC

;

이 쿼리입니다.

AA의 안쪽쿼리는 EXPLAIN을 사용했을 때 MS_SNSR_LOG_CUST_CD_FACT_CD_SNSR_CD_SNLG_DT_INDEX를 사용하며 1초안쪽으로 결과가 나옵니다.

문제는 전체 쿼리를 실행했을 때 index가 정상적으로 타지지 않습니다.

SNLG_TIME이란 컬럼으로 GROUP BY와 ORDER BY를 사용하기 때문에 해당 컬럼으로만 index를 생성해도 똑같은 결과입니다. 실행시간은 30초 이상입니다.

GROUP BY와 ORDER BY절에서 해당 컬럼 사용 시 INDEX를 제가 잘못잡은 것인지 아니면 쿼리를 잘못짠 것인지 궁금합니다.

이상입니다. 감사합니다.