[DB] MySQL 엔진 아키텍처 (Real MySQL 8.0)

안녕하세요. 회사와 함께 성장하고 싶은 KOSE입니다.

 

오늘은 MySQL의 엔진 아키텍처에 대해서 정리하는 글을 작성하고자 합니다.

 

1. MySQL 전체 구조도

MySQL은 MySQL 엔진과 스토리지 엔진으로 구성되어 있으며, 이를 MySQL 서버라고 부릅니다.

 

2. 구조도 요약

- MySQL 엔진 구성요소

• 커넥션 핸들러
• SQL 파서
• 전처리기
• 옵티마이저

- 스토리지 엔진

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

• 키 캐시 (MyISAM 스토리지 엔진)
• InnoDB 버퍼 풀

 

3. MySQL 스레딩 구조

 

MySQL 서버는 멀티스레드 프로그램입니다.

MySQL은 클라이언트 요청에 따라 스레드를 계속 생산하지 않고 정해진 개수를 상황에 따라 위임하여 처리합니다.

(과도한 멀티스레딩은 오히려 컨텍스트 스위칭 성능이 떨어뜨립니다 참고자료:  https://www.crocus.co.kr/1364) 

따라서, 멀티스레드 프로그램들은 스레드 풀(thread pool)을 이용해서  스레드의 개수를 제한합니다.

 

스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드의 개수를 줄여서 동시 처리되는 요청이 많더라도, MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 합니다.

서버는 요청이 들어올 때마다 새 스레드를 생성하는 대신, 스레드 풀에게 작업을 위임하게 되고

작업은 내부적으로 큐에 저장하고 스레드들은 큐에서 작업을 위임받아 처리합니다.

- MySQL은 스레드 기반으로 작동하며, 크게 포그라운드와 백그라운드 스레드로 구분할 수 있습니다.

 

 

포그라운드 스레드 

- 클라이언트 사용자가 요청하는 쿼리 문장을 처리

- InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리

 

InnoDB의 백그라운드 스레드

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

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

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

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

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

 

백그라운드 스레드가 디스크에 기록하는 역할을 수행하는 이유는, 사용자의 'select' 요청은 지연될 수가 없지만, insert, update, delete는 해당 요청을 처리하되 바로 데이터 파일에 접근하여 수정하지 않고 일정 시간의 지연을 가지며 일괄적으로 처리를 수행하도록 할 수 있습니다. (비정상적인 종료를 방지한 언두, 로그 기록 등 필수)

 

쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크를 사용할 때는 2 ~ 4 정도로 설정하는 것이 좋습니다.

 

4. 쿼리 실행 구조

 

- 쿼리 파서

쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰이 인식할 수 있는 최소 단위의 어휘나 기호로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미합니다. 쿼리 문장의 기본적인 문법 오류는 이 과정에서 발견됩니다.

selecr * from member;
-> select 에러

 

- 전처리기

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

각 토큰을 테이블 이름이나 칼럼 이름, 또는 내장함수와 같은 개체를 매핑해 해당 객체에 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행, 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러집니다.

select * from member;
-> SELECT command denied to user 'kose'@'localhosts' for table 'member'

 

- 옵티마이저

옵티마이저랑 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 수행

옵티마이저 튜닝에 따라 성능 차이가 발생할 수 있습니다.

 

- 실행 엔진

실행 엔진은 옵티마이저의 결과에 따라 만들어진 계획을 핸들러에게 요청, 요청 후 처리된 결과를 다른 핸들러의 요청의 입력으로 처리하는 중간 처리 단계 역할 수행합니다.

 

- 핸들러

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

 

- 쿼리 캐시

MySQL 서버에서 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했지만, 쿼리 캐시는 테이블 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야 합니다.

따라서, 심각한 동시 처리 성능 저하, 버그의 원인되어 쿼리 캐시는 MySQL 8.0에서 완전히 제거 

 

 

5. InnoDB 스토리지 엔진 아키텍처

프라이머리 키

- InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장됩니다.

- 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있습니다.

- 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다.

 

MVCC

- 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능 MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있습니다.

- InnoDB는 언두 로그를 이용해 이 기능을 구현하고, 하나의 레코드에 대해 여러 개의 버전이 동시에 관리됩니다. 

- Row 수준의 락을 제공하므로, 다중 동시성 제어를 효율적으로 수행할 수 있습니다.

- 격리 수준 READ_UNCOMMITED -> InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환하고

  READ_COMMITED -> 변경되기 이전의 내용을 가지고 있는 언두 영역의 데이터 /반환

 

어댑티브 해시 인덱스

- 어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스입니다.

- InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있습니다.

- 해시 인덱스는 인덱스 키 값과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리합니다.

- 인덱스 키 값은 B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값 조합으로 생성합니다.

 

언두로그

- InnoDB 스토리지 엔진은 트랜잭션 격리 수준을 보장하기 위해 DML 변경되기 이전 버전의 데이터를 별도로 백업합니다.

- 트랜잭션 롤백 대비용, 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공합니다.

 

 

체인지 버퍼

- RDMBS에서 레코드가 insert 되거나 update 될 때, 데이터 파일을 변경하는 작업과 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요합니다.

- 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모합니다.

- 버퍼 풀에 있다면 바로 업데이트를 수행하지만, 임시 공간에 값을 저장해 두고 사용자에게 결과를 반환합니다.

- 반드시 중복 여부를 체크해야하는 유니크 제약 조건 인덱스는 사용 불가합니다.

 

 

리두 로그

- 리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치입니다.

- MySQL 서버를 포함한 대부분 데이터베이스 서버는 데이터 변경 내용을 로그에 먼저 기록하고, 쓰기 비용이 낮은 자료 구조를 가진 리두 로그, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전 상태로 복구합니다.

 

6. MyISAM 아키텍처

정적인 데이터를 저장하고 자주 읽기 작업이 일어나는 테이블에 적합한 엔진입니다

 

- 구조가 단순하고 속도가 빠르고 데이터 저장에 실제적인 제한이 없고 매우 효율적으로 저장합니다.

- 테이블 작업시 특정 행을 수정하려고 하면 테이블 전체에 락이 걸려서  다른 사람이 작업할 수 없습니다.

- 트랜잭션에 대한 지원이 없기때문에 작업 도중의 에러 발생 시, 데이터 무결성이 깨질 수 있습니다.

 

7. MyISAM vs InnoDB 차이점 표 비교

 

 

참조: https://velog.io/@sweet_sumin/DB-%EC%8A%A4%ED%86%A0%EB%A6%AC%EC%A7%80-%EC%97%94%EC%A7%84-%EB%B3%84-%EC%B0%A8%EC%9D%B4