[RealMySQL8.0] 아키텍쳐

 

MySQL 엔진 아키텍쳐 

 

MySQL 엔진 

- 클라이언트 접속, 쿼리 요청 및 SQL 파서 및 전처리, 옵티마이저를 담당하는 부분

 

스토리지 엔진 

- 요청된 SQL을 처리를 수행 ( 기본적인 CRUD 로직을 처리 ) 

- 여러 개의 스토리지 엔진을 동시에 사용하여 처리할 수 있음.

- MyISAM, InnoDB, Memory 등이 있음. 

- CREATE 명령어로 테이블을 생성할 때, ENGINE={스토리지명}을 정의하면 정의된 엔진을 사용할 수 있음. 

 

# INNODB 기반 테이블 생성 
CREATE TABLE iwant_innodb (fd1 INT, fd2 INT) ENGINE=INNODB;

# MYISAM 기반 테이블 생성 
CREATE TABLE iwant_myisam (fd1 INT, fd2 INT) ENGINE=MYISAM;

# 데이터베이스에 할당된 테이블의 정보 확인 -> 여기서 practice는 MySQL 스키마
SHOW TABLE STATUS FROM practice; 

+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| iwant_innodb | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                0 |            0 |         0 |           NULL | 2025-07-17 02:07:31 | NULL                | NULL       | utf8mb4_general_ci |     NULL |                |         |
| iwant_myisam | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 2533274790395903 |         1024 |         0 |              1 | 2025-07-17 02:07:59 | 2025-07-17 02:07:59 | NULL       | utf8mb4_general_ci |     NULL |                |         |
| test_table   | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                0 |            0 |         0 |           NULL | 2025-07-17 02:03:13 | NULL                | NULL       | utf8mb4_general_ci |     NULL |                |         |
+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
3 rows in set (0.02 sec)

 

 

핸들러 API 

- MySQL 엔진이 스토리지 엔진(InnoDB 등)에 데이터 읽기/쓰기 요청할 때 사용되는 API

- 작업 횟수는 SHOW GLOBAL STATUS LIKE 'Handler%'; 명령으로 확인 가능

mysql>  SHOW GLOBAL STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 640   |
| Handler_delete             | 8     |
| Handler_discover           | 0     |
| Handler_external_lock      | 6885  |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 8     |
| Handler_read_first         | 52    |
| Handler_read_key           | 1845  |
| Handler_read_last          | 0     |
| Handler_read_next          | 4101  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 212   |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 355   |
| Handler_write              | 42    |
+----------------------------+-------+
18 rows in set (0.02 sec)

 

 

MySQL 스레딩 구조 

 

- 스레드 기반 구조로 포그라운드(클라이언트 연결 처리), 백그라운드(내부 작업 수행) 동작한다.

- 일반적으로 약 3개의 포그라운드, 다수의 백그라운드 스레드가 존재하며 performance_schema.threads 테이블로 스레드 정보 확인이 가능하다.

SELECT * FROM performance_schema.threads ORDER BY type, thread_id;

 

 

포그라운드 스레드

- 사용자의 요청을 처리하는 스레드로 데이터를 읽는 작업 처리. 

- 클라이언트 연결당 하나씩 스레드 생성

- 디스크에 직접 데이터를 쓰거나 하는 작업은 백그라운드에서 동작함. 

- 읽기는 버퍼/캐시 우선, 없을 땐 디스크 읽기

- MySQL에 접속된 클라이언트의 수 만큼 포그라운드 스레드가 존재함. 

- MyISAM은 포그라운드가 디스크까지 쓰기 처리, InnoDB는 백그라운드 스레드에 위임

 

백그라운드 스레드

- 버퍼 병합, 로그 기록, 버퍼 풀 플러시, 읽기, 락/교착 모니터링 등등의 작업이 이루어지는 스레드.

- 디스크 자체에 데이터를 기록하는 작업은 백그라운드 스레드에서 동작함. 

- 현재 로그나 버퍼풀에 있는 데이터를 디스크에 기록함. 

- MySQL 5.5 에서는 읽기/쓰기 스레드 수 조정 가능: innodb_write_threads, innodb_read_threads

- InnoDB는 쓰기 지연 처리, MyISAM은 즉시 쓰기

 

메모리 구조

- 글로벌 메모리: 서버 시작 시 할당, 모든 스레드 공유

- 로컬 메모리: 클라이언트별로 독립 할당, 공유되지 않음

    예: 정렬 버퍼, 조인 버퍼, 네트워크 버퍼 등

