주사기든남자 (59.♡.52.125)
2024년 5월 12일 PM 02:51 · 수정됨(05. 17. 22:15)
비개발자출신으로 여가시간에 맨땅에 헤딩중입니다.(chatgpt 만쉐이)
synology NAS에 mariadb 사용중입니다.
개발할 내용이 있어 8개의 테이블에 테이블당 60만개의 데이터를 4가지 조건으로 join을 하려고 합니다.
단순히 쿼리를 실행해보니 실행시간이 한시간이 훌쩍 넘어가던데요;
데이터 제공 서비스로는 부적합한 조건이라 생각되어
각 테이블을 지역별로 테이블을 쪼갠 뒤 각 테이블을 조건에 맞춰서 한테이블을 만들어서 지역별 테이블로 구성하려고 합니다.
위와 같이 진행할 경우 실행시간 단축에 도움이 될까요?
그리고 mariadb말고 postgresql이 있던데 이쪽은 복잡한 쿼리에 좀더 나은 효율을 보이는지 궁금합니다.
댓글 (17)
- 미
미소섭
24.05.12 · 106.♡.131.119
- 주
주사기든남자
→ 미소섭 작성자 24.05.12 · 59.♡.52.125
결국은 테이블 디자인이랑 인덱싱으로 해결봐야한다는 거네요.
답글 감사합니다! -
디디클
24.05.12 · 124.♡.100.52
위에 말씀하셨듯이 Index 구성으로 성능을 높을수 있습니다.
다만 DB마다 다르지만, 해당 데이터 분포가 대략 3% 정도 이내에 들어와야 Index 를 사용하고 아니면 Full Scan 으로 전체 테이블을 Scan 하게 됩니다. (설계할때 참고)
쉽계 얘기해서 되도록 식별 가능한 유니크한 필드 기준으로 Index 를 생성하고 Where 절이나 Join 절에 사용하면 됩니다. - 주
주사기든남자
→ 디클 작성자 24.05.14 · 59.♡.52.125
일단 인덱스를 추가해서 해보고있는데 확실히 그전보다는 속도가 빨라진 느낌입니다! -
Aassa2000
24.05.13 · 106.♡.113.70
DB관점에서 60만건은 많은 데이터건수라 할순 없고 인덱스를 활용 안하셨거나 조인문이 잘못됬을 가능성이 크네요 테이블 스크립트와 쿼리문을 올려주시면 좀더 자세한 조언을 받으실수 있을것 같습니다 ^^ - 주
주사기든남자
→ assa2000 작성자 24.05.14 · 59.♡.52.125
[code=sql]UPDATE `15051055_2023` b
LEFT JOIN (
SELECT hp_name, area_code1, area_code2, code
FROM `15051055_2022`
GROUP BY hp_name, area_code1, area_code2, code
) AS b2 ON b.hp_name = b2.hp_name AND b.area_code1 = b2.area_code1 AND b.area_code2 = b2.area_code2
SET b.code = b2.code
WHERE b.code = '' or b.code is null;
[/code]
위와 같은 쿼리를 입력했는데 소요시간이 2시간 반 걸렸고 업데이트행이 496837 나오네요; 쿼리를 잘못짠건지 아니면 업데이트의 경우 시간이 많이 소요되는건지 모르겠네요;; -
꿈꿈꾸미
→ 주사기든남자 24.05.15 · 1.♡.3.32
정확한 것은 plan을 봐야겠지만, b 테이블을 Full Scan하면서 b2를 반복하면서 group by하는 것 같습니다. (lock이 없다는 가정하에)
b2가 먼저 Access하여 한번만 읽도록 힌트를 추가하든지, 쿼리를 수정해야 할 것 같습니다 - 주
주사기든남자
→ 꿈꾸미 작성자 24.05.15 · 121.♡.101.96
아 해결했습니다 혹시나해서 left join을 inner join으로 변경했더니 업데이트 속도가 확실히 개선됐네요 - A
assa2000
→ 주사기든남자 24.05.16 · 123.♡.145.230
15051055_2022테이블을 조인하는데 group by 로 그룹핑 하는 이유가 있을까요?
쿼리문을 보니 하시고자 하는 내용이 2023테이블에 code값이 널이거나 빈값이 있으면 2022테이블의code 값을 넣어주는 쿼리로 보입니다
조인을 하실때 left join ( ) <--괼호안에 셀렉트문을 그냥 2022테이블로 적어주시면 훨씬 속도가 개선될것 같네요
update 2023 b inner join 2022 b2 on ... 이렇게 변경해보시고 결과가 같은지 확인해 보세요
쿼리플랜과 인덱스등을 보면 좀더 해석이 가능 하겠지만 올려주신 쿼리를 보고 짐작해서 말씀드려봅니다 - A
assa2000
→ assa2000 24.05.16 · 123.♡.145.230
UPDATE `15051055_2023` b
INNER JOIN `15051055_2022` b2 ON (b.hp_name = b2.hp_name AND b.area_code1 = b2.area_code1 AND b.area_code2 = b2.area_code2)
SET b.code = b2.code
WHERE b.code = '' or b.code is null;
join문에 인라인쿼리를 사용하면 index를 사용하지 못하게 되어 느려질 것입니다.
댓글을 작성하려면 이 필요합니다.
쿼리에 결정적인 역할을 하는것은 인덱스입니다
주려는 저건들이 인덱싱이 잘 되어있지 않으면 어떤 db로 하시건 느릴꺼에요
주요 조건들로 인덱싱을 해보시면 결과가 많이 빨라질 수 있을꺼에요