[MySQL 튜닝] 튜닝 사전 지식

코린이·2025년 9월 6일
조인 알고리즘NL JOINnested loop join드라이빙드리븐실행계획튜닝힌트

MySQL 쿼리 성능 UP

목록 보기
3/4

✅ 드라이빙 테이블 VS 드리븐 테이블

테이블 조인(JOIN) 시 가장 먼저 접근되는 테이블을 드라이빙(Driving) 테이블, 이후에 접근하는 테이블을 드리븐(Driven) 테이블이라 부른다.

📌 드라이빙 테이블

드라이빙 테이블은 데이터의 선택 범위를 좁혀 주는 테이블을 의미한다.

즉, 조건절(WHERE) 등을 통해 크기를 최소화할 수 있는 테이블이 우선적으로 드라이빙 테이블로 선택된다.

※ Why 드라이빙 테이블?
드라이빙 테이블이 작을수록 드리븐 테이블에 대한 접근 횟수가 줄어든다.

따라서 조인 성능을 높이기 위해 DB 엔진은 더 경량화된 테이블을 드라이빙 테이블로 우선 선택한다.

📌 드리븐 테이블

드리븐 테이블은 인덱스가 반드시(거의 필수적으로) 필요하다.

드라이빙 테이블의 ROW데이터로 드리븐 테이블을 탐색하기 때문에, 인덱스가 없다면 Full Scan이 발생하여 성능이 급격히 저하된다.

📌 드라이빙 테이블 선택 조건

  • 양쪽 테이블 모두 인덱스 존재 -> 더 경량화된 테이블이 드라이빙 테이블로 지정

  • 한쪽 테이블만 인덱스 존재 -> 인덱스가 없는 테이블이 드라이빙 테이블로 지정

  • 양쪽 모두 인덱스 없음 -> 드라이빙·드리븐 구분 의미가 없으며, Full Scan으로 실행됨

📌 요약

  • 조인(JOIN) 시 드라이빙 테이블과 드리븐 테이블로 나뉜다.
  • 드라이빙 테이블은 작을수록 성능에 유리하다.
  • 드리븐 테이블에는 인덱스가 필수적이다.
  • 어떤 테이블이 드라이빙/드리븐으로 선택될지는 옵티마이저의 실행 계획에 따라 결정한다.

✅ 조인 알고리즘

MySQL에서 사용되는 조인(JOIN) 방식은 크게 Nested Loop(NL) 과 Hash 방식이 있다.

※ 참고
조인(JOIN) 쿼리문의 70~80%는 Nested Loop(NL) 방식을 사용한다.

📌 Nested Loop Join

Nested Loop Join은 가장 일반적인 조인(JOIN) 방식이다.

  • 드라이빙 테이블의 데이터를 기반으로 드리븐 테이블의 데이터를 찾는 방식을 Nested Loop Join이라 부른다.

Nested Loop Join 과정에서 드리븐 테이블 탐색은 랜덤 I/O 방식으로 이루어지며, 이는 Nested Loop Join의 가장 큰 특징이다.

또한, 드리븐 테이블에 인덱스가 반드시 필요하다. 인덱스가 없다면 Full Scan이 발생하여 성능이 크게 저하된다.

▶︎ Nested Loop Join 성능 향상

Nested Loop Join의 성능을 높이기 위해서는 아래와 같은 검토가 필요하다.

  1. 드라이빙 테이블 경량화
  2. 드리븐 테이블 인덱스 구성 설계

📌 Hash Join

Hash Join은 스토리지가 아닌 메모리에 해시 테이블을 생성하여 조인(JOIN) 하는 방식이다.

드라이빙 테이블을 기반으로 메모리에 해시 테이블을 생성한다. 이때 조인 키(해시 키)를 기준으로 해시 구조를 만든다.

조인 시 드리븐 테이블의 키값을 해시 키로 만들어 메모리에 있는 해시 테이블과 비교하여 데이터를 반환한다.

※ 참고
해시 조인은 해시 키값을 기반으로 동작하기 때문에 랜덤 I/O가 발생하지 않는다.

✅ 힌트

힌트는 옵티마이저가 실행 계획을 선택할 때 참고 용도로 추가 정보를 주는 기술이다.

※ 참고
DB의 통계 정보에 문제가 있는 경우 옵티마이저는 잘못된 선택을 할 가능성이 있다.

이때 쿼리 작성자가 힌트를 사용하여 옵티마이저에게 보다 효율적인 실행 계획을 만들 수 있도록 유도할 수 있다.

힌트를 줘도 물리적으로 불가능하면 옵티마이저가 무시할 수 있습니다.
(예: 조건이 없어 인덱스 탐색이 불가 → 최대 ‘풀 인덱스 스캔’)

📌 STRAIGHT_JOIN

FROM 절에 나열된 테이블 수서로 조인을 유도하는 힌트다.

테이블의 조인 순서를 A -> B 로 유도

SELECT /*! STRAIGHT_JOIN */ 
	A.name
    , A.age
FROM A JOIN B
	ON A.id = B.id
;

📌 USE INDEX

특정 인덱스를 사용하도록 유도하는 힌트다.

테이블 조인 시 PRIMARY 사용 유도

SELECT 
	A.name
    , A.age
FROM A /*! USE INDEX (PRIMARY) */
	JOIN B ON A.id = B.id 
;

📌 FORCE INDEX

특정 인덱스를 사용하도록 강하게 유도하는 힌트다.

테이블 조인 시 PRIMARY 사용 강제 유도

SELECT
	A.name
    , A.age
FROM A
	JOIN B /*! USE INDEX (PRIMARY) */ ON A.id = B.id 
;

📌 IGNORE INDEX

