데이터베이스 질문과 답변

mariadb index 문의드립니다 0 6 876

by Atreyu [MySQL] Mariadb mysql index [2021.12.31 12:04:12]

안녕하세요 

개발 도중 이해가 가지 않는 부분이 있어 문의드립니다.

현재 사용하는 쿼리에서 발생하는 속도 문제입니다.

1,PRIMARY,HDI,range,"PRIMARY,hc_meta_info_CUST_CD_FACT_CD_DOCU_CD_META_DT_index",hc_meta_info_CUST_CD_FACT_CD_DOCU_CD_META_DT_index,148,,7,Using where; Using index
4,DEPENDENT SUBQUERY,MS_META_BOLT,index,,ms_meta_bolt_CUST_CD_FACT_CD_MTBT_DATE_index,36,,2817692,Using where; Using index
3,DEPENDENT SUBQUERY,HC_META_TEST,ref,PRIMARY,PRIMARY,82,"blue_biz_gupanjang.HDI.CUST_CD,blue_biz_gupanjang.HDI.FACT_CD",3605,Using where; Using index
2,DEPENDENT SUBQUERY,HC_META_DETAIL,ref,PRIMARY,PRIMARY,82,"blue_biz_gupanjang.HDI.CUST_CD,blue_biz_gupanjang.HDI.FACT_CD",1255,Using where; Using index
 

EXPLAIN에서 보면 MS_META_BOLT를 서브쿼리로 COUNT를 가져오는 용도로 사용중인데 인덱스를 사용하는거롤 확인이 됩니다.

하지만 EXPLAIN을 제거하고 쿼리 실행 시 해당 서브쿼를 넣으면 거의 40초가량 쿼리가 걸립니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE MS_META_BOLT
(
    NO             BIGINT(11) 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 '센서코드',
    PMOK_CD        VARCHAR(20)      NULL COMMENT '제품코드',
    MTBT_QTY       DECIMAL(11, 3)   NULL COMMENT '수량',
    MTBT_DT        DATETIME         NULL COMMENT '금속검출시간',
    BOLT_YN        CHAR             NOT NULL COMMENT '검출여부 ( 검출 : Y, 미검출 : N)',
    SEND_YN        CHAR DEFAULT 'N' NULL COMMENT '전송여부',
    MTBT_TEST_YN   CHAR DEFAULT 'N' NULL COMMENT '시편검사여부',
    MTBT_TEST_TYPE CHAR(10)         NULL COMMENT '시편타입',
    MT_TEST_NO     BIGINT           NULL,
    MTBT_ORGN_CD   VARCHAR(20)      NULL COMMENT '이탈사유',
    MTBT_ORGN_MAGT VARCHAR(20)      NULL COMMENT '이탈조치내역',
    MTBT_NT        VARCHAR(20)      NULL COMMENT '비고',
    MTBT_CRDT      DATETIME         NOT NULL COMMENT '등록일시',
    MTBT_UPDT      DATETIME         NOT NULL COMMENT '수정일시',
    MTBT_SABN      VARCHAR(20)      NULL COMMENT '등록수정자',
    MTBT_DATE      DATE             NULL,
    PUSH_YN        CHAR DEFAULT 'N' NULL COMMENT '이탈푸시알림 전송 여부',
    PRIMARY KEY (NO, CUST_CD, FACT_CD, SNSR_CD)
)
    COMMENT '금속검출내역';
 
CREATE INDEX MS_META_BOLT_CUST_CD_FACT_CD_MTBT_DT_INDEX
    ON MS_META_BOLT (CUST_CD, FACT_CD, MTBT_DT);

해당 테이블의 정보이고 인덱스를 거치면서도 저렇게 오래 걸리는 이유가 궁금합니다

by 마농 [2021.12.31 13:09:45]

스칼라서브쿼리 형태로 쓰는 듯 한데요?
스칼라서브쿼리는 메인 테이블 건수만큼 반복 수행됩니다.
0.1초 짜리 느리지 않은 쿼리라고 해도 40번 수행하면 4초가 되고 400번 수행하면 40초가 됩니다.
쿼리를 보여주세요.
인덱스 쓴다고 해서 무조건 빠른 건 아닙니다.
2817692 이라고 표현된 부분이 레코드 건수라고 한다면?
인덱스 스캔으로 280만건을 가져온다는 거네요?
인덱스 스캔하기에는 너무 범위가 넓네요.
인덱스를 스캔하는것이 어울리지 않는 대량이고
또한 풀스캔하기에도 스칼라서브쿼리와는 어울리지 않네요.
쿼리를 보여주세요.

by Atreyu [2022.01.03 13:13:48]

