[MySQL] EXPLAIN 실행계획과 파티셔닝 정리

1. 문서 제목

MySQL EXPLAIN 실행계획과 파티셔닝 정리

2. 기술 개요 요약

EXPLAIN은 MySQL 옵티마이저가 SQL을 어떤 방식으로 실행할 계획인지 보여주는 진단 도구다. 실제 성능 문제를 볼 때는 type, key, rows, filtered, Extra, partitions 같은 컬럼을 함께 확인해야 한다. 특히 type = ALL, 과도하게 큰 rows, Using filesort, Using temporary는 튜닝 후보가 될 수 있다.

파티셔닝은 하나의 큰 테이블을 같은 MySQL 서버 안에서 여러 물리 파티션으로 나누는 기능이다. 핵심 효과는 인덱스처럼 “빠르게 찾는 것”이라기보다, 조건과 관련 없는 파티션을 제외하는 partition pruning을 통해 “처음부터 볼 데이터 덩어리를 줄이는 것”이다. 다만 파티션 키가 쿼리 조건에 없으면 pruning 효과가 약하고, MySQL에서는 파티션 표현식에 사용된 모든 컬럼이 모든 unique key와 primary key에 포함되어야 하는 제약도 고려해야 한다.

---

3. 핵심 기능/개념 정리

3-1. EXPLAIN 주요 컬럼

컬럼	설명	실무 포인트
id	SELECT 식별자	복잡한 쿼리에서 실행 단위 구분
select_type	SELECT 유형	SIMPLE, PRIMARY, SUBQUERY, DERIVED 등 확인
table	접근하는 테이블	어떤 테이블부터 읽는지 확인
partitions	사용되는 파티션	파티셔닝 테이블에서 pruning 여부 확인
type	테이블 접근 방식	ALL이면 풀 테이블 스캔 가능성
possible_keys	후보 인덱스	옵티마이저가 고려한 인덱스
key	실제 선택된 인덱스	기대 인덱스와 다르면 통계/조건/인덱스 설계 점검
key_len	사용된 인덱스 길이	복합 인덱스가 어디까지 활용됐는지 추정
ref	인덱스와 비교된 값 또는 컬럼	조인 조건 확인
rows	읽을 것으로 추정한 row 수	통계 기반 추정치이며 실제 처리 row와 다를 수 있음
filtered	조건 통과 예상 비율	rows * filtered / 100으로 남는 row 수 추정
Extra	추가 실행 정보	Using index, Using filesort, Using temporary 등 확인

3-2. type 접근 방식 해석

type	의미	해석
system, const	한 번에 1행 수준으로 확정	매우 좋음
eq_ref	조인에서 unique/PK로 1행 매칭	좋음
ref	non-unique index로 여러 행 매칭	일반적으로 양호
range	인덱스 범위 스캔	범위가 넓으면 rows 확인 필요
index	인덱스 전체 스캔	테이블 전체보다 작을 수 있지만 여전히 전체 인덱스 스캔
ALL	풀 테이블 스캔	대용량 테이블이면 우선 점검 대상

3-3. Extra에서 자주 보는 신호

Extra	의미	실무 포인트
Using index	인덱스만으로 필요한 컬럼을 처리	covering index 효과. 좋은 신호일 때가 많음
Using where	읽은 row에 WHERE 조건 적용	흔한 정보. 단독으로 나쁜 신호는 아님
Using filesort	인덱스 순서로 정렬하지 못해 별도 정렬	ORDER BY와 인덱스 순서 점검
Using temporary	임시 테이블 사용	GROUP BY, DISTINCT, 정렬/집계 최적화 검토
Using index condition	Index Condition Pushdown 사용	스토리지 엔진 단계에서 조건 일부를 처리

3-4. 파티셔닝 핵심 개념

항목	설명	실무 포인트
Partitioning	하나의 테이블을 여러 물리 파티션으로 나눔	같은 DB 서버 안의 물리 분할
Sharding	데이터를 여러 DB 서버로 분산	서버 단위 수평 분산
Partition Key	row가 어느 파티션에 들어갈지 결정하는 기준	쿼리 조건에 자주 등장해야 pruning 가능
Partition Pruning	조건과 무관한 파티션을 실행 계획에서 제외	파티셔닝의 핵심 성능 효과
DROP PARTITION	특정 파티션을 통째로 제거	오래된 로그/이력 데이터 삭제에 유리

3-5. 파티셔닝 종류

방식	설명	예시
RANGE	값의 범위로 분할	날짜/연도별 주문 테이블
LIST	특정 값 목록으로 분할	지역 코드, 상태값
HASH	해시 함수 결과로 분할	균등 분산 목적
KEY	MySQL 내부 해시 함수를 사용	명시적 해시 함수 대신 컬럼 기반 분산

---

4. 사용 예시 및 코드 스니펫

4-1. EXPLAIN 기본 사용

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 123
  AND created_at >= '2026-01-01';

점검 순서:

1. type이 ALL인지 확인한다.
2. key가 기대한 인덱스인지 확인한다.
3. rows가 테이블 크기 대비 과도하게 큰지 확인한다.
4. Extra에 Using filesort, Using temporary가 있는지 확인한다.
5. 파티셔닝 테이블이면 partitions 컬럼에서 실제 읽는 파티션을 확인한다.

4-2. 복합 인덱스와 key_len

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 123
  AND created_at >= '2026-01-01';

key = idx_orders_user_created로 표시되더라도 복합 인덱스가 모든 컬럼을 효과적으로 사용했다고 단정하면 안 된다. key_len, 조건 형태, rows를 함께 봐야 한다. 특히 복합 인덱스는 leftmost prefix와 등호/범위 조건 순서의 영향을 받는다.

