[DeepDive🤿] SQL 실행계획은 누가 정할까? - DB 옵티마이저

DeepDive 주제

💡 옵티마이저(Optimizer)에 대해 서술하시오.

주제를 보고 든 생각

• 옵티마이저? 최적화? 쿼리 최적화 말하는건가?
• 쿼리조회하는 시간을 줄여서 성능높으려고 쓰는거 아닌가?
• mysql, oracle 등 DB엔진마다 다르게 동작하는건가?

👉 SQL 최적화 vs 쿼리 최적화 vs DB 최적화

• SQL 최적화 = 쿼리 최적화 → 이번 딥다이브 주제
  
• DB 최적화 → SQL 최적화를 포함하는 더 넒은 개념, DB전체의 구조적 최적화(스토리지 엔진/캐싱/버퍼 튜닝, 파티셔닝, 샤딩 등 구조적 최적화)

옵티마이저(optimizer, 최적화)란?

• 사용자가 작성한 SQL 문장을 실제로 어떻게 실행할지를 결정하는 실행 계획(Execution Plan)을 생성하는 DBMS 내부 구성 요소
• Why use?

  • 같은 SQL에도 실행 방법이 여러가지임

  • 데이터 양/분포에 따라 효율적인 방법이 달라짐

  • 위에 것을 다 고려하여 SQL마다 사람이 실행계획을 직접고르기는 불가능!

    ⇒ 즉, DBMS가 자동으로 최적의 실행 계획을 골라 제일 효율이 좋은 실행계획을 골라줌

쿼리 실행 절차

💡 SQL Parsing > Optimization + Generation > Execution

1. SQL Parsing
   • 사용자로부터 요청된 SQL문장을 쪼개서 구성요소를 파악하고 이를 분리하여 파스 트리를 만듬
   • SQL문장이 문법적으로 잘못됐다면 이 단계에서 걸러짐
2. Optimization + Generation (최적화 및 실행 계획 수립)
   • 파스트리 참조하면서 조인순서, 인덱스 사용 유무 등 다양한 조합을 고려하며 실행계획 세우고 엔진이 실제로 실행할 수 있는 코드나 프로시저 형태로 포맷팅
3. Execution
   • 2번에서 결정된 실행계획(테이블의 읽기 순서, 선택된 인덱스 등)대로 스토리지 엔진에 레코드를 읽어오고 실행하여 사용자에게 결과값 반환

옵티마이저의 종류

1. 규칙 기반 최적화(RBO : Rule-Based Optimizer) ← 예전 초기 버전의 오라클에서 사용
   • 옵티마이저에 이미 내장된 우선순위에 따라 실행계획을 수립하는 방식
   • 통계정보(테이블 레코드 건수, 컬럼값의 분포도 등)을 조사 X → 같은 쿼리에 거의같은 실행방법 만들어냄 → But, 사용자의 데이터 분포도는 매우 다양하기에 이미 오래전부터 사용X
   • 일반적으로 고정된 RBO의 우선순위
     
2. 비용 기반 최적화(CBO : Cost-Based Optimizer) ← 현재 대부분의 RDBMS가 사용
   • 쿼리를 처리하기 위한 여러가지 방법들의 각 단위 작업 비용(부하) 정보와 대상 테이블의 예측된 통계정보를 이용해 실행 계획별 비용을 산출하여 비용이 최소인 방식을 선택해여 실행하는 방법

🤔 결국 옵티마이저는 DBMS 내부 엔진으로 내가 건들 수 없는데 왜 알아야할까?

• 옵티마이저가 실행계획을 자동으로 고르지만 항상 최선은 아님! → 그 실행계획을 우리가 읽을 줄 알아야 상황에 맞는 쿼리로 문제를 해결 할 수 있음 ⇒ 즉, 쿼리를 어떻게 짜느냐, 인덱스를 어떻게 두느냐에 따라 옵티마이저의 선택이 달라짐(성능이 달라짐)
• ex) Like 사용시 인덱스 탈때와 안탈때

SELECT * FROM member WHERE name LIKE '%유진';
// -> %가 앞에 있으면 인덱스를 쓸 수 없음 → 풀스캔해야함
// -> B-Tree인덱스는 앞부분부터 비교검색하니까

SELECT * FROM member WHERE name LIKE '한%';
// -> %가 뒤에 있으면 옵티마이저가 인덱스를 활용 가능.

