MySQL 구조 및 동작 흐름 (Real MySQL 8.0 - 4장 아키텍처)

 

요약

---

MySQL은 접속 클라이언트, 엔진, 스토리지 엔진으로 구분할 수 있다.

엔진에서는 SQL문장을 분석하고 검증하고 최적화한 뒤 스토리지 엔진을 통해 데이터를 읽고 쓴다.

스토리지 엔진은 실제 디스크에 접근해 데이터를 읽고 쓰는 역할을 수행한다. InnoDB, MyISAM 등을 선택할 수 있다.

InnoDB는 기본으로 선택되는 스토리지 엔진이며, 버퍼풀, 언두 로그 등을 통해 MVCC 를 제공한다.

 

 

 

📕 주요 용어 정리

---

의사소통의 장애물을 가장 먼저 제거해봅시다!!

https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

 

• MySQL 엔진 : MySQL 두뇌에 해당. 쿼리 파싱, 최적화, 실행계획 생성 등을 담당. 운전자.
• MySQL 스토리지 엔진 : MySQL 손, 발에 해당. 디스크에 읽고 쓰기를 담당. 자동차. 운전자에게 핸들을 제공함.
• 핸들러 : MySQL 스토리지 엔진이 MySQL 엔진을 위해 열어 놓은 API. 조작하기 위한 핸들. 스토리지 엔진 자체를 이르기도 함.
• InnoDB, MyISAM, MEMORY 등 : MySQL 스토리지 엔진의 일종. 테이블마다 스토리지 엔진을 선택할 수 있으나 InnoDB가 압도적이며 기본값.
• MySQL 서버 : MySQL 엔진과 MySQL 스토리지 엔진을 아울러 이르는 말.
• 사용자 스레드 : 연결 요청이 왔을 때 처리를 위해 할당하는 스레드. 포그라운드 스레드이며 다른 스레드와 독립적.
• 백그라운드 스레드 : MyISAM은 사용자 스레드가 쓰기 스레드 역할까지 담당해서 응답 시간이 늦음. InnoDB는 백그라운드 스레드가 쓰기를 담당함.
• 글로벌 메모리 : 글로벌 메모리는 시스템 변수에 설정된 만큼 OS로부터 할당받는 모든 스레드가 공유하는 공간이다. 할당 시 주의 필요. 테이블 캐시, InnoDB 버퍼 풀, InnoDB 리두 로그 버퍼
• 로컬 메모리(세션 메모리) : 사용자 스레드가 할당되어 요청을 처리하는 과정에서 스레드마다 독립적으로 할당되는 공간.
• 플러그인 : MySQL 서버 전체에 적용 가능한 기능. 완전히 새로운 기능을 플러그인을 이용해 구현 가능함.
• 컴포넌트 : 플러그인과 비슷한 역할을 하나, 플러그인의 단점을 극복. 플러그인은 플러그인끼리 통신 못하고, MySQL 서버의 변수와 함수를 직접 호출하기에 캡슐화를 위배하고, 상호 의존 설정이 불가해 초기화가 어려움.
• 토큰 : MySQL이 인식 가능한 최소단위의 어휘, 기호
• 쿼리 파서 : 기본 문법 오류 검증. SQL문장을 토큰으로 분리해서 트리 형태 구조로 만들어냄.
• 전처리기 : 토큰에 사용된 테이블 이름, 칼럼 이름, 내장 함수 등이 실제로 존재하는지, 접근 권한이 있는지 검증.
• 옵티마이저 : DBMS의 두뇌. 쿼리 최적화를 담당. 어떤 기준으로 최적화를 위한 선택을 하는지, 어떻게 이를 도울지 배우게 될 예정.
• 실행 엔진 : 스토리지 엔진이 제공하는 핸들러 API를 호출하고, 결과를 다음 핸들러 API를 호출하며 전달하는 등 요청과 결과를 연결시킨다.
• 쿼리 캐시 : 빠른 응답이 필요한 환경에서 SQL 실행 결과를 메모리에 캐시했다 빠르게 응답하는 용도. 캐싱 유지 및 관리 비용으로 인한 성능 저하로 인해 8에서 제거.
• InnoDB : 독보적인 스토리지 엔진. MVCC를 지원하여 높은 동시성 처리가 가능함.
• MVCC : Multi Version Concurrency Control. 버퍼 풀, 언두 로그를 이용해 격리 수준에 따른 읽기 기능 제공. 이에 따라 No-Locking Consistent Read가 가능.
• 버퍼 풀 : InnoDB 스토리지 엔진 중 가장 핵심적인 부분. 쓰기 작업을 지연하고 일괄 처리해 디스크 작업 횟수를 줄인다.
• 언두 로그 : UPDATE 실행 시, 이전 버전은 언두 로그에 기록되고, 새 요청은 버퍼 풀에 저장된다. 트랜잭션 완료 시 언두 로그 내 내용은 제거된다. 트랜잭션 롤백이 수행될 경우, 언두 로그에 있는 값을 이용해 복구한다. 언두 로그가 장시간 유지되는 경우 성능에 좋지 않아 급증 여부를 모니터링하는 것이 좋다. 비정상적 DB 요청이 많이 오거나 처리되고 있는지 확인할 수 있는 기준이 될 수 있다.
• READ_UNCOMMITTED : 아직 반영되지 않은 수정본을 보여줌. 즉, 버퍼 풀에만 있고 디스크에는 반영되지 않은, 커밋되지 않은 변경분이 반환된다.
• READ_COMMITTED : 최종 커밋본을 보여줌. 즉, UPDATE 요청이 처리중이고 아직 커밋되지 않았을 경우, 언두 로그에 있는 마지막 커밋 버전을 반환한다.
• No-Locking Consistent Read : 별도의 메모리 공간인 버퍼 풀과 언두 로그를 이용하기 때문에 트랜잭션이 진행중이더라도 원하는 버전에 대한 읽기가 가능하다.
• 테이블 스페이스 : 언두 로그가 저장되는 곳 이르는 말이다. 커밋이 반영되면 여기에 저장되었던 임시 값이 불필요해지고, 이를 퍼지 스레드(Purge Thread)가 삭제한다.
• 퍼지 스레드 : 트랜잭션이 커밋되어 불필요해진 언두 테이블 스페이스를 제거하는 스레드. 주기적으로 수행되며 이 작업을 언두 퍼지(Undo Purge) 라고 이른다.
• 리두 로그 : 트랜잭션 수행 도중 시스템의 비정상 종료가 일어났을 때 이를 복구하기 위해, 요청 시작 시 요청 내용을 기록하는 공간이다. MySQL 서버가 새로 기동될 때 리두 로그를 확인해서 반영되지 않은 데이터가 있는지 검사한다.

 

 

 

