Published on

Real MySQL 아키텍처 개인적인 정리

Authors
  • avatar
    Name
    김민석
    Twitter

Introduction

Real MySQL 아키텍처

image.png

크게 4가지로 구분 해볼 수 있다.

  • MySQL 접속 클라이언
  • MySQL 엔진
  • MySQL 스토리지 엔진
  • 운영체제, 하드웨어

MySQL 접속 클라이언

  • MySQL은 대부분 프로그래밍 언어에 대해 접속 API를 제공한다.
  • 쉘에서도 쉘 스크립트를 통해 접속이 가능하다.

MySQL 엔진

MySQL의 두뇌를 담당하는 곳이다.

  • 클라이언트 접속과 SQL 요청을 처리.
  • 쿼리 파서, 전처리기, 옵티마이저, 쿼리 실행 등으로 이루져 있다.
    • (옵티마이저가 중요) MySQL 엔진을 두뇌라고 비유한 이유는 옵티마이저 때문이다.
"옵티마이저란?"
- 요청된 SQL문을 최적화해서 실행시키기 위해 실행 계획을 짜는 중요한 역할 한다.
- 쿼리 변환, 비용 최적화, 실행 계획 수립

MySQL 스토리지 엔진

MySQL의 손발 역할을 담당하는 곳이다.

  • 데이터를 실제로 디스크에 저장하거나, 디스크에 저장된 데이터를 읽어오는 역할을 한다.
  • 옵티마이저가 작성한 실행 계획에 따라서 스토리지 엔진을 호출해서 쿼리를 실행한다.

핸들러 API

  • MySQL 엔진이 스토리지를 호출할때 사용하는 API를 핸들러 API라고 한다.
  • 또한 핸들러 API를 직접 구현해서 나만의 스토리지 엔진을 추가할 수도 있다.

운영체제, 하드웨어

  • 마지막으로 MySQL 실제 테이블의 데이터와 로그 데이터를 파일로 저장하는 운영체제, 하드웨어로 나눠 볼 수 있다.

쿼리 실행 과정

image.png

쿼리 캐시

  • SQL 실행 결과를 메모리에 캐싱하는 역할
    • 사용자가 SQL 요청을 MySQL로 보내면 가장 먼저 쿼리 캐시를 만난다.
    • 쿼리 캐시는 쿼리 요청 결과를 캐싱하는 모듈이다.
  • 동일 SQL 실행시 이전 결과 즉시 반환
    • 쿼리 캐시를 통해서 동일한 SQL 요청에 대한 결과를 즉시 빠르게 받을 수 있다.
  • 테이블의 데이터가 변경되면 캐싱된 데이터 삭제 필요
    • 하지만 쿼리 캐시는 캐싱하고 있는 데이터의 테이블이 변경 된다면 더 이상 쓸모 없어지게 된 캐싱 데이터를 삭제해야 한다.
    • 캐싱 데이터가 삭제 될때마다 쿼리 캐시에 접급하는 쓰레드에 Lock이 걸리는데, 이는 심각한 동시 처리 성능 저하를 유발한다.
  • MySQL 8.0 부터 완전히 제거
    • MySQL 8.0부터는 쿼리 캐시가 완전히 삭제 되었다.

쿼리 파서

쿼리 파서는 기본적인 SQL 문장 오류를 체크한다.

그리고 SQL 문장을 의미있는 단위의 토큰으로 쪼갠 다음에 트리로 만든다.

  • 트리를 Parse Tree 라고 부른다.
  • MySQL은 내부적으로 Parse Tree를 사용해서 쿼리를 실행한다.

전처리기

쿼리 파서가 만든 Parse Tree를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 검사한다.

  • 파스 트리의 토큰을 하나씩 검사하면서, 토큰에 해당하는 테이블 이름이나 컬럼 등이 실제로 존재하는 값인지 체크하고, 접근 권한에 대해서도 체크 한다.

옵티마이저

옵티마이저는 SQL을 최적화해서 실행 시키는 쿼리 실행 계획을 만든다.

  • 옵티마이저가 SQL을 최적화 하는 방법은 2가지가 있다.
    • ‘규칙 기반 최적화’와 ‘비용 기반 최적화’
      • 규칙 기반 최적화: 옵티마이저에 내장된 우선 순위에 따라 점수를 매겨 실행 계획을 수립하는 방법이다.
      • 비용 기반 최적화: SQL을 처리하는 다양한 방법을 마련해두고 각 방법의 비용과 테이블 통계 정보를 통해서 실행 계획을 수립하는 방법이다.

