Skip to content

MySQL&InnoDB.md

Latest commit

 

History

History
258 lines (158 loc) · 15.7 KB

MySQL&InnoDB.md

File metadata and controls

258 lines (158 loc) · 15.7 KB

MySQL과 InnoDB의 주요 특징 및 동작 원리

MySQL을 사용하다 보면 InnoDB, 스토리지 엔진, 트랜잭션, ACID 같은 단어들을 자주 만나게 된다. 하지만 이 용어들이 정확히 어떤 역할을 하는지 몰랐기 때문에 이번에는 MySQL과 InnoDB의 구조 및 동작 원리를 학습하려고 한다.

MySQL이란?

RDBMS와 릴레이션 모델의 기본 개념을 학습하면서 데이터베이스 관리 시스템(DBMS)의 개념을 알게 되었다.

MySQL은 관계형 데이터베이스 관리 시스템(RDBMS)이다. 즉, 데이터를 테이블이라는 구조에 따라 행(Row)과 열(Column) 형태로 저장하고, SQL을 통해 데이터를 삽입/조회/수정/삭제할 수 있다.

애플리케이션은 직접 파일에 데이터를 저장하지 않는다. 대신 DBMS에게 이 데이터 저장해줘, 이 조건에 맞는 데이터를 찾아줘라고 요청한다.

MySQL은 이 요청을 처리하고 결과를 응답하는 중재자 역할을 한다.

스토리지 엔진이란?

나는 MySQL을 만능 도구처럼 생각했다. 실제로는 MySQL 내부에서도 역할이 분리되어 있다는 것을 알게 되었다.

InnoDB는 높은 신뢰성과 고성능의 균형을 잡는 일반 목적 스토리지 엔진이다. MySQL 8.4에서 InnoDB는 기본 MySQL 저장 엔진이라고 한다. 다른 기본 저장 엔진을 구성하지 않은 경우 엔진 조항없이 생성 테이블 문을 발행하면 InnoDB 테이블이 생성된다. Introduction to InnoDB: MySQL Docs

공식 문서에 InnoDB는 MySQL의 기본 스토리지 엔진이라고 한다. 즉, MySQL이 InnoDB를 사용하는 것이라고 이해할 수 있다.

스토리지 엔진은 실제 데이터를 어떻게 디스크에 저장하고, 어떻게 읽을 것인지를 담당하는 모듈이다. MySQL은 다양한 스토리지 엔진을 지원하며, 테이블 단위로 지정할 수 있다.

[애플리케이션]
      ↓ (SQL)
[MySQL Server]  ← SQL 파서, 옵티마이저, 캐시, 사용자 인증 등 처리
      ↓
[스토리지 엔진 API]
      ↓
[InnoDB / MyISAM / Memory 등 다양한 스토리지 엔진]
  • MySQL Server: SQL 문장을 분석하고 처리하는 두뇌 역할 (파싱, 최적화, 사용자 인증 등).
  • Storage Engine : 데이터를 실제로 저장하고 읽는 일을 하는 하위 계층.
    • MySQL 8.4의 기본 스토리지 엔진은 InnoDB이며, 특별히 지정하지 않으면 InnoDB로 테이블이 생성된다.

InnoDB란? - MySQL의 기본 스토리지 엔진

InnoDB는 MySQL의 기본 스토리지 엔진이다.

주요 특징

기능 설명
트랜잭션 지원 COMMIT, ROLLBACK, SAVEPOINT 등 사용 가능
ACID 보장 데이터 무결성과 안정성 보장
행 단위 잠금(Row-level Lock) 동시에 여러 사용자가 작업해도 충돌 최소화
MVCC 지원 동시성 제어로 읽기/쓰기 충돌 최소화
버퍼 풀 캐시 자주 사용되는 데이터 페이지를 메모리에 캐싱
자동 복구 기능 비정상 종료 후 WAL(Write-Ahead Log)을 기반으로 복구 가능
외래 키(Foreign Key) 관계형 무결성 보장 가능

InnoDB의 메모리/디스크 구조

innodb-architecture

위 이미지는 InnoDB가 메모리 구조와 디스크 구조를 어떻게 구성하고 데이터를 처리하는지를 시각화한 것이다. 출처: MySQL Docs

특징만 보고 싶다면, InnoDB의 주요 특징 섹션을 참고하자.

