SQL 쿼리 조인 질문드립니다(MYSQL 실행 계획 최적화)

Question

안녕하세요.오랜만에 질문 글을 작성하네요.MySQL 조인, 정렬 최적화에 대한 질문입니다.우선 처음에는 아래와 같은 쿼리를 작성했습니다.SELECT ... C.ID, C.NAME, ... FROM A INNER JOIN B ON B.ID = A.B_ID INNER JOIN C ON C.B_ID = B.ID WHERE A.NUM = ? ORDER BY C.ID DESC LIMIT ?하지만 실행 계획을 살펴보니테이블 탐색은 각 테이블마다 필요한 인덱스가 잡혀있어 인덱스를 적절히 사용하여 이루어졌지만ORDER BY C.ID DESC 때문에 최종적으로는 테이블 A에 대해 using temporary, using filesort가 발생하였습니다.실제 쿼리 실행 시간 역시 테이블 C의 전체 레코드 1백만 건 기준 10 sec 이 넘게 걸렸습니다.느린 이유를 짐작해보자면, 테이블 B와 연관된 테이블 C를 처음부터 끝까지 읽어온 후 C.ID DESC로 정렬하고필터링하기 때문인 것 같습니다.테이블 C는 데이터 삽입이 빈번하게 예상되는 테이블이기 때문에 반드시 튜닝이 필요합니다.따라서 제가 고안해 낸 조인 순서 및 정렬, 필터링 과정은 다음과 같습니다.(설명을 잘 못하는 터라 그림을 준비했습니다)테이블 A의 A.num = ? 인 레코드와 테이블 B의 B.id = A.b_id 인 레코드를 내부 조인하여 가져옵니다.그리고 가져온 각각의 테이블 B 레코드에 대해서 테이블 C와 내부 조인하여 C.b_id = B.id 인 레코드들을 각각 상위 최대 N개씩 가져옵니다.조회 된 C 레코드 전부를 C.id 를 기준 내림차순으로 정렬하고 상위 N개를 뽑아냅니다.이 결과를 다른 테이블들과 조인하여 사용합니다.그림에 나타난 테이블 B를 제외한 테이블A, C 모두 그림에 나온 순서대로 세컨더리 인덱스가 잡혀있습니다.ex. 테이블은 A는 (A.num, A.b_id), 테이블 C는 (C.b_id, C.id)기준이 되는 테이블 A의 레코드가 K개라면, 데이터 베이스는 K * 20 개의 레코드에 대해서만 정렬, 필터링 작업을 하면 되므로 훨씬 효율적이라고 판단했습니다.하지만 위와 같은 방식으로 실행하게 하려고, CTE도 써보고 FROM 절 서브쿼리도 써봤지만파일소트가 계속 발생(주로 테이블 A에 대해)하고 쿼리 실행 시간도 개선되지 않네요.어떤 방식으로 쿼리를 작성해야 하는지 도움 부탁드리겠습니다.항상 귀중한 시간 내어 답변 작성해주시는 오키 회원분들(특히, 시니어 현직자분들)께 감사드립니다.제안해주신 쿼리의 실행계획 첨부합니다.

ahuynahcuy · Accepted Answer

SELECT ... C.ID, C.NAME, ... FROM (select * from A WHERE NUM = ?) A INNER JOIN B ON B.ID = A.B_ID INNER JOIN C ON C.B_ID = B.ID ORDER BY C.ID DESC LIMIT ?

코딩하는 두루미 · Answer

쿼리 튜닝에 대해서는 잘 알지 못하는데 아이디어를 한번 내보고 싶은데요..!From절에 정렬된 C테이블 데이터를 서브쿼리로 넣고 이후 조인으로 b, a순으로 붙이면 어떻게 될까요?

와플치킨 · Answer

A의 키값이 뭔지 모르겠는데 A테이블이 엄청 큰 것 같은데 B_id로 인덱싱 해놓는건 어떨까요?..

stopoff · Answer

이론상 드라이빙 테이블이 A라면 A, B 테이블 조인 후 데이터 와 C테이블이 조인 되어 ORDER BY는 영향이 없어야 할 것 같은데 드라이빙 테이블이 C테이블로 되는 거 같네요..조인시 인덱스가 원하는 인덱스가 아닌 다른 인덱스가 실행 될지도 모르겠습니다..인덱스 구성은 위의 글만으로는 알 수가 없으니아래와 같이 인라인뷰로 감싸보는 것은 어떨까요?(각 테이블의 ID가 primary key 이고 primary index로 잡혀 있다면원하는데로 나와야 하는데요..)SELECT *FROM ( SELECT ... C.ID, C.NAME, ... FROM A INNER JOIN B ON B.ID = A.B_ID INNER JOIN C ON C.B_ID = B.ID WHERE A.NUM = ?)ORDER BY ID DESCLIMIT ?

장독깨기 · Answer

order by id descmysql 은 인덱스 생성 시 오름차순, 내림차순을 지정할 수 있을 겁니다.이 부분 한 번 알아보세요.

SQL 쿼리 조인 질문드립니다(MYSQL 실행 계획 최적화)

5개의 답변

기술 목록