MySQL 아키텍처

MySQL의 구조

---

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분한다.

MYSQL 엔진

클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러, SQL 파서 및 전처리기, 그리고 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다. 성능 향상을 위해 캐시나 버퍼 풀과 같은 보조 저장소 기능도 포함돼 있다.

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어 오는 역할을 한다. 여러 가지 스토리지 엔진을 사용할 수 있다. 

CREATE TABLE test_table (col1 INT, col2 INT) ENGINE = INNODB

위와 같이 테이블을 생성하면 해당 테이블의 CRUD 작업은 InnoDB 스토리지 엔진이 처리한다.

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽을 때, 각 스토리지 엔진에 요청하게 되며, 이러한 요청을 핸들러(Handler) 요청이라고 한다. 여기서 사용되는 API를 핸들러 API 라고 한다.

 

MySQL 스레딩 구조

---

MySQL은 스레드 기반으로 동작한다. 포그라운드 스레드와 백그라운드 스레드로 구분된다.

포그라운드 스레드 (클라이언트 스레드)

최소 서버에 접속한 클라이언트 수만큼 존재한다. 각 사용자가 요청한 쿼리 문장을 수행하는 역할이다.

백그라운드 스레드

MyISAM에는 없지만, InnoDB에는 아래와 같이 백그라운드로 처리되는 것들이 있다.

• 인서트 버퍼(Insert Buffer)를 병합하는 스레드
• 로그를 디스크로 기록하는 스레드
• InnoDB 버퍼 풀의 데이터를 디스크로 기록하는 스레드
• 데이터를 버퍼로 읽어 오는 스레드
• 잠금이나 데드락을 모니터링하는 스레드

메모리 할당 및 사용 구조

---

MySQL에서는 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분한다.

글로벌 메모리 영역

MySQL 서버가 시작되면서 생성되는 영역이다. 클라이언트 스레드 수와 무관하게 일반적으로 하나의 메모리 공간만 할당된다.

로컬 메모리 영역

클라이언트 스레드가 쿼리를 처리 하는데 사용하는 메모리 영역이다. 스레드 별로 독립적으로 사용된다. 즉, 다른 스레드와 공유되지 않는다. 커넥션 버퍼와 정렬(sort) 버퍼 등이 있다. 

쿼리 실행 과정

---

1. 쿼리 파서(Parser) : 
2. 전처리기(Preprocessor)
3. 쿼리 옵티마이저(Query Optimizer)
4. 쿼리 실행기(Query Execution)
5. 핸들러(스토리지 엔진)

 

• 파서 : 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업. 여기서 쿼리 문장의 기본 문법 오류가 발견됨.
• 전처리기 : 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점을 확인. 토큰을 테이블 이름, 칼럼 이름, 내장 함수 등을 매핑해서 해당 객체의 존재 여부와 접근 권한 등을 확인.
• 옵티마이저 : 쿼리 문장을 어떻게 저렴한 비용으로 가장 빠르게 처리할지 결정하고, 실행계획 작성을 담당.
• 실행 엔진 : 실행 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할.
• 핸들러(스토리지 엔진) : MySQL 서버의 가장 밑단에서 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어오는 역할.

복제

---

복제는 2대 이상의 MySQL 서버가 동일한 데이터를 담도록 실시간으로 동기화하는 기술이다. 보통 읽기, 쓰기 모두 가능한 마스터 서버(Primary)와 읽기만 가능한 슬레이브 서버(Secondary)로 나뉜다. 

마스터

마스터 역할의 서버는 복제를 위해서 MySQL 바이너리 로그를 활성화해서 남겨야 한다. 바이너리 로그에는 DML(데이터를 조작하는 문장)과 DDL(스키마를 변경하는 문장)이 기록된다. 슬레이브 서버에서 변경 내역을 요청하면 해당 로그를 읽어 슬레이브로 넘긴다.

슬레이브

슬레이브 역할의 서버에서는 마스터 서버에 접속할 정보(IP, PORT, 계정)를 가지고 있으면 된다. 마스터 역할의 서버로 데이터 동기화 요청을 하고 받아온 내역을 릴레이 로그에 기록하고, SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 데이터를 동기화한다.

 

주의점