- 로컬 메모리는 필요할 때만 할당되며 쿼리 종료 시 해제됨

 

플러그인 스토리지 엔진 모델

- MySQL은 스토리지 엔진, 인증 방식, 파서 등 다양한 기능을 플러그인 형태로 확장 가능

- 핸들러는 MySQL 엔진 ↔ 스토리지 엔진의 연결 고리 역할

-기본 엔진 외에도 외부에서 개발된 플러그인 추가 가능

 

컴포넌트 (MySQL 8.0+ 이후 부터)

- 플러그인의 단점을 보완한 새 구조

   - MySQL 플러그인들간에 통신이 안되기 때문에, 상화 의존관계 설정이 안되는 이슈가 있음.

   - 컴포넌트 간 통신 가능, 캡슐화, 의존성 정의 지원

   - 예: validate_password 등의 인증 컴포넌트로 설치/관리

 

쿼리 실행 흐름

1. 사용자의 쿼리 요청 

2. 쿼리 파서: SQL 문법 분석  ( 쿼리 파서에서 트리형태로 쿼리를 재구성하고 문법적인 오류를 잡아줌 )

3. 전처리기: 테이블/컬럼 존재 및 권한 검증

4. 옵티마이저: 효율적인 실행 계획 선택 ( 쿼리를 어떻게 빠르게 처리할지 정함 )

5. 실행 엔진: 핸들러를 호출해 실제 데이터 조작

6. 핸들러: 스토리지 엔진 단에서 디스크 I/O 수행 (MyISAM 또는 InnoDB)

 

쿼리 캐시 (삭제됨)

- 이전에는 쿼리 캐시로 성능 향상을 기대했지만, 동시성 처리에 문제와 버그 많음

- MySQL 8.0에서 완전 제거됨

 

스레드 풀 

- CPU 효율 개선 & 컨텍스트 스위치를 최소화하는 기능을 제공

- 그룹 단위 스레드 처리, thread_pool_size, thread_pool_max_threads, thread_pool_oversubscribe, thread_pool_stall_limit 등의 설정 가능

- 사용자 요청을 포워드 스레드를 CPU가 최대한 잘 처리할 수 있는 수준으로 줄여 동시 처리되는 요청이 많더라도 한정된 스레드에서 처리 될 수 있도록 처리하가능하도록 하기 위함.

 

트랜잭션 지원 메타데이터(8.0 버전 부터)

- 8.0 버전 부터는 테이블의 구조정보와 같은 메타데이터들이 별도 파일이 아닌, 트랜잭션을 지원하는 InnoDB 스토이지 엔진에 테이블 형태로 저장되도록 개선되었다. 

- 스키마 정보가 파일로 저장되었을 경우에는 중간에 파일 쓰다가 에러나면 문제가 될수 있는데, 스키마 변경 작업중에 MySQL 서버가 비정상적으로 종료되더라도 스키마 변경이 완전한 성공 또는 완전한 실패로 정리 된다.

 

 

 

InnoDB 주요 특징

- 레코드 기반의 잠금을 지원하기에 높은 동시성 처리가 가능하고 이에 따른 안정성이 뛰어남. 

- 프라이머리키 ( PK )에 의해서 데이터가 클러스터링이된다. 

- 외래키를 지원 ( MyISAM )은 외래키 지원 X 

- MVCC를 구조( InnoDB의 버퍼풀, 언두로그, 데이터 파일(디스크))를 기반으로 격리성 수준 레벨의 잠금 없이 일관된 읽기 기능을 지원.

 - 자동 데드락 감지 기능과 장애 복구 기능이 들어가있음 

- 디스크의 쓰기 작업을 줄이기 위한 데이터를 메모리 ( InnoDB 버퍼풀)에 두었다가 한번에 메모리에 올리기 때문에 데이터 in-out 효율성이 높고, INSERT, UPDATE, DELETE에 대해서 트랜잭션 처리를 효율적으로 처리할 수 있음. 

- 언두로그 및 로그 버퍼 기능을 지원하고 

- 어댑티브 해시 인덱스를 제공함. 

1. 프라이머리 키 기반 클러스터링

InnoDB는 클러스터형 스토리지 엔진으로, 테이블의 모든 레코드는 프라이머리 키를 기준으로 정렬된 형태로 저장된다. 즉, 데이터가 저장되는 순서가 프라이머리 키의 값에 따라 결정된다.