4-3. RANGE 파티셔닝 예시

CREATE TABLE orders (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  created_at DATE NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

파티션 키인 created_at이 조건에 포함되면 관련 파티션만 읽을 수 있다.

EXPLAIN
SELECT *
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01';

EXPLAIN의 partitions 컬럼에 p2026만 표시된다면 partition pruning이 적용된 것이다.

4-4. 오래된 데이터 삭제

ALTER TABLE orders DROP PARTITION p2024;

대량의 오래된 데이터를 DELETE WHERE created_at < ...로 지우면 row 단위 삭제 비용과 undo/redo 부담이 커질 수 있다. 날짜 기준 파티셔닝이 잘 되어 있다면 오래된 파티션을 통째로 제거하는 방식이 더 효율적일 수 있다.

---

5. 실제 사용 시 주의점 / Best Practice

5-1. EXPLAIN은 계획이지 실측 결과가 아니다

rows, filtered는 통계 기반 추정치다. 실제 실행 시간, 실제 읽은 row 수와 다를 수 있으므로 느린 쿼리는 EXPLAIN ANALYZE나 실제 모니터링 지표와 함께 봐야 한다.

5-2. possible_keys에 있어도 실제로 쓰인다는 뜻은 아니다

possible_keys는 후보이고 key가 실제 선택된 인덱스다. 기대한 인덱스가 선택되지 않았다면 조건 선택도, 통계, 복합 인덱스 순서, 컬럼 타입 불일치, 함수 사용 여부를 확인한다.

5-3. Using filesort와 Using temporary는 맥락으로 판단한다

둘이 항상 장애 원인은 아니지만, 대용량 정렬·집계에서 자주 병목이 된다. ORDER BY, GROUP BY, DISTINCT 컬럼과 인덱스 순서가 맞는지 확인한다.

5-4. 파티셔닝은 인덱스 대체재가 아니다

파티셔닝은 읽을 파티션 수를 줄이는 기능이고, 인덱스는 파티션 내부에서 row를 빠르게 찾는 기능이다. 보통 둘은 대체 관계가 아니라 보완 관계다.

5-5. 파티션 키가 WHERE 조건에 없으면 효과가 약하다

파티션 키가 쿼리 조건에 포함되지 않으면 모든 파티션을 읽어야 할 수 있다. 이 경우 파티션 관리 비용만 늘고 성능 이점은 작을 수 있다.

5-6. MySQL 파티셔닝은 unique key 제약을 고려해야 한다

MySQL에서는 파티션 표현식에 사용된 모든 컬럼이 테이블의 모든 unique key와 primary key에 포함되어야 한다. 기존 테이블에 파티셔닝을 나중에 적용할 때 PK/UK 설계를 바꿔야 할 수 있다.

5-7. 기존 대용량 테이블 파티셔닝은 마이그레이션 작업이다

ALTER TABLE ... PARTITION BY ...는 단순 메타데이터 변경으로 끝나지 않을 수 있다. 대용량 테이블에서는 rebuild, 락, 복제 지연, 디스크 사용량 증가를 고려하고 온라인 DDL 도구나 신규 테이블 이관 전략을 검토한다.

5-8. 파티셔닝 제약은 MySQL 제품 제약으로 말한다

파티션 키와 unique key 제약은 MySQL 파티셔닝의 중요한 제약이다. 모든 RDBMS가 같은 방식으로 제한되는 것은 아니므로, 면접에서는 "MySQL 기준"이라고 범위를 명확히 잡는 편이 안전하다.

5-9. 면접 답변으로 압축하기

EXPLAIN은 MySQL 옵티마이저가 쿼리를 어떻게 실행할지 보여주는 실행계획 도구입니다. 먼저 type으로 접근 방식을 보고, key로 실제 사용 인덱스를 확인하며, rows와 filtered로 예상 스캔 규모를 봅니다. Extra의 Using filesort, Using temporary는 정렬·집계 병목 후보이고, Using index는 covering index로 인덱스만 보고 처리할 수 있다는 뜻입니다. 파티셔닝은 큰 테이블을 물리 파티션으로 나눠 partition pruning을 통해 불필요한 파티션을 스캔하지 않게 하는 기능입니다. 다만 파티션 키가 조건에 없으면 효과가 약하고, MySQL에서는 파티션 키가 모든 PK/Unique Key에 포함되어야 하는 제약을 고려해야 합니다.

---

검증 메모

• MySQL 공식 문서 기준으로 EXPLAIN 주요 컬럼, partition pruning, partition key와 unique key 제약을 대조했다.
• rows, filtered는 실측값이 아니라 추정치라는 문장을 유지했다.
• 파티셔닝 제약은 MySQL 제품 제약이므로 다른 DBMS 일반론으로 확장하지 않도록 보완했다.

6. 참고자료 / 공식 문서 출처

• MySQL Reference Manual: EXPLAIN Output Format
  https://dev.mysql.com/doc/en/explain-output.html
• MySQL Reference Manual: Partitioning Overview
  https://dev.mysql.com/doc/refman/8.4/en/partitioning-overview.html
• MySQL Reference Manual: Partition Pruning
  https://dev.mysql.com/doc/refman/8.2/en/partitioning-pruning.html
• MySQL Reference Manual: Restrictions and Limitations on Partitioning
  https://dev.mysql.com/doc/refman/8.3/en/partitioning-limitations.html
• MySQL Reference Manual: Partitioning Keys, Primary Keys, and Unique Keys
  https://dev.mysql.com/doc/refman/8.1/en/partitioning-limitations-partitioning-keys-unique-keys.html