MYSQL EXPLAIN 사용법 (쿼리 성능 문제 확인)

데이터베이스에 데이터가 많이 쌓일수록 성능이 좋은 쿼리를 사용하는 게 중요합니다. 그렇지 않으면 데이터 출력이 늦어지고 그 과정에서 리소스를 과도하게 점유할 수 있습니다. 쿼리 성능이 좋지 않다고 판단되면 MYSQL에서는 EXPLAIN 문으로 분석해 볼 수 있습니다.

MYSQL EXPLAIN

구문

EXPLAIN 사용법은 진단하려는 쿼리문 앞에 EXPLAIN을 붙이면 끝입니다.

EXPLAIN 쿼리문

 

예를 들어 orders 테이블 전체를 select 하는 쿼리면 아래처럼 씁니다.

EXPLAIN SELECT * FROM orders;

 

 

explain을 사용하면 여러 결과값을 얻을 수 있습니다. 이 요소들을 보고 쿼리문 성능을 추정합니다. 결론적으로 이 결과값을 해석하는 것이 중요합니다.

 

컬럼명	설명
id	쿼리 내의 SELECT 문을 식별하는 번호입니다.
select_type	쿼리의 종류를 나타냅니다.
table	참조되는 테이블의 이름입니다.
partitions	쿼리가 참조하는 파티션의 명칭입니다.
type	조인 타입을 나타냅니다 (ALL, index, range, ref 등).
possible_keys	MySQL이 이 쿼리에 대해 사용할 수 있는 인덱스입니다.
key	실제로 사용된 인덱스입니다.
key_len	사용된 키의 길이입니다.
ref	인덱스를 사용할 때 어떤 컬럼 또는 상수가 사용되었는지 나타냅니다.
rows	MySQL이 쿼리를 처리하기 위해 스캔해야 할 것으로 예상되는 행 수입니다.
Extra	쿼리 실행에 대한 추가적인 정보입니다.

 

예시)

아래의 orders 테이블을 예시로 성능이 좋을 가능성이 높은 쿼리와 성능이 나쁠 가능성이 높은 쿼리를 하나씩 보겠습니다.

 

orders 테이블은 orders_id가 primary key이고 user_id와 order_date에 인덱스가 생성되어 있습니다.

 

 

 

성능이 좋고 나쁘고를 결정하는 건 물론 1차적으로 데이터베이스의 크기와 컴퓨팅 속도에 달렸습니다. 다만 explain을 분석하는 관점에서는 최대한 적은 수의 행을 스캔하고, 인덱스를 활용 쿼리를 사용하는 게 중요합니다.

 

1. 성능 좋은 쿼리

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

 

 

• type: ref 인덱스를 사용해서 특정 값에 대한 레코드를 빠르게 찾습니다. type 중 성능이 좋을 것으로 예상되는 값입니다.
• possible_keys: customer_id_index 쿼리에 사용할 수 있는 인덱스가 있습니다.
• key: customer_id_index 실제로 사용된 인덱스를 의미합니다. 인덱스를 사용하고 있습니다. 
• key_len: 5(바이트) 인덱스 길이가 적당합니다.
• rows: 2 지금은 샘플 데이터가 너무 작기 때문에 큰 의미는 없지만 적은 수의 행을 검사하고 있습니다.

 

2. 성능 나쁜 쿼리

성능이 나쁠 것으로 예상되는 쿼리는 대체로 인덱스를 활용하지 않고, 너무 많은 행을 읽습니다.

EXPLAIN SELECT * FROM orders ORDER BY order_date;

 

 

• type: ALL 전체 테이블을 스캔합니다. 좋지 못한 방식입니다.
• possible_keys: NULL 사용할 수 있는 인덱스가 없습니다.
• key: NULL 실제로 사용된 인덱스가 없습니다.
• key_len: NULL 인덱스를 사용하지 않기 때문에 길이도 값이 없습니다.
• rows: 3 현재는 샘플이 작아서 큰 의미는 없습니다. 
• Extra: Using filesort 결과를 정렬하기 위해 추가적인 파일 기반 정렬 작업이 필요함을 의미합니다.

 

샘플 데이터가 그리 현실적이진 않지만, 2가지 케이스에 대한 explain 결과값을 비교해 봤습니다. 실무에도 참고가 되시길 바랍니다.