메모리 내 구조(In-Memory Structures)

버퍼 풀 (Buffer Pool)

InnoDB의 가장 큰 메모리 영역이다. 테이블 데이터와 인덱스 페이지를 캐시하여 디스크 I/O를 최소화한다. 자주 사용하는 데이터를 메모리에 유지함으로써 읽기 성능을 크게 향상시킨다.

크기는 MySQL 설정(innodb_buffer_pool_size)을 통해 조절 가능하다.

변경 버퍼 (Change Buffer)

보조 인덱스(Secondary Index)에 대한 변경 사항을 디스크에 바로 기록하지 않고 메모리에 잠시 저장한다. 나중에 페이지가 버퍼 풀에 로드될 때 실제로 디스크에 반영된다. innodb_change_buffering 설정을 통해 제어할 수 있다.

적응형 해시 인덱스 (Adaptive Hash Index)

자주 조회되는 인덱스 페이지에 대해 해시 인덱스를 생성하여 조회 성능을 높인다.

InnoDB가 자동으로 생성하고 관리한다. 해시 인덱스를 통해 일반 B-Tree보다 빠른 조회가 가능하다.

로그 버퍼 (Log Buffer)

트랜잭션으로 인해 발생한 변경 사항을 로그 파일에 기록하기 전까지 임시로 저장하는 메모리 공간이다. 디스크 쓰기 작업을 모아서 한 번에 처리함으로써 성능을 최적화한다. 이 버퍼는 주기적으로 또는 트랜잭션 커밋 시 디스크의 로그 파일에 기록된다.

참고: InnoDB In-Memory Structures: MySQL Docs

디스크 상 구조(On-Disk Structures)

InnoDB는 데이터의 영속성을 위해 다양한 구조를 디스크에 저장한다.

시스템 테이블스페이스 (System Tablespace)

모든 InnoDB 테이블과 인덱스를 저장하는 기본 저장소이다. Undo 로그, InnoDB 데이터 사전 등의 내부 데이터도 저장된다.

테이블 전용 테이블스페이스 (File-Per-Table Tablespace)

테이블마다 별도의 .ibd 파일에 데이터를 저장할 수 있게 해준다. 테이블 단위의 관리, 백업, 복구가 용이하며, 시스템 테이블스페이스보다 유지보수가 편하다.

innodb_file_per_table 옵션이 기본적으로 활성화되어 있다.

로그 파일 (Redo/Undo Logs)

  • Redo Log: 데이터 변경 이력을 기록해 충돌이나 장애 발생 시 복구에 사용된다. 트랜잭션이 완료되기 전에 로그에 기록되고, 이후에 실제 데이터 페이지에 반영된다.
  • Undo Log: 트랜잭션 롤백을 위해 이전 값을 저장하는 로그이다. 트랜잭션이 완료되기 전까지 데이터의 이전 상태를 유지한다.

이 로그들을 통해 InnoDB는 ACID 트랜잭션의 원자성(Atomicity)과 내구성(Durability)을 보장한다.

더블라이트 버퍼 (Doublewrite Buffer)

데이터 손상 방지를 위한 추가 보호 메커니즘이다. InnoDB는 데이터를 디스크에 직접 쓰지 않고, 먼저 더블라이트 버퍼에 기록한 후 실제 데이터 파일에 기록한다. 이로 인해 데이터 손상이나 시스템 크래시 시에도 데이터 무결성을 유지할 수 있다.

데이터를 디스크에 쓰기 전에 중간 버퍼에 한 번 더 기록하여, 시스템 크래시 시에도 데이터 무결성을 확보한다.

InnoDB On-Disk Structures: MySQL Docs

InnoDB 트랜잭션 처리 흐름과 ACID 보장 방식

트랜잭션 처리 흐름

InnoDB는 트랜잭션을 지원한다. 트랜잭션은 데이터베이스에서 수행되는 일련의 작업 단위를 의미한다. 트랜잭션은 COMMIT, ROLLBACK, SAVEPOINT 등의 명령어를 사용하여 관리할 수 있다.

트랜잭션 시작 → 변경 → 커밋 흐름을 살펴보자.

트랙잭션 시작

사용자가 BEGIN, START TRANSACTION, 또는 SET autocommit = 0 등을 통해 트랜잭션을 시작한다. 내부적으로 트랜잭션 ID가 부여되며, Undo Log 공간이 할당된다.

