[4기 대구 박병권] 데이터베이스 EXPLAIN을 활용한 슬로우 쿼리 검색

안녕하십니까?

대구멤버십 21-2기 박병권입니다.

 

오늘은 데이터베이스 EXPLAIN을 이용하여 Full-Scan하는 쿼리를 찾는 방법에 대해 이야기 하려고 합니다.

 

일반적으로 쿼리가 빠르게 동작하지 않는 경우는 대부분 너무 많은 데이터를 이용하기 때문입니다. 이런 슬로우쿼리는 필요이상의 데이터를 가져오는지 확인해야 하며, 필요이상으로 많은 행을 분석하는지 확인을 해보아야 합니다.

또한 해당 쿼리가 Full-Scan을 하는지 인덱스를 이용하여 참조를 하여 검색을 하는지 혹은 원하는 키가 아닌 다른 키로 검색을 하는 지 확인을 해보아야 할 때가 있습니다. 이럴 경우 EXPLAIN을 활용한다면 조금 더 빠른 쿼리를 작성하는데 도움이 될 것입니다.

 

EXPLAIN은 SQL을 수행하기 전 데이터를 어떻게 가져올 건지에 대한 실행계획을 의미합니다.

간단한 예로 인덱스를 이용하여 빠른 탐색과 인덱스를 제거한 후 탐색 속도를 예로 들겠습니다.

 

EXPLAIN 구문을 사용하면 아래 같이 여러 속성들을 볼 수 있습니다.

 

먼저 아래 구문을 실행합니다.

간단한 쿼리로 인덱스을 사용하면서 사용자 아이디를 이용하여 정보를 얻는 쿼리입니다.

 

-id : 번호

-select_type: 서브쿼리 혹은 단순한 쿼리

- table : 참조된 테이블

- type : 검색 방법

- possible_key : 검색가능한 키

- key : 실제 사용한 키

- key_len : 키 길이

- row : 참조된 행의 수

 

 

 

여러 속성 중 가장 중요하게 봐야 하는 부분은 type부분입니다. type부분은 아래와 같습니다.

- ALL : 전체 검색

- INDEX : 인덱스만 정의 되어 있는 곳을 검색

- RANGE : 범위를 제한하여 검색하기 때문에 ALL보다 빠른 검색

- REF : 특정 값이 매칭되어 있는 행을 탐색합니다. ex) 외래키를 이용한 검색

- CONST : 검색하는 값이 상수로 대체 시켜서 한 번의 읽음으로 값을 검색할 수 있음

 

위 쿼리는 인덱스가 적용된 상태이며 검색에 필요한 행은 1개입니다. type 역시 const이므로 아주 빠른 검색 시간을 기대할 수 있습니다.

 

동일한 데이터베이스에서 인덱스를 제거하고 쿼리를 실행합니다.

 

 

인덱스를 제거하였으므로 전체 탐색을 할 수 밖에 없기 때문에 type에 ALL이 있습니다.

또한 검색수는 전체 행의 수인 25151개를 참조해야 검색이 가능합니다.

ALL이 확인되는 쿼리는 조금 더 빠른 쿼리를 위해서 인덱스를 활용해서 빠른 쿼리로 바꿔야 합니다.

 

---

- 참고문헌

1. MySQL 성능 최적화 / 위키북스 / 배론 슈와츠 지음  

---