기본 데이터 처리

• DB가 데이터를 기본적으로 어떻게 읽고, 조인하고, 정렬할까?

1. 풀 테이블 스캔 vs 풀 인덱스 스캔

• 풀 테이블 스캔 : 테이블의 모든 레코드를 처음부터 끝까지 읽음
  • 테이블의 레코드 건수 작을때
  • Where절이나 On에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • ex ) SELECT * FROM member;
• 풀 인덱스 스캔 : 테이블을 직접 읽지 않고, 인덱스만 끝까지 읽음
  • SELECT 컬럼이 전부 인덱스에 포함될 때(covering index) 유리.
  • ex ) SELECT count(*) FROM member;
    • 단순히 개수만 세는것이라 풀테이블보다는 용량이 작은 풀 인덱스 스캔을 선택할 확룔이 높음
• 리드 어헤드 : 풀 스캔을 할 경우 백그라운드 스레드를 사용하여 효과적으로 처리하는 방법
  • 풀 스캔할때 순서대로 하나씩 읽어드리는 것이 아니라, 미리 읽어서 버퍼풀에 가져다 두는것.
  • InnoDB 스토리엔진에서 사용
  • 여기서 말하는 풀스캔 = 풀 테이블, 풀인덱스 모두를 일컫는말

💡 서비스 설계 관점

• “소규모 테이블” → 풀스캔 괜찮음
• “자주 검색/정렬되는 컬럼” → 인덱스 주기

2. 병렬처리

• 하나의 쿼리를 여러 스레드가 나누어 처리하는 것
• 아무런 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리가능
• 스레드 개수가 많을수록 쿼리처리시간은 줄겠지만, CPU코어 개수를 넘어서는 경우에는 오히려 성능저하
• ex) SET SESSION innodb_parallel_read_threads=2;

💡서비스 설계 관점

• 보통 MySQL 단독보단 DW/OLAP(분석용) DB에서 활용 가치 ↑
• 일반 웹서비스 트랜잭션성 쿼리에서는 거의 신경 안 써도 됨.

3. ORDER BY 처리

1. 인덱스 이용
   • 장점 : 이미 정렬된 인덱스를 사용하여 매우 빠름
   • ex) SELECT * FROM members ORDER BY age;
     • age가 이미 인덱스로 지정되어있다면, 옵티마이저는 그냥 인덱스 순서대로 데이터를 읽어오기만 하면됨(별도의 정렬 연산 하지X)
   • 단점 : 쓰기 작업시 인덱스 추가/삭제 작업이 필요해서 느림, 인덱스↑→메모리↑
2. Filesort이용
   • 과정 : 조건에 맞는 테이블 가져옴 → 결과를 임시 tb/메모리(소트버퍼)에 담음 → 그 결과를 다시 정렬 알고리즘 돌림→ 정렬된 결과 반환
   • 인덱스를 사용하지 않는 경우 - 정렬기준↑, group by / distinct 사용할경우 등
   • 장점 : 인덱스를 이용할때의 단점이 없어짐.
   • 단점 : 정렬 작업이 쿼리 실행 시 처리되므로 레코드 건수가 많아질수록 쿼리의 응답 속도가 느림,디스크 I/O 발생 가능
   • 소트버퍼 : 정렬을 수행하기 위한 별도의 메모리 공간, 실행완료 후 바로 반납
     • 정렬해야하는 레코드 크기에 따라 가변적으로 증가, 최대 사용 가능한 버퍼공간 지정 가능
     • 정렬에 필요한 메모리가 최대치보다 큰경우 레코드를 여러조각으로 나눠 처리하기 위해 디스크 사용→ 디스크 I/O ↑ → 그렇다고 소트버퍼를 크게 설정해도 성능은 비슷함
   • 정렬 알고리즘
     1. 싱글 패스(Single-pass) : 레코드 전체를 소트 버퍼에 담는 정렬 방식 → MySQL 최신버전에서 사용
        • 정렬기준 컬럼, Select의 컬럼 전부 담음
     2. 투 패스(Two-pass) : 정렬 기준 컬럼과 PK만 소트 버퍼에 담는 정렬 방식
        • 소트 버퍼에 담은걸 순서대로 다시 PK로 테이블을 읽어 select할 컬럼 가져옴 → 즉, 테이블 2번 읽어여함 ⇒ MySQL 최신버전에서는 주로 싱글 패스 사용(투패스 사용하는 경우도 존재함)