여기서 클러스터링이란 비슷한 데이터를 묶어서 저장하는 방식이다. InnoDB에서는 이를 프라이머리 키 기준으로 판단한다. 따라서 숫자가 순차적으로 증가하는 PK를 사용하는 것이 성능에 유리하다.

모든 세컨더리 인덱스는 해당 레코드의 PK 값을 포함한다. 세컨더리 인덱스를 통해 검색하더라도 실제 레코드를 찾기 위해 다시 PK를 통해 클러스터링 인덱스를 조회해야 한다. 이 때문에 PK의 길이가 너무 길거나 비효율적이면 성능에 영향을 줄 수 있다.

 

2. 외래 키(Foreign Key) 지원

InnoDB는 MySQL에서 유일하게 외래 키를 지원하는 스토리지 엔진이다. 외래 키는 부모 테이블과 자식 테이블 간의 참조 관계를 명시하고, 이 관계를 자동으로 관리할 수 있게 한다.

외래 키가 설정되면 부모 테이블의 PK가 삭제되거나 수정될 때 자식 테이블의 관련 데이터도 자동으로 함께 삭제되거나 제한되도록 설정할 수 있다 (ON DELETE, ON UPDATE 옵션). 다만, 성능 상의 이유로 실무에서는 외래 키를 잘 사용하지 않는다. 이유는 다음과 같다.

• 부모/자식 테이블 양쪽 모두 인덱스가 반드시 필요하다.
• 데이터 변경 시 참조 무결성을 확인해야 하므로 추가적인 잠금이 발생한다.
• 이로 인해 데드락 발생 확률이 높아진다.
• 외래 키 없이도 JOIN을 통해 참조 무결성을 검증할 수 있다.

데이터 마이그레이션이나 테스트 환경에서 외래 키 제약 조건을 임시로 끌 수도 있다.

SET foreign_key_checks=OFF; -- 비활성화
SET foreign_key_checks=ON; -- 활성화

 

3. MVCC (Multi Version Concurrency Control)

InnoDB는 잠금 없이 일관된 읽기를 제공하기 위해 MVCC 구조를 채택한다. MVCC는 하나의 레코드에 대해 여러 버전을 유지하며, 각 트랜잭션은 자신만의 일관된 데이터 스냅샷을 읽는다.

이 구조를 통해 읽기 작업은 쓰기 작업과 충돌하지 않고 수행될 수 있다. MVCC는 언두 로그, 버퍼 풀, 트랜잭션 ID를 조합하여 구현된다.

트랜잭션 격리 수준에 따라 데이터의 가시성이 달라지며, InnoDB의 기본 격리 수준은 REPEATABLE READ다. 

• READ UNCOMMITTED: 커밋되지 않은 데이터도 읽을 수 있다.
• READ COMMITTED: 커밋된 데이터만 읽을 수 있다.
• REPEATABLE READ: 동일 트랜잭션 내에서 항상 같은 데이터를 읽는다.
• SERIALIZABLE: 가장 엄격한 격리 수준으로 완전한 테이블 락이 필요하다.

MVCC의 핵심은 언두 로그다. 언두 로그에는 레코드 변경 이전의 값이 저장되며, 트랜잭션 롤백이나 과거 시점의 데이터를 읽기 위해 사용된다.

---

4. 자동 데드락 감지

InnoDB는 내부적으로 잠금 대기 상태를 그래프로 관리한다. 특정 트랜잭션이 잠금 획득을 기다리는 구조가 순환되면, 이를 데드락으로 간주한다.

데드락이 발생하면 InnoDB는 자동으로 감지하여 트랜잭션 중 하나를 강제로 롤백시킨다. 이때 선택 기준은 롤백 비용이다. 언두 로그의 크기가 작고 처리 비용이 적은 트랜잭션을 우선 롤백시킨다.

이러한 기능 덕분에 개발자가 직접 데드락을 감지하고 처리할 필요는 없지만, 가능한 데드락이 발생하지 않도록 트랜잭션 순서와 잠금 범위를 정리하는 것이 좋다.

---

5. 자동 장애 복구

InnoDB는 MySQL이 비정상적으로 종료되었을 때도 자동으로 복구할 수 있는 기능을 제공한다. 서버 재시작 시 리두 로그를 기반으로 복구 과정을 수행하고, 미완료된 트랜잭션은 롤백된다.