특정 인덱스를 사용 못 하게 유도하는 힌트다.

테이블 조인 시 PRIMARY 사용 금지 유도

SELECT
	A.name
    , A.age
FROM A
	JOIN B /*! IGNORE INDEX (PRIMARY) */ ON A.id = B.id 
;

✅ 실행계획

EXPLAIN 을 사용하여 옵티마이저가 선택한 쿼리 실행계획을 확인할 수 있다.

실행계획은 아래 사진처럼 다양한 항목으로 쿼리문의 실행 방식을 예측할 수 있다.

※ 참고
실행계획은 다양한 항목으로 출력된다. 그 중에서 select_type,type, key, Extra 항목은 쿼리 튜닝에 있어 중요한 지표로 사용된다.

📌 id

id는 최소한의 단위 SELECT문 마다 부여되는 식별자다.

실행 계획에서 id는 같은 값으로 중복되어 보일 수 있는데, 이는 같은 SELECT 블록 안의 테이블이라는 뜻이다.

같은 id별로 출력되는 데이터 중 가장 먼저(위에) 나오는 ROW 데이터가 드라이빙 테이블을 의미하고, 그 다음 나오는 ROW 데이터가 드리븐 테이블을 의미한다.

📌 select_type ⭐️

쿼리문의 SELECT 유형에 따라 다양한 항목으로 분리된다.

  • simple : 가장 단순한 SELECT문 쿼리문
  • primary : 서브쿼리 또는 UNION 구문이 포함된 전체 쿼리문에서 최초 접근한 테이블
  • subquery : 독립적으로 움직이는 하나의 서브쿼리 (다른 테이블 및 쿼리문에 종속X)
  • derived : 메모리 또는 디스크에 임시로 생성된 테이블 (서브 쿼리 등)
  • union : UNION 또는 UNION ALL이 포함된 구문에서 첫 번째 이후 접근된 테이블 (primary 값 참고)
  • union result : UNION이 포함된 구문에서만 보이는 값으로 중복 제거를 위해 메모리나 디스크에 임시로 생성된 테이블 (오직 UNION문 에서만 확인 가능)
  • dependent subquery : UNION 또는 UNION ALL이 포함된 구문에서 메인 테이블의 영향을 받는 첫 번째 테이블
  • dependent union : UNION 또는 UNION ALL이 포함된 구문에서 메인 테이블의 영향을 받는 첫 번째 이후 테이블
  • materialized : 조인 등의 가공 작업을 위해 임시로 만들어진 테이블

📌 table

SELECT문에 사용된 테이블 이름을 의미한다. alias가 있다면 테이블이름 대신 alias이름이 표시된다.

임시 테이블(서브 쿼리 등)을 사용할 경우 테이블 이름, alias 대신 다른 이름으로 표기될 수 있다.

📌 partitions

테이블이 파티션으로 구성되어 있는 경우 출력되는 값이다.

📌 type ⭐️

테이블 데이터를 어떻게 접근할 것인지를 나타내는 항목이다.

아래 항목은 const(가장 빠름) ~ all(가장 느림)으로 분리되어 구분된다.

  • const : 단 1건의 ROW 데이터만 접근하는 유형 (PK, 유니크 키 사용이 대표)
  • eq_ref : 드리븐 테이블에서 매번 1건의 ROW 데이터만을 조회하는 유형 (드라이빙 테이블의 조인 키가 드리븐 테이블에 유일한 경우)
  • ref : ROW 데이터의 접근 범위가 2개 이상인 유형
  • range : 연속된 범위 데이터를 접근하게 되는 유형
  • index_merge : 특정 테이블에 생성된 2개 이상의 인덱스가 병합되어 동시에 적용되는 유형
  • index : 인덱스의 데이터를 Full Scan한 유형
  • all : 테이블의 데이터를 Full Scan한 유형

📌 possible_keys

테이블에서 사용할 수 있는 인덱스의 후보군을 확인하는 항목이다.

📌 key ⭐️

인덱스 후보군에서 실제 사용할 인덱스를 알려주는 항목이다.

📌 key_len

사용된 인덱스의 Bytes을 의미한다.

📌 ref

테이블을 접근한 조건을 명시한 항목이다.

📌 rows

접근할 레코드 행의 수(예상 수치)를 의미하는 항목이다.

📌 filtered

MySQL 엔진에서 필터 조건에 의해 최종 반환되는 데이터의 비율(%)을 의미한다.

📌 Extra ⭐️

쿼리문이 어떻게 수행할 것인지에 대한 부가적인 정보를 알려주는 항목이다.

  • Distinct : 중복이 제거되어 유일한 값을 찾을 때 출력 정보 (DISTINCT, UNION)
  • Using where : where절에 필터 조건을 사용하여 데이터 추출
  • Using temporary : 임시 테이블 생성
  • Using index : 인덱스만 읽어 쿼리 수행 (커버링 인덱스)
  • Using filesort : ORDER BY가 인덱스를 활용 못 하고, 메모리에 올려 별도의 추가 정렬 작업을 수행
  • Using index for group-by : 쿼리문에 GROUP BY, DISTINCT 구문이 포함될 때, 정렬된 인덱스를 기준으로 순차적으로 GROUP BY 연산 수행
  • Using index for skip scan : 모든 인덱스 데이터를 읽는 게 아닌 필요한 인덱스 값만을 읽어 스캔
  • FirstMatch() : 인덱스 스캔 시 첫 번째로 일치하는 레코드만 찾으면 검색 중단
profile
코린이
이전 포스트

[MySQL CRUD] 다양한 INSERT

다음 포스트

[MySQL 튜닝] 튜닝 기본 ~ 심화

0개의 댓글