[MySQL] MySQL 아키텍처를 간단히 살펴보기

지금껏 MySQL을 쓰면서 innoDB는 무엇이고 스레드풀은 무엇인지 등, 내부에 대해 용어만 듣고 정확히 아는 것이 별로 없었다.

 

MySQL의 내부를 살펴보며 각각을 하나씩 살펴보려고 한다.

 

 

MySQL 엔진 아키텍처

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진의 두 가지로 나뉜다.

 

MySQL 엔진은 클라이언트로부터 요청된 문장을 분석하거나, 최적화하여 처리하는 역할을 담당하는데, 그림에서 보이듯 커넥션 핸들러, 파서, SQL 인터페이스, SQL 옵티마이저, 캐시버퍼 등이 있다.

 

스토리지 엔진은 실제 데이터를 디스크 스토리지에 저장하거나 조회하는 역할을 담당한다. 여러 엔진을 동시에 사용할 수 있다. MyISAM 엔진, InnoDB 엔진 등이 있다.

 

이렇게만 말하면 확 와닿지는 않으니, 실제 쿼리가 어떤 절차로 실행되는지를 따라가보며 각 기능을 이해해보자!

 

 

 

 

MySQL 쿼리 처리 절차

https://hoing.io/archives/2478

쿼리는 위 그림의 순서로 실행된다.

 

우선 위 그림에는 없지만 앞선 그림에서 보이듯, 프로토콜을 통해 전달된 SQL 요청은 최상위 계층인 Connection Pool 를 거친다.

 

 이후 전달된 SQL 요청이 Parser→ Preprocessor → Query optimizer → Query execution engine → Storage engines → Data 로 연결되는 것을 볼 수 있다.

 

 

각각을 살펴보자.

 

 

Connection Pool Layer

connection pool은 client의 connection을 생성하고 관리한다. 여기서 Client의 호스트, 패스워드, 이름을 체크하는 인증 기능과 Client가 특정 쿼리를 수행할 수 있는 지를 체크하는 보안의 역할도 수행한다.

 

Parser

쿼리 파서는 쿼리를 토큰으로 분리해 트리 구조로 만든다. 그 과정에서 쿼리 문법 오류를 체크해 사용자에게 반환한다.

 

Preprocessor(전처리기)

preprocessor에서는 파서에서 만들어진 트리를 기반으로 구조적인 문제점을 확인한다. 예를 들어 테이블 이름, 컬럼명 등이 존재하는지, 접근 권한이 올바른지를 확인한다.

 

Query Optimizer

MySQL 옵티마이저는 CBO 를 사용한다. CBO란 Cost-Based Optimizer 로 비용 기반 최적화를 말한다. 수행 가능한 방법의 비용과 테이블 통계 정보를 통한 Cost 에 따른 실행 계획을 수립하는 방법이다.

 

쉽게 말해, 쿼리를 실행하기 위한 효율적인 경로를 결정하는 것을 말한다. 쿼리를 내부적으로 실행할 수 있는 형태로 변환하고, 여러 가지 실행 계획을 생성한다. 그리고 그 중 가장 비용이 적은 계획을 선택해 실제 쿼리로 실행한다.

 

예시로 살펴보기

예시 코드를 통해 조금 더 쉽게 살펴보자.

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.department_name = 'Engineering';

만약 위 코드를 실행한다고 가정해보자. 그럼 이 쿼리를 실행하는 방법을 두 가지로 생각해볼 수 있다.

1. 인덱스를 활용한다.(2) employees 테이블에서 department_id가 위에서 찾은 ID와 일치하는 행을 찾기 위해 idx_dept_id 인덱스를 사용
2. (1) departments 테이블에서 department_name이 'Engineering'인 행을 찾기 위해 PRIMARY 인덱스를 사용
3. 테이블 스캔을 사용한다.(2) 각 department_id에 대해 departments 테이블을 스캔하여 department_name을 확인
4. (3) 조건에 맞는 행만 결과로 반환
5. (1) employees 테이블의 모든 행을 스캔하여 각 department_id를 가져온다.

