데이터베이스 질문과 답변

MySQL 튜닝 질문 0 6 1,363

by 꿈을향해 [MySQL] [2018.03.09 18:58:04]

MySQL 튜닝 질문 드립니다.. 

쿼리 내용은 아래와 같습니다.


SELECT 1
FROM A_TABLE A
WHERE 1=1
AND (A.SITE_NO
   , DATE_FORMAT(A.ACCESS_DTTM, '%Y%m%d')
   , CASE WHEN A.DEVICE_TYPE = 'P' THEN '11'  
     WHEN A.DEVICE_TYPE = 'M' THEN '12'  
     ELSE '99' END
   , A.IP)
     NOT IN (SELECT F.SITE_NO, CONCAT(F.YR, F.MM, F.DT), F.EQPM_GB_CD, F.VISIT_IP
           FROM F_TABLE F
          WHERE 1=1
        AND F.SITE_NO    = A.SITE_NO
        AND F.YR         = DATE_FORMAT(A.ACCESS_DTTM, '%Y')
        AND F.MM         = DATE_FORMAT(A.ACCESS_DTTM, '%m')
        AND F.DT         = DATE_FORMAT(A.ACCESS_DTTM, '%d')
        AND F.EQPM_GB_CD = CASE WHEN A.DEVICE_TYPE = 'P' THEN '11'
                    WHEN A.DEVICE_TYPE = 'M' THEN '12'
                    ELSE '99' END
        AND F.VISIT_IP   = A.IP
        );

 

 

실행계획은 아래와 같습니다.

ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 PRIMARY A ALL         2620137 Using Where
2 DEPENDENT SUBQUERY F eq_ref PRIMARY PRIMARY 348 A.SITE_NO,func,func 1 Using Where; Using index

 

not in이 옵티마이저에 의해서 not exists로 로 변환되서 수행되는 것 같은데

subquery를 접근할때 1건만 찾고 나와서 문제가 안되는데 A테이블에서 2백만건을 읽어서 그걸 건건히 subquery에 접근하다 보니 문제가 되는것 같습니다.

not in이여서 옵티마이저가 semi로 변환도 안되는 것 같고 join조건만 있고 where절은 없으니 여기서 더 줄일수 있는지도 모르겠습니다.

인터넷에 outer join으로 변형시켜 보라해서 쿼리를 변형시키니 성능이 더 안좋아지는 결과만 나오네요..

현재 수행시간은 30초정도 됩니다. 1시간마다 도는 batch쿼리여서 시간문제는 안되는데 cpu가 100%를 쳐서 이걸 좀 줄여보려고합니다.

nl조인은 cpu가 많이 안드는걸로 아는데 이것도 좀 이상하네요.... 

계속 고민하다가 고수님들께 질문드려봅니다. oracle과 달라서 많이 헷갈리네요. 

 

by 마농 [2018.03.12 08:31:47]

1. NOT IN 의 사용법이 이상하네요?
 - EXISTS 서브쿼리에서는 메인 컬럼과 조인하는 형태로 사용하고
 - NOT IN 서브쿼리에서는 조인 없이 독립적으로 사용합니다.
 - NOT IN 서브쿼리를 마치 NOT EXISTS 서브쿼리처럼 사용했네요.
 - 서브쿼리에 조인 조건이 있으므로 인해 dependent subquery 가 되었네요.
2. SELECT 절이 이상하네요?
 - SELECT 절에 1 만 오는게 맞나요?
 - 어떤 의도로 작성된 쿼리인지?

by 꿈을향해 [2018.03.12 10:02:21]

1. 넵. 그부분은 이상하게 생각하여서 SUBQUERY에 있는 WHERE절을 제외시켜서 동작시키니 성능상 더 안좋게 변해버립니다. dependent subquery는 똑같은데 subqery에서 index를 못타는 상황이 되어버리네요. semi join으로 변형시켜 보려고 그런건데 찾아보니 not in은 semi로 못바꾼다고 하네요..   left join으로 변형시키니 아래와 같이 나오고 상관subqery는 피해서 실행계획상은 좀 더 좋아진것도 같지만 실제수행시간은 비슷합니다.

SELECT 1
FROM A_TABLE A
LEFT JOIN (SELECT DISTINCT SITE_NO, YR, MM, DT, EQPM_GB_CD, VISIT_IP
	       FROM F_TABLE F
	    ) F ON F.SITE_NO    = A.SITE_NO
		AND F.YR         = DATE_FORMAT(A.ACCESS_DTTM, '%Y')
		AND F.MM         = DATE_FORMAT(A.ACCESS_DTTM, '%m')
		AND F.DT         = DATE_FORMAT(A.ACCESS_DTTM, '%d')
		AND F.EQPM_GB_CD = CASE WHEN A.DEVICE_TYPE = 'P' THEN '11'
					WHEN A.DEVICE_TYPE = 'M' THEN '12'
					ELSE '99' END
		AND F.VISIT_IP   = A.IP
WHERE F.SITE_NO IS NULL;
id select_type table type possible_keys key key_len ref rows extra
1 PRIMARY A ALL         2620137  
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 348 A.SITE_NO,func,func 10 Using Where; Not exists; Using index
2 DERIVED F index PRIMARY PRIMARY 348   34590 Using index; Distinct

 

2. SELECT절의 원래 구문은 많은 컬럼을 가져와서 가공해서 보여주고 있는 것이지만 너무 길어서 1로 생략을 하였습니다.

 

그리고 저 구문의 실제 결과 row는 현재는 0건입니다. 이부분을 이용해서 뭔가 방법이 있을 것 같은데 떠오를 듯 말 듯 합니다.;;;

by 마농 [2018.03.12 10:04:59]

Distinct 빼보세요.

by 꿈을향해 [2018.03.12 10:31:14]

DISTINCT를 빼면 id2번인 곳의 extra컬럼에서 distinct가 없어지는 것 말고는 동일합니다. 수행시간도 동일합니다.

그리고 위 join부분 ref컬럼에 적다가 놓친부분이 있는데 A.SITE_NO,func,func,func,func,A.IP 이렇게 되어있습니다.

cpu만 100% 안치면 상관 없을 것 같은데 join 조건을 가공해서 그런것도 있는 것 같지만 mysql성능도 조금 의심스럽습니다. 

by 마농 [2018.03.12 16:42:35]

A 에는 인덱스가 없고 아우터 조인이나 안티조인을 하게 되므로
수행 순서는 항상 A > F 로 수행되게 됩니다.
a 가 적고, f 가 많다면? 아주 훌륭한 방법입니다.
그런데 지금은 정반대 싱황이네요.
F 의 인덱스를 정상적으로 이용하는게 그나마 다행이라고 할 수 있네요.
오라클에서는 Hash Join Right (Semi/Anti/Outer) 라는 기능이 있습니다.
서브테이블을 먼저 읽고 메인테이블을 읽는 방식이죠.
이러한 방식이 가능하면 좋겠는데요? MySQL 에 이런 기능이 있을런지?

by 꿈을향해 [2018.03.12 19:24:58]

Maria에 hash join이 있는걸로 아는데 이것도 subquey부터 읽지는 못하는걸로 압니다. Mysql은 hash join이 없는걸로 알고요;; cpu 점유율만 좀 낮추면 소요시간은 좀 더 늘어나도 될것같은데 파라미터중에 그런건 안보이네요.. 사양을 높여야하나..

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