개발/Database

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

신매력 2012. 11. 18. 15:46

내가 만든 웹페이지중에 엄청나게 속도가 느린 곳이 있었다.

분명 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

 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를 하면 

모든 곳이 탐색될 것이다..