[DataBase] MYSQL 아키텍처

나는 기존의 프로젝트에서 조회 기능들을 최적화 하고 싶어졌다. 조회 기능을 개선하려면 MySQL이 무엇인지, 어떻게 동작하며, 어떤 구조로 이루어져 있는지 정확히 이해하는 것이 필수적이라고 생각했습니다.

이 글에서는 MySQL의 기본  아키텍처 부터 동작 원리, 그리고 최적화 기법까지 차근차근 다뤄보겠습니다.

 

MYSQL 아키텍처

MYSQL의 아키텍처는 다음과 같은 구조로 구별됩니다.

https://velog.io/@kidae92/

MYSQL 접속 클라이언트

프로그래밍 언어 대해 접속 API를 제공합니다. 그뿐만 아니라 Shell에서도 Shell Script를 통해 접속이 가능합니다.

즉, 사용자의 응용 프로그램과 소통하는 계층입니다.

 MYSQL 엔진

MYSQL의 두뇌를 담당합니다. Client접속과 SQL 요청을 처리합니다.

MYSQL 스토리지 엔진

MYSQL의 손과 발에 해당합니다.

 

자세한 내용은 동작순서에서 다루겠습니다.

 

MYSQL 동작 순서

Parser

파서의 주요 기능은 다음과 같습니다.

1. 문법 검사 : SQL의 문법 규칙을 따르는지 확인합니다.
2. 구조 분석 : SQL문을 구성하는 요소를 식별하고 파스트리 라는 내부 데이터 구조로 변환합니다.

PreProcessor

전처리의 주요기능은 다음과 같습니다.

1. Parese Tree를 기반으로 SQL 문장 구조 체크
2. Parse Tree의 토큰이 유효한지 체크

Optimizer

옵티마이저의 주요기능은 다음과 같습니다.

1. 파싱된 쿼리를 받아 효율적으로 처리합니다.
2. SQL을 최적화 하는 방법은 2가지가 존재합니다.
   • 비용 기반 최적화 : 각 실행 계획의 비용을 계산하여 비용이 가장 낮은 비용의 계획을 선택하여 수행.
   • 규칙 기반 최적화 : 옵티마이저에 내장된 우선순위에 따라 수행

Engine Executor

옵티마이저가 만든 실행계획 그대로 수행하기 위해 스토리지 엔진을 호출하여 레크드를 읽고 씀

Storage Engine

스토리지 엔진은 SQL 쿼리의 요청에 따라 데이터를 저장, 검색, 수정, 삭제하는 I/O 작업을 수행합니다.

대표적으로 InnoDB와 MyISAM 등이 있으며, MySQL은 플러그인 형태로 다양한 스토리지 엔진을 지원합니다.

MYSQL의 핵심 Stroage Engine

InnoDB

다양한 기능을 통해 데이터베이스의 신뢰성과 성능을 보장합니다. 아래에서 주요 기능을 하나씩 살펴보겠습니다.

PK에 의한 클러스터링

InnoDB는 클러스터링 인덱스를 통해 기본 키(PK)를 기준으로 데이터를 정렬하여 물리적으로 저장합니다. 이로 인해 PK를 사용한 범위 검색이나 단일 레코드 조회 속도가 빨라집니다. 그러나 쓰기(삽입, 업데이트 등) 시 데이터가 정렬된 상태를 유지해야 하므로, 인덱스 재구성 작업이 발생할 수 있어 쓰기 성능이 저하될 수 있습니다.

 

클러스터링 인덱스 : 데이터를 저장할때 기준이 되는 정렬된 순서

MVCC(Multi Version Concurrency Control)

기본적으로 Commit, RollBack 기능을 제공한다.

• InnoDB 버퍼풀 : 디스크에 저장하기 전까지 잠시 버퍼링하는 공간
• 언두 로그 : 변경되기 이전 데이터를 백업해두는 공간

InnoDB 버퍼풀에 쓰기 작업이 잠시 버퍼링된 상황에서 조회 쿼리가 들어온다면 디스크의 값을 가져올까요 버퍼풀의 값을 가져올까요?

이때는 데이터베이스에 설정된 격리수준에 따라 다릅니다.

• READ_UNCOMMITTED : 아직 커밋되지 않은 변경 사항도 그대로 보이므로, 버퍼풀에 있는 최신(변경된) 데이터를 그대로 반환합니다.
• READ_COMMITTED, REATABLE_READ, SERIALAZABLE : 이들 격리수준에서는 커밋되지 않은 변경 사항이 보이지 않아야 합니다. 따라서 InnoDB는 버퍼풀에 있는 데이터 페이지에서 변경된 데이터를 읽은 후, 해당 레코드의 과거 버전을 undo 로그를 참조하여 재구성합니다.

레코드 단위 잠금

InnoDB는 저 수준의 락을 구현했는데 두가지 타입으로 구별됩니다.

• Shared Lock : 읽기 작업을 허용합니다. 여러 트랜잭션이 동시에 같은 행을 읽을 수 있습니다
• Exclusive Lock : 쓰기 작업 시 사용되며, 다른 트랜잭션이 해당 행에 접근하지 못하게 막습니다.

MyISAM

MyISAM은 간단한 구조와 빠른 읽기 성능을 특징으로 하지만, 트랜잭션 지원이나 고급 잠금 기능이 부족해 특정 상황에서만 적합.

특징은 다음과 같습니다.

• 클러스터링 지원 X
• 트랜잭션 지원 X
• 외래키 지원 X
• 테이블 단위 잠금
• 키 캐시 사용(인덱스 정보만 버퍼링)
• 전문 검색, 공간 좌표 검색 기능 지원

 

 

참고 :

https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html

MySQL :: MySQL 8.4 Reference Manual :: 17.7.1 InnoDB Locking

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