🍰 MySQL에 적용된 레이어드 아키텍처

---

MySQL에도 레이어드 아키텍처가?!

 

Utility Layer

외부의 요청을 처리하기 위해 제공되는 MySQL Connectors가 제공됩니다.
MySQL Connectors에 전달된 요청은 다시 MySQL 엔진에게 전달됩니다.
레이어드 아키텍처 관점에서 이러한 MySQL Connectors를 Utility Layer 라고 부르기도 합니다.

 

SQL Layer

MySQL 엔진에 요청이 전달되면, 가장 먼저 쿼리 파서가 문법적 오류를 검증합니다.
그 뒤엔 전처리기가 컬럼명, 테이블명 등이 실제로 존재하는지, 접근 권한이 있는지 검증합니다.
이후엔 옵티마이저가 쿼리 최적화를 수행합니다.
그 뒤엔 실행 엔진이 스토리지 엔진이 제공하는 핸들러 API를 호출하여 쿼리를 실제로 수행합니다.
이러한 일련의 과정이 일어나는 MySQL 엔진을 SQL Layer 라고 부르기도 합니다.

 

Storage Engine Layer

스토리지 엔진은 실제 디스크에 접근해 읽고 쓰는 역할을 담당합니다.
MySQL 엔진에게 핸들러 API를 제공하여 쿼리를 수행할 수 있게 해줍니다.
MySQL 8.0 버전에선 InnoDB가 기본 스토리지 엔진으로 사용됩니다.

 

 

 

🕵️‍♀️ MySQL에서 스프링이 보인다!

---

스프링에서 접했던 설계의 원칙들이, 비단 웹 애플리케이션에서만 적용되는 원칙이 아니었을지도?!

 

이렇게 레이어별로 관심사를 분리해둔 MySQL의 아키텍처는
Spring 에서 자주 보았던 레이어드 아키텍처와도 유사해보이는 지점이 있습니다.

 

첫째로 레이어별로 관심사를 분리하여 느슨한 결합을 가져간 지점이 보인다는 것입니다.

