[ Database - Intermediate ] - MySQL 구조 및 동작 흐름 (MySql 8.0 Architecture)
이번 포스팅은 MySQL에 대해 더욱 자세히 이해하고자, MySQL의 전체적인 아키텍처에 대해 조사해보는 시간을 가졌습니다.
https://www.youtube.com/watch?v=vQFGBZemJLQ
위 우테코 강의를 참고했으니 참고하실 분은 해주시면 되겠습니다.
MySQL 아키텍처
- MySQL 접속 클라이언트
- MySQL은 많은 언어에서 접속 클라이언트를 제공합니다. 그리고 쉘에서도 쉘 스크립트를 통해서도 MySQL에 접속할 수 있습니다.
- MySQL 엔진
- 클라이언트 접속과 SQL 요청을 처리합니다. 이는 쿼리 파서, 전처리기, 옵티마이저, 실행 엔진 등으로 이루어져 있습니다. 여기서 중요한 옵티마이저는 요청 SQL문을 최적화해서 실행시키기 위해 실행 계획을 짭니다. 이는 실제 쿼리 실행 과정을 살펴볼때에 자세히 살펴보도록 하겠습니다.
- MySQL스토리지 엔진
- 데이터를 실제로 디스크에 저장하거나, 디스크에 저장된 데이터를 가져오는 역할을 합니다. 즉 MySQL 엔진의 옵티마이저가 짠 SQL 실행 계획에 따라 스토리지 엔진을 적절히 호출해서 쿼리를 실행합니다. 여기서 스토리지 엔진을 호출할 때 사용하는 것을 Handler API라고 하고, 실제로 이 Handler API를 구현해서 플러그인으로 스토리지 엔진을 커스텀하고 추가할 수 있습니다.
- 운영체제 파일시스템과 하드웨어
- 이는 실제 데이터를 저장하고 파일과 로그를 관리하는 부분입니다.
쿼리 실행 과정
오른쪽 그림으로 더 세분화한 그림으로 알아보도로 하겠습니다.
쿼리 캐시
이는 SQL 실행 결과를 메모리에 캐시하는 역할을 합니다. 즉 동일한 SQL 구문에 따라 응답 데이터를 즉시 받을 수 있게 됩니다. 하지만 쿼리 캐시는 캐싱하고 있는 데이터의 테이블이 변경 된다면, 더 이상 쓸모 없어지게된 캐싱 데이터를 삭제해야합니다. 이렇게 캐싱 데이터가 삭제될 때마다, 쿼리 캐시에 접근하는 쓰레드에 락을 걸게 되는데 이는 심각한 동시 처리 성능 저하를 유발하게 됩니다.
이는 여러 쓰레드가 쿼리 캐시를 수정하려고 할 때, 캐시에 있는 데이터의 일관성을 지키기 위함입니다. 그리고 어떻게 보면 트랜잭션의 일부로, 원자성과 격리 수준을 보장하기 위해 락을 걸게됩니다. 하지만 이는 성능 저하를 유발하게 되므로, MySQL 8.0에서는 쿼리 캐시가 완전히 삭제되었다고 합니다.
쿼리 파서
이는 SQL 문장을 파싱합니다. 그리고 의미있는 단위의 토큰으로 쪼갠 다음에 트리로 만듭니다.
위와같이, 쿼리 파서는 트리를 기반으로 쿼리를 처리합니다.
전처리기
전처리기는 쿼리 파서가 만든 Parse Tree를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 검사합니다.트리의 토큰을 하나씩 검사하면서 토큰에 해당하는 테이블 이름이나 컬럼이 실제로 존재하는지도 체크하고, 접근 권한등에 대해서도 체크를 합니다.
옵티마이저
위에서도 말했지만, 옵티마이저는 SQL 실행을 최적화해서 실행 계획을 수립하는 역할을 합니다. 이는 크게 2가지 방법이 있습니다.
규칙기반 최적화 방법은 옵티마이저에 내장된 우선순위에 따라서 실행 계획을 수립하는 방법입니다. 반해서 비용 기반 최적화는 SQL을 처리하는 다양한 방법을 마련해두고, 각 방법의 비용과 테이블 통계 정보를 토대로 실행 계획을 수립하는 방법입니다.
쿼리 실행 엔진
이는 옵티마이저가 만들어준 쿼리 실행 계획에 따라서 스토리지 엔진을 적절히 호출해서 쿼리를 수행하는 역할을 합니다.
스토리지 엔진
이는 쿼리 실행 엔진으로부터 Handler API로 부터 원하는 파일을 가져오게 됩니다. 대표적으로는 InnoDB, MyISAM 스토리지 엔진등이 있습니다. 이 스토리지 엔진은 플러그인으로 제공되기 때문에, 사용자는 원하는 스토리지 엔진을 선택해서 사용할 수 있습니다.
뿐만 아니라 MySQL은 사용자 인증 모듈, 검색 모듈등도 플러그인 형태로 제공합니다. 하지만 플러그인끼리는 통신할 수 없고, 플러그인은 MySQL 변수나 함수를 직접 호출하기 때문에, 캡슐화를 위반한다는 한계점도 있습니다. 따라서 MySQL 8.0부터는 플러그인 아키텍처를 대신한 컴포넌트 아키텍처를 지원하기 시작했습니다.
이제 대표적인 스토리지 엔진인 InnoDB와 MyISAM에 대해서 살펴보도록 하겠습니다.
InnoDB 스토리지 엔진 & 특징
PK에 의한 클러스터링
프라이머리 키에 대한 클러스터링은, 프라이머리 키를 기준으로 데이터를 묶어서 저장하는 것입니다. 즉 PK기준으로 정렬해서 디스크에 저장하는 것을 말합니다. PK기반으로 인덱스를 만들어 주어 데이터 파일에 접근하기도 합니다. 그래서 PK 기반 범위 검색 속도가 매우 빨라집니다.
하지만 이는 쓰기 성능은 저하됩니다. PK 값이 바뀌면 그에따라 레코드의 물리적 순서도 하나하나 바꿔줘야 하기 때문입니다. 일반적으로 데이터 읽기 작업이 많기 때문에, 읽기 성능을 얻는 클러스터링을 하는 것이 합리적입니다.
그리고 InnoDB에서 인덱스를 지정하지 않으면, 내부적으로 PK를 자동 생성해서 클러스터링을 하는데, 이렇게 내부적으로 생성된 PK는 내부적으로 사용할 수 없기 때문에 PK를 사용자가 직접 설정해주는 것이 좋습니다.
트랜잭션 - MVCC (Multi Version Concurrency Control)
InnoDB는 기본적으로 Commit, Rollback기능을 제공합니다. 그리고 MVCC또한 제공합니다. InnoDB의 버퍼풀은, 변경된 데이터를 잠시 임시 저장하는 곳을 말하고, 언두 로그는 변경되기 이전 데이터를 백업 해두는 공간이라고 보면 됩니다.
위와같이 버퍼풀, 언두 로그에 update query가 날라간 상황이라고 해봅시다. 이때 사용자가 해당 레코드를 읽는다면 어떤 값을 읽게 될까요? 바로 정답은 데이터베이스에 설정된 트랜잭션 격리 수준에 따라 다릅니다.
격리 수준이 "READ_UNCOMMITED"라면 버퍼풀에 있는 레코드를 읽을 것입니다. 그 이유는 Diry-Read를 허용하기 때문입니다. 그리고 격리 수준이 "READ_COMMITED" or "REPEATABLE_READ" or "SERIALAZABLE"이라면 언두 로그에 있는, 변경되기 이전 데이터인 언두 로그의 레코드를 읽게 됩니다. MVCC는 이와같이 트랜잭션 격리 수준에 따라 레코드 접근 방식을 다르게 하는 것이라고 이전 포스팅에서 다루었었습니다.
MVCC의 장점은, 이를 통해 레코드에 잠금을 걸지 않고도, 트랜잭션 격리수준에 따라 일관적인 데이터 읽기를 수행할 수 있다는 것이 매우 중요합니다.
언두 로그와 리두 로그
언두 로그는 위에서 본것과 같이, 트랜잭션을 보장하기 위해서 commit 이전에 update한 레코드 정보를 백업해둡니다. 이는 Recovery를 보장하기 위해 Rollback시 언두 로그에 백업된 데이터를 복원하기 위함입니다.
리두 로그는 변경된 데이터를 백업하는데 (Commit 완료된 데이터) 이는 영속성 보장을 위함입니다. 이는 하드웨어 또는 소프트웨어 문제로 MySQL이 비정상적으로 종료하면 리두 로그를 통해 데이터를 복원할 수 있게 합니다.
트랜잭션 - 레코드 단위 잠금
InnoDB는 동시성 문제를 고려해서 레코드에 대한 접근을 막습니다. 이는 레코드 단위로 잠금을 걸기 때문에 동시처리에 좋습니다. 하지만 이는 실제로 레코드에 거는것이 아니라 인덱스 레코드를 잠급니다.
- User 테이블의 총 레코드 수는 5000개
- 성시 칼럼이 '박'인 레코드는 300개 존재
- 성씨 칼럼이 '박'이고, 이름 칼럼이 '병욱'인 레코드는 1개 존재
- 성씨 칼럼에는 idx_성씨 인덱스가 걸려있음
위 상황을 가정하고 예시를 들겠습니다.
즉 위와같이 업데이트할 레코드를 검색할때, 사용된 인덱스 레코드가 잠깁니다. 위와같은 상황에서는 '박'씨 인덱스 레코드가 모두 잠기고, 실제 '박'씨 레코드 300개도 모두 잠기게 됩니다.
만약 성씨 인덱스가 아예 없었더라면 아래와 같이 됩니다.
사용된 Id(pk)를 full-scan하게 되어 모든 PK 인덱스가 잠기게 되어, 모든 테이블 레코드에 lock이 걸리게 됩니다. 즉 user 테이블의 5000개의 레코드가 전부 잠기게 되는 것입니다.
그리고 만약, 성씨와 이름에 대한 복합 인덱스를 생성했다면 아래와 같이 하나의 레코드만 lock이 걸리게 될 것입니다.
이렇게 인덱스를 어떻게 설정 하는지에 따라 레코드 잠금 범위가 달라질 수 있으니 InnoDB르를 사용할때에는 인덱스를 신중하게 설정해야 합니다.
버퍼풀
버퍼풀은 데이터 캐싱이나 쓰기 작업을 지연시켜서, 일관적으로 작업을 처리해주기도 합니다.
버퍼풀은 일정한 크기의 페이지 단위로 데이터를 캐싱합니다. 운영체제가 가상 메모리를 효율적으로 사용하기 위해 페이징을 하는 것처럼, 데이터베이스도 테이블 데이터에 대해 페이징을 합니다. InnoDB는 페이지 교체 알고리즘으로, LRU 알고리즘을 사용하고 있습니다.
그리고 쓰기 지연을 지연시키는 작업을 한다고 했습니다. Insert, Update, delete query를 통해서 변경된 페이지를 Dirty Page라고 합니다. InnoDB는 이러한 Dirty Page를 모았다가 이벤트를 발생시켜서 한 번에 디스크에 반영시키게 됩니다. 이는 랜덤 I/O를 줄이기 위해 사용됩니다.
어댑티브 해시 인덱스
어댑티브 해시 인덱스는, 페이지에 빠르게 접근하기 위한 해시 자료구조 기반 인덱스입니다. 이는 <인덱스 키, 페이지 주소값> 쌍으로 구성되어, 자주 요청되는 페이지에 대해 InnoDB가 자동으로 생성하는 인덱스입니다. 이를 통해 자주 사용하는 쿼리를 등록해서 쿼리를 더 빠르게 처리할 수 있습니다.