study
MySQL 엔진 아키텍처 DeepDive
9
9분
MySQL vs Oracle 아키텍처 및 실무 성능 이슈 비교
MySQL 엔진 아키텍처(Real MySQL 4.1장)와 Oracle SQL 처리 과정(친절한 SQL 튜닝 1장) 기반 정리 문서
1. 핵심 비교 요약표
| 구분 | MySQL | Oracle |
|---|---|---|
| 전체 구조 | 분리형: MySQL 엔진(두뇌) + 스토리지 엔진(손발), API 통신 | 일체형: SQL 엔진과 스토리지 엔진이 강결합 |
| 작동 방식 | 스레드 기반: 포그라운드/백그라운드 스레드 | 프로세스 기반: 요청마다 서버 프로세스 생성 |
| 메모리 구조 | 글로벌/로컬: 버퍼 풀(공유) + 정렬·조인 버퍼(스레드별) | SGA/PGA: 버퍼 캐시·공유 풀(SGA) + 정렬·세션 정보(PGA) |
| SQL 캐시 | 8.0부터 쿼리 캐시 폐지 → 매번 최적화, 버퍼 풀 효율 의존 | 라이브러리 캐시로 실행 계획 공유. 하드/소프트 파싱 구분 |
| 🚨 핵심 성능 이슈 | OOM 위험: 로컬 메모리(sort_buffer_size 등) 과설정 + 커넥션 폭주 → DB 다운 |
하드 파싱 오버헤드: 리터럴 사용 → 매번 파싱 → CPU 스파이크 + 래치 경합. 바인드 변수 필수 |
| 정렬/조인 튜닝 | 메모리 초과 시 Filesort(디스크). 인덱스로 정렬 회피가 핵심 | Sort Area 초과 시 Temp 테이블스페이스 사용. Sort Area 튜닝 + 인덱스 설계 |
2. 구조적 차이점 및 실무 성능 이슈 상세
| 구분 | MySQL 8.0 | Oracle | 🛠 실무 튜닝 포인트 |
|---|---|---|---|
| 전체 아키텍처 | 플러그인 기반 분리형 — MySQL 엔진(파싱·최적화)과 스토리지 엔진(디스크 I/O)이 분리 | 일체형 — 파싱~I/O까지 하나의 시스템으로 통합 동작 | [MySQL] 핸들러 API 통신 오버헤드 가능 → 실행 계획(EXPLAIN) 확인 필수 |
| SQL 파싱/캐싱 | 파서→전처리기→옵티마이저 순 실행. 쿼리 캐시는 동시성 문제로 8.0에서 제거 | 라이브러리 캐시에 실행 계획 저장·재사용(Soft Parsing). 미스 시 하드 파싱 | [Oracle] 하드 파싱 시 CPU·딕셔너리 부하 → 바인드 변수 필수. [MySQL] 바인드 변수는 보안 목적이 주. 버퍼 풀 I/O 효율에 집중 |
| 메모리 구조 | 글로벌(버퍼 풀 등 공유) + 로컬(정렬·조인 버퍼, 스레드별 동적 할당) | SGA(공유) + PGA(프로세스 전용) | [MySQL] 로컬 메모리는 커넥션마다 독립 할당 → 과설정 + 커넥션 폭주 시 OOM Killer 위험 |
| 데이터 I/O | InnoDB 버퍼 풀에 데이터·인덱스 캐시. MyISAM은 OS 캐시 의존 | 해시 체인으로 버퍼 캐시 스캔 → 미스 시 디스크 블록 읽기 | [공통] 디스크 I/O는 메모리 대비 수십~수백 배 느림. 인덱스 설계로 캐시 히트율 향상 + 풀 스캔 최소화 |
3. 세부 아키텍처 항목별 비교
| 비교 항목 | MySQL | Oracle | 핵심 차이 | 관련 명령어 | 비고 | 출처 |
|---|---|---|---|---|---|---|
| 스토리지 엔진 | 플러그인 모델 — 핸들러 API로 InnoDB, MyISAM 등 교체 가능 | 단일 엔진 — 데이터 저장을 DBMS가 통합 처리 | MySQL은 파싱/최적화와 I/O가 구조적으로 분리됨. Oracle은 IOT 등을 테이블 옵션으로 제공 | SHOW ENGINES, ENGINE=INNODB |
유연하나 엔진 간 기능 차이(트랜잭션 등)를 사용자가 관리해야 함 | [1, 2] |
| 프로세스/스레딩 | 스레드 기반 — 포그라운드(커넥션) + 백그라운드 스레드 | 프로세스 기반 — 연결당 전용 서버 프로세스 생성 | MySQL은 스레드 풀로 컨텍스트 스위치 절감 가능. Oracle은 프로세스별 PGA 할당 | thread_pool_size, performance_schema.threads |
스레드 모델은 자원 효율적이나 장애 전파 위험. 프로세스 모델은 독립성 강함 | [1-3] |
| 메모리 관리 | 글로벌(공유) + 로컬(스레드별 독립) | SGA(공유) + PGA(프로세스 전용) | 둘 다 공유/전용 영역 구분. MySQL 로컬 메모리는 쿼리 시 동적 할당/해제 | innodb_buffer_pool_size, sort_buffer_size |
MySQL은 쿼리 단위 동적 할당 강조 | [1, 3] |
| 인덱스 구조 | InnoDB는 PK 기준 클러스터링 저장 (기본값) | 힙 구조 기본. 필요 시 IOT 명시 생성 | InnoDB = Oracle IOT와 동일 구조가 기본. Oracle은 ROWID 기반 힙이 기본 | PRIMARY KEY, ORGANIZATION INDEX |
InnoDB는 PK가 물리 저장 순서 결정 → PK 설계 중요도가 Oracle보다 높음 | [2, 4] |
| 데이터 딕셔너리 | 8.0부터 InnoDB 시스템 테이블에 트랜잭션 기반 저장 (이전: *.frm 파일) | 시스템 테이블스페이스에 통합 관리 | 8.0에서 파일 기반의 원자성 결여 문제 해결 → 스키마 변경 중 크래시에도 무결성 보장 | information_schema.TABLES |
— | [2] |
4. 메모리 구조 대응 관계
| 역할 | MySQL | Oracle | 설명 |
|---|---|---|---|
| 공유 — 데이터 캐시 | InnoDB 버퍼 풀 (innodb_buffer_pool_size) |
SGA — DB 버퍼 캐시 | 데이터 페이지/블록 캐시로 반복 I/O 방지 |
| 공유 — SQL 캐시 | SGA — 라이브러리 캐시 | Oracle은 파싱된 SQL·실행 계획을 저장·재사용 | |
| 전용 — 정렬 | sort_buffer_size (스레드별) |
PGA — Sort Area | 초과 시 MySQL은 Filesort, Oracle은 Temp 사용 |
| 전용 — 조인 | join_buffer_size |
PGA — Hash Area | 조인 처리용 전용 메모리 |
| 전용 — 기타 | read_buffer_size 등 |
PGA — 세션 정보 | 필요 시 동적 할당/해제 |
5. SQL 처리 과정 비교
| 단계 | MySQL 8.0 | Oracle | 핵심 차이 |
|---|---|---|---|
| 1. 파싱 | 파서가 문법 검증 + 파스 트리 생성 | SQL 파서가 구문 분석 | 유사 |
| 2. 전처리 | 전처리기가 테이블/컬럼 존재·권한 확인 | 딕셔너리 조회로 객체·권한 확인 | MySQL은 전처리 단계를 명시 분리 |
| 3. 캐시 조회 | 라이브러리 캐시 검색 → 히트 시 소프트 파싱으로 즉시 실행 | Oracle 최대 차별점. 히트 시 최적화를 건너뜀 | |
| 4. 최적화 | 옵티마이저가 매번 비용 기반 실행 계획 수립 | 캐시 미스 시에만 하드 파싱 → 실행 계획 생성 | MySQL은 항상, Oracle은 하드 파싱 시에만 수행 |
| 5. 실행 | 실행 엔진 → 핸들러 API → 스토리지 엔진 | SQL 실행 엔진이 직접 데이터 접근 | MySQL은 핸들러 API 추상화 계층 존재 |
6. 실무 성능 이슈 요약
| 🚨 이슈 | 대상 | 원인 | 증상 | 해결 |
|---|---|---|---|---|
| OOM | MySQL | 로컬 메모리 과설정 + 커넥션 폭주 | OS 메모리 부족 → OOM Killer로 프로세스 종료 | 로컬 메모리 보수적 설정. max_connections 제한. 커넥션 풀 사용 |
| 하드 파싱 | Oracle | 리터럴 값 직접 삽입 → 매번 새 SQL로 인식 | CPU 스파이크, 래치 경합, 응답 시간 급증 | 바인드 변수 사용. cursor_sharing=FORCE 검토 |
| 핸들러 API 오버헤드 | MySQL | 스토리지 엔진 특성 무시한 비효율 쿼리 | 엔진 간 불필요한 통신 증가 | EXPLAIN으로 실행 계획 확인. 엔진 특성에 맞는 쿼리 작성 |
| 디스크 정렬 | 공통 | 정렬 데이터가 메모리(Sort Buffer/Area) 초과 | 디스크 I/O로 성능 급감 | 인덱스로 정렬 회피. 정렬 컬럼 최소화 |
| 버퍼 캐시 미스 | 공통 | 인덱스 미비·불필요한 풀 스캔 | 디스크 I/O 급증 | 인덱스 설계로 히트율 향상. 테이블 액세스 최소화 |
7. 정렬 및 조인 튜닝 비교
| 항목 | MySQL | Oracle |
|---|---|---|
| 정렬 메모리 | sort_buffer_size (스레드별) |
PGA — Sort Area (sort_area_size / 자동 PGA) |
| 메모리 초과 시 | Filesort (디스크 임시 파일) | Temp 테이블스페이스 (디스크) |
| 정렬 튜닝 원칙 | 인덱스로 정렬 회피 최우선. ORDER BY가 인덱스 순서와 일치하도록 설계 |
Sort Area 튜닝 + 인덱스 설계. 불필요한 정렬 제거 |
| 조인 메모리 | join_buffer_size (Block NL / Hash Join) |
PGA — Hash Area |
| 조인 최적화 | 드라이빙 테이블 선택 + 인덱스 활용. 8.0부터 Hash Join 지원 | NL / Sort Merge / Hash Join 중 옵티마이저 선택 |
8. 인덱스 및 데이터 저장 구조 비교
| 항목 | MySQL (InnoDB) | Oracle |
|---|---|---|
| 기본 테이블 구조 | 클러스터형 인덱스 — PK 기준 물리 정렬 저장 | 힙 구조 — 삽입 순서 저장, ROWID로 식별 |
| IOT 지원 | InnoDB 자체가 IOT 구조 (기본값) | ORGANIZATION INDEX로 명시 생성 시에만 |
| PK 설계 중요도 | 🔴 매우 높음 — 물리 저장 순서 결정 | 🟡 보통 — 논리적 식별자, 저장 순서와 무관 |
| 데이터 딕셔너리 | 8.0부터 InnoDB 시스템 테이블에 트랜잭션 기반 저장 | 시스템 테이블스페이스에 통합 관리 |
| 관련 명령어 | SHOW ENGINES, ENGINE=INNODB, information_schema.TABLES |
ORGANIZATION INDEX, DBA_TABLES, DBA_DATA_FILES |
댓글