• 하나의 슬레이브는 하나의 마스터만 설정 가능
• 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정
• 슬레이브 서버는 마스터와 동일한 사양이 적합
• 복제가 불필요한 경우 바이너리 로그 중지

InnoDB 스토리지 엔진

---

InnoDB는 여러 스토리지 엔진 중, 유일하게 레코드 기반의 락(Lock)을 제공한다. 이로 인해 높은 동시성 처리가 가능하고 안정적이다. 

InnoDB 스토리지 엔진의 특성

• 테이블은 Primary Key 순서대로 디스크에 저장되어 Range scan이 빠름.
• MVCC를 이용하여 락을 걸지 않고 읽기 수행
• 외래 키 지원 : 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 대한 인덱스 생성이 필요해, 변경 시에는 반드시 부모 테이블과 자식 테이블의 데이터가 있는지 체크하는 작업이 필요하므로 잠금(Lock)이 전파된다. Lock이 여러 곳에 전파되면서 데드락 발생을 시킬 수 있다.

InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스를 메모리에 캐시해 두는 공간이다. (전체 물리 메모리의 50% ~ 80% 수준의 크기를 가진다.) 쿼리로 인해 변경 되어야 할 레코드들이 디스크 이곳저곳에 있을 때, 메모리에 모아서 한 번에 디스크 작업을 발생시키도록 쓰기 작업을 지연하는 역할도 한다. 

언두 로그

UPDATE, DELETE 문과 같이 데이터를 변경하는 쿼리로 데이터를 변경했을 때 변경되기 이전 데이터를 보관하는 공간이다. 롤백이나 트랜잭션 격리 수준에 따라 대응하기 위해 사용된다.

인서트 버퍼

INSERT, UPDATE 등 데이터 파일을 변경할 때는 데이터뿐만 아니라 연관된 인덱스를 변경하는 작업이 필요하다. 인덱스를 변경하는 작업은 랜덤 디스크를 읽는 작업이 필요하므로 자원 소모가 큰데, 이를 최적화하기 위해 InnoDB가 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트하지만 변경해야 할 인덱스 페이지가 버퍼 풀에 없는 경우는 디스크로부터 읽어와서 버퍼 풀에 적재하고 인덱스를 업데이트를 해야 하는데 이때, 즉시 업데이트하지 않고 임시 공간에 저장해둔다. 이 임시 공간이 인서트 버퍼다.

리두 로그 및 로그 버퍼

InnoDB 버퍼 풀만으로는 데이터베이스의 ACID를 보장할 수 없는데 이를 위해서 순차적으로 디스크에 기록하는 로그 파일인 리두 로그가 존재한다.

MVCC (Multi Version Concurrency Control)

목적은 Lock(잠금)을 사용하지 않는 일관된 읽기를 하는 데 있다. 하나의 레코드에 대해 여러 버전으로 관리하는 방식을 MVCC라 한다. MySQL은 레코드를 여러 버전으로 관리하기 위해 트랜잭션의 커밋/롤백 여부와 상관없이 변경 이전 레코드의 데이터를 Undo 로그에 남겨둔다.

 

Undo 로그를 통해 여러 버전으로 관리하면 격리 수준에 따라 Lock(잠금)을 사용하지 않고 일관된 조회가 가능하다.

• READ_UNCOMMITED : InnoDB 버퍼 풀이나 데이터 파일로부터 변경된 최신 버전의 레코드를 읽어서 반환
• READ_COMMITED 이상 : 메모리 영역인 Undo 로그에 저장된 레코드 값으로 레코드를 읽어서 반환

참조

---

https://www.youtube.com/watch?v=vQFGBZemJLQ&t=439s 

MySQL 아키텍처 (MySQL 엔진 구조, 스토리지 엔진 구조, 스레드 구조, 메모리 구조, 쿼리 실행 구조, R

MySQL - 아키텍처 정리 (1) - Real MySQL 8.0

[Real MySQL] 3. 아키텍처

MySQL :: MySQL 8.0 Reference Manual :: 15.1.1 Benefits of Using InnoDB Tables

MySQL 아키텍처 (MySQL 엔진 구조, 스토리지 엔진 구조, 스레드 구조, 메모리 구조, 쿼리 실행 구조, R