MySQL 쿼리 속도 향상. Explain으로 실행 계획 보기. Index 설정

문제

테스트 환경에서 5000건 조회 시, 2분 22초 걸렸던 조회 쿼리가, Real 환경에선 18분 4초가 소요되었다.

쿼리 구성은 대략적으로 아래와 같았다.

SELECT
	customer.account_name,
	order.account_id,
	order_item.item_id
	order_item.item_name
	order_item.net_amount
FROM order_item
INNER JOIN order ON order.id = order_item.order_id
INNER JOIN customer ON customer.account_id = order.account_id
WHERE customer.account_id = 'accountId'
	AND order.country = 'KOREA'
	AND order.month = '202307'
ORDER BY order_item.item_id;

원인

Database에서 인덱스 확인

테스트 환경의 Database와 Real 환경의 Database에 설정된 인덱스를 비교해보았다.

아래 쿼리를 입력하면 테이블에 설정된 인덱스를 조회할 수 있다.

SHOW INDEX FROM 테이블명;
SHOW INDEX FROM customer;

조회하면 결과가 아래와 같이 나온다. 참고로, Cardinality는 Unique한 값의 개수이다. 즉, Cardinality가 낮을수록 중복된 값이 많기 때문에 Index로 적절하지 않다.

customer, order, order_item에 설정된 인덱스가 테스트 환경과 Real 환경에서 모두 동일했다.

테이블 별 데이터양(Row 수) 확인

테스트 환경과 Reql 환경에서 테이블 별 Row수를 확인했다.

cusotmer 개수만 비교해봤을 때, 26.8배 차이가 났다.

테이블명	테스트 DB	Real DB
customer	49,179	1,322,520
order	16,688	373,699
order_item	8,456	231,365

 

테스트 환경과 Real 환경에 설정된 인덱스는 동일하고, 데이터 양이 차이가 많이남으로 조회 쿼리 자체의 성능 문제라 판단했다.

실행 계획 확인

쿼리를 튜닝하기 위해 실행계획을 확인하였다.

실행계획은 SQL에서 쿼리 옵티마이저가 쿼리를 실행하기 위해 선택한 최적의 실행 방법이다. 실행 계획을 분석하여 조인 순서나 인덱스 설정으로 쿼리를 최적화할 수 있다.

 

MYSQL에서는 EXPLAIN을 실행문 앞에 붙여서, 실행 계획을 확인할 수 있다.

EXPLAIN SELECT
	customer.account_name,
	order.account_id,
	order_item.item_id
	order_item.item_name
	order_item.net_amount
FROM order_item
INNER JOIN order ON order.id = order_item.order_id
INNER JOIN customer ON customer.account_id = order.account_id
WHERE customer.account_id = 'accountId'
	AND order.country = 'KOREA'
	AND order.month = '202307'
ORDER BY order_item.item_id;

 

실행 결과는 아래와 같이 나온다.

각 컬럼에 대한 정보는 아래 블로그글 참고.

https://wildeveloperetrain.tistory.com/203

 

order 테이블에서 사용되는 index key가 IXFK_order_country로, country컬럼을 인덱스로 사용하고 있었다.

country 컬럼을 사용했을 때 조회되는 rows 수가 184,497개로 Index로 사용되기 좋지 않았다.

즉, 적절한 index가 없기 때문에, country 인덱스를 사용하고 있었던 것이다.

해결

order 테이블이 customer 테이블과 조인할 때, account_id 컬럼을 사용하므로, order테이블에서 account_id를 인덱스로 설정해주기로 했다.

CREATE INDEX IXFK_order_account_id ON order (account_id);

 

다시 실행계획을 조회하면 아래와 같이 변경되어 나온다. order 테이블에서 조회되는 rows수가 줄어든 것을 확인할 수 있다.

참고 자료

https://wildeveloperetrain.tistory.com/203

MySQL EXPLAIN 실행 계획을 통한 쿼리 최적화