Query Processing

MySQL이 하나의 SQL 쿼리를 받아 결과를 반환하기까지, 내부는 MySQL 서버(Server)와 스토리지 엔진(Storage Engine) 두 컴포넌트가 핸들러 API를 통해 협력하여 처리한다.

• MySQL 서버: 쿼리의 논리적인 처리 전반 담당
  • SQL 파싱
  • 권한/스키마 확인
  • 쿼리 재작성
  • 비용 기반 최적화
  • 실행 계획 수립 및 제어
  • 정렬/집계/표현식 계산
  • 임시테이블 관리
  • 바이너리 로그(Binlog) 관리
• 스토리지 엔진: 데이터의 물리적인 저장 및 접근 담당
  • 실제 데이터 / 인덱스 페이지 접근
  • 인덱스 탐색
  • 레코드 읽기·쓰기
  • MVCC (Multi-Version Concurrency Control)
  • 잠금(Lock) 관리
  • 리두(Redo) 및 언두(Undo) 로그 관리
  • 크래시 리커버리
• 핸들러 API (Handler API): 서버와 스토리지 엔진 간의 인터페이스

쿼리 생명주기 (Query Lifecycle)

1단계: 접속 / 파싱 / 전처리

클라이언트의 요청을 받아 쿼리를 이해하는 초기 단계

1. 커넥션 및 인증: 클라이언트 연결을 수립하고, 스레드를 할당하며 사용자 인증 및 권한 확인
2. SQL 파싱: SQL 텍스트를 최소 단위인 토큰으로 분해하고, 문법을 검사하여 추상 구문 트리(AST, Abstract Syntax Tree) 생성
3. 전처리: AST를 기반으로 의미 분석 수행
   • 상수 전파/폴딩 (Constant Folding): WHERE id = 1 + 2 같은 표현식을 WHERE id = 3으로 미리 계산하여 단순화
   • 기타 전처리: 함수, 표현식 등을 내부적으로 처리하기 쉬운 형태로 변환

2단계: 쿼리 최적화

옵티마이저가 가장 효율적인 실행 계획(Execution Plan)을 수립하는 핵심 단계

1. 쿼리 재작성 (Query Rewrite): 옵티마이저는 더 효율적인 실행이 가능한 형태로 쿼리 구조를 내부적으로 변환
   • 서브쿼리 변환: IN (subquery) 형태를 더 효율적인 세미조인(Semi-Join)으로 변환하는 등 다양한 최적화 시도
   • 파티션 프루닝 (Partition Pruning): 파티션된 테이블에서, WHERE 조건에 명시된 파티션만 스캔하도록 계획하여 불필요한 I/O를 제거
   • SARGable 변환: WHERE a * 10 = 100을 WHERE a = 10처럼 인덱스를 사용할 수 있는 형태(SARGable)로 최대한 변환
2. 비용 기반 최적화 (Cost-Based Optimization): 테이블의 통계 정보(레코드 수, 컬럼 값의 분포 등)를 바탕으로 각 실행 방법의 비용을 계산하고 최적의 계획 선택
   • 접근 경로 선택: const, ref, range, index, ALL(Full Table Scan) 등 최적의 데이터 접근 방식 결정
   • 조인 순서 결정: 여러 테이블 조인 시, 중간 결과 집합을 최소화하는 최적의 조인 순서를 탐색
   • 인덱스 전략 결정
     • 인덱스 스캔 범위 산출: WHERE 조건에서 인덱스를 사용할 수 있는 부분을 추출하여 스토리지 엔진에 전달할 스캔 경계 결정
     • 특수 인덱스 스캔 검토: 인덱스 머지, 인덱스 스킵 스캔, 루스 인덱스 스캔(Using index for group-by) 등의 사용 여부 평가
     • Index Condition Pushdown(ICP): 인덱스에 포함된 컬럼만으로 판단 가능한 WHERE 조건을 스토리지 엔진으로 내려보낼지 결정(스토리지 엔진 단에서 필터링 효율 상승)
   • 정렬/그룹화 전략 결정: ORDER BY나 GROUP BY를 인덱스 순서만으로 처리할 수 있는지 판단(가능하다면 filesort나 임시 테이블 생성 생략)
   • I/O 최적화 결정: Multi-Range Read (MRR), Batched Key Access (BKA) 등 I/O 효율을 높이는 고급 기법의 사용 여부 결정

