MySQL :: inner join :: (이너)조인과 인덱스의 실험!!! (explain 활용)

현재 프로젝트를 하는 중에 인덱스를 활용한 이너 조인(inner join) 을 필요로 하는 게 있었다. 그런데 중요한 것은 조인을 설정할 때는 조인을 하려는 값을 인덱스를 걸어줘야, 성능이, 즉 속도가 빨리 나올 수 있다. 

여기서 그럼 이너조인이 뭔지 살펴보자.

가령 대학교의 컴퓨터 공학부에서 학생들의 이름과 학번을 관리하는 파일이 있고, 데이터베이스 과목을 수강하는 학생들의 학번과 성적을 관리하는 테이블이 있다고 하자.

학생들의 테이블을 student 라고 하고 데이터베이스 과목을 수강하는 학생들의 테이블을 db 라고 하면

아래와 같은 쿼리 문을 실행해 주자.

create table student(

stuId tinyint,

name varchar(10),

index idx_stuId(stuId)

);

 

 

create table db(

stuId tinyint,

score tinyint,

index idx_stuId(stuId)

);

insert into student values(101, '고길동');

insert into student values(102, '둘림');

insert into student values(103, '까아치');

insert into student values(104, '열심이');

insert into db values(102, 98);

insert into db values(103, 76);

그럼 그 결과는 아래와 같다.

 

 

 

 

자 이제, 이너 조인이 뭔지 알아보자. 데이터베이스 담당 교수님께서, 수업을 듣는 학생들의 이름을 알고 싶은데 db 테이블 만으로는 학번 밖에 알 수 없다. 이 때, student 테이블에는 학번과 이름 정보가 있으므로 student 테이블과 db 테이블의 stuId 가 같은 값들로만 구성된 테이블을 만들면 데이터베이스 수업을 듣는 학생들의 이름과 학번, 점수를 알 수 있을 것이다. 그 쿼리문은 아래와 같다.

select student.stuId, student.name, db.score from student, db where student.stuId = db.stuId;

그리고 그 결과는 아래와 같다.

 

 

이처럼 이너조인은, 두 테이블의 데이터들 중에 공통된 값(들)이 있는 데이터들이 다리역할이 되어 두 테이블에 있는 정보를 합쳐서 정보를 제공하는 일을 한다. (정말 나만의 정의가 아닐 수 없다;;ㅋㅋ)

이제 정말 말하고 싶은 것을 말하겠다.

이너 조인이 이러한 특성을 가지는데, 지금이야 데이터가 몇개가 안되지만, 데이터가 계속 쌓여서 십 만개, 백 만개, 천 만개 정도가 되면 이너 조인을 그냥 해버리면 시간이 무척 오래 걸린다. 그래서 앞에서 테이블을 만들어 줄 때 index 를 걸어줘서, 조인의 속도를 향상시키려고 한 것이다. 

그런데 여기서 생각해 볼 것이,, 이너 조인 할 때, 비교되는 값이 모두 index 처리하는 것이 두 테이블 중 한쪽만 처리하는 것보다 더 성능이 좋아지는가 하는 것이다. 내 생각엔 당연히 비교되는 두 테이블의 속성 모두 인덱서를 줘야 성능이 더 좋을 것이라고 생각해서 위와 같이 stu 와 db 테이블 모두 인덱스를 걸어줬다. 

그런데 실제로 그게 아니었다!!! explain 을 이용해서 select 할 때 실제로 인덱스가 사용됐는지 여부와, 사용됐다면 어떠한 인덱스가 사용됐는지 알 수 있다.

explain select student.stuId, student.name, db.score from student, db where student.stuId = db.stuId;

와 같은 쿼리문을 실행했을 때 결과값이 아래와 같다. 

 

 

빨간색 박스 친 부분을 보라!!! db 테이블에서 사용 가능한 인덱스 키(possible_keys)는 idx_stuId 인데 실제로 그 키가 select 문을 실행할 때 사용되지 않았다는 것이다. 즉,

select student.stuId, student.name, db.score from student, db where student.stuId = db.stuId;

위의 쿼리문을 실행하면 조인을 하는데 이 때 사용되는 인덱스는 student table 의 idx_stuId 뿐이다. 이 말이 의미하는 바는 다음과 같다. 이제부터 내가 말을 잘써야 하는데,,, 집중해서 써보자.

