[Mysql] INNER JOIN + ORDER BY 에서 filesort 제거해보기

Mysql 에서 INNER JOIN + Order By 를 하게 될때, Slow 쿼리가 발생될수 있으니까 실행 계획을 확인해보고 사용해야 한다.

 

아래의 테이블이 있다고 가정해 보자.

Category 와 Product 테이블이 있다고 가정을 하고, 1:N 관계를 갖고 있다.

Product 테이블에는 category_id 컬럼이 인덱스로 잡혀 있다. (FK 아님)

 

Product 테이블과 category 를 조인하고 product 테이블의 PK 로 정렬해서 조회를 해보자.

select * from `product` p, `category` c
where p.category_id = c.id
order by p.id desc limit 1000;

 

테스트 데이터를 category 10건 + product + 10건을 넣고, 실행 계획을 확인해 보자.

빠른 쿼리다! Mysql 옵티마이저에 의해 드라이빙 테이블이 product 로 선택이 되었고, 드리븐 테이블은 category 로 선택이 되어서

Product 의 PK 로 order by 를 수행할때, 정렬 작업을 수행하지 않고, 정렬된 B-Tree 인덱스 를 순서대로 읽어서 빠르게 조회가 가능하다.

 

 

그렇다면 Select + Insert 쿼리로 Product 테이블에 데이터를 약 200만건 넣어보자

insert into product (`name`, category_id)
select `name`, category_id from product;

 

실행 계획을 확인해 보자.

Using temporary; Using filesort

느린 쿼리다! Extra 에 보면 아래와 같은 메시지가 나온다.

Using temporary; Using filesort

• Using temporary : 임시 테이블 생성
• Using filesort : 정렬 작업 수행

두개의 테이블을 조인한 임시테이블을 사용하였고, 조인된 임시테이블을 정렬작업을 수행하였다라는 뜻이다.

그리고 Mysql 옵티마이저도 드라이빙 테이블도 product 가 아닌 category 테이블을 사용하였다.

 

Mysql 의 옵티마이저는 조인되는 컬럼이 양쪽 테이블에 모두 인덱스가 있다라고 한다면, 테이블 통계값을 보고 드라이빙 테이블을 선택하게 되어 있는데, product 테이블이 100만건이 되므로 category 를 선택한 것이다.

 

임시테이블을 만들고 정렬작업을 수행한 이유는?

B-Tree 인덱스를 이용한 정렬은 ORDER BY 절에 들어가는 컬럼이 드라이빙 테이블에 속하고, 그 컬럼이 인덱스로 있어야 가능하다.

 

해당 쿼리에서는 Order By 절에 Product 의 ID가 들어가 있는데, Mysql 옵티마이저는 드라이빙 테이블을 Product 테이블이 아닌, Category 테이블로 선택하였기 때문에 Product의 ID 컬럼의 B-Tree 인덱스 순서대로 읽지 못하여 임시테이블을 만들고 정렬 작업을 수행한 것이다.

 

빠른 쿼리로 변경을 하려면 2가지 방법이 있다.

 

1. Left Join 을 하는 방법

 

Left Join 을 하게 되면 Mysql 의 옵티마이저가 드라이빙 테이블이 product 로 결정되기 때문에 B-Tree 의 인덱스 순서대로 읽기가 가능해서 임시테이블 + 정렬작업이 필요없게 되서 빠른 쿼리가 가능하다.

select * from product p
left join category c on c.id = p.category_id
order by p.id desc limit 1000;

실행 계획을 확인해 보자.

 

2. STRAIGHT_JOIN 사용하는 방법

 

STRAIGHT_JOIN을 이용해서 명시된 순서대로 조인을 하는 방법이다.

이렇게 하면 Product 테이블이 항상 드라이빙 테이블이 된다.

select * from product p
straight_join category c on c.id = p.category_id
order by p.id desc limit 1000;

실행 계획을 확인해보자