3단계: 실행(서버 + 엔진)

옵티마이저가 수립한 실행 계획에 따라 실제 작업 수행

1. 핸들러 준비: 서버가 실행 계획에 따라 각 테이블에 대한 핸들러를 열고, 선택된 인덱스와 스캔 범위를 스토리지 엔진에 전달
2. 데이터 접근 및 필터링(엔진 수행)
   • 인덱스 탐색 및 행 읽기: 스토리지 엔진이 B+Tree 인덱스를 실제로 탐색하여 레코드 조회
   • 커버링 인덱스 vs 더블 리드
     • 커버링 인덱스: 세컨더리 인덱스 사용 시, 인덱스만으로 모든 데이터를 처리할 수 있으면 즉시 반환
     • 더블 리드: 그렇지 못하는 경우, 세컨더리 인덱스에서 찾은 PK를 사용해 클러스터형 인덱스를 다시 한번 조회
   • 엔진 레벨 최적화 수행
     • ICP: 인덱스에 포함된 컬럼만으로 WHERE 조건을 미리 필터링하여 서버로 전달되는 데이터 양을 줄임
     • MRR/BKA: 서버의 지시에 따라 여러 키 조회를 모아 물리적으로 정렬 후 처리하거나(MRR), 조인 키를 배치로 묶어(BKA) 랜덤 I/O를 최소화
3. 서버 후처리 작업: 스토리지 엔진으로부터 받은 로우(Row)를 가지고 나머지 논리적인 처리를 수행
   • WHERE 잔여 조건 평가: 인덱스나 ICP로 거르지 못한 나머지 WHERE 조건(다른 테이블 컬럼 참조, 함수 사용 등)을 최종 평가
   • JOIN 수행: 드라이빙 테이블의 로우를 기준으로 조인 순서에 따라 다음 테이블에 키 조회를 요청하고 결과 병합
   • GROUP BY / 집계: 인덱스로 최적화되지 않은 경우, 내부 임시 테이블을 사용하여 그룹화 수행
   • ORDER BY (Filesort): 인덱스 순서로 정렬이 해결되지 않았다면, 서버가 정렬 버퍼(메모리)를 사용해 정렬(filesort)을 수행(데이터가 크면 디스크 기반 임시 파일 사용)
   • DISTINCT / 윈도우 함수 / 표현식 계산: 중복 제거, 윈도우 함수 계산, 모든 스칼라 표현식 계산 등 수행
   • LIMIT 처리: 결과가 LIMIT 개수에 도달하면 서버는 즉시 실행을 중단시키고 결과 반환

4단계: 쓰기 쿼리 및 트랜잭션

INSERT, UPDATE, DELETE 쿼리의 추가적인 단계

1. 변경 대상 탐색: 읽기 쿼리와 유사하게 플랜에 따라 변경할 로우 탐색
2. 엔진 작업: 스토리지 엔진이 실제 변경 수행
   • 트랜잭션 처리: 언두(Undo) 로그를 기록하고, MVCC 및 잠금(레코드 락, 넥스트 키 락)을 적용하여 데이터 정합성 보장
   • 제약 조건 확인: 유니크 키, 외래 키 등의 제약 조건 충돌을 감지
3. 서버-엔진 협력
   • 바이너리 로그 기록(서버 수행): 복제 및 시점 복구를 위해 변경 내역을 바이너리 로그 기록
   • 리두 로그 기록(엔진 수행): 크래시 리커버리를 위해 변경 내역을 리두(Redo) 로그 기록
   • 2단계 커밋: 바이너리 로그와 리두 로그의 원자성을 보장하기 위해 두 컴포넌트가 협력하여 커밋 완료

요약

기능/작업	주 담당	설명
플랜 수립 (인덱스 선택, 조인 순서 등)	서버	옵티마이저의 핵심 역할
인덱스 탐색 및 레코드 I/O	엔진	B+Tree 접근, 페이지 읽기/쓰기
트랜잭션, MVCC, 잠금(Lock), 복구	엔진	InnoDB의 핵심 기능
WHERE 조건 평가	서버	엔진이 ICP로 일부 처리
GROUP BY / ORDER BY / DISTINCT	서버	인덱스로 최적화 가능 시 엔진의 순차 스캔 활용
임시 테이블 / 파일 정렬 (Filesort)	서버	메모리 또는 디스크 사용
조인 / 함수 / 표현식 계산	서버	논리적인 데이터 가공
바이너리 로그 / 복제	서버	엔진과 2단계 커밋으로 협력
파티션 프루닝 / MRR / BKA	서버 (결정)	엔진이 효율적인 읽기 수행

