[MySQL] 아키텍처

MySQL 엔진 아키텍처

---

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

MySQL 엔진

MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.

또한, MySQL은 표준 SQL(ANSI SQL) 문법을 지원하므로 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.

스토리지 엔진

요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어온다.

MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
아래와 같이 테이블이 사용할 스토리지 엔진을 지정하면 이후 해당 테이블의 모든 읽기 작업이나 변경 작업은 정의된 스토리지 엔진이 처리한다.

mysql> CREATE TABLE product (fd1 INT, fd2 INT) ENGINE=INNODB;

핸들러 API

MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 여기서 사용되는 API를 핸들러 API라고 한다. InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받는다.

핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는 지는 SHOW GLOBAL STATUS LIKE 'Handler%'; 명령으로 확인할 수 있다.

Mac 유저라면, 터미널에서 cd /usr/local/mysql/bin (MySQL 설치 경로로 이동 후) -> ./mysql -u root -p (루트 계정으로 접속) 을 거치면 MySQL 접속이 가능하다.

MySQL 스레딩 구조

---

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 크게 포그라운드(Foreground) 스레드와 백그라운드(Background) 스레드로 구분할 수 있다.

실행 중인 스레드의 목록은 아래와 같이 performance_schema 데이터베이스의 threads 테이블을 통해 확인할 수 있다.

SELECT thread_id, name, type, processlist_user, processlist_host FROM performance_schema.threads ORDER BY type, thread_id;

위 테이블에서 thread/sql/one_connection 스레드만 실제 사용자의 요청을 처리하는 포그라운드 스레드다. 백그라운드 스레드의 개수는 MySQL 서버의 설정에 따라 가변적일 수 있다. 동일한 이름의 스레드가 2개 이상씩 보이는 것은 MySQL 서버의 설정 내용에 의해 여러 스레드가 동일 작업을 병렬로 처리하는 경우이다.

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

포그라운드 스레드는 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.

또한, 데이터를 MySQL의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.

백그라운드 스레드

InnoDB는 아래와 같이 여러 가지 작업이 백그라운드에서 처리된다.

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

InnoDB는 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있다. 때문에 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때 까지 기다리지 않고 다른 작업을 이어나갈 수 있다. 하지만 MyISAM에서 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없다.

메모리 할당 및 사용 구조

---

글로벌 메모리 영역과 로컬 메모리 영역은 MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분된다.

글로벌 메모리 영역

클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다.

필요에 의해 2개 이상의 메모리 공간을 할당받을 수도 있지만 클라이언트 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유된다.

대표적인 글로벌 메모리 영역은 아래와 같다.

• 테이블 캐시
• InnoDB 버퍼 풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

로컬 메모리 영역

세션 메모리 영역이라고도 표현하며, MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역이다.

MySQL 서버는 클라이언트가 접속하면 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 이 스레드는 각 클라이언트별로 독립적으로 할당되며 절대 공유되지 않는다는 특징이 있다.

로컬 메모리 공간의 또 한 가지 중요한 특징은 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있다는 점이다. 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간(커넥션 버퍼나 결과 버퍼)도 있고, 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(소트 버퍼나 조인 버퍼)도 있다.

대표적인 로컬 메모리 영역은 아래와 같다.

• 정렬 버퍼(Sort buffer)
• 조인 버퍼
• 바이너리 로그 캐시
• 네트워크 버퍼

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

---

MySQL은 이미 기본적으로 많은 스토리지 엔진을 가지고 있다. 기본적으로 제공되는 스토리지 엔진 이외에 부가적인 기능을 더 제공하는 스토리지 엔진이 필요하다면, 필요한 요건을 기초로 다른 전문 개발 회사 또는 사용자가 직접 스토리지 엔진을 개발하는 것도 가능하다.

MySQL에서 쿼리가 실행되는 과정에서 거의 대부분의 작업이 MySQL 엔진에서 처리되고, 마지막 "데이터읽기/쓰기" 작업만 스토리지 엔진에 의해 처리된다. 이는 곧, 사용자가 새로운 용도의 스토리지 엔진을 만든다 하더라도 DBMS의 전체 기능이 아닌 일부분의 기능만 수행하는 엔진을 작성하게 된다는 의미이기도 하다.

컴포넌트

---

MySQL 8.0부터는 아래의 기존 MySQL 서버의 플러그인 단점을 보완해서 컴포넌트로 구현됐다.

• 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음
• 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음(캡슐화 안 됨)
• 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움

쿼리 실행 구조

---

위 그림은 MySQL이 쿼리를 실행하는 구조를 그림으로 표현한 것이며, 아래와 같이 기능별로 나누어 볼 수 있다.

쿼리 파서

쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 한다. 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 된다.

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행한다.

옵티마이저

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리하기 위해 결정하는 역할을 담당하며, DBMS의 두뇌에 해당한다고 볼 수 있다.

실행 엔진

실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
아래는 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정한 예이다.

1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3. 읽어온 레코드들을 1번에서 준비한 임시 테이블에 저장하라고 다시 핸들러에게 요청
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에게 다시 요청
5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

핸들러 (스토리지 엔진)

MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요쳥에 따라 데이터를 디스크로 저장하고, 디스크로부터 읽어 오는 역할을 담당한다.

핸들러는 결국 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 MyISAM 스토리지 엔진이 핸들러가 되고, InnoDB 테이블을 조작하는 경우에는 InnoDB 스토리지 엔진이 핸들러가 된다.

쿼리 캐시

---

쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 데이터를 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였다. 하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야 했기 때문에 심각한 동시 처리 성능 저하를 유발했다.

결국 MySQL 8.0으로 올라오면서 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되고, 관련된 시스템 변수도 모두 제거 됐다.

Reference
백은빈, 이성욱. 『Real MySQL 8.0』. 위키북스, 2022.