위 두 가지중 인덱스를 활용하면, 모든 행을 스캔하는 테이블 스캔보다 빠르기 때문에 첫 번째 실행 계획이 선택된다.

 

 

만약 실행 계획을 확인하고 싶다면, 쿼리문 앞에 explain을 붙여서 실행하면 된다.

 

EXPLAIN SELECT * FROM users WHERE age > 20;

 

 

 

인덱스와 테이블 스캔 비교?

이 예시만 보고는 이런 생각도 들 수 있다.

대부분의 경우에서 인덱스를 사용하는 계획이 더 빠른거 아닌가? 그럼 내부적으로 인덱스만 사용하게 하지 않고 왜 테이블 스캔도 굳이 계산을 하는거지?

하지만 인덱스가 없는 열을 검색하는 상황이 있을 수도 있다는 점과, 조건이 매우 광범위한 경우에는 인덱스를 사용하는 것이 비효율적일 수 있다는 것을 간과한 것이다.

SELECT * FROM employees WHERE department_id > 0;

예를 들어 이렇게 조건식에서 모든 행을 읽어야할 때는 인덱스를 사용하는 것이 더 오버헤드가 클 수 있다.

 

 

Query Execution Engine(실행 엔진)

실행 엔진은 Query Optimizer에서 만들어진 실행 계획을 각 스토리지 엔진에게 요청하고, 받은 결과를 또 다른 스토리지 엔진에 다시 요청하여 입력으로 연결하는 역할을 수행한다.

 

여기서 필요한 개념이 핸들러 API 다. 스토리지 엔진에 쓰기 또는 읽기를 요청하게 될 때의 요청을 핸들러(Handler) 요청이라고 하고, 여기서 사용 되는 API를 핸들러 API 라고 한다.

 

 

Storage Engine(스토리지 엔진)

스토리지 엔진은 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어오는 역할을 수행한다.

 

 

스토리지 엔진 종류

MySQL은 스토리지 엔진을 플러그인 방식으로 사용할 수 있다. 그래서 원하는 스토리지 엔진을 구성해 사용 가능하다.

 

MySQL에서 대표적으로 알아야 할 두 가지의 스토리지 엔진(MyISAM, InnoDB)에 대해 간략하게만 알아보자

 

MyISAM 엔진

MyISAM은 InnoDB에 빠르지만 동시성 제어가 어렵다. 트랜잭션을 지원하지 않고, Table-Level의 Lock을 제공한다.

데이터를 매우 효율적으로 저장하기에 전체적인 속도가 InnoDB 엔진보다 빠르지만 Table-Level의 Lock을 사용해 갱신 작업 속도가 느리다.

그래서 Write 작업이 없는 Select 위주의 테이블에 적합하다

 

InnoDB 엔진

InnoDB는 MySQL 5.5 버전 이후부터 사용되는 기본 스토리지 엔진이다.

 

 

내부 아키텍처는 위와 같은데, 이 부분은 다음 포스팅에서 다시 다루어 볼 것이다.

 

그럼에도 가장 큰 특징을 몇 가지 말해보자면,

 

우선 Buffer Pool이 존재해서, 디스크 상의 데이터나 인덱스를 메모리에 캐싱해둘 수 있다.

그리고 트랜잭션을 제공해 데이터 무결성을 보장하고, MVCC 및 Row-Level의 Lock을 사용해 동시성을 제공한다.

또 ACID 모델을 따르기 때문에 커밋, 롤백 및 충돌 복구 기능을 갖추고 있다.

MyISAM 엔진보다 갱신 작업 속도가 빠르기 때문에 Write 작업이 많은 갱신 위주의 테이블에 적합하다

 

 

(이 부분에 대해서는 추후에서 다시 상세하게 다뤄볼 것이니 간략하게만 알고 넘어가자)

 

 

 

참고자료

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

https://hoing.io/archives/2478

https://exem.tistory.com/1680?category=1010730
https://spidyweb.tistory.com/460