MySQL SQL 파서(Parser)와 옵티마이저(Optimizer) 내부 동작 정리

0. 들어가며

MySQL을 공부하면서 MySQL 파서와 옵티마이저에 대해 알게 되었습니다.

MySQL은 SQL을 그대로 실행하지 않고, 먼저 해석한 뒤 어떤 방식으로 실행할지를 결정합니다. 이 과정이 결국 DB에 큰 영향을 준다는 점이 중요하게 느껴졌습니다.

 

 

예전에는 그냥 SQL만 잘 짜면 되는 거 아닌가라는 생각을 했었는데 이 과정을 알고 난 후에는 같은 SQL문이라도 DB 성능을 결정하는 것은 옵티마이저와 파서라는 걸 알게 되었습니다.

 

그래서 이번 글에서는 MySQL에서 SQL이 처리되는 흐름을 기준으로 파서와 옵티마이저가 각각 어떤 역할을 하고 이 과정이 왜 DB 성능과 직결되는지를 공부하면서 이해한 내용 위주로 정리해보려고 합니다.

 

---

1. MySQL에서 SQL이 처리되는 흐름

MySQL은 클라이언트로부터 전달받은 SQL을 곧바로 실행하지 않습니다. 내부적으로 여러 단계를 거치면서 SQL을 분석하고, 실행 계획을 세운 뒤 실제 데이터를 조회합니다.

 

전체 흐름을 단순화하면 다음과 같습니다.

클라이언트
↓
SQL 파서(Parser) : SQL 문법이 올바른지 확인하고, SQL을 내부에서 이해할 수 있는 구조로 변환
↓
전처리기(Preprocessor) : 테이블과 컬럼이 실제로 존재하는지, 권한 문제는 없는지 등 의미적인 검증을 수행
↓
옵티마이저(Optimizer) : 인덱스를 사용할지, 어떤 순서로 테이블을 조인할지 등 SQL을 어떤 방식으로 실행할지 결정
↓
실행기(Executor) : 옵티마이저가 선택한 실행 계획을 바탕으로 스토리지 엔진에 데이터를 요청
↓
스토리지 엔진(InnoDB) : 실제 디스크에 저장된 데이터를 읽고 쓰는 역할

이 글에서는 이 중에서 파서와 옵티마이저에 집중해서 살펴봅니다.

파서와 옵티마이저를 집중해서 다루는 이유는 DB 성능에 가장 큰 영향을 주는 판단이 이 두 단계에서 이루어지기 때문입니다.

 

파서는 SQL을 이해하는 역할을 하고 옵티마이저는 그 SQL을 어떻게 실행할지 결정합니다. 이후 단계들은 이 결정을 그대로 따를 뿐입니다.

 

따라서 SQL 성능을 이해하려면 이제는  문법이나 인덱스만 보는 것이 아니라 이 처리 흐름 전체를 함께 이해할 필요가 있기때문입니다.

 

---

2. SQL 파서(Parser)란?

파서는 SQL 문장을 문자열이 아닌 구조적인 형태로 바꾸는 역할을 합니다.

 

예를 들어 다음과 같은 SQL이 있다고 가정해봅니다.

SELECT name FROM users WHERE age > 20;

 

이 쿼리가 실행되기 까지의 여정을 한번 살펴 보겠습니다.

• 조회 대상 컬럼: name
• 테이블: users
• 조건: age > 20

 

파서는 SQL의 문법이 올바른지 검사하고, 이 SQL이 어떤 의미를 가지는지 구조화합니다.

---

2-1.  파서의 SQL문 3단계 처리

 

파서는 SQL 문을 세 단계로 처리합니다.

 

1단계: 어휘 분석 (Lexical Analysis) : SQL 문을 의미 있는 토큰으로 분해합니다.

 

예를 들어 

SELECT name FROM users WHERE id = 1

 위 쿼리 문을 SELECT | name | FROM | users | WHERE | id | = | 1

  키워드, 테이블명, 컬럼명, 연산자를 각각 구분합니다. 

---

 

2단계: 구문 분석 (Syntax Analysis) : SQL 문법이 맞는지 검증합니다.

-- 잘못된 문법
SELECT FROM users WHERE name;

문법 오류가 있으면 "You have an error in your SQL syntax" 에러를 반환합니다. 올바르면 파싱 트리(Parse Tree)를 생성합니다.

 

---

3단계: 전처리 (Preprocessor)

실제로 존재하는지, 권한이 있는지 확인합니다.

SELECT name FROM non_exist_table;  -- 테이블 없으면 에러
SELECT salary FROM employees;      -- 권한 없으면 에러

뷰를 테이블로 확장하고, 별칭을 해석하며, 서브쿼리를 처리합니다.

---

2-1.  파서특징

파서의 특징은 성능을 고려하지 않는다는 것입니다. 파서는 오직 sql이 실행 가능 형태인가를 판단하는데만 집중합니다.

---

3. 옵티마이저(Optimizer)란?

옵티마이저는 파서가 만든 파싱 트리를 받아서 가장 빠른 실행 방법을 찾는 MySQL의 핵심 엔진입니다.

 

