데이터베이스 질문과 답변
안녕하세요~ 저는 이제 사회로 나온 9개월차 개발자입니다...
학교에서 프로젝트를 진행했던 것과는 다르게, 사회에 나오니 운영중에 발생하는 쿼리 문제들이 있더라구요...그래서 이렇게 질문을 남기게 되었습니다.
SELECT time, name
FROM tableA
WHERE service != '1111111'
AND id IN ( SELECT id FROM tableB WHERE id IN('777777') )
쿼리는 위와 같습니다.
제가 깊게 생각하지 않고 쿼리를 짜써 서브 쿼리의 IN 절 안에 들어오는 값이 1개 (777777) 밖에 없음에도(실제로는 동적으로 넘어오는 값) IN절 사용하였고, 그리고 서브쿼리의 반환되는 값이 1개의 로우밖에 없는데도 id IN 을 사용하였습니다.
아무리 신입이지만 부끄러운 쿼리더군요...
프로젝트 오픈전에는 위의 쿼리도 문제가 없이 작동해서 그냥 넘어간것 같습니다...
근데 문제는 테이블들의 데이터가 증가하면서 문제가 발생하였습니다.
오늘보니 위 쿼리를 조회하는데 15초나 걸리더군요 ㅠ
그래서 IN절 대신 = 을 사용하여 쿼리를 바꾸니 0.3초도 걸리지 않았습니다.
제가 궁금한건, 어차피 IN 절에 들어오는 값도 1개인데 이렇게 속도가 차이가 날 수 있나 하는게 의문이었습니다.
실제로 실행계획을 보니, 예상 COST는 280 정도 였습니다.
또한 Index 도 (service, id) 값으로 주었으며, where절에서도 인덱스 순서대로 조건을 주었으나
full scan이 발생했습니다.
그리고 스캔해서 데이터를 가져온 후 Nested Loop Join를 하던데 이게 문제였던 걸까요?
문제는 해결했지만, 알고 넘어가지 않으면 제것이 된다고 생각하지 않기에 이렇게 도움을 청합니다~
혹시 알고계신 선배님들이 계시다면, 답변을 남겨주시면 감사히 공부하도록 하겠습니다 ㅎ
아 그리고!!
SELECT time, name
FROM tableA
WHERE service != '1111111'
AND id = ( SELECT id FROM tableB WHERE id IN('777777') )
실제로 문제는 IN을 모두 = 로 바꾼게 아닌 서브쿼리에서 받아오는 값에 대한 IN 절을 =로 바꾸기만 한것으로도 해결되었습니다!!
제 생각을 말씀 드리기에 앞서 약간 부연 설명을 먼저 하자면..
부정비교는 기본적으로 인덱스를 활용 못 합니다.
따라서 선두 컬럼인 service를 부정비교(!=) 하면 기본적으로는 인덱스 활용 못 하는 게 맞습니다만,
service의 갯수가 한정적이라면 skip scan이 가능합니다.
예를 들어 service 의 갯수가 10000개 인 것 가운데 1111111 인것 제외한 것에서 id가 777777인 것 을 찾으려면
service 9999개 중에 777777을 찾아야 하니 옵티마이저가 이렇게 찾을 바에는 풀스캔 하자고 실행계획을 잡을 수 있어요.
servcie 의 갯수가 3개라고 생각하면 2개의 service 중에 777777 인 것만 찾으면 되니 선두 컬럼은 무시(?) 하고
후행컬럼으로 스캔할 수 있습니다.(skip scan)
이제부터는 제 추측이이예요.
위와 같은 상태에서 스킵스캔을 한다고 했을 때
id의 값이 매우 여러개라면, 예를 들어 service 아이디가 2222222인 것의 id의 종류는 100개라고 했을 때
서브쿼리에서 99개의 id가 나올 거라고 판단되면 옵티마이저는 인덱스스캔을 할까요?
제 생각에는 풀스캔을 할 것 같습니다.
위의 쿼리 그대로 작성하신 게 맞다면 우리가 보기에는 서브쿼리에서 id가 777777인 것만 나오겠구나 생각되겠지만
옵티마이저는 in절이네? id 값이 여러개 나올 수도 있겠네? 판단할 수 있다는 거죠.
in 대신 =을 썼다면 무조건 단일 값이 나올 거라는 것을 테이블 값들을 분석해놓지 않았어도 옵티마이저는 판단할 수 있고
인덱스 스캔을 시도했을 거라고 생각합니다.
추가로 말씀드리면 서브쿼리를 작성하고 =으로 비교하는 것은 서브쿼리에 rownum=1을 사용하시든지 항상 단일 값이
보장되게 작성하셔야해요. 데이터에 따라서 복수의 값이 나오면 쿼리 오류 납니다.
운영중에 오류 만나면 당혹스럽겠죠?ㅎㅎ
2017년 제가 프로젝트 시작하기 전에 질문 이것저것 많이 올리셨던 게 생각나는데
벌써 취업하셔서 열심히 일하시고 계시네요^^
항상 노력하시는 모습 보기 좋습니당^^
친절한 답변 감사합니다 ㅎㅎ
선두 컬럼을 부정비교 하면 인덱스를 타기 힘들다는건 처음 알았네요!!ㅎ
단일값이 보장되게...!! 혹시 모르니 한번 더 확인해봐야겠네요 ㅎ
우와 2017년...!! ㅎㅎ 맞아요 ㅎ 그때 OCP랑 SQLD 준비하면서 조금씩 디비에 대해 흥미를 붙였던것 같아요 ㅎ
관심에 감사드립니다 ㅎ 더 열심히 하는 모습 보여드릴게요 ㅎㅎㅎ
좋은 하루 보내세요~~ㅎ
실행계획도 확인하셨다고 하니.
두 실행계획의 차이를 분석하시면 됩니다.
인덱스 선두항목의 조건이 부정조건이므로 인덱스 사용이 어렵습니다.
1. 굳이 IN 절을 쓸 필요가 있는가? = 로 변경
2. 굳이 서브쿼리를 쓸 필요가 있는가? 조인으로 변경
3. 굳이 조인을 할 필요가 있는가?
A 의 ID 는 항상 B 에 있다고 가정한다면? 조인은 필요 없습니다. B 조인 제거.
불필요한 요소들을 줄이고 쿼리를 간결하게 작성할수록 오류가능성이 적어지고 성능도 개선됩니다.
항상 완벽한 답변에 감사합니다 마농님 ㅎ
덕분에 서브쿼리 와 조인에 대한 성능 비교를 하는 글들을 찾아볼 수 있었어요 ㅎ
항상 쿼리를 짤때는 불필요한 요소는 줄여라!!
좋은 하루보내세요~ㅎ
) 버튼을 클릭하여 작성 하시면 됩니다.