10. 실행 계획 (Execution Plan)

10장: 실행 계획 (Execution Plan)

실행 계획을 이해하는 것은 개발자와 DBA의 필수 역량이다. 쿼리 튜닝은 곧 옵티마이저가 수립한 실행 계획의 합리성을 판단하고, 비합리적일 경우 이를 최적의 경로로 개선하도록 유도하는 작업이기 때문이다. 만약 옵티마이저의 통계 정보가 부정확하여 잘못된 실행 계획이 수립되면, 0.1초 만에 완료될 수 있는 쿼리가 1시간 이상 소요되는 치명적인 성능 저하를 초래할 수 있다

1. 쿼리 실행 계획의 토대: 통계 정보 (Statistics)

MySQL 서버는 비용 기반 최적화(Cost-based optimizer, CBO) 방식을 채택하고 있으며, 이 CBO가 최적의 실행 경로를 찾기 위해 가장 중요하게 참조하는 것이 바로 통계 정보이다.

1.1 히스토그램 (Histogram)

MySQL 5.7 버전까지는 인덱스된 컬럼에 대해서만 통계 정보를 수집했기 때문에, 인덱스가 없는 컬럼의 데이터 분포를 알 수 없어 실행 계획의 정확도가 떨어지는 경우가 잦았다. 이를 개선하기 위해 MySQL 8.0 버전부터는 히스토그램 기능이 도입되었다.

히스토그램은 컬럼의 데이터 분포도를 파악하는 도구로, 인덱스되지 않은 컬럼에 대해 주로 사용되었다. DBA는 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 수동으로 실행하여 이 정보를 수집하고 관리해야 한다. 이 통계 정보가 있다면, 옵티마이저는 다중 테이블 조인 시 어떤 테이블을 먼저 읽어야 조인 횟수를 최소화할 수 있을지 훨씬 더 정확하게 판단할 수 있다.

1.2 코스트 모델 (Cost Model)

쿼리 비용은 디스크 읽기, 메모리 페이지 읽기, 인덱스 키 비교 등 단위 작업들의 비용을 모두 합산하여 계산된다. 과거 버전에서는 이러한 단위 작업의 비용이 소스 코드에 고정된 상수값으로 정의되어 있었기 때문에, 서버가 사용하는 하드웨어 특성을 반영하지 못해 최적화에 방해가 되었다.

MySQL 5.7부터는 관리자가 이 단위 작업들의 비용을 직접 조정할 수 있는 코스트 모델이 도입되었다. 이 비용 정보는 mysql.server_cost 및 mysql.engine_cost 테이블에 저장되며, 예를 들어 디스크 I/O 비용을 높게 설정하거나 메모리 I/O 비용을 낮게 설정함으로써, 옵티마이저가 특정 하드웨어 환경에 최적화된 실행 계획을 수립하도록 유도할 수 있다.

2. 실무 관점의 실행 계획 분석 (EXPLAIN)

EXPLAIN 명령의 결과는 쿼리 성능 문제를 진단하는 핵심 도구이다. 각 컬럼은 쿼리의 실행 경로, 데이터 접근 방식, 그리고 MySQL 내부 최적화가 어떻게 작동했는지에 대한 심층적인 정보를 제공한다.

2.1 type 컬럼 (접근 방법, Access type)의 중요성

type 컬럼은 테이블에서 레코드를 읽는 방식을 나타내며, 쿼리 성능의 효율성을 평가하는 데 가장 중요한 지표이다.

1. 최상위 효율성 (const, eq_ref, ref):
   • const는 쿼리가 프라이머리 키(PK)나 유니크 키 전체를 동등 조건(=)으로 검색하여 반드시 1건의 레코드만 반환하는 경우에 사용되며, 최고의 성능을 보장한다.
   • eq_ref는 조인에서 두 번째 테이블이 첫 번째 테이블의 PK나 유니크 키를 동등 조건으로 참조하며 반드시 1건만 반환될 때 나타낸다.
   • ref는 PK나 유니크 키가 아닌 인덱스를 동등 조건으로 검색하거나, PK/유니크 키의 일부 컬럼만 사용하는 경우에 나타나며, 여러 건의 레코드가 반환될 수 있지만 여전히 매우 효율적인 접근 방식이다.
2. 효율적 범위 검색 (range):
   • range는 인덱스를 활용하여 BETWEEN, IN, LIKE 'prefix%' 등 특정 범위의 레코드를 스캔할 때 사용된다. 대부분의 온라인 서비스 쿼리가 이 범위를 사용하는 경우가 많다.
3. 비효율적 접근 (index 및 ALL):
   • index는 인덱스 풀 스캔(Full Index Scan)을 의미한다. 인덱스의 처음부터 끝까지 모든 리프 노드를 읽는 방식이다. 이는 테이블 풀 스캔보다는 빠를 수 있으나, 필요한 부분만 선택적으로 읽는 range 접근보다는 비효율적이다.
   • ALL은 풀 테이블 스캔(Full Table Scan)을 의미하는 가장 비효율적인 접근 방법이다. 인덱스를 사용할 수 없을 때 선택되며, 대량의 데이터 분석 작업이 아닌 이상, ALL이 표시되었다면 "심각한 튜닝"이 필요함을 의미한다.

2.2 rows 및 filtered 컬럼 (예측 정확도 및 필터링 효율)

rows와 filtered 컬럼은 옵티마이저가 얼마나 정확하게 데이터 접근을 예측했는지, 그리고 비효율적인 필터링 작업이 발생하는지 판단하는 실무적으로 중요한 지표이다.