쿼리 실행 엔진

  • 옵티마이저가 만들어준 쿼리 실행 계획에 따라서 스토리지 엔진을 적절히 호출해서 쿼리를 수행하는 역할을 담당한다.
옵티마이저가 만든 실행 계획대로 스토리지 엔진을 호출해서 레코드를 읽고 쓴다

스토리지 엔진

쿼리 실행 엔진이 요청한대로, 데이터를 디스크에 저장하거나, 디스크로부터 데이터를 읽어 오는데, 대표적으로 InnoDB, MyISAM 스토리지 엔진이 있다.

  • 이 스토리지 엔진은 플러그인 형태로 제공되기 때문에 사용자는 원하는 스토리지 엔진을 선택해서 사용할 수 있다.

InnoDB 스토리지 엔진

image.png

크게 3가지로 구분 해볼 수 있다.

  • 프라이머리(PK) 키에 의한 클러스터링
  • 트랜잭션 지원
    • MVCC, 리두 로그 & 언두 로그, 레코드 단위 잠금
  • InnoDB 버퍼풀 & 어댑티브 해시 인덱스

프라이머리 키에 의한 클러스터링

image.png

프라이머리 키를 기준으로 데이터를 묶어서 저장하는 것이다.

  • PK 순서대로 레코드를 정렬해서 디스크에 저장하는 것을 뜻한다.
  • 참고로 InnoDB 에서는 PK를 통해서 데이터 파일에 접급하며, PK에 대한 인덱스를 자동으로 생성해주기도 한다.
  • 클러스터링을 하면 PK 기반 범위 검색 속도가 매우 빨라진다. 이유는 PK 기준으로 데이터가 정렬되어서 묶여서 한 군데 저장 되어있기 때문이다.
  • 하지만 쓰기 성능은 저하 된다. PK 값이 바뀌면 그에 따라 레코드의 물리적 순서도 하나씩 바꿔줘야 하기 때문이다.
  • 웹 서비스는 쓰기보다 읽기 요청이 훨씬 많기 때문에 쓰기 성능을 희생하고 읽기 성능을 얻는 클러스터링을 하는 것이 합리적이기 때문이다.
- 레코드를 PK순으로 정렬해서 저장
- PK 인덱스 자동 생성
- PK를 통해서만 레코드에 접근 가능
- PK를 통한 범위 검색이 매우 빠름
- But 클러스터링 때문에 쓰기 성능 저하

트랜잭션 - MVCC(다양한 버전이 동시에 관리된다)

image.png

InnoDB는 기본적으로 Commit, Rollback 기능을 제공한다. 그리고 MVCC 라는 기능도 제공한다.

  • 위 그림을 보면
    • InnoDB 버퍼풀은 변경된 데이터를 디스크에 반영하기전까지 잠시 버퍼링 하는 공간이다.
    • 언두 로그는 변경되기 이전 데이터를 백업 해두는 공간이라고 보면 된다.
user 라는 테이블에 
id는 1번, 이름은 '김민석', 지역은 '부산'인 
레코드를 삽입하는 쿼리를 실행하고 커밋을 찍어본다.
  • 그러면 버퍼 풀에 새로 삽입한 레코드가 생긴다.
image.png
  • 이 상태에서 ‘김민석’의 지역을 ‘서울’로 바꾸는 update 쿼리를 날리면 버퍼풀에 있는 ‘김민석’ 레코드의 지역은 바뀌고 이전 ‘부산’이였던 컬럼 값은 id와 함께 언두로그에 복사되는 것이다.
image.png
  • 이 상태에서 다른 트랜잭션이 ‘김민석’의 레코드를 조회한다면, 어떤 결과를 반환할까?
    • 이건 트랜잭션 격리 수준에 따라 다르다.
      • READ_UNCOMMITTED라면 버퍼풀에 있는 것이 조회되고,
      • 나머지 격리 수준이라면 언두 로그에 있는 데이터가 조회 되는 것이다.

언두 로그 & 리두 로그

  • 언두 로그
    • 변경되기 이전 데이터를 백업해서 트랜잭션을 보장한다.
      • 롤백을 한다면 언두 로그에 백업된 데이터로 그대로 복원해주면 되는 것이다.
    • 트랜잭션 격시 수준도 보장해준다.
      • 트랜잭션 격리 수준에 따라 언두 로그에 있는 데이터를 반환해준다.
  • 리두 로그
    • 리두 로그는 트랜잭션의 영속성을 보장해준다.
      • 서버 비정상 종료시, 리두 로그에 백업된 데이터 복원 한다.
    • 변경된 데이터, commit 된 데이터를 백업 한다.