변경 작업 수행 (INSERT / UPDATE / DELETE)

변경은 실제 디스크 파일이 아닌 버퍼 풀에 있는 데이터 페이지에 먼저 적용된다. 변경 내역은 동시에 Undo Log와 Redo Log(Log Buffer)에 기록된다.

로그 버퍼 플러시

트랜잭션 중 발생한 변경 사항은 우선 Log Buffer에 기록되고, 이후 일정 조건에 따라 Redo Log 파일로 플러시된다.

이때까지는 데이터 파일에는 적용되지 않은 상태이다. 즉, 데이터는 메모리에만 존재한다. 이 상태에서 시스템이 비정상 종료되면 데이터는 손실된다. 트랜잭션이 완료되기 전까지는 데이터가 디스크에 반영되지 않기 때문이다.

트랜잭션 커밋 (COMMIT)

트랜잭션이 커밋되면 Log Buffer에 저장된 Redo 로그가 디스크의 로그 파일로 플러시된다. 이 시점에서 트랜잭션은 "완료된 작업"으로 간주된다.

하지만 데이터 자체는 아직 디스크에 반영되지 않았을 수 있다. 나중에 디스크로 천천히(Lazy) 반영된다.

이러한 처리가 가능한 이유는 Redo Log를 통해 장애 상황에서도 데이터를 복구할 수 있기 때문이다.

트랜잭션 롤백 (ROLLBACK)

롤백 요청 시 Undo Log를 사용해 데이터를 이전 상태로 복원한다. 아직 커밋되지 않았기 때문에, 디스크에는 아무런 변경도 적용되지 않는다.

ACID 보장 방식

트랜잭션은 ACID라고 불리는 네 가지 속성을 가진다.

  • 원자성(Atomicity): 트랜잭션은 모두 실행되거나 전혀 실행되지 않아야 한다.
    • 실패 시 Undo Log를 통해 이전 상태로 복원한다.
  • 일관성(Consistency): 트랜잭션 실행 전후로 데이터베이스가 일관된 상태를 유지해야 한다.
    • 애플리케이션의 제약 + InnoDB의 제약 (외래키 등)이 결합되어 데이터의 일관성을 보장한다.
  • 고립성(Isolation): 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다.
    • InnoDB는 MVCC (다중 버전 동시성 제어) 를 통해 고립성을 유지한다.
    • 트랜잭션마다 보는 데이터가 다를 수 있으며, Undo Log와 버전 관리를 통해 이뤄진다.
  • 지속성(Durability): 트랜잭션이 완료되면 결과는 영구적으로 반영되어야 한다.
    • 커밋된 트랜잭션은 시스템 크래시 후에도 유지된다. 이는 Redo Log와 Doublewrite Buffer 덕분이다.
      • Redo Log로 재실행이 가능하고,
      • Doublewrite Buffer로 디스크 손상 위험 방지한다.

행 단위 잠금(Row-level Lock)

InnoDB는 행 단위 잠금을 지원한다. 즉, 데이터베이스의 특정 행에 대해서만 잠금을 걸 수 있다. 여러 사용자가 동시에 데이터에 접근할 수 있도록 하여 성능을 향상시킨다.

다중 버전 동시성 제어(MVCC)

MVCC(Multi-Version Concurrency Control)는 다수의 트랜잭션이 동시에 실행될 때, 서로 간섭 없이 독립적으로 작업을 수행할 수 있도록 돕는 동시성 제어 기법이다.

핵심 원리는 트랜잭션마다 자신만의 데이터 스냅샷(버전)을 참조하도록 하는 것이다. 각 트랜잭션은 시작 시점의 데이터를 기준으로 작업하며, 그 트랜잭션이 종료되기 전까지는 다른 트랜잭션에 영향을 주거나 받지 않는다. 이렇게 하면 잠금(Lock)을 사용하지 않고도 데이터 일관성을 유지할 수 있다.

예를 들어, 어떤 사용자가 데이터를 수정하고 있는 동안에도 다른 사용자는 기존 버전의 데이터를 읽을 수 있어 조회에 지장이 없다. 데이터 읽기 시 잠금이 필요 없기 때문에, 시스템의 병렬 처리 성능이 크게 향상된다.