• rows: 옵티마이저가 인덱스나 테이블에서 조건에 일치한다고 예측한 레코드의 총 갯수이다. 이 숫자는 옵티마이저가 비용 계산의 주요 근거로 삼는다.
• filtered: rows로 읽은 레코드 중에서 MySQL 엔진 레이어의 추가적인 필터링 조건(체크 조건)을 통과하여 최종적으로 남을 레코드의 비율(%)을 예측한 값이다.

만약 rows 값이 크지만 filtered 값이 현저히 낮다면 (예: 50% 미만), 이는 인덱스가 불필요하게 넓은 범위를 커버하도록 설계되었거나 사용되었다는 의미이다. 스토리지 엔진이 많은 레코드를 읽어 왔지만, MySQL 엔진이 이들을 버리는 비효율적인 필터링 작업을 수행했다는 결정적인 증거이다.

2.3 Extra 컬럼 (최적화 상세 진단)

Extra 컬럼은 쿼리 처리에 사용된 내부 최적화 알고리즘을 나타내며, 성능 개선을 위한 핵심 힌트를 담고 있다.

1. Using index (커버링 인덱스): 가장 중요한 성능 지표 중 하나이다. 쿼리가 필요한 모든 컬럼을 인덱스 자체에서만 얻어내어 데이터 파일에 접근하는 랜덤 I/O를 완벽하게 회피했다는 의미이다. 이를 커버링 인덱스라고 하며, 쿼리 속도를 수십 배 빠르게 만들 수 있는 핵심 튜닝 목표가 된다.
2. Using filesort (별도 정렬): 성능 저하의 주요 원인을 나타낸다. ORDER BY 절이 기존 인덱스를 사용하지 못하고, 메모리 내의 소트 버퍼(Sort buffer)나 심지어 디스크를 사용하는 Filesort라는 별도의 정렬 과정을 거쳤음을 의미한다. 정렬 대상 레코드 건수가 많을 경우 디스크 I/O를 유발하여 성능이 급격히 느려지므로, 가능한 한 인덱스를 활용하여 이 메시지를 없애야 한다.
3. Using temporary (내부 임시 테이블): 심각한 성능 저하 가능성을 시사한다. 주로 인덱스를 활용하지 못한 GROUP BY 또는 DISTINCT 작업이 발생했을 때, MySQL이 결과를 저장하기 위해 내부 임시 테이블을 생성했다는 의미이다. 이 임시 테이블이 메모리 크기를 초과하면 자동으로 디스크로 전환되어 성능이 크게 떨어진다. 이 메시지는 종종 Using filesort와 함께 나타나며, 쿼리 튜닝이 시급함을 나타낸다.
4. Using where (MySQL 엔진 필터링): 스토리지 엔진이 읽어온 레코드에 대해 MySQL 엔진 레이어에서 추가적인 필터링 조건(체크 조건)을 적용했음을 의미한다. 이 메시지 자체는 일반적이지만, filtered 컬럼의 값이 낮을 경우 (즉, 읽어온 레코드 대비 버려진 레코드가 많을 경우) 비효율적인 필터링이 발생했음을 확증하게 된다.
5. Using index condition (ICP): 인덱스 컨디션 푸시 다운(Index condition pushdown) 최적화가 적용되었음을 나타낸다. 이 최적화는 인덱스 레인지 스캔에 직접 사용될 수는 없지만, 인덱스에 포함된 필터링 조건(체크 조건)을 MySQL 엔진이 스토리지 엔진으로 미리 밀어 넣어서 (Push down), 불필요한 레코드를 MySQL 엔진으로 전송하는 것을 줄여 효율을 높인다.

 

 

실제 예

id|select_type|table|partitions|type|possible_keys  |key            |key_len|ref          |rows|filtered|Extra                      |
--+-----------+-----+----------+----+---------------+---------------+-------+-------------+----+--------+---------------------------+
 1|SIMPLE     |bcf  |          |ALL |PRIMARY        |               |       |             |  57|    50.0|Using where; Using filesort|
 1|SIMPLE     |bcr  |          |ref |fk_rules_format|fk_rules_format|5      |utrang.bcf.id|   1|   100.0|                           |

{
"EXPLAIN select ": [
	{
		"id" : 1,
		"select_type" : "SIMPLE",
		"table" : "bcf",
		"partitions" : null,
		"type" : "ALL",
		"possible_keys" : "PRIMARY",
		"key" : null,
		"key_len" : null,
		"ref" : null,
		"rows" : 57,
		"filtered" : 50.0,
		"Extra" : "Using where; Using filesort"
	},
	{
		"id" : 1,
		"select_type" : "SIMPLE",
		"table" : "bcr",
		"partitions" : null,
		"type" : "ref",
		"possible_keys" : "fk_rules_format",
		"key" : "fk_rules_format",
		"key_len" : "5",
		"ref" : "utrang.bcf.id",
		"rows" : 1,
		"filtered" : 100.0,
		"Extra" : null
	}
]}

 

항목	값	의미
id	1	한 개의 SELECT 문이라는 뜻
select_type	SIMPLE	서브쿼리 없는 단순 SELECT
table	bcf, bcr	두 개 테이블 JOIN
type	ALL(bcf), ref(bcr)	bcf는 풀스캔, bcr은 인덱스 조인
possible_keys	PRIMARY, fk_rules_format	각각 쓸 수 있는 인덱스 목록
key	(없음), fk_rules_format	실제로 사용된 인덱스
rows	51(bcf), 1(bcr)	MySQL이 예상하는 읽을 행 수
Extra	Using where; Using filesort(bcf)	where 조건, 정렬(ORDER BY)을 메모리에서 수행