다만, 물리적인 디스크 손상이나 로그 파일 손상이 발생한 경우 자동 복구가 실패할 수 있다. 이 경우 innodb_force_recovery 파라미터를 사용하여 수동으로 복구 과정을 유도해야 한다.

 

innodb_force_recovery = 1 # 최소 단계부터 시작, 최대 6까지

 

복구가 불가능하다면 마지막 백업 데이터를 사용하여 복원해야 한다.

 

6. InnoDB 버퍼 풀(Buffer Pool)

 

버퍼 풀은 디스크 I/O를 줄이기 위한 핵심 메모리 캐시다. 디스크의 데이터 페이지 및 인덱스 페이지를 메모리에 캐시해두고, 이 공간에서 직접 읽고 쓸 수 있다.

쓰기 작업은 즉시 디스크에 반영되지 않고, 버퍼 풀에서 일괄 처리된다. 변경된 페이지는 "더티 페이지"로 분류되며, 나중에 플러시 작업을 통해 디스크로 저장된다.

버퍼 풀은 LRU(Latest Recently Used) 기반으로 관리되며, 실제로는 Old 리스트와 New 리스트로 구분된다. 이는 일회성 대용량 쿼리로 인해 자주 사용하는 데이터가 캐시에서 밀려나지 않도록 하기 위한 구조다.

버퍼 풀의 크기는 innodb_buffer_pool_size로 조정할 수 있으며, 보통 전체 메모리의 50~70% 수준으로 설정한다.

 

7. 더블 라이트 버퍼(Double Write Buffer)

 

디스크에 데이터를 기록할 때 일부만 기록되어 발생하는 문제를 Partial Page Write, 또는 Torn Page라고 한다. InnoDB는 이를 방지하기 위해 더블 라이트 버퍼를 사용한다.

쓰기 작업은 먼저 더블 라이트 버퍼 영역에 기록되고, 문제가 없을 경우 실제 데이터 파일로 복사된다. 문제가 생기면 버퍼에 남아있는 데이터를 사용해 복구할 수 있다. 이는 신뢰성을 높이기 위한 추가적인 보호 장치다.

 

8. 언두 로그(Undo Log)

 

언두 로그는 레코드를 변경하기 전의 상태를 기록하는 공간이다. 주요 용도는 다음과 같다.

1. 롤백 처리: 트랜잭션 도중 오류가 발생하면 변경 전 상태로 되돌리기 위해 사용된다.
2. MVCC 구현: 트랜잭션 간 일관된 읽기를 제공하기 위해 과거 버전의 데이터를 조회할 수 있게 한다.

언두 로그는 트랜잭션 커밋 이후에도 일정 시간 유지되며, 사용자가 트랜잭션을 커밋했더라도 다른 트랜잭션에서 아직 해당 데이터를 읽고 있다면 삭제되지 않는다.

 

9. 체인지 버퍼(Change Buffer)

 

인덱스를 가진 테이블에 대한 INSERT, DELETE, UPDATE 작업은 인덱스 페이지에 랜덤 I/O를 발생시킨다. 이때 디스크 접근을 줄이기 위해 InnoDB는 체인지 버퍼를 사용한다.

체인지 버퍼는 보조 인덱스에 대한 변경사항을 메모리에 먼저 저장하고, 나중에 디스크에 반영한다. 이 방식은 트랜잭션 처리 성능을 크게 향상시킬 수 있다.

 

10. 리두 로그(Redo Log)

 

리두 로그는 데이터베이스의 영속성을 보장하는 핵심 로그 파일이다. 트랜잭션이 커밋되면 해당 내용은 리두 로그에 먼저 기록된다.

이후 디스크의 실제 데이터 파일에 변경사항이 반영되지 않았더라도, 리두 로그를 사용하면 장애 발생 시 작업을 복원할 수 있다.

트랜잭션 커밋 시점에 리두 로그에 기록되면 "성공"으로 간주되며, 이후의 플러시 작업은 백그라운드에서 처리된다.

 

11. 어댑티브 해시 인덱스

 

InnoDB는 자주 사용되는 인덱스 접근 패턴을 감지하여 자동으로 해시 인덱스를 생성한다. 이를 어댑티브 해시 인덱스라고 한다.

B-Tree 구조를 타지 않고 해시값으로 직접 검색할 수 있기 때문에 쿼리 처리 속도가 개선된다. 단, 해시 인덱스는 메모리 기반이며, 인덱스 생성/삭제 자체가 오버헤드가 될 수 있다. 따라서 모든 경우에 유리한 것은 아니다.