MySQL 엔진 아키텍처

MySQL 전체 구조

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

MySQL 엔진

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

스토리지 엔진

• MySQL엔진은 요청된 SQL 문장을 분석하거나 최적화하는 등 DBMS의 두뇌 역할을 한다.

• 실제 데이터를 디스크에 저장하거나 디스크로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다.

• 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM)이나 InnoDB 버퍼 풀(Inno DB)과 같은 기능을 내장하고 있다.

핸들러 API

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

MySQL 스레딩 구조

• MySQL 서버는 스레드 기반이다.

• 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.

• performance_scheme 데이터베이스의 threads 테이블을 통해 실행 중인 스레드의 목록을 확인할 수 있다.

• thread/sql/one_connection 스레드는 실제 사용자의 요청을 처리하는 포그라운드 스레드다.

• 백그라운드 스레드의 개수는 MySQL 서버의 설정에 따라 가변적이다.

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

• 포그라운드 스레드는 최소환 MySQL 서버에 접속된 클라이언트의 수만큼 존재한다.

• 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.

• 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 되돌아간다.

• 이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 캐시에 존재하게 한다.

• 이 스레드 캐시의 최대 스레드 개수는 시스템 변수로 설정할 수 있다.

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

• InnoDB의 경우 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB의 경우 여러가지 작업이 백그라운드로 처리된다.

• 인서트 버퍼를 병합하는 스레드

• 로그를 디스크로 기록하는 스레드

• InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드

• 데이터를 버퍼로 읽어 오는 스레드

• 잠금이나 데드락을 모니터링하는 스레드

• 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없다.

• 쓰기 스레드는 아주 많은 작업을 백그라운드에서 처리하기 때문에 2개 이상의 개수로 설정하는 것이 좋다. (innodb_write_io_threads 시스템 변수로 스레드 개수 설정)

• 사용자의 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만, 읽기 작업은 절대 지연될 수 없다.

• 그래서 일반적인 상용 DMBS에는 대부분 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 있으며 InnoDB 또한 이러한 방식으로 처리한다. 이러한 이유로 InnoDB에서는 insert, update, delete 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장 될 때까지 기다리지 않아도 된다.

메모리 할당 및 사용 구조

• MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역, 로컬 메모리 영역으로 구분

• 글로벌 메모리 영역은 MySQL서버가 시작되면서 운영체제로부터 할당된다. (MySQL의 시스템 변수로 설정해 둔 만큼 운영체제로부터 메모리를 할당받는다.)

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

글로벌 메모리 영역

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

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

대표적인 글로벌 메모리 영역

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

로컬 메모리 영역

• 세션 메모리 영역이라고도 한다.

• MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.

• 클라이언트가 MySQL서버에 접속하면 MySQL 서버에서는 클라이언트 커넥션으로부터의 요청을 처리하기 위해 스레드를 하나씩 할당하게 되는데, 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다.

• 클라이언트와 MySQL 서버와의 커넥션을 세션이라고 하기 때문에 세션 메모리 영역이라고도 한다.

• 로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다.

• 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 MySQL이 메모리 공간을 할당조차도 하지 않을 수도 있다.

• 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간도 있고(커넥션 버퍼, 결과 버퍼) 쿼리를 실행하는 순간에만 할당했다가 다시 해제는 공간(소트버퍼, 조인버퍼)도 있다.

대표적인 로컬 메모리 영역

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

MySQL 엔진과 스토리지 엔진의 처리 영역

SQL 파서(MySQL엔진) -> SQL 옵티마이저(MySQL엔진) -> SQL 실행기(MySQL엔진) -> 데이터 읽기/쓰기(스토리지) -> 디스크

쿼리 실행 구조

쿼리 실행 구조

SQL 요청 -> 쿼리 파서 -> 전처리기 -> 옵티마이저 -> 쿼리 실행기 -> 스토리지 엔진

쿼리 파서

• 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위)으로 분리해 트리 형태의 구조로 만들어 낸다.

• 쿼리 문장의 문법 오류는 이 과정에서 발견된다.

전처리기

• 파서에서 만들어진 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.

• 각 토큰을 테이블 이름, 칼럼, 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인한다.

옵티마이저

• 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할이다.

• DBMS의 두뇌에 해당한다.

실행 엔진(쿼리 실행기)

• 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발.

• 하는일 예시 - group by를 처리하기 위해 옵티마이저가 임시 테이블을 사용하기로 결정한 상황

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

핸들러(스토리지 엔진)

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

• 핸들러는 결국 스토리지 엔진을 의미한다.