데이터베이스 질문과 답변
안녕하세요
개발 도중 이해가 가지 않는 부분이 있어 문의드립니다.
현재 사용하는 쿼리에서 발생하는 속도 문제입니다.
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초가량 쿼리가 걸립니다.
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);
해당 테이블의 정보이고 인덱스를 거치면서도 저렇게 오래 걸리는 이유가 궁금합니다
스칼라서브쿼리 형태로 쓰는 듯 한데요?
스칼라서브쿼리는 메인 테이블 건수만큼 반복 수행됩니다.
0.1초 짜리 느리지 않은 쿼리라고 해도 40번 수행하면 4초가 되고 400번 수행하면 40초가 됩니다.
쿼리를 보여주세요.
인덱스 쓴다고 해서 무조건 빠른 건 아닙니다.
2817692 이라고 표현된 부분이 레코드 건수라고 한다면?
인덱스 스캔으로 280만건을 가져온다는 거네요?
인덱스 스캔하기에는 너무 범위가 넓네요.
인덱스를 스캔하는것이 어울리지 않는 대량이고
또한 풀스캔하기에도 스칼라서브쿼리와는 어울리지 않네요.
쿼리를 보여주세요.
답변이 늦어 죄송합니다.
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가 문제가 되는 부분입니다.
현재 제가 한 방법은 날짜를 파라메터로 받아올 수 있는 부분이라 해당 부분은 with로 빼서 작업하였습니다.
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
견문이 좁아 많이 배우고 있습니다.
다른 방법으로는 어떻게 개선하면 좋을지 조언 부탁드립니다!
MS_META_BOLT 의 인덱스는 (CUST_CD, FACT_CD, MTBT_DT) 인데
조건은 (CUST_CD, FACT_CD, MTBT_DATE) 로 사용되네요?
MTBT_DT 와 MTBT_DATE 두개의 다른 컬럼이 있네요?
테이블 스키마가 중간에 잘린 것 같습니다!
index는 (CUST_CD, FACT_CD, MTBT_DT), (CUST_CD, FACT_CD, MTBT_DATE) 이렇게 2개 잡혀있습니다
) 버튼을 클릭하여 작성 하시면 됩니다.