조아상사
로그인
회원가입
정보찾기

개발한당

(탄핵 재표결 3일 전)   🔥 제목 시위는 [말머리] 또는 임시게시판(불타는앙)을 이용바랍니다.
강릉하얀감자탕

[질문]8개의 테이블, 테이블당 60만개의 데이터의 조합

알림
|
X
주사기든남자 59.♡.52.125
2024.05.12 14:51
674
17
0
쓰기

비개발자출신으로 여가시간에 맨땅에 헤딩중입니다.(chatgpt 만쉐이)

synology NAS에 mariadb 사용중입니다.

개발할 내용이 있어 8개의 테이블에 테이블당 60만개의 데이터를 4가지 조건으로 join을 하려고 합니다.

단순히 쿼리를 실행해보니 실행시간이 한시간이 훌쩍 넘어가던데요;

데이터 제공 서비스로는 부적합한 조건이라 생각되어

각 테이블을 지역별로 테이블을 쪼갠 뒤 각 테이블을 조건에 맞춰서 한테이블을 만들어서 지역별 테이블로 구성하려고 합니다.

위와 같이 진행할 경우 실행시간 단축에 도움이 될까요?

그리고 mariadb말고 postgresql이 있던데 이쪽은 복잡한 쿼리에 좀더 나은 효율을 보이는지 궁금합니다.


댓글 17
no_profile 미소섭 (106.♡.131.119)
05.12 15:37
간단하게 첨언을 하자면
쿼리에 결정적인 역할을 하는것은 인덱스입니다
주려는 저건들이 인덱싱이 잘 되어있지 않으면 어떤 db로 하시건 느릴꺼에요
주요 조건들로 인덱싱을 해보시면 결과가 많이 빨라질 수 있을꺼에요
주사기든남자 (59.♡.52.125)
05.12 15:54
@미소섭님에게 답글 결국은 테이블 디자인이랑 인덱싱으로 해결봐야한다는 거네요.
답글 감사합니다!
디클 (124.♡.100.52)
05.12 19:53
위에 말씀하셨듯이 Index 구성으로 성능을 높을수 있습니다.
다만 DB마다 다르지만, 해당 데이터 분포가 대략 3% 정도 이내에 들어와야 Index 를 사용하고 아니면 Full Scan 으로 전체 테이블을 Scan 하게 됩니다. (설계할때 참고)

쉽계 얘기해서 되도록 식별 가능한 유니크한 필드 기준으로 Index 를 생성하고 Where 절이나 Join 절에 사용하면 됩니다.
주사기든남자 (59.♡.52.125)
05.14 09:46
@디클님에게 답글 일단 인덱스를 추가해서 해보고있는데 확실히 그전보다는 속도가 빨라진 느낌입니다!
assa2000 (106.♡.113.70)
05.13 12:40
DB관점에서 60만건은 많은 데이터건수라 할순 없고 인덱스를 활용 안하셨거나 조인문이 잘못됬을 가능성이 크네요 테이블 스크립트와 쿼리문을 올려주시면 좀더 자세한 조언을 받으실수 있을것 같습니다 ^^
주사기든남자 (59.♡.52.125)
05.14 09:45
@assa2000님에게 답글 
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;