레코드 단위 잠금

  • 데이터 베이스에서 데이터를 변경할때는 동시성 문제를 고려해서 레코드에 대한 접근을 막는데, 이걸 잠금이라고 한다.
  • InnoDB는 레코드 단위로 잠금을 걸기 때문에 동시처리 성능이 좋다
    • 실제로는 레코드 그 자체를 잠그는 것이 아니라, 인덱스 레코드를 잠그는 것이다.
- 레코드 단위로 잠금을 걸기 때문에 동시 처리 성능이 좋다.
- 사실 레코드 자체를 잠그는 것이 아니라, 인덱스를 잠근다.

레코드 단위 잠금 - 예시

image.png

성별은 ‘남자’ 이름은 ‘김민석’이라는 레코드의 운동 컬럼을

‘축구’에서 ‘달리기’로 바꾸는 update 쿼리를 날리면 그림 처럼 ‘김민석’ 레코드만 잠기지 않는다.

image.png

InnoDB는 레코드 그 자체를 잠그지 않고, 인덱스 레코드를 잠그는 방식으로 처리 되기 때문이다.

  • 업데이트 할 레코드를 검색할때, 사용된 인덱스 레코드가 잠긴다.
  • 그림처럼 검색에 사용된 성별 인덱스 레코드가 모두 잠기게 되는 것이다.
image.png
  • 만약 성별 인덱스가 없었다면?

‘김민석’ 레코드를 찾기 위해서 기본으로 생성된 PK 인덱스를 사용하여 테이블 풀 스캔 한다.

  • 이 경우에도 검색에 사용된 모든 PK 인덱스가 잠긴다.
  • 전체의 레코드가 100개라고 한다면 100개의 레코드가 전부 잠기게 되는 것이다.
하지만. 복합 인덱스를 생성했다면, 내가 원하는 김민석 레코드만 잠글 수 있다.

InnoDB 스토리지 엔진 특징 - 버퍼풀

image.png

버퍼풀의 용도 2가지

  • 데이터 캐싱
  • 쓰기 지연 버퍼

데이터 캐싱

버퍼풀은 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐싱해두는 공간이다.

  • 버퍼풀은 쓰기 작업을 지연시켜서 일괄적으로 작업을 처리해주기도 한다.
  • 버퍼풀은 SQL 요청 결과를 일정한 크기의 페이지 단위로 캐싱하는데, 운영체제가 가상 메모리를 효율적으로 사용하기 위해 페이징을 하는 것 처럼 데이터베이스도 테이블 데이터에 대해 페이징을 한다.
    • InnoDB는 페이지 교체 알고리즘으로 LRU 알고리즘을 사용하고 있다.

쓰기 지연 버퍼

  • insert, update, delete 명령으로 변경된 페이지를 더티 페이지라고 부른다.
    • InnoDB는 이 더티 페이지들을 모았다가 주기적으로 이벤트를 발생시켜서 한번에 디스크에 반영한다.
    • 이렇게 변경된 데이터를 한번에 모았다가 처리하는 이유로는 랜덤 I/O를 줄이기 위해서 이다.

어댑티브 해시 인덱스

  • 인덱스 키와 페이지의 주소값 쌍으로 구성된 인덱스 이다.
  • 페이지에 빠르게 접근하기 위해 사용되는데 어댑티브 해시 인덱스는 사용자가 자주 요청하는 데이터에 대해서 InnoDB가 자동으로 만들어 준다.
  • 원하는 페이지에 빠르게 접근할 수 있기 때문에 쿼리를 더 빠르게 처리할 수 있다.

MyISAM 스토리지 엔진

image.png

MyISAM 스토리지 엔진에서는 클러스터링과 트랜잭션 그리고 외래키를 지원하지 않는다.

  • 테이블 단위로 잠금을 걸기 때문에 동시 처리에 불리하다
  • MyISAM에도 InnoDB 버퍼풀과 같은 역할을 하는 키 캐시가 존재한다.
  • 버퍼 풀과 달리 키 캐시는 인덱스만을 대상으로 동작하는 것이 특징이다.
  • MyISAM은 전문 검색, 공간 좌표 검색 기능 등을 지원 한다.

마무리

  • 결국, InnoDB 스토리지 엔진이 기본 엔진으로 채택되었다.
  • 하지만, MySQL의 시스템 테이블은 여전히 MyISAM을 사용하고 있다는 것이다.
  • MySQL 8.0부터는 모든 테이블이 InnoDB 스토리지 엔진을 사용하게 되었고 MyISAM에서만 지원하던 전문 검색 기능이나 공간 좌표 검색 기능을 InnoDB에서도 제공하게 되었다는 것이다.
Discuss on Twitter • View on GitHub