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

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