데이터베이스 질문과 답변

mysql varchar column 인덱스 0 3 1,161

by ahriow [MySQL] [2018.11.15 13:17:55]

안녕하세요.

mysql 인덱스 관련 질문 있습니다.

db에 검색 날짜가 '20181115131600' varchar 형태로 들어가있습니다.

해당 데이터를 date between '20181115' and concat('20181115','99') 로 기간검색을 하고 있는데 

최대한 속도가 나오게 하려면 어떻게 튜닝을 해야 할까요??

by 마농 [2018.11.15 13:41:46]

해당 컬럼에 인덱스가 있다면? 정상적으로 인덱스를 탈 것 같습니다.
쿼리 속도가 안나와서 질문하신 건가요?
건수 정보라던가 좀더 상세한 설명이 있어야 할 것 같습니다.

by ahriow [2018.11.15 14:44:06]
상품명 가격 주문시간 주문위치
사과 1000 20170504123456 서울
라면 5000 20180205063000 대전
사과박스 4300 20180303030303 대구
자동차 200 20180306060606 부산
비행기 50000 20180402060204 서울
자동차 60512 20180925234027 서울
복숭아 82315 20180504020802 부산
배 51687 20180806032251 서울
... ... ... ...

위와같은 형식으로 데이터가 들어가 있습니다. 1년데이터는 약 50만건 입니다.

 

 

주문기간 aFrom : 201804, aTo : 201806 파라미터를 넣어주면

프로그램단에서 전기 시간을 계산해서 bFrom : 201801 bTo : 201803 을 추가로 넣어준 뒤

나와야 하는 최종 결과값은 아래와 같습니다.(데이터는 막 넣은거기 때문에 형식만 봐주세요!!)

기간 총판매금액 총판매건수 최다판매(건수기준)시각 최다판매요일 최다판매지역
2018-04 ~ 2018-06 50000 25 16시 화요일 서울
2018-01 ~2018-03 25000 50 02시 월요일 부산
전기 대비 +25000 -25      
전기대비비율 100% -50%      

결과값중 전기 대비 , 전기 대비 비율 행은 하단 쿼리에서 나온 결과값을 가지고

프로그램상에서 재계산 해서 넣기때문에 무시하셔도 좋습니다.

(쿼리로 계산해 낼 수 있다면 가르침 부탁드립니다..)

 

한두달은 부담되지 않는 시간인데 1년치를 돌리면 40초정도가 걸리네요..

mysql 5.7 버전을 쓰고 있어서 window function 을 사용하지 못해서 아래처럼 무식하게 쿼리를 짜놓은 상태입니다.

 

