[MySQL 쿼리 최적화] 실행 계획을 분석해보자

현재 프로젝트에 남은 인덱스는 두 개이지만, DB 테이블에 남아있는 인덱스는 실험하면서 걸어둔 인덱스가 모두 남아있었다.

 

 

아래 쿼리는 현재 댓글 작성이 열려 있어서 댓글 작성이 진행되고 있는 게시글의 최신순 10건 조회 쿼리다.

이 쿼리는 지금 인덱스를 잘 타고 있을까?

 

 

 

 

• 항상 주요 지표인 select_type, type, Extra를 잘 살펴보자. select_type은 SIMPLE type은 ref Extra에 Using where

 

해당 쿼리의 실행계획은 MySQL 서버에서 체크 조건(Using where)을 확인해주고 있긴 하지만 아주 효율적인 것을 알 수 있다. 어

어? 현재 인덱스 추가를 해 두지 않은 인덱스를 선택하고 있다.

 

내가 걸어놓은 Posts 테이블의 인덱스는 idx_is_completed_created_at과 idx_is_completed_id_created_at 두 개이다.

혹시나 예전에 인덱스 설계를 하면서 작성하고 코드에서만 삭제 했던 인덱스가 DB에서는 남아있을 수 있다고 생각해서 Posts의 인덱스 목록을 살펴보았다.

 

 

아래 명령어를 입력하면 해당 테이블에 존재하는 모든 인덱스를 조회할 수 있다.

 

 

사용하지 않는 인덱스는 모두 삭제해주자. 실무적으로는 invisible 설정을 하면 옵티마이저가 해당 인덱스를 사용하지 않는다. 사용하지 않는 것을 확인하면 삭제하도록 하자.

DROP INDEX 인덱스명 ON 테이블명;

 

 

 

 

 

 

테스트 겸 인덱스가 아닌 체크 조건으로 걸려있는 is_deleted를 빼고 테스트를 해봤는데,

MySQL 서버가 따로 체크조건을 필터링하지 않으니

책에서 읽은 것 처럼 Extra에 Using where이 바로 사라진다.

 

이런 곳에서 책 읽은 보람을 찾는 것 같다.

 

 

 

 

type 컬럼의 ref 접근 방법은 무려 4등이다. 이미 충분히 빠르다고 볼 수 있지만,

왜 ref 타입으로 접근하고 있는지 알아보고 혹여 인덱스가 잘못 걸려 있다면 개선해 나가보자.

 

 

<참고> 인덱스의 종류와 관계없이 동등(equal) 조건으로 검색할 때는 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다. 하지만 동등한 조건으로만 비교되므로 매우 빠른 레코드 조회 방법의 하나다.

 

 

 

ref 접근 방법의 설명을 읽어보고 쿼리문을 다시 읽어보면

explain select * from posts p where p.is_completed = false and p.is_deleted = false order by p.created_at desc limit 10;

현재 쿼리도 충분히 좋은 상태라는 것을 알 수 있다.

 

이유는 인덱스를 걸 때, 카디널리티가 높은 컬럼에 인덱스를 걸어야 효율이 좋은데 이 경우 뽑고 싶은 데이터는 ‘현재 완료 상태가 아닌데 소프트 딜리트 상태가 아니면서 생성일자가 최신인 데이터’이므로 소프트 딜리트 여부도 복합인덱스 컬럼으로 추가해주면 더 적합한 인덱스 설계라고 생각된다.

 

단, 고려할 점은 인덱스 키 길이가 그만큼 늘어난다는 점인데, 삭제 여부는 boolean 타입이므로 충분히 감안할 수 있다고 생각한다

따라서, 가장 많이 필터링 할 수 있는 것을 앞에서부터 (is_completed, is_deleted, created_at) 순서로 인덱스를 설계했다. 이제 성능을 비교해보자.

 

 

인덱스를 걸기 전은 아래와 같다.

 

 

 

 

인덱스를 건 후

두 인덱스 중 새로 만든 인덱스를 옵티마이저는 더 낫다고 선택했다.

 

 

 

그럼 성능은..?

 

 

 

 

사실, 성능 자체는 비슷하다. 이럴 경우 index의 key_length 를 is_deleted를 선택하지 않으면 더 줄일 수 있으니 둘 다 괜찮은 방법이라고 생각한다.