실제 쿼리 실행 예시

SELECT
    u.name,
    o.order_date,
    o.amount
FROM
    users u
JOIN
    orders o ON u.id = o.user_id
WHERE
    u.status = 'ACTIVE' AND o.amount > 10000
ORDER BY
    o.order_date DESC
LIMIT 10;

• users 테이블: id(PK), name, status (status 컬럼에 인덱스 존재)
• orders 테이블: id(PK), user_id, order_date, amount (order_date 컬럼에 인덱스 존재)

1단계: 접속, 파싱, 전처리

• 서버는 클라이언트로부터 위 SQL 텍스트를 수신
• 파서는 SQL 문법을 검사하고 SELECT, FROM, JOIN, WHERE 등의 키워드와 users, orders 같은 객체를 식별하여 AST 생성
• 전처리기는 users와 orders 테이블 및 그 안의 id, name, status, order_date 등 컬럼이 실제로 존재하는지 확인

2단계: 쿼리 최적화

1. 조인 순서 결정: users를 먼저 읽을지, orders를 먼저 읽을지 비용 계산
2. 접근 경로 평가
   • users 테이블: WHERE u.status = 'ACTIVE' 조건을 처리하기 위해 status 인덱스를 사용 혹은 테이블 전체를 스캔하는 것이 나은지 평가
   • orders 테이블: ORDER BY o.order_date DESC LIMIT 10 구문을 보고, order_date 인덱스를 이용해 먼저 가져오는 것으로 판단
3. 실행 계획 최종 결정(예시): 옵티마이저는 order_date 인덱스를 활용하는 것이 비용이 가장 적다고 판단하여 다음과 같은 실행 계획 수립
   • 드라이빙(Driving) 테이블: orders
   • 접근 방식
     1. orders 테이블의 order_date 인덱스를 역순(DESC)으로 스캔
     2. WHERE o.amount > 10000 조건을 만족하는 레코드를 찾음
     3. 찾은 orders 레코드의 user_id를 이용해 users 테이블을 PK로 조인
     4. 조인된 users 레코드의 status가 'ACTIVE'인지 최종 확인
     5. 위 조건을 모두 만족하는 결과 10개를 찾을 때까지 반복하고, 10개가 채워지면 즉시 실행을 중단

3단계: 실행

1. 핸들러 준비: 서버가 orders 테이블과 users 테이블에 대한 핸들러를 열음
2. 데이터 접근
   • 서버 -> 엔진: orders 테이블의 order_date 인덱스를 역순으로 스캔해서 레코드 찾아달라는 요청 전송
   • 엔진: order_date 인덱스의 B+Tree를 역순으로 스캔하여, 해당 인덱스의 실제 데이터 레코드에 접근하여 o.amount > 10000 조건을 만족하는 레코드를 찾아 서버에 전달
   • 서버 -> 엔진: 방금 받은 orders 레코드의 user_id 값으로 users 테이블의 PK를 조회해서 레코드를 찾아달라는 요청 전송
   • 엔진: users 테이블의 PK 인덱스를 통해 해당 사용자를 즉시 찾아 서버에 전달
3. 서버 후처리 작업
   • WHERE 잔여 조건 평가: 서버는 전달받은 users 레코드의 status가 'ACTIVE'인지 최종 확인
     • 만약 'ACTIVE'가 아니라면, 해당 레코드 쌍은 버리고 2단계로 돌아가 다음 orders 레코드 요청
   • ORDER BY (Filesort): 이 실행 계획에서는 order_date 인덱스를 순서대로 읽었으므로, 별도의 정렬(Filesort) 작업은 필요하지 않음
   • LIMIT 처리: 위 과정을 반복하여 최종 조건을 만족하는 결과 10개가 모이면, 서버는 스토리지 엔진에 더 이상 데이터를 요청하지 않고 실행을 즉시 중단