답변이 늦어 죄송합니다. 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
SELECT *
FROM
    (
        SELECT CUST_CD
             , FACT_CD
             , (SELECT FACT_NM
                FROM DF_FACT_INFO
                WHERE CUST_CD = HDI.CUST_CD
                  AND FACT_CD = HDI.FACT_CD
            ) FACT_NM
             , DOCU_CD
             , META_CD
             , META_DT
             , (SELECT COUNT(*)
                FROM HC_META_DETAIL
                WHERE CUST_CD = HDI.CUST_CD
                  AND FACT_CD = HDI.FACT_CD
                  AND META_CD = HDI.META_CD
             ) ALL_COUNT
             ,(SELECT COUNT(*)
               FROM MS_META_BOLT
               WHERE CUST_CD = HDI.CUST_CD
                 AND FACT_CD = HDI.FACT_CD
                 AND MTBT_DATE = HDI.META_DT
            ) T_COUNT
             , META_ST
             , META_WRITER
             , (SELECT USER_NM
                FROM DF_USER_INFO
                WHERE CUST_CD = HDI.CUST_CD
                  AND USER_CD = META_WRITER
             ) META_WRITER_NM
             , (SELECT DOCU_CHCKR
                FROM HC_DOCU_INFO
                WHERE CUST_CD = HDI.CUST_CD
                  AND FACT_CD = HDI.FACT_CD
                  AND DOCU_CD = HDI.DOCU_CD
             ) DOCU_CHCKR
             , META_CHCKR
             , (SELECT USER_NM
                FROM DF_USER_INFO
                WHERE CUST_CD = HDI.CUST_CD
                  AND USER_CD = META_CHCKR
             ) META_CHCKR_NM
             , META_CHCK_YN
             , (SELECT USER_HACCP_YN
                FROM DF_USER_INFO
                WHERE USER_CD = 'test'
             ) USER_HACCP_YN
             , META_CONFR
             , (SELECT USER_NM
                FROM DF_USER_INFO
                WHERE CUST_CD = HDI.CUST_CD
                  AND USER_CD = META_CONFR
             ) META_CONFR_NM
             , META_CONF_YN
             , META_CRDT
             , META_UPDT
             , META_SABN
             , (SELECT USER_NM
                FROM DF_USER_INFO
                WHERE CUST_CD = HDI.CUST_CD
                  AND USER_CD = META_SABN
             ) USER_NM
        FROM HC_META_INFO HDI
    ) A
WHERE CUST_CD = '123456'
  AND FACT_CD = '20181018123456781000'
  AND DOCU_CD = '20191209144223075016'
  AND DATE_FORMAT(META_DT, '%Y-%m-%d')  >=  '2021-12-24'
  AND DATE_FORMAT(META_DT, '%Y-%m-%d')  <=  '2021-12-31'
ORDER BY META_DT

늦어진 쿼리는 현재 저렇게 사용하고 있습니다.

서브쿼리 부분의 T_COUNT가 문제가 되는 부분입니다.

by Atreyu [2022.01.03 13:14:25]

현재 제가 한 방법은 날짜를 파라메터로 받아올 수 있는 부분이라 해당 부분은 with로 빼서 작업하였습니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
WITH CTE AS (
    SELECT CUST_CD, FACT_CD, MTBT_DATE, COUNT(*) CNT
    FROM MS_META_BOLT
    WHERE CUST_CD = '123456'
      AND FACT_CD = '20181018123456781000'
      AND MTBT_DATE >=  '2021-12-24'
      AND MTBT_DATE <=  '2021-12-31'
    GROUP BY CUST_CD, FACT_CD, MTBT_DATE
)
  SELECT
           HDI.CUST_CD
         ,HDI.FACT_CD
         ,DOCU_CD
          ,META_DT
         , (SELECT COUNT(*)
            FROM HC_META_DETAIL
            WHERE CUST_CD = HDI.CUST_CD
              AND FACT_CD = HDI.FACT_CD
              AND META_CD = HDI.META_CD
        ) ALL_COUNT
         ,(SELECT COUNT(*)
           FROM HC_META_TEST
           WHERE CUST_CD = HDI.CUST_CD
             AND FACT_CD = HDI.FACT_CD
             AND META_CD = HDI.META_CD
    ) T_COUNT
     ,IFNULL((SELECT CNT
       FROM CTE
       WHERE CUST_CD = HDI.CUST_CD
         AND FACT_CD = HDI.FACT_CD
         AND MTBT_DATE = HDI.META_DT
    ), 0) N_COUNT
    FROM HC_META_INFO HDI
WHERE HDI.CUST_CD = '123456'
  AND HDI.FACT_CD = '20181018123456781000'
  AND DOCU_CD = '20191209144223075016'
AND META_DT >=  '2021-12-24'
AND META_DT <=  '2021-12-31'
ORDER BY META_DT

 

by Atreyu [2022.01.03 13:15:01]

견문이 좁아 많이 배우고 있습니다.

다른 방법으로는 어떻게 개선하면 좋을지 조언 부탁드립니다!

by 마농 [2022.01.03 18:03:56]

MS_META_BOLT 의 인덱스는 (CUST_CD, FACT_CD, MTBT_DT) 인데
조건은 (CUST_CD, FACT_CD, MTBT_DATE) 로 사용되네요?
MTBT_DT 와 MTBT_DATE 두개의 다른 컬럼이 있네요?

by Atreyu [2022.01.05 09:35:59]

테이블 스키마가 중간에 잘린 것 같습니다!

index는 (CUST_CD, FACT_CD, MTBT_DT), (CUST_CD, FACT_CD, MTBT_DATE)  이렇게 2개 잡혀있습니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입