• 정렬 처리방법
  • 인덱스를 이용한 정렬 → 조인에서 드라이빙 테이블만 정렬 → 조인에서 조인 결과를 임시 테이블로 저장 후 정렬
  • 드라이빙 테이블 : 조인을 실행하기 전에 첫번째로 읽히는 테이블

💡 서비스 설계 관점

• “이 컬럼으로 정렬 자주 한다” → 해당 컬럼에 인덱스 걸면 → 옵티마이저가 인덱스 정렬 사용
• “정렬 케이스가 다양하다” → 모든 경우를 인덱스로 커버 불가 → 옵티마이저는 파일소트 선택

4. GRUOP BY 처리

1. 인덱스 스캔을 이용하는 GROUP BY
   • 조인의 드라이빙 테이블에 인덱스가 있고, 해당 인덱스를 사용하여 그루핑을 하는 경우, 인덱스를 순서대로 읽으면서 그루핑 작업을 수행, 그 결과로 조인을 처리
2. 루스 인덱스 스캔을 이용하는 GROUP BY
   • 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 방식
   • ex) SELECT a, MAX(b) FROM t GROUP BY a
     • 인덱스 (a, b)가 있을 때, 각 a 그룹에서 b의 최소/최대가 되는 경계값만 찍고 건너뜀
3. 임시 테이블을 사용하는 GROUP BY
   • 인덱스를 전혀 사용하지 못하는 경우 임시 테이블 사용

💡 서비스 설계 관점

• 자주 쓰는 GROUP BY 패턴이 고정적 ? → 인덱스 스캔을 이용하는 GROUP BY
• 그룹별 대표값(최신/최소/최대/Distinct)이 핵심? → 루스 인덱스 스캔을 이용하는 GROUP BY

옵티마이저의 실행 계획 확인 방법

• type: ALL → 풀스캔 / type: ref → 인덱스 스캔
• Extra가 Using filesort → 파일소팅 사용 / Using index → 인덱스 사용

옵티마이저는 RDBMS, NoSQL 다 사용 가능한건가?

💡 둘다 사용가능하지만, RDBMS가 더 많이 발전됨.
NoSQL은 복잡한 쿼리 언어가 없거나 단순 → 인덱스 선택, 샤딩 키 선택 정도의 간단한 옵티마이저는 존재함
위에 설명한 것들은 RDBMS 기준!

📌 마무리

옵티마이저란, 사용자가 보내는 SQL을 어떻게 하면 비용이 적고, 빠르게 실행할 수 있는지를 계획하는 엔진으로, 옵티마이저의 동작 원리를 알아야 “내 쿼리가 왜 느린지” 원인을 찾고, 어떻게 고칠지(쿼리 수정/인덱스 추가/캐싱) 판단할 수 있다!

📝 회고

이번 정리를 통해 “SQL이 실행되기까지 DB 내부에서 어떤 일이 벌어지는지”를 구조적으로 이해하게 되었다.
특히 같은 SQL이라도 실행계획은 여러 가지가 될 수 있고,
옵티마이저가 통계 정보와 비용을 기반으로 실행 방법을 선택한다는 점이 인상 깊었다.

이전에는 EXPLAIN 결과를 보더라도
type, Extra, Using filesort 같은 항목들이 단순한 용어처럼 느껴졌다면,
이제는 왜 이런 실행계획이 나왔는지,
그리고 쿼리를 어떻게 바꾸면 옵티마이저의 선택이 달라질지를 고민할 수 있게 되었다.

또한 단순히 “인덱스를 쓰면 빠르다”가 아니라,

• 언제 풀 테이블 스캔이 더 나을 수 있는지
• ORDER BY / GROUP BY에서 인덱스가 어떻게 활용되는지
• 파일소트와 임시 테이블이 왜 성능 병목이 되는지

같은 DB의 기본 데이터 처리 방식을 자세히 이해해볼 수 있었던 의미있는 시간이었던 것 같다.

[참고자료]

https://dev.mysql.com/doc/refman/8.4/en/optimization.html
Real MySQL 8.0
https://velog.io/@kwontae1313/옵티마이저
https://velog.io/@cham-chi/DB-데이터베이스-쿼리-옵티마이저Query-Optimizer에-대하여