예를 들어 사용자 테이블에서 나이가 20살 이상인 사람을 찾는 쿼리가 있다고 해봅시다.

SELECT * FROM users WHERE age > 20;

이 쿼리를 실행하는 방법은 여러 가지가 있습니다. 테이블의 모든 데이터를 하나하나 확인할 수도 있고, 나이 인덱스가 있다면 그것을 활용할 수도 있습니다. 옵티마이저는 이런 여러 선택지 중에서 가장 효율적인 방법을 선택합니다.

 

예를 들어, 테이블 전체를 스캔하면 100만 행을 확인해야 하지만 age 인덱스를 사용하면 5만 행만 확인하면 됩니다. 당연히 옵티마이저는 후자를 선택합니다.

---

판단을 내리는 기준 - 통계정보

옵티마이저는  통계정보를 기반으로 판단을 내립니다. 여기에는 다음과 같은 정보가 포함됩니다.

 

 테이블 메타데이터

• 각 테이블의 row 수
• 인덱스 목록
• PK / FK 정보

 컬럼 통계 (InnoDB)

• Cardinality (고유값 수)
• 데이터 분포 (히스토그램이 있다면 활용)
• NULL 비율

이 통계를 바탕으로 각 실행 방법의 비용을 계산합니다. 여기서 비용이란 읽어야 할 데이터 양, CPU 사용량, I/O 비용 등을 종합적으로 평가한 값입니다.

 

만약 테이블에 백만 개의 데이터가 있는데 조건을 만족하는 게 5만 개라면 전체를 스캔하는 것보다 인덱스를 써서 5만 개만 확인하는 게 훨씬 효율적입니다. 옵티마이저는 이런 계산을 해서 인덱스를 사용하기로 결정합니다.

 

---

실행 계획(Execution Plan) 생성 순서

MySQL InnoDB 옵티마이저는 통계 정보를 기반으로 후보 실행 계획을 생성한 뒤,
복합 인덱스 선택 가능성과 JOIN 순서를 고려하여 전체 비용이 가장 낮은 경로를 선택합니다.

예시 EXPLAIN 결과:

id: 1
select_type: SIMPLE
table: o
type: range
key: idx_orders_status_created_user
rows: 20000000
Extra: Using index condition; Using where

id: 1
table: u
type: eq_ref
key: PRIMARY
rows: 1

 

 

옵티마이저는 카디널리티가 높은, 즉 중복도가 낮은 컬럼을 먼저 필터링하고적절한 인덱스를 선택하여 가장 적은 rows에서 순회하도록 실행 계획을 생성합니다.
10억 rows 환경에서는 결국 “얼마나 빨리 rows를 줄이느냐”가 성능의 핵심입니다.

---

쿼리를 더 효율적으로

옵티마이저는 쿼리 자체를 더 효율적인 형태로 바꾸기도 합니다.

 

• 서브쿼리를 조인으로 변환
• 불필요한 조건 제거

또한 여러 인덱스가 있을 때는 가장 효율적인 인덱스를 선택합니다.

 

예를 들어 서브쿼리로 작성된 쿼리를 조인으로 변환하거나, 불필요한 조건을 자동으로 제거합니다. 우리가 쓴 쿼리가 비효율적이더라도 옵티마이저가 알아서 개선해주는 경우가 많습니다.

---

인덱스 선택

여러 인덱스가 있을 때 가장 효율적인 것을 선택합니다.

CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_age_city ON users(age, city);

SELECT * FROM users WHERE age > 20 AND city = 'Seoul';

옵티마이저는 두 조건을 모두 활용할 수 있는 idx_age_city 복합 인덱스를 선택합니다.

---

조인 순서 결정

여러 테이블 조인 시 효율적인 순서를 정합니다.

SELECT * 
FROM orders o           -- 100만 행
JOIN customers c        -- 10만 행
  ON o.customer_id = c.id
WHERE c.city = 'Seoul'; -- 1000행으로 줄어듦

옵티마이저는 customers를 먼저 필터링해서 1000행으로 만든 뒤 orders와 조인합니다.

 

옵티마이저의 단점

옵티마이저도 완벽하지는 않습니다.

통계 정보가 오래되면 잘못된 판단을 할 수 있고, 함수를 사용한 조건이나 너무 복잡한 쿼리에서는 최선의 선택을 못할 때도 있습니다.

 

이를 극복하기 위해 개발자가 EXPLAIN으로 실행 계획을 확인하고, 필요하면 쿼리를 다시 작성하거나 힌트를 줘서 옵티마이저를 보조해야 합니다. 

---

마치며

SQL선을 이해하려면 MySQL 파서와  옵티마이저에 대한 이해가 정말 중요한것 같습니다.

옵티마이저는 우리대신 고민해주는 똑똑한 조수 같은 존재라고 생각합니다.

하지만 조수를 제대로 활용하려면 그가 어떻게 생각하는지 알아야 합니다. 

그래야 더 나은 쿼리를 작성할 수 있고, 문제가 생겼을때 빠르게 해결 할 수 있을 것입니다. 

마지막까지 읽어주셔서 감사합니다.