MVCC는 일반적으로 트랜잭션의 격리 수준 중 반복 가능 읽기(Repeatable Read) 이상을 구현할 때 사용되며, 대표적으로 PostgreSQL과 같은 데이터베이스 시스템이 이 방식을 사용한다고 한다.

버퍼 풀 캐시(Buffer Pool Cache)

InnoDB는 자주 사용되는 데이터 페이지를 메모리에 캐싱하여 성능을 향상시킨다. 버퍼 풀은 디스크 I/O를 줄여서 데이터베이스의 성능을 높인다.

변경된 데이터는 즉시 디스크에 저장되지 않고, 버퍼 풀에 먼저 저장되고 나중에 플러시된다. 따라서 버퍼 풀 크기 조절은 성능에 큰 영향을 준다고 한다.

자동 복구 기능

InnoDB는 비정상 종료 후에도 데이터베이스를 자동으로 복구할 수 있는 기능을 제공한다. Write-Ahead Log(WAL)라는 로그 파일을 사용한다.

WAL은 데이터베이스의 변경 사항을 기록하는 로그 파일이다. 트랜잭션이 완료되기 전에 로그에 기록되고, 이후에 실제 데이터 페이지에 반영된다. 이로 인해 시스템이 비정상 종료되더라도 데이터 무결성을 유지할 수 있다.

InnoDB 테이블의 모범 사례

모든 테이블에 기본 키(Primary Key)를 지정하자.

조회가 자주 일어나는 컬럼을 별도로 식별자를 만들 수 없다면 AUTO_INCREMENT 속성을 가진 컬럼을 기본 키로 설정해주는 것이 좋다.

기본 키는 InnoDB에서 클러스터형 인덱스를 구성할 때 핵심이 되고, 검색 성능에도 영향을 준다고 한다.

자동 커밋(autocommit)은 끄자

autocommit이 켜져 있으면 쿼리 하나 실행할 때마다 매번 COMMIT이 발생해서 디스크 쓰기 횟수가 많아지고 성능이 떨어질 수 있다. (그러나 autocommit은 기본값으로 켜져 있다.)

  • 수백 번의 커밋을 초당 처리하려 하면 스토리지 성능에 병목이 생길 수 있다.

그러면 왜 autocommit이 기본값으로 켜져 있을까?

  • autocommit을 켜두면 실수로 트랜잭션을 종료하지 않아도 되므로, 데이터베이스가 잠기는 상황을 피할 수 있다.
  • 과거 MySQL의 기본 스토리지 엔진이 MyISAM이었는데, 이 엔진은 트랜잭션을 지원하지 않았다고 한다. 그래서 모든 작업이 자동 커밋처럼 동작하는 구조였고, 그 연장선으로 autocommit이 기본값으로 설정된 것이다.

관련된 DML은 하나의 트랜잭션으로 묶자

여러 개의 INSERT, UPDATE, DELETE 작업은 START TRANSACTION과 COMMIT으로 묶어서 처리하는 게 좋다고 한다.

  • 커밋을 너무 자주 해도 문제고, 반대로 너무 오랫동안 커밋하지 않아도 잠금이 길어져 성능에 악영향을 줄 수 있다.

적절한 범위에서 작업을 묶어 트랜잭션을 관리하자.

LOCK TABLES는 피하자

InnoDB는 동시에 여러 세션이 같은 테이블에 읽고 쓸 수 있도록 잘 설계돼 있어서 굳이 테이블 잠금을 사용할 필요가 없다.

특정 행만 잠그고 싶다면 SELECT ... FOR UPDATE 구문을 사용해서 필요한 행 단위 잠금(Row-level Lock)을 걸 수 있다.

마무리

MySQL도 역할과 책임에 따라 여러 구성 요소로 나뉘어져 있다는 것을 알게 되었다. InnoDB의 구조와 동작 원리를 조금이라도 이해함으로써 MySQL을 더 효과적으로 활용할 수 있을 것 같다.

생각해볼 부분

  • MySQL의 기본 스토리지 엔진이 바뀐다면 사용자 입장에서는 어떤 변화가 있을까?
  • MVCC가 좋아보이는데, 어떤 단점이 있을까?
    • 실제 애플리케이션을 작성하는 개발자 입장에서는 주의할 점이 있을까?