데이터베이스 질문과 답변
안녕하세요.
선택기간 중간 날짜들을 가져오고
테이블에서 시작일, 종료일을 구해 시작,종료일 사이의 날짜들을 가져오는 쿼리입니다.
동작은 잘 되지만 속도가 너무 느린데 개선 방법이 있을까요?
있다면 조언좀 부탁드리겠습니다.
쿼리입니다.
+ 불필요한 WHERE절과 테이블명은 변경했습니다.
+ A괄호 내 쿼리 조회속도는 65~73 msecs, B괄호 내 쿼리 조회속도는 1~2msecs 정도 나옵니다.)
SELECT
DISTINCT A.SELECTED_DATE AS CALENDAR_DATE
FROM (
SELECT * FROM
(SELECT ADDDATE('2018-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) SELECTED_DATE 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) v
WHERE SELECTED_DATE BETWEEN '2018-07-01' AND '2018-10-01'
) A
JOIN
(SELECT from_unixtime(A.SDATE, '%Y-%m-%d') AS SDATE
, from_unixtime(A.EDATE, '%Y-%m-%d') AS EDATE
FROM T_TABLE1 A
UNION
SELECT from_unixtime(A.SDATE, '%Y-%m-%d') AS SDATE
, from_unixtime(A.EDATE, '%Y-%m-%d') AS EDATE
FROM T_TABLE2 A
UNION
SELECT from_unixtime(A.SDATE, '%Y-%m-%d') AS SDATE
, from_unixtime(A.EDATE, '%Y-%m-%d') AS EDATE
FROM T_TABLE3 A
UNION
SELECT from_unixtime(A.SDATE, '%Y-%m-%d') AS SDATE
, from_unixtime(A.EDATE, '%Y-%m-%d') AS EDATE
FROM T_TABLE4 A
) B
ON A.SELECTED_DATE IN( B.SDATE , B.EDATE )
WHERE A.SELECTED_DATE < UNIX_TIMESTAMP('2018-10-01')
A괄호 쿼리
| SELECTED_DATE |
|---|
| 2018-07-01 |
| 2018-07-02 |
| 2018-07-03 |
| 2018-07-04 |
| 2018-07-05 |
| 2018-07-06 |
| 2018-07-07 |
| 2018-07-08 |
| 2018-07-09 |
| 2018-07-10 |
| 2018-07-11 |
| 2018-07-12 |
| 2018-07-13 |
| 2018-07-14 |
| 2018-07-15 |
| 2018-07-16 |
| 2018-07-17 |
| 2018-07-18 |
| 2018-07-19 |
| 2018-07-20 |
| 2018-07-21 |
| 2018-07-22 |
| 2018-07-23 |
| 2018-07-24 |
| 2018-07-25 |
| 2018-07-26 |
| 2018-07-27 |
| 2018-07-28 |
| 2018-07-29 |
| 2018-07-30 |
| 2018-07-31 |
| 2018-08-01 |
| 2018-08-02 |
| 2018-08-03 |
| 2018-08-04 |
| 2018-08-05 |
| 2018-08-06 |
| 2018-08-07 |
| 2018-08-08 |
| 2018-08-09 |
| 2018-08-10 |
| 2018-08-11 |
| 2018-08-12 |
| 2018-08-13 |
| 2018-08-14 |
| 2018-08-15 |
| 2018-08-16 |
| 2018-08-17 |
| 2018-08-18 |
| 2018-08-19 |
| 2018-08-20 |
| 2018-08-21 |
| 2018-08-22 |
| 2018-08-23 |
| 2018-08-24 |
| 2018-08-25 |
| 2018-08-26 |
| 2018-08-27 |
| 2018-08-28 |
| 2018-08-29 |
| 2018-08-30 |
| 2018-08-31 |
| 2018-09-01 |
| 2018-09-02 |
| 2018-09-03 |
| 2018-09-04 |
| 2018-09-05 |
| 2018-09-06 |
| 2018-09-07 |
| 2018-09-08 |
| 2018-09-09 |
| 2018-09-10 |
| 2018-09-11 |
| 2018-09-12 |
| 2018-09-13 |
| 2018-09-14 |
| 2018-09-15 |
| 2018-09-16 |
| 2018-09-17 |
| 2018-09-18 |
| 2018-09-19 |
| 2018-09-20 |
| 2018-09-21 |
| 2018-09-22 |
| 2018-09-23 |
| 2018-09-24 |
| 2018-09-25 |
| 2018-09-26 |
| 2018-09-27 |
| 2018-09-28 |
| 2018-09-29 |
| 2018-09-30 |
| 2018-10-01 |
B괄호 쿼리
| SDATE | EDATE |
|---|---|
| 2018-07-30 | 2018-07-31 |
| 2018-08-02 | 2018-08-03 |
| 2018-08-07 | 2018-08-08 |
| 2018-08-09 | 2018-08-10 |
| 2018-08-14 | 2018-08-15 |
| 2018-08-16 | 2018-08-17 |
| 2018-08-29 | 2018-08-29 |
| 2018-09-03 | 2018-09-03 |
| 2018-09-10 | 2018-09-10 |
| 2018-09-17 | 2018-09-17 |
| 2018-09-24 | 2018-09-24 |
| 2018-07-25 | 2018-07-26 |
| 2018-07-27 | 2018-07-28 |
| 2018-08-20 | 2018-08-21 |
| 2018-07-28 | 2018-07-29 |
| 2018-08-06 | 2018-08-07 |
| 2018-08-07 | 2018-08-09 |
| 2018-08-10 | 2018-08-11 |
| 2018-08-13 | 2018-08-14 |
| 2018-08-17 | 2018-08-18 |
| 2018-08-27 | 2018-08-28 |
| 2018-08-27 | 2018-07-29 |
쿼리 결과 :
| CALENDAR_DATE |
|---|
| 2018-07-25 |
| 2018-07-26 |
| 2018-07-27 |
| 2018-07-28 |
| 2018-07-29 |
| 2018-07-30 |
| 2018-07-31 |
| 2018-08-02 |
| 2018-08-03 |
| 2018-08-06 |
| 2018-08-07 |
| 2018-08-08 |
| 2018-08-09 |
| 2018-08-10 |
| 2018-08-11 |
| 2018-08-13 |
| 2018-08-14 |
| 2018-08-15 |
| 2018-08-16 |
| 2018-08-17 |
| 2018-08-18 |
| 2018-08-20 |
| 2018-08-21 |
| 2018-08-27 |
| 2018-08-28 |
| 2018-08-29 |
| 2018-09-03 |
| 2018-09-10 |
| 2018-09-17 |
| 2018-09-24 |
1. UNION 은 UNION ALL 로 바꿔어야 합니다.
2. 조인 조건이 왜 BETWEEN 이 아니고 IN 조건인거죠?
3. mySQL 버전이 어떻게 되나요?
마농님 안녕하세요! 항상 좋은 답변 감사드립니다.
1. UNION ALL이였는데 UNION으로 바꿔보니 결과는 같고 기분탓인지 속도가 조금 빨라져 UNION으로 바꿔봤습니다.
2. 이것도 1번답변같이 BETWEEN보다 속도가 좀더 나오는것같아 변경했습니다.
3. 5.7버전입니다 (Ver 14.14 Distrib 5.7.9, for Win64 (x86_64))
1. UNION ALL 은 단순 합치기이고, UNION 은 합치면서 정렬 및 중복제거 합니다.
- UNION 과 UNION ALL 은 그 쓰임이 다르며
- UNION ALL 이 쓰일 곳에 UNION 을 써도 결과는 같지만 불필요한 정렬 및 중복제거로 인한 비효율 발생합니다.
2, BETWEEN 과 IN
- 이 또한 쓰임새가 전혀 다릅니다. 물론 결과도 다릅니다.
- 결과가 달라지는데 속도가 무슨 소용이 있나요.
3, 각 테이블의 정보를 알려주세요
- 전체 건수 및 기간조건을 만족하는 건수
- 기간 정보 항목에 인덱스 유무
넵 1번 2번 참고하여 수정하였습니다.
3.
TABLE1 : 전체건수 12, 조건을 만족하는 건수 : 6
TABLE2 : 전체건수 13, 조건을 만족하는 건수 : 11
TABLE3 : 전체건수 9, 조건을 만족하는 건수 : 7
TABLE4 : 전체건수 31, 조건을 만족하는 건수 : 19
TABLE5(각 테이블(TABLE1, TABLE2, TABLE3, TABLE4)마다 함께 조회하는 테이블) : 전체건수 2300, 조건을 만족하는 건수 : 1
TABLE6 (TABLE4의 조건절에 들어가는 테이블) : 전체건수: 984, 조건 만족건수 : 1
1,2,3,4테이블과 5테이블은 아래와 같이 합쳐서 조회합니다.
SELECT from_unixtime(A.SDATE, '%Y-%m-%d') AS SDATE
, from_unixtime(A.EDATE, '%Y-%m-%d') AS EDATE
FROM TABLE1 A, TABLE5 U
WHERE U.U_IDX = 1
AND A.COL1!= 11
AND A.COL2!= 6
AND A.COL3!= 7
AND A.SDATE < UNIX_TIMESTAMP('2018-10-01')
AND A.EDATE >= UNIX_TIMESTAMP('2018-07-01')
AND( (A.U_IDX = U.U_IDX)
OR (A.R_IDX = U.U_IDX)
OR (A.C_IDX = U.U_IDX)
OR (A.E_IDX = U.U_IDX)
OR (CAST(U.COL1 as UNSIGNED) >= 1000)
OR (
U.U_IDX IN (
SELECT S.C_IDX FROM TABLE6 S
WHERE 1=1
AND S.COL1 = 'aaa'
AND S.J_IDX = A.C_IDX
)
)
)
이상입니다.
정보가 주실때마다 다릅니다.
추가되고 빠지고 이런건 이해하는데, 그게 아니라 아얘 다른 것은 곤란합니다.
처음 질문에 table1 과 table2 는 각각 조회하는데?
마지막 주신 쿼리에 table1 과 table2 를 조인하는 것은 무엇?
1,2,3,4테이블과 5테이블은 아래와 같이 합쳐서 조회합니다.
라고 하셨는데? 5테이블은 안보이고 6테이블만 보이는 것은 무엇?
1,2,3,4테이블 건수는 상당히 작네요?
5,6테이블이 건수가 있긴 하지만 그리 큰 건수도 아니고.
이렇게 건수가 적은데 속도가 안나온다는게 이해가 안가고요.
각 테이블에 인덱스는 있는지?
조인은 제대로 하고 있는지? 의문이네요?
정확한 정보를 주세요.
마지막 쿼리 오타입니다 (FROM TABLE1 A, TABLE5 U)로 정정했습니다.
개별로 조회하면 속도가 잘 나오는데 A괄호테이블과 조인하면 조회시간이 4~8초정도 소요됩니다..
쪽지기능이라도있으면 전체쿼리를 보내드릴텐데 그런 기능이없네요.. ㅜㅜ
OR 조건이 이상합니다.
t5 의 u_idx 가 1건이고 이걸로 a 와 조인하는데? 조인 조건이 OR 로 연결되어 있네요.
그런데 a 와 관계 없는 두가지 조건이 또 OR 로 연결되어 있네요?
그러면 이 두개 조건중 하나만 만족해도 a 와의 조인조건은 무의미해 집니다.
즉 a 와 u 가 조건없이 크로스 조인을 하게 되어 카티션곱이 발생됩니다.
아무리 봐도 이상한 OR 조건입니다.
TABLE5와 TABLE6을 제외하고 조회해도 4~5초 소요됩니다..
아래는 TABLE5, TABLE6을 제외한 실제 쿼리입니다.
----
b 의 1,2,3,4 의 건수가 정말 30건도 안되나요?
더 많은데 조건 준 결과건수 말하는 것 아닌가요?
건수가 그렇게 적은데 느릴리가 없어 보입니다.
혹시 a 의 달력을 만들어 내는 과정 때문에 느린 건 아닐런지요?
a 의 t4 를 제거하고 쿼리하면 면초가 나오나요?
혹시 차례대로 t3, t2 를 제거하면 속도가 줄어드나요?
이게 원인이라면?
차라리 달력 테이블을 미리 만들어 두고 사용하는 것은 어떨까요?
답변이 늦어서 죄송합니다.b의 1,2,3,4 건수는 말씀해드린대로고
말씀해주신것처럼 달력쿼리의 t4를 제거하고 조회하니 900mecs대 로 나옵니다.
기존 4000~8000mecs이상 걸리던 쿼리다보니 체감이 많이 되었습니다.
거기에 t3까지 제거하니 50mecs대가 나오네요!
달력부분만 조회해봐도 날짜지정에 문제가없어보여 이대로 사용하면 될 것 같습니다. 도움 주셔서 감사드립니다.
마지막에 올린 쿼리는 삭제하겠습니다.
좋은 밤 되세요
1. 달력 테이블 또는 일련번호 테이블(copy_t) 사용하시기 바랍니다.
2. OR 조건의 이상한 점, 문제점 해결하기시기 바랍니다.
1. 달력테이블또는 일련번호 테이블을 별도로 만들라는 말씀이신거죠?
2. 넵 다시 고쳐보겠습니다.
감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.