Mysql STRAIGHT_JOIN으로 튜닝하기
안녕하세요. 개발 까마귀입니다.
오늘 알려드릴거는 "STRAIGHT_JOIN" 입니다.
STRAIGHT_JOIN?
별거 아닙니다 여러분이 작성하신 SQL문에 FROM 절에 명시된 테이블 순서대로 조인하도록 하는 조인 힌트 명령문입니다.
SELECT * FROM A INNER JOIN B ON A.id = B.id위 SQL문 에서 A 테이블은 드라이빙 테이블 이고 B 테이블은 드리븐 테이블 입니다.
하지만 옵티마이저에 의해서 드라이빙 테이블과 드리븐 테이블에 순서가 바뀌지 않게끔 STRAIGHT_JOIN으로 힌트를 유도하는 것이죠
언제 사용해야하는가?
위에 말씀드렸다 시피 FROM 절에 명시된 테이블 순서대로가 아닌 옵티마이저가 판단하에 순서가 뒤바뀌고 해당 옵티마이저에 판단으로 속도가 느려졌을 때 사용하면 좋습니다.
SELECT 매핑.사원번호, 부서.`부서번호` FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.`부서번호` = 부서.`부서번호`
AND 매핑.`시작일자` >= '2002-03-01';
부서 테이블의 총 rows는 9개 부서사원_매핑 테이블 총 개수는 30만개 입니다.
실행계획을 보면 아시다시피 FROM 절에 드리븐 테이블로 명시한 부서 테이블이 실행계획에서는 드라이빙 테이블로 명시가되어있습니다.
위 쿼리에서는 옵티마이저가 판단할 때는 부서 테이블이 적으니깐 드라이빙 테이블로 사용한것같습니다.
대부분의 기술 블로그에서도 드라이빙 테이블에 개수가 적어야지 성능이 나온다고 알려줍니다.
당연한것이 Nested Loop JOIN 특성을 따르니깐요.
하지만 위 쿼리를 실행하면 10초가 걸립니다. 즉 실 서비스에서는 실행조차 할 수없는 쿼리입니다.
이유를 살펴보면 WHERE 절에 매핑.`시작일자` >= '2002-03-01' 문이 있습니다.
즉 부서사원_매핑 테이블이 드리븐 테이블이므로 NESTED_LOOP JOIN 특성에 따라 WHERE 절에 매핑.`시작일`자 >= '2002-03-01' 문이 부서 테이블 rows 만큼 9번 탐색이 되는거죠 매우 비효율적입니다.
안그래도 30만건으로 rows가 큰 테이블에 9번이나 데이터를 탐색한다는것은 튜닝이 필요한 쿼리입니다.
SELECT COUNT(*) AS cnt FROM 부서사원_매핑 매핑 WHERE 매핑.`시작일자` >= '2002-03-01';위 SQL문을 돌렸을 때 나타나는 검색 결과는 1341건입니다. 그렇다는거는 JOIN을 하기전 매핑.`시작일자` >= '2002-03-01' 문이 먼저 실행해서 30만건인 매핑 테이블을 1341건으로 조인하면 빠르게 데이터 추출이 가능할것입니다.
STRAIGHT_JOIN으로 튜닝
SELECT STRAIGHT_JOIN 매핑.사원번호, 부서.`부서번호` FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.`부서번호` = 부서.`부서번호`
AND 매핑.`시작일자` >= '2002-03-01';
위 쿼리로 실행했을 때 지연시간은 0.030초 10초에 매우 줄어진거를 확인할수있습니다.
이렇게 테이블 rows 로만 판단해서 옵티마이저가 드라이빙 드리븐 테이블을 바꾸었고 지연시간이 많이 늦춰줬을 때 사용해보면 좋습니다. 하지만 항상 STRAIGHT_JOIN을 사용해서 해결할수있는 거는 아닙니다.
실행계획과 테이블의 rows를 잘 분석해서 사용해야합니다.
실무에서 사용함으로써 문제해결
저는 커뮤니티라는 서비스를 유지보수 및 개발을 담당하고 있었습니다. 그저 사용자끼리 게시판 및 댓글로 소통하는 MSA 입니다. 하지만 갑자기 게시판 진입 안된다는 CS가 엄청 들어오고 30초 동안 기다려서 게시판 진입시 개발자 및 서비스를 욕하는 게시글이 엄청 많더군요 ㅎㅎ
해당 문제는 `feeds` 테이블 즉 게시글을 저장하는 테이블과 `category` 게시글 카테고리 테이블이 조인함으로 써 문제가 발생한 것입니다. 위에서 예제로 설명드렸다시피 `feeds` 테이블은 드라이빙 테이블로 명시가되어있었고 WHERE 절에는 `feeds` 테이블에 조건절 밖에없었습니다. 하지만 옵티마이저는 rows 개수로만 판단했고 몇백만건이 있는 `feeds` 테이블 보다 30건밖에 없는 `category` 테이블이 더 드라이빙 테이블에 적합하다는 처참한 판단을 내림으로서 `feeds` 테이블에 몇백만건이 `category` 테이블에 명시된 rows 개수만큼 WHERE 절을 돌았으니 당연히 30초 그 이상에 지연시간이 걸렸습니다. 혼자 며칠동안 분석하면서 문제를 해결했었던 기억이 있습니다 ㅎㅎ
옵티마이저에 판단이 무조건 맞지는 않습니다. 그리고 또 STRAIGHT_JOIN 이 만능은 아니므로 드라이빙 테이블과 드리븐 테이블을 잘 분석하고 실행계획 또한 잘 파악해서 사용하는것이 좋습니다. 또한 여러 ORM에서는 STRAIGHT_JOIN 힌트를 지원하지 않으므로 ORM을 사용한다면 raw query로 문제를 해결해야할겁니다.
감사합니다.
댓글