SELECT     type,
                    concat(concat(substring(start_date,1,4),'-',substring(start_date,5,2)),' ~ ',concat(substring(end_date,1,4),'-',substring(end_date,5,2))) 기간,
                    총판매금액,
                    총판매건수,
                    최다판매시각,
                    case 최다판매요일     
                        when 1 then '일요일' 
                        when 2 then '월요일'
                        when 3 then '화요일'
                        when 4 then '수요일'
                        when 5 then '목요일'
                        when 6 then '금요일'
                        when 7 then '토요일'
                    end 최다판매요일,
                    최다판매지역
    FROM
    (
        SELECT    '1' AS type,
                        SUM(판매금액) AS 총판매금액,
                        COUNT(판매건수) AS 총판매건수,
                        CONCAT((SELECT substring(주문시간,9,2) FROM  테이블 WHERE 주문시간 BETWEEN aFrom AND concat(aTo,'99') group by substring(주문시간,9,2) order by count(가격) desc limit 1),'시') AS 최다판매시각,
                        (SELECT dayofweek(주문시간) FROM  테이블 WHERE 주문시간 BETWEEN aFrom AND concat(aTo,'99') group by dayofweek(주문시간) order by count(가격) desc limit 1) AS 최다판매요일,
                        (SELECT 주문위치 FROM  테이블 WHERE 주문시간 BETWEEN aFrom AND concat(aTo,'99') group by 주문위치 order by count(가격) desc limit 1) AS 최다판매지역,
                        aFrom AS start_date,
                        aTo AS end_date
        FROM 테이블
        WHERE 주문시간 BETWEEN aFrom AND concat(aTo,'99')
        UNION ALL
        SELECT     '2' AS type,
                        SUM(판매금액) AS 총판매금액,
                        COUNT(판매건수) AS 총판매건수,
                        CONCAT((SELECT substring(order_date,9,2) FROM  테이블 WHERE 주문시간 BETWEEN bFrom AND concat(bTo,'99') group by substring(주문시간,9,2) order by count(가격) desc limit 1),'시') AS 최다판매시각,
                        (SELECT dayofweek(order_date) FROM  테이블 WHERE 주문시간 BETWEEN bFrom AND concat(bTo,'99') group by dayofweek(주문시간) order by count(가격) desc limit 1) AS 최다판매요일,
                        (SELECT 주문위치 FROM  테이블 WHERE 주문시간 BETWEEN bFrom AND concat(bTo,'99') group by 주문위치 order by count(가격) desc limit 1) AS 최다판매지역,
                        bFrom AS start_date,
                        bTo AS end_date
        FROM 테이블
        WHERE 주문시간 BETWEEN bFrom AND concat(bTo,'99')
    ) a 

위와같이 각각 다 서브쿼리로 돌려서 뽑아내고 있습니다.

가르침 부탁드립니다. ㅠ

 

by 마농 [2018.11.15 15:28:00]

MariaDB 10.3 에서 테스트했습니다.

WITH t AS
(
SELECT '20180101010000' dt, 100 amt, 'A' loc
UNION ALL SELECT '20180201010101', 100, 'B'
UNION ALL SELECT '20180301010000', 100, 'B'
UNION ALL SELECT '20180401010000', 100, 'B'
UNION ALL SELECT '20180501010000', 100, 'B'
UNION ALL SELECT '20180601010000', 100, 'B'
UNION ALL SELECT '20180602020000', 100, 'B'
UNION ALL SELECT '20180603020000', 100, 'A'
UNION ALL SELECT '20180604020000', 100, 'A'
UNION ALL SELECT '20180605020000', 100, 'A'
)
SELECT sdt, edt
     , amt
     , cnt
     , MIN(CASE WHEN gb = 1 THEN v END) tm
     , MIN(CASE WHEN gb = 2 THEN v END) dy
     , MIN(CASE WHEN gb = 3 THEN v END) loc
  FROM (SELECT sdt, edt
             , SUM(amt) amt
             , SUM(cnt) cnt
             , gb
             , v
          FROM (SELECT sdt, edt, gb, v
                     , SUM(amt) amt
                     , COUNT(*) cnt
                  FROM (SELECT CASE WHEN dt > '201804' THEN '201804' ELSE '201801' END sdt
                             , CASE WHEN dt > '201804' THEN '201806' ELSE '201803' END edt
                             , gb
                             , CASE gb
                               WHEN 1 THEN CONCAT(SUBSTR(dt, 9, 2), '시')
                               WHEN 2 THEN CONCAT(SUBSTR('일월화수목금토', DAYOFWEEK(dt), 1), '요일')
                               WHEN 3 THEN loc
                                END v
                        	   , amt
                          FROM t a
                             , (SELECT 1 gb UNION ALL SELECT 2 UNION ALL SELECT 3) b
                         WHERE dt BETWEEN '201801' AND CONCAT('201806', '9')
                        ) a
                 GROUP BY sdt, edt, gb, v
                 ORDER BY sdt, edt, gb, cnt DESC
                ) a
         GROUP BY sdt, edt, gb
        ) a
 GROUP BY sdt, edt, amt, cnt
 ORDER BY sdt DESC
;

 

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