[ MySql ] JOIN 속도를 빠르게! EXPLAIN, STRAIGHT_JOIN

내가 만든 웹페이지중에 엄청나게 속도가 느린 곳이 있었다.
분명 DB에서 SELECT하는데 문제가 있을 것이었다.

문제의 그 쿼리는 3개의 테이블을 JOIN해서 SELECT 하는데, 매우 느렸다. 

아래와 같은 방법을 써서 JOIN 하는 쿼리의 속도를 개선시켰다.





1. 쿼리의 성능을 측정(EXPLAIN)

그 쿼리가 어떻게 돌아가는지 보기 위해 EXPLAIN 을 써보았다.

EXPLAIN
SELECT * FROM 테이블명 WHERE 조건 


다음과 같은 쿼리를 실행하면, 
아래와 같은 항목이 나타난다. 

 id
select type 
table 
type 
possible_keys
key 
key_len 
ref 
extra 


각 항목에 대한 자세한 정보는
http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21444&cat1=7&cat2=217&cat3=227&lang=k 
이곳을 참조 하면 된다.


이중에 유심히 봐야할 정보는 type 이다.

내 경우에는 type이 all 로 나타나고 있었다. 풀스캔 하고있었단 소리..
또한, extra에 using temporary 라든가 여러가지가 나타나면 속도는 굉장히 느려진다.




2. JOIN 쿼리의 최적화 


아래 예제의 테이블을 조인한다고 가정해보자.

A테이블 (이름이 명시된 테이블) 

A_ID(PK) 
NAME 
 1    사자 
 2    고양이 
 3
 신매력 

B테이블 (스킬이 명시된 테이블)

B_ID(PK) 
SKILL 
 1   
 얼음꽃 
 2    흐규흐규 

C테이블 (A와 B를 참조하는 관계테이블)

INDEX_01 : A_ID
INDEX_02 : B_ID

A_ID(FK) 
B_ID(FK) 
 1   
1 
 1
2 
 2   2 


여기서, A B C 테이블을 조인해서 모든 컬럼을 가져와야한다고 하면 어떤 순서로 조인해야할까?


먼저 A테이블과 C를 조인한다고 하면..

SELECT * FROM A 
LEFT JOIN C
ON A.A_ID = C.A_ID   가 될텐데,

이렇게 하면 ALL 이 될 것이다.
왜냐>??

A테이블은 C에 없는 A_ID 행들을 쫙 가지고있다.
A기준으로 찾는다고 생각을 해보면, A테이블을 다 뒤져보게 되는 것이다.

그래서 반대로 조회를 해야한다.


SELECT * FROM C 
LEFT JOIN A
ON C.A_ID = A.A_ID

더구나 C 테이블의 C.A_ID는 인덱스까지 잡혀있다.
EXPLAIN으로 나온 type중에 index로 나와도 ALL 다음으로 최악이지만
ALL보다는 훨씬 빠르다.. (using temporary 등등 그런게 없어진다)


그래서 세개를 다 합친 쿼리는 대충 아래와 같이 된다.

SELECT * FROM C
LEFT JOIN A
ON C.A_ID = A.A_ID
LEFT JOIN B
ON C.B_ID = B.B_ID 



3. STRAIGHT_JOIN


EXPLAIN으로 봤더니 또 type이 ALL이었다...


그 이유는 MySql 에서 내부적으로 join 순서를 바꿔서 실행하기 때문이다.(mysql 속의 통계 디비를 참조하여 실행된다고 함)

그래서.. 저 명시된 순서로 조인을 하기 위해서는 STRAIGHT_JOIN을 사용해야한다.


SELECT STRAIGHT_JOIN * FROM C
LEFT JOIN A
ON C.A_ID = A.A_ID
LEFT JOIN B
ON C.B_ID = B.B_ID  



* 참고

나의 경우는 저렇게 하고도 또 ALL이 두번 나타났는데, 
그 이유는...

1. ORDER BY 없이 LIMIT 를 거는 행위를 해서 ㅠㅠ
(어느 컬럼 기준인지 모르므로 풀스캔을 하게 되었다)

2. ORDER BY 하는 컬럼의 기준 때문이었다. 
저 위에 예제 테이블에서 A 테이블이나 B테이블 기준으로 ORDER BY를 하면 
모든 곳이 탐색될 것이다..


출처: http://marobiana.tistory.com/35 [Take Action]