지금 student 테이블은 데이터가 네 개이고, db 테이블은 두 개 이다. 지금 inner join(흔히 조인이라 하면 이너조인) 으로 테이블 두 개를 합치려는데 그 때 조건이 양 테이블의 stuId 값이 같은 경우에만 합치겠다고 했다. 그럼 이제 mysql 은 쿼리 문을 받았으니 비교를 해야한다. mysql 은 두 개 중에 하나의 테이블(A 라고 지칭)을 선택한 뒤 첫 행의 stuId 값을 가져온다. 첫 행의 stuId 값을 100 이라고 하자. 그럼 이제 mysql 은 100 값을 손에 들고서는 또다른 테이블(B 라고 지칭)을 첫 행부터 끝 행까지 모두 서치해서 B 테이블의 stuId 값 들 중에 100 과 같은 값을 가지는 행이 있는 지 없는 지 확인 한다. 원래는 처음부터 끝까지 서치해야하니까 시간이 많이 걸리겠지만, 인덱스가 걸려있으므로 훨씬 빨리 수행한다. 그래서, 만약 100 이라는 값을 가지는 행이 테이블 B 에 존재하면, 테이블 A 의 첫 행과 테이블 B 의 방금 찾은 그 행을 합친다. 이렇게 100 라는 값의 서치가 끝이나면, 이제 A 테이블의 첫 행에 대한 일이 끝이 났다. 이제 A 테이블의 두 번째 행의 stuId 값을 가져온다. 그 값이 200 이라고 치자. 그럼 200 값을 들고 테이블 B 에게 찾아가, 아까 했던 것처럼, 테이블 B 의 stuId 에 걸려있는 인덱스를 활용해서 200 이라는 값을 가지는 행이 있는지 없는지 조사한다. 조사가 끝나면 mysql 은 이제 테이블 A 의 세번째 행의 stuId 값을 가지고 다시 테이블 C 를 찾아가서 같은 일을 반복한다. 그래서 테이블 A 의 끝행까지 모두 조사가 끝나면 이너조인이 된 테이블이 나오는 것이다.

자, 그럼,, 문제는 mysql 은 두 테이블 중 어느 테이블을 A 로 선택하느냐 하는 문제가 남았다. 즉, student 테이블이 A 테이블이 되야 하는가, 아니면 db 테이블이 A 테이블이 되야 하는가 이다. 답은 간단하다.(물론 순수히 내 생각이긴 하지만..) 데이터가 적은 테이블을 A 테이블로 선택하면 된다. 

뭔 말이냐면, 가령 student 를  A 테이블로 선택했다 치자. 현재 student 테이블은 데이터가 네 개이기 때문에, 각 행 마다 stuId 값을 가져와서 db 테이블의 (인덱스 되 있는) stuId 와 비교하는 연산을 총 네 번 해야 한다. 그러나 db 를 A 테이블로 선택했다면, db 테이블은 데이터가 두 개 이기 때문에, 각 데이터 별로 stuId 값을 가져와서 student 테이블의 (인덱스 되 있는) stuId 와 비교 연산을 총 두 번만 하면 된다. 

그러므로 속도 향상을 위해, mysql 은 db 를 테이블 A 로 선택했고, 그러므로 사용되는 인덱스는 student 테이블의 index 인 것이다. 

그럼, 반대로 이젠 db 테이블의 데이터가 더 많으면 어떻게 될까?? 당연히 student 가 A 테이블이 되어, 사용되는 인덱스는 db 의 인덱스가 될 것이다.

지금까지 예로 든 것은 두 테이블에 인덱스를 걸어논 경우이다. 그러나 알다시피 primary key 나 unique key 의 경우에도 인덱스를 생성하는데, primary key 나 unique key 가 있는 테이블과 조인하는 경우에는 테이블의 데이터가 더 많든 더 적든 상관없이 primary key / unique key 를 가지는 테이블의 primary key / unique key 를 인덱스로 활용하지 않을 가 생각했다. 그래서 이를 테스트 하기 위해 실험중 이다...

좀 전에 20만건의 데이터를 가지는 테이블 A 와 약 5만건의 데이터를 가지는 테이블 B 와 조인 하는 실험을 했다. 테이블 A 는 주키(primary key) 를 가지고 있었기 때문에, 주 키를 가지고 인덱싱을 타서 조인이 (인덱싱 없이 하는 것 보다 당근 상대적으로) 빨랐다. 실제 쿼리문을 수행하는데에는 평균 1.5초가 걸렸다.

그래고 지금은 20만건의 데이터를 가지는 테이블 A 와 약 25만건의 데이터를 가지는 테이블 B 와 조인하는 실험을 했다.(바로 위 실험에서 테이블 B 의 자료를 5 만에서 25 만으로 바꿨을 뿐이다.) 조건은 위와 같다. 평균 5초 넘게 걸렸다. 게다가 이번에 explain 으로 찍어보니까 주키가 있는 테이블의 인덱스(주키가 가지고 있는 인덱스)를 쓰지 않고 그냥 인덱스 걸어논 테이블의 인덱스를 타서 조인하였다. 테스트를 해보니 위의 내 생각이 틀렸다는 것을 알 수 있었다.

흠,, 아직 조인에 대해 정확히 잘은 모르겠다. 하루 정일 여러 곳의 포스팅한 글과 외국 블로그 까지 찾아갔지만, 아직은 정확히 조인이라는 것에 대해 잘 모르겠다. 계속적인 연구가 필요할 것 같다.