쿼리 속도를 향상 시키고 싶습니다. 0 1 1,113

by Atreyu [MySQL] 쿼리속도 MYSQL 날짜 [2018.11.01 18:23:53]


한 년도의 각각 날짜의 데이터들을 가져와서 월별로 뽑아내는 쿼리입니다. 

안쪽 서브쿼리 D, T의 쿼리가 문제일거라 생각해서 안쪽 쿼리들은 1초내외로 수정했는데..

T와 D를 조인하는 과정에서 쿼리 속도가 엄청 늘어지더라구요. 

D를 없애버리고 T의 날짜 데이터로만 잘라내서 뽑아내려고 하니까 그렇게 되면 T의 데이터가 없는 것들은 아예 나오지 않을 것 같더라구요.

초보 개발자라 어떤식으로 구상을 해야할지 잘 보이지 않아 도움을 요청 드립니다. 감사합니다.

SELECT  GROUP_CONCAT(DATA.DAY ORDER BY DATA.DAY) AS DAY,
        GROUP_CONCAT(CONCAT("'", DATA.DAY, "'") ORDER BY DATA.DAY) AS GDAY,
		GROUP_CONCAT(DATA.S_MIN ORDER BY DATA.DAY) AS S_MIN,
		GROUP_CONCAT(DATA.R_MIN ORDER BY DATA.DAY) AS R_MIN,
		GROUP_CONCAT(DATA.P_PER ORDER BY DATA.DAY) AS P_PER,
		ROUND(SUM(DATA.S_MIN) / 12) S_AVG,
		ROUND(SUM(DATA.R_MIN) / 12) R_AVG,
		ROUND(SUM(DATA.P_PER) / 12) P_AVG
FROM
(
    SELECT  DATE_FORMAT(D.DAY, '%Y-%m') DAY,
            IFNULL(SUM(T.PRS_MIN), 0) S_MIN,
            IFNULL(SUM(T.R_MIN), 0) R_MIN,
            IFNULL(ROUND(IFNULL(SUM(T.PRS_MIN), 0) / IFNULL(SUM(T.R_MIN), 0) * 100), 0) P_PER
    FROM
    (
        SELECT ADDDATE('2018-01-01', t4*10000 + t3*1000 + t2*100 + t1*10 + t0) DAY
        FROM
            (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
            (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
            (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
            (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
            (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
        WHERE YEAR(ADDDATE('2018-01-01', t4*10000 + t3*1000 + t2*100 + t1*10 + t0)) = '2018'
    ) D
    LEFT JOIN
    (
    	SELECT PW_EDATE
    	     , R_MIN
    	     , RP.PRS_MIN
    	 FROM (
    	 		SELECT  OW.PW_EDATE,
    	 				OW.PW_NUMB,
		                (IFNULL(TIMESTAMPDIFF(MINUTE, OW.PW_SDATE, OW.PW_EDATE),0) - (IFNULL(TIMESTAMPDIFF(MINUTE, STOP_SDATE, IFNULL(STOP_EDATE, NOW())), 0))) R_MIN,
		                O.P_CODE,
		                O.REV_CODE,
		                OW.PRS_CODE,
		                OW.PRS_NUMB
		        FROM ORDER_WORK OW
		        LEFT JOIN ORDER_INFO O ON O.PI_NUMB = OW.PI_NUMB AND O.O_ENABLE = 'Y'
		        LEFT JOIN ORDER_WORK_STOP WS ON WS.PI_NUMB = OW.PI_NUMB AND WS.PW_NUMB = OW.PW_NUMB
		        WHERE OW.PW_STATE = '03'
		        AND DATE_FORMAT(OW.PW_EDATE, '%Y') = '2018'
		        GROUP BY OW.PW_NUMB
    	      ) M 
    	      LEFT JOIN REV_PROCESS RP ON RP.P_CODE = M.P_CODE AND RP.REV_CODE = M.REV_CODE AND RP.PRS_CODE = M.PRS_CODE AND RP.PRS_NUMB = M.PRS_NUMB 
    ) T ON DATE_FORMAT(T.PW_EDATE, '%Y-%m-%d') = D.DAY
--     WHERE YEAR(D.DAY) = '2018'
    GROUP BY DATE_FORMAT(D.DAY, '%Y-%m')
    ORDER BY DATE_FORMAT(D.DAY, '%Y-%m')
) DATA

 

by 마농 [2018.11.05 08:47:40]

1. 컬럼을 가공하지 마시고 조건을 가공하세요.
  - 변경전 : DATE_FORMAT(OW.PW_EDATE, '%Y') = '2018'
  - 변경후 : OW.PW_EDATE BETWEEN '2018-01-01' AND '2018-12-31'
2. 달력을
  - 실시간으로 만들어 쓰지 마시고
  - 테이블을 미리 만들어 두고 사용하세요.
3. Group By 구문이
  - MySQL 에서만 허용되는 비표준 구문이네요.
  - 표준 구문을 사용하세요.

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