서비스를 개발하는 서버 개발자라면, 빼놓을 수 없는 고민은 데이터를 어떻게 저장하고 어떻게 읽을지에 대한 고민이다.
대부분의 서비스는 데이터를 저장하기 위해 RDB에 저장하고, 많은 RDB 솔루션 중 MySQL을 가장 많이 이용한다. 실제로 나도 MySQL을 이용해 데이터를 저장하고, 서빙하고 있다.
그러다 문득, "내가 MySQL이라는 솔루션을 제대로 이해하고 사용하는건가?"라는 의문이 들었다. 그래서 MySQL 서적 중 가장 많이 읽히는 RealMySQL을 읽었다. 이번글에서는 RealMySQL을 읽으면서 서비스 개발자가(내가..) 알고있으면 좋은 지식들을 정리하겠다.
MySQL 아키텍쳐
MySQL Engine : MySQL의 두뇌에 해당하는 처리를 수행한다. 요청된 SQL 문장을 분석하고, 최적화해 스토리지 엔진의 핸들러 API를 이용해 데이터를 가져온다.
Storage Engine : 실제 데이터를 디스크 스토리지에 저장하거나, 디스크 스토리지로부터 데이터를 읽어온다.
MySQL 서버에서 MySQL 엔진은 하나이지만 스토리지 엔진은 여러개를 동시에 사용할 수 있다. 여러 스토리지 엔진이 있지만 가장 많이 사용되는 스토리지 엔진은 InnoDB이다.
사용자가 MySQL 서버에 쿼리를 요청하면, 아래와 같이 쿼리와 같은 단계를 통해 필요한 데이터를 반환한다.
InnoDB 아키텍쳐
InnoDB 스토리지 엔진은 레코드 기반의 잠금을 제공하고, 그 덕분에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. 위 장점들 덕분에 가장 대중적으로 사용된다.
Buffer Pool : 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다. 덕분에 Disk I/O 횟수를 줄일 수 있다.
Change Buffer : InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 둬서 성능을 향상시킨다. 이때 사용하는 임시 공간이 Change Buffer 이다.
Double Write Buffer : 여러개의 데이터 변경 사항을 디스크에 기록하기 전에, 변경된 데이터들을 하나의 페이지로 묶어 Double Write Buffer에 기록한다. 그 후 실제로 변경 사항을 디스크에 작성한다. Double Write Buffer는 변경 데이터를 디스크에 기록하다 MySQL 서버가 종료될 경우 사용된다. MySQL 서버가 재시작하면 Double Write Buffer에 있는 데이터를 비교해 누락된 데이터를 업데이트해준다.
Undo Log & Undo Tablespaces : InnoDB는 MVCC를 지원하기 위해 변경 이전 값을 Undo Log에 저장한다. 그리고 Undo Log가 저장되는 공간이 Undo Tablespaces이다.
Log Buffer & Redo Log : Redo Log는 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다. 서버는 데이터 변경 내용을 로그로 먼저 기록한다. Log Buffer는 Redo Log 버퍼링에 사용되는 공간이다.
Adaptive Hash Index : InnoDB가 자체적으로 만든 인덱스, 주로 사용자가 자주 요청하는 데이터에 대해 생성한다.
Tempoary Tablespaces : 임시로 생성된 테이블이 저장되는 공간이다.
Index
Disk I/O는 CPU 연산과 비교하면 굉장히 오래걸리는 연산이다. 따라서, DB 성능 튜닝에 가장 중요한 부분이 어떻게 Disk I/O를 줄일지다. 그리고 Disk I/O를 줄이기 위해서는 인덱스 설계가 굉장히 중요하다.
MySQL에서는 여러 종류의 Index를 지원하지만, 가장 많이 사용되는 B-Tree와 InnoDB Storage Engine에서 제공하는 Clustering Index에 대해 정리해보겠다.
B-Tree Index
B-Tree Index는 B-Tree 자료구조를 사용한 인덱스이다.
B-Tree 자료구조의 특성 덕분에 인덱스 키 기준으로 데이터가 정렬되어있다. 또, 원하는 데이터는 O(logN) 비용으로 찾을 수 있다.
리프노드는 항상 실제 데이터를 찾아가기 위한 주솟값을 가지고 있다. 하지만 위 그림을 보면 리프노드가 데이터 주소값이 아니라 프라이머리 키 값을 갖고 있다. 이건 InnoDB Storage Engine의 특징이다. 언듯 보면 프라이머리 인덱스를 통해 실제 주솟값을 찾는 오버해드가 추가되어 안좋을 것 같지만, 장단점을 갖고 있다. (자세한 내용은 아래서 더 설명하겠다.)
Clustering Index (a.k.a Clustering Table)
InnoDB Storage에서는 비슷한 프라이머리 키를 묶어서 하나의 디스크 페이지에 저장한다. 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안한 아이디어다. 클러스터링 인덱스의 구조는 아래와 같다.
InnoDB에서는 프라이머리 키의 인데스는 위 그림처럼 구성된다. 리프노드에 실제 데이터들이 있고, 각 데이터는 프라이머리 키를 기준으로 클러스터링 되어 있다. 그리고 세컨더리 인덱스들의 리프 노드에서는 실제 데이터의 위치가 아니라 프라이머리 키 값을 갖고 있다.
위 와 같은 구조로 얻는 장단점은 아래와 같다.
| 장점 | 단점 |
| - 프라이머리 키로 검색할 때 처리 성능이 매우 빠르다. (특히 범위 검색이 엄청 빠르다.) - 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다. |
- 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인데긋의 크기가 커진다. - 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느리다. - INSERT 할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느리다. - 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느리다. |
쿼리 실행계획 이해하기
EXPLAIN 명령어를 사용하면 MySQL이 쿼리를 어떻게 수행할지 알려주는 실행계획을 확인할 수 있다. 실행계획으로 나오는 컬럼들의 의미를 정리해보겠다.
id : 단위 SELECT 쿼리별로 부여되는 식별자 값. id 값과 테이블 접근 순서와는 무관하다.
select_type : 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다. 타입의 종료는 공식문서를 참고하자.
table : 읽을 테이블을 의미한다. 별칭을 사용하면 별칭이 표시된다. 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다.
partitions : 옵티마이저가 쿼리 처리를 위해 필요한 파티션 목록들
type : MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 알려준다. 대표적으로 인덱스를 사용했는지, 테이블 풀 스캔을 했는지를 이를 통해 알 수 있다. 타입의 종료는 공식문서를 참조하자.
possible_keys : 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스 목록. 따라서 possible_keys에 인덱스 이름이 나와도, 그 인덱스를 사용했다라고 판단하면 안된다.
key : 실제로 사용할 인덱스를 표시한다.
key_len : 인덱스 정보를 어디까지 사용했는지를 나타낸다. 이정보를 통해 다중 컬럼으로 구성된 인덱스에서 몇개의 컬럼까지 사용했는지 알 수 있다.
ref : type의 값이 ref 일때 참조 조건으로 어떤 값이 제공됐는지 보여준다.
rows : 실행 계획의 효율성 판단을 위해 예측해던 레코드 건수를 보여준다. 따라서 실제 쿼리 결과 반환된 레코드 건수와 일치하지 않는 경우가 많다.
filtered : 필터링되고 남은 데이터 비율을 보여준다. 예를 들어 rows가 233이고 filterered가 16.03이면, 37(233 * 0.1603)건의 데이터가 가져와질거라고 옵티마이저가 판단한다.
extra : 주로 내부적인 처리 알고리즘에 대해 조금 더 깊이 있는 내용을 보여준다. 예를 들면 임시 테이블이 사용되었는지, 인덱스 LooseScan을 했는지 등의 정보들이 나온다. 자세한 항목들은 공식문서를 참고하자.
(MySQL 8.0.18 부터는 EXPLAIN ANALYZE 기능을 이용하면 쿼리의 실행 계획과 단계별 소요된 시간 정보까지 확인할 수 있다.)
'Infrastructure' 카테고리의 다른 글
| Redis Operate (5) | 2025.08.07 |
|---|---|
| Redis Eviction Strategy (0) | 2025.07.24 |
| Redis 성능의 비밀: 메모리부터 싱글 스레드까지 파헤치기 (0) | 2025.05.21 |
| K8S CPU Requests와 Limits 의미 (0) | 2024.03.31 |
| Redis Pub/Sub과 Kafka 비교 (0) | 2024.02.17 |