InnoDB 이외의 스토리지 엔진을 선택할 일이 거의 없어보이긴 하지만,
스토리지 엔진은 핸들러 API를 제공하고, MySQL 엔진은 이러한 핸들러 API를 호출하는 구조입니다.
그렇기에 스토리지 엔진이 달라질 수 있는 구조로 보입니다.
또한 MySQL 엔진은 가장 핵심적인 쿼리를 분석하고 최적화하는 역할만 담당하고,
실제 디스크에 접근해 데이터를 읽고 쓰는 역할은 스토리지 엔진에게 위임합니다.
이는 마치 Repository 패턴에서 나타나는 Repository와 DAO의 관계와 유사해보입니다.

둘째로 하나의 요청에 대해 하나의 스레드를 할당해 처리한다는 점입니다.

스프링에서는 하나의 Http Request에 대해 하나의 스레드를 할당해 처리합니다.
컨트롤러 앞에서 인터셉터와 아규먼트 리졸버가 기본적인 요청의 유효성 검증을 처리하고,
컨트롤러는 요청을 처리할 주체에게 메시지를 전달합니다.

MySQL 에서도 하나의 요청에 대해 하나의 사용자 스레드를 할당해 처리합니다.
쿼리 파서와 전처리기에서 SQL 요청의 유효성 검증을 처리합니다.

 

다른 점을 살펴보자면, 스프링의 Http Request는
데이터베이스 처리까지 완료된 이후에 응답이 가능한데요,

MySQL의 요청은 InnoDB를 사용한다는 가정하에,
디스크에 쓰기 작업은 완료되기를 기다리지 않고 응답이 바로 진행됩니다.

디스크에 접근하는 비용이 크기 때문에,
디스크에 쓰는 작업은 백그라운드 스레드를 이용해 쓰기 지연 및 일괄 처리하기 때문입니다.

 

 

 

🏊 하나의 SQL문이 응답되기까지

---

MySQL 8.0 기준, SQL 요청이 왔을 때 처리 완료되기까지의 전반적 흐름을 훑어봅시다!

 

1. MySQL Connectors에 요청이 도착합니다.

2. 사용자 스레드가 할당되고 요청이 MySQL 엔진에 전달됩니다.

3. 토큰 파서가 SQL을 MySQL이 이해 가능한 최소 단위로 잘라내고, 문법 유효성을 검증합니다.

4. 전처리기가 컬럼명, 테이블명 등이 존재하는지 확인하고, 접근 권한이 있는지 검증합니다.

5. 옵티마이저가 사용자가 전달한 SQL문을 어떻게 실행해야 효율적일지 결정합니다.

6. 실행 엔진이 핸들러 API(스토리지 엔진, 다수의 경우 InnoDB)를 호출하고 결과를 가지고 다시 호출하며 흐름을 제어합니다.

7. 결과를 반환합니다.

8. 백그라운드 스레드에서 커밋되었으나 디스크에 반영되지 않은 내용을 디스크에 접근하여 일괄 처리합니다.

 

비어있는 부분이 많습니다만 우선 개괄적으로만 이해해보고자 작성해봅니다 ^^;

 

 

 

📈 📉 MVCC : Multi Version Concurrency Control

---

레코드 레벨의 트랜잭션이 지원된다, 하나의 레코드에 여러 버전이 동시에 관리된다 라는 개념을 이해해봅시다!

시나리오

UPDATE 요청이 왔다고 가정해볼게요!
요청을 처리할 내용들이 리두 로그에 기록됩니다.
트랜잭션이 생성되고, 버퍼 풀에 요청을 처리할 내용이 기록됩니다.
언두 로그에는 UPDATE 요청이 오기 전, 마지막 COMMIT 버전이 보관됩니다.
트랜잭션이 완료되고, 커밋이 수행되면, 버퍼 풀에 있는 내용이 디스크에 반영됩니다.

 

READ_UNCOMMITTED, READ_COMMITTED

이러한 과정이 처리되는 도중, COMMIT이 완료되기 이전에 SELECT 조회 요청이 오면 어떻게 될까요?

아직 디스크에 반영되지 않았으나 UPDATE 요청이 처리되고 있는 버퍼 풀의 내용을 반환해야 할까요
아니면 최종 COMMIT 버전인 언두 로그에 있는 내용을 반환해야 할까요?

버퍼 풀의 내용을 반환하는 것이 READ_UNCOMMITTED 설정이고,
언두 로그에 있는 내용을 반환하는 것이 READ_COMMITTED 설정입니다.

이처럼 두가지 버전을 동시에 관리할 수 있고, 설정에 따라 다른 값을 보여주기에
이를 MVCC라고 부릅니다.
또한 이러한 기능을 InnoDB가 제공하기 때문에, 테이블 단위의 락을 걸지 않아도 되어서
No-Locking Consistent Read가 가능하다 라고 이야기 합니다.

롤백이 수행된다면 트랜잭션 진행 중 롤백이 수행된다면, 언두 로그에 있는 값을 이용해 복원을 진행합니다.

 

리두 로그를 통한 복원

리두 로그는 MySQL이 재실행될 때 검사합니다.

마지막 COMMIT과 리두 로그의 내용이 일치하는지 검사하여,
불일치한다면 리두 로그를 기반하여 복구를 진행합니다.

트랜잭션은 정상 처리 완료되어 COMMIT 으로는 반영해야 한다고 선언되었으나,
백그라운드 스레드가 디스크에 반영하기 이전에 서버가 비정상적으로 종료되었다가 재실행된다면
이때 리두 로그를 통해 복구가 진행됩니다.

 

 

 

✨ 스터디에서 다뤘던 화두들

---

함께 스터디하는 크루들과 나눴던 이야기들을 기록해봅니다!

 

FK를 써야할까?

• CASECADE로 삭제하더라도 자바 코드 단위에서 삭제되는 걸 명시해야 객체지향적인 거 같다.
• 데이터 정합성이 중요한 도메인에선 써야할 것 같다
• 사람의 정합성을 믿을 수 없다
• FK라면 인덱싱이 되기 때문에 MySQL 엔진에게 힌트를 주어 성능 최적화를 꾀할 수도 있을 것 같다
• MyISAM이 FK를 지원 안 하는 게 아니라 SET foreign_key_checks = OFF; 와 같은 편의 기능을 제공하지 않는다.

 

장바구니 미션에서 orders와 orders_detail 테이블은 왜 나눠져있을까?

• 테이블도 객체처럼 최소한의 역할만 가지게 설계하고, 이들이 협력하도록 구성한다.
• 조회 편의성과 중복 데이터를 줄인다는 측면에서 테이블이 나눠질 이유가 있다.
• 정규화에 대해서 추후 자세히 다뤄보자.

 

쿼리 캐시는 어떨 때 쓸 수 있을까?

• 쿼리 캐시는 8버전부터 성능 및 오류 발생 등의 이유로 제거되었지만, 특수한 경우 사용을 고려할 수 있다고 한다.
• 사전 처럼 데이터의 변경이 극단적으로 없는 경우에는 쿼리 캐시의 사용을 고려해볼 수 있을 것 같다.

 

언두 로그, 리두 로그

• CUD 요청이 오면, 리두 로그에 요청 내용을 저장한다.
• UD 요청일 경우, 버퍼 풀에는 이번 요청 결과를, 언두 로그에는 기존 버전을 저장한다.
• 커밋이 완료되면 버퍼 풀에 있는 내용이 백그라운드 스레드에 의해 디스크에 저장된다.
• 커밋 없이 롤백이 진행되면 언두 로그에 저장된 내용을 이용해 복원한다.
• 백그라운드 스레드가 디스크에 저장하기 전에 서버가 내려가면 이슈가 발생한다.
• MySQL 재실행되면 리두 로그를 이용해 디스크에 반영되지 않은 내용을 복구한다.

 

 

 

🤗 소감

---

스프링에서 학습했던 설계 원리 중 일부 내용이 MySQL 설계에서도 발견되는 것 같아 신기하고 재밌었습니다.

MySQL 아키텍처라는 말만 듣고 정말 노잼일까봐 걱정이었는데 생각보다 어렵지 않고 정말 재밌었습니다.

이후로 다른 챕터를 공부하기 위한 기초 지식들을 쌓는 과정이었던 것 같습니다.

 

information_schema 의 ENGINE 테이블에서 사용 가능한 스토리지 엔진들을 확인할 수 있었는데,
이 중 트랜잭션을 지원하는 유일한 DB가 InnoDB였습니다.

 

 

학습 출처

---

Real MySQL 8.0 4장 아키텍처

[10분 테코톡] 🔫 우기의 MySQL 아키텍처

MySQL 8.0 Reference