EXPLAIN
MySQL에서는 EXPLAIN 키워드를 통해 실행계획을 뽑아 볼 수 있습니다.
아래와 같이 분석하고 싶은 쿼리 시작부에 EXPLAIN 키워드를 붙여줍니다.
EXPLAIN
SELECT id, email, password, name, address, create_date, role
FROM MEMBER_INFO
WHERE email = 'suyeon@naver.com';
쿼리 실행시 위와 같은 데이터를 확인할 수 있습니다.
각각의 컬럼을 살펴보도록 하겠습니다.
id : 실행 순서를 표시하는 숫자
select_type : SELECT 문의 유형을 출력
- SIMPLE : UNION이나 서브쿼리를 사용하지 않은 단순 SELECT문
partitions : 데이터가 저장된 논리적인 영역
특정 파티션에 선택적으로 접근하는 것이 SQL 성능 측면에서 유리하다.
type : 테이블의 데이터를 어떻게 찾을 것인지에 관한 정보
-
const : 조회되는 데이터가 단 1건일 때 출력되는 유형으로 성능상 매우 유리한 방식이다.
고유 인덱스(UNIQUE)나 기본 키(PK)를 사용하여 단 1건의 데이터에만 접근하면 되므로
속도나 리소스 사용 측면에서 굉장히 유리하다. -
ref : 데이터의 접근 범위가 2개 이상일 경우
데이터의 양이 많다면 접근해야 할 데이터 범위가 넓어져 성능 저하의 원인이 될 수 있다. -
all : 테이블 풀스캔이 된다.
문제점
예상했던 type은 const였으나 기대와 달리 ref를 출력하고 있었습니다.
원인을 파악하기 위해 MEMBER 테이블의 인덱스를 출력해보았습니다.
SHOW INDEX FROM MEMBER_INFO;
해당 서비스의 email은 id처럼 사용되고 있기 때문에 서비스 전체에서 고유한 값을 가지게 됩니다. 그러나 email 컬럼에 걸린 인덱스를 확인해본 결과 고유 인덱스 설정이 되어있지 않았습니다.
※ 고유 인덱스는 인덱스를 구성하는 열들의 데이터가 유일하다는 의미입니다.
따라서 아래와 같이 email 컬럼에 unique index 설정하였습니다.
실행 계획을 재 출력해본 결과 type이 const로 잘 출력되는 것을 확인해볼 수 있었습니다.
항상 인덱스를 고려하라
다른 예시를 하나 더 소개해드리겠습니다.
현재 진행중인 숙박업소 예약 서비스에서는 예약 가능 유무를 확인하기 위해 인벤토리 테이블을 조회합니다.
이 때 아무런 인덱스를 걸어주지 않은 경우 아래와 같이 테이블 풀스캔을 하는 것을 알 수 있습니다.
EXPLAIN
select * from room_inventory where roomtype_id = 1 and
inventory_date >= '2021-10-01' and inventory_date <= '2021-10-03';
CREATE UNIQUE INDEX idx_roomtype_date ON room_inventory(roomtype_id, inventory_date);이 때 위와 같이 Multi Column Index를 걸어줍니다.
다시 한번 실행계획을 뽑아보면 테이블 풀스캔이 아닌 Range 스캔을 진행하는 것을 확인할 수 있습니다.
쿼리 수행시 예상검색 행수가 전체 데이터 수인 20건에서 3건으로 줄어든 것을 확인할 수 있습니다.