위와 같은 쿼리를 입력했는데 소요시간이 2시간 반 걸렸고 업데이트행이 496837 나오네요; 쿼리를 잘못짠건지 아니면 업데이트의 경우 시간이 많이 소요되는건지 모르겠네요;;
꿈꾸미 (1.♡.3.32)
05.15 19:01
@주사기든남자님에게 답글 정확한 것은 plan을 봐야겠지만, b 테이블을 Full Scan하면서 b2를 반복하면서 group by하는 것 같습니다. (lock이 없다는 가정하에)
b2가 먼저 Access하여 한번만 읽도록 힌트를 추가하든지, 쿼리를 수정해야 할 것 같습니다
주사기든남자 (121.♡.101.96)
05.15 19:26
@꿈꾸미님에게 답글 아 해결했습니다 혹시나해서 left join을 inner join으로 변경했더니 업데이트 속도가 확실히 개선됐네요
assa2000 (123.♡.145.230)
05.16 00:16
@주사기든남자님에게 답글 15051055_2022테이블을 조인하는데 group by 로 그룹핑 하는 이유가 있을까요?
쿼리문을 보니 하시고자 하는 내용이 2023테이블에 code값이 널이거나 빈값이 있으면 2022테이블의code 값을 넣어주는 쿼리로 보입니다
조인을 하실때 left join ( ) <--괼호안에 셀렉트문을 그냥 2022테이블로 적어주시면 훨씬 속도가 개선될것 같네요
update 2023 b inner join 2022  b2 on ... 이렇게 변경해보시고 결과가 같은지 확인해 보세요
쿼리플랜과 인덱스등을 보면 좀더 해석이 가능 하겠지만 올려주신 쿼리를 보고 짐작해서 말씀드려봅니다
assa2000 (123.♡.145.230)
05.16 08:34
@assa2000님에게 답글 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를 사용하지 못하게 되어 느려질 것입니다.
주사기든남자 (59.♡.52.125)
05.16 11:17
@assa2000님에게 답글 인덱스 사용을 유도하는 방식을 쿼리를 짜는게 중요하네요
주사기든남자 (59.♡.52.125)
05.16 11:08
@assa2000님에게 답글 일단 chatgpt를 사용해서 최적화를 진행했었구요
해당 컬럼을 그룹핑한 이유는 이 테이블자체가 병원내 기기정보인데 병원식별코드값을 일괄입력하기위한 코드라서요
그룹핑을 할경우 검색행수가 1/10 이상으로 줄어들어서 진행했습니다.
혹시 그룹핑을 할 필요가 없었을까요?;;
꿈꾸미 (1.♡.3.32)
05.16 18:13
@주사기든남자님에게 답글 그룹핑을 해서 건수가 줄었다면 그룹핑을 반드시 해서 조인을 해야합니다. 그보다는 병원식별코드를 관리하는 마스터성 테이블이 따로 있어야 합니다. 그래야 그룹핑을 하지 않고 조인을 해서 성능이 좋아집니다
assa2000 (211.♡.190.219)
05.17 08:00
@주사기든남자님에게 답글 그룹핑해서 서브쿼리 태이블을 만들면 기존에 해당테이블에 존재하던 인덱스를 이용할수 없게됩니다 새로운 테이블로 인식 하거든요 쿼리의성능은 어떻게 빠르게 원하는 데이터를 찾아내는가 하는 싸움이라 할수 있습니다 그래서 데이터량을(db가 결과값을 찾기워해 접근하는 건수) 줄이는 과정이 중요한데 적절한 인덱스가 없다면 그룹핑해서 건수(엑세스) 를 줄이는것도 하나의 방법이겠지만 테이블에 데이터가 많은 상황에서 그룹핑 자체가 부담이 될수 있습니다
assa2000 (211.♡.190.220)
05.17 08:10
@assa2000님에게 답글 그래서 어떤 방법이 좋을지 선택하기 위해 explain(쿼리 플랜을 떠보는 것입니다)
explain + query하면 DB에서 쿼리 실행계획을 알려줍니다. 쿼리문을 변경해 가면서 최적의 쿼리를 찾아내는 것이 쿼리 튜닝의 과정의 일부라 할 수 있겠습니다.
꿈꾸미 (1.♡.3.32)
05.17 22:15
@assa2000님에게 답글 인덱스가 만능이 아닙니다. ^^
건수가 상대적으로 적을 때만 유리하지, 건수가 많아지면 full scan이 더 빠릅니다
no_profile 살포시 (126.♡.10.46)
05.13 13:06
위에 분들이 설명을 잘 해 주셨지만, 어떻게 해도 풀스켄이 뜨면 각 테이블 별로 검색해서 결과 보여주는게 빠를 수도 있습니다.
쓰기