MySQL 서버(MySQL 엔진, InnoDB 스토리지 엔진) 정리

MySQL 서버

MySQL 서버 = MySQL 엔진 + 스토리지 엔진

MySQL 엔진: 클라이언트로부터 오는 요청 처리(요청된 SQL 문장을 분석, 최적화, ...)를 담당. 서버 접속 및 쿼리 요청 처리하는 커넥션 핸들러, SQL 파서, 전처리기, 쿼리 최적화를 옵티마이저로 이루어짐. SQL 처리를 위한 두뇌역할

스토리지 엔진: 실제 데이터를 디스크 스토리지에 저장하거나 조회하는 부분 담당. 실질적인 데이터 저장 및 조회 역할 담당.

MySQL 서버 구조

InnoDB 스토리지 엔진

MySQL 이 제공하는 스토리지 엔진 중 유일하게 레코드 기반 락(Lock)을 제공하며 이로 인해 높은 동시성 처리가 가능하며 안정적이다.
MySQL 스토리지 엔진 중 가장 많이 사용된다고 한다.

InnoDB 스토리지 엔진 특징

• 테이블은 Primary Key 순서대로 디스크에 저장되며 그렇기 때문에 Range scan이 굉장히 빠르다.
• 실행계획 결정에서 다른 보조 인덱스에 비해 Primary Key가 선택될 확률이 높다.
• MVCC를 이용하여 락을 걸지 않고 읽기 수행
  • MVCC가 뭔지는 아래에서 참조
• 외래 키 지원
  • 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 대한 인덱스 생성이 필요하고 변경 시에는 반드시 부모 테이블과 자식 테이블의 데이터가 있는지 체크하는 작업이 필요하므로 잠금(Lock)이 전파되는 특징이 있다. Lock이 여러 곳에 전파되면서 데드락 발생을 시킬 수 있기 때문에 실무에서 제약을 걸지 않는 경우가 많다.
• 자동 데드락 감지 : 감지 시 변경된 레코드가 가장 작은 트랜잭션을 롤백해버려서 데드락을 풀어준다.
• 자동 장애 복구 : 완료하지 못한 트랜잭션이나 일부만 기록되어 손상된 데이터 페이지 등을 자동 복구한다

InnoDB 구조

메모리 영역

• InnoDB 버퍼 풀: 실제 데이터 블록(페이지)를 메모리에 적재하는 영역 + insert 버퍼 + undo 레코드
• 로그 버퍼 : 로그 스레드에 의해 로그 파일로 기록되기 전 버퍼링하는 영역

디스크 영역

• 시스템 테이블 스페이스
• 사용자 테이블 스페이스
• Redo 로그

InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스를 메모리에 캐시해두는 공간. (실질적인 데이터 영역이므로 메모리의 대부분을 차지함.)

쿼리로 인해 변경되어 할 레코드들이 디스크 이곳저곳에 있는데 메모리에 모아서 한 번에 디스크 작업을 발생시키는 "쓰기 지연" 역할을 하며,
더티 페이지(디스크에 기록되지 않은 데이터를 가진 페이지)를 주기적으로 체크하여 (특정 조건에 맞으면) 디스크에 쓰는 작업을 함.

데이터와 인덱스를 저장한다!

Undo 레코드

UPDATE, DELETE 문과 같이 데이터를 변경하는 쿼리로 데이터를 변경했을 때 변경되기 이전 데이터를 보관하는 공간.
롤백 용도나 트랜잭션 격리 수준에 따라 대응하기 용도로 사용된다.

변경 이전 레코드 값을 기록하여 롤백 및 트랜잭션 격리 수준을 보장한다!

insert 버퍼

INSERT, UPDATE 등 데이터 파일을 변경할 때는 데이터만이 아니라 연관된 인덱스를 변경하는 작업이 필요하다.
인덱스를 변경하는 작업은 랜덤 디스크를 읽는 작업이 필요하므로 자원 소모가 크다.
이를 최적화하기 위해 InnoDB가 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트하지만 변경해야 할 인덱스 페이지가 버퍼 풀에 없는 경우는 디스크로부터 읽어와서 버퍼 풀에 적재하고 인덱스를 업데이트를 해야 하는데 이때, 즉시 업데이트하지 않고 임시 공간에 저장해둔다. 이 임시 공간이 인서트 버퍼다.

레코드 변경에 의해 인덱스를 변경해야 할 때, 인덱스 페이지가 없을 경우 바로 인덱스 페이지를 로드해서 인덱스를 업데이트하지 않고 자연스럽게 인덱스가 있는 페이지가 사용될 때까지 버퍼링한다!

redo 로그

데이터 파일을 변경하는 것은 랜덤하게 디스크에 기록해야 하기 때문에 디스크를 상당히 바쁘게 하는 비용이 비싼 작업이다.
이 작업을 모아서 처리하여 성능을 향상하기 위해 InnoDB 버퍼 풀 같은 장치가 있다.
그러나 InnoDB 버퍼 풀만으로는 데이터베이스의 ACID를 보장할 수 없다.
이를 위해서 순차적으로 디스크에 기록하는 로그 파일을 가지고 있는데 이곳이 리두(Redo) 로그다.
너무 많은 양의 변경이 일어나게 되면 리두(Redo) 로그에 기록하는 것이 큰 문제가 되는데 이 부분을 보완하기 위해서 로그 버퍼도 있다.

데이터베이스의 acid를 보장한다!

MVCC(Multi Version Concurrency Control)

목적은 Lock(잠금)을 사용하지 않는 일관된 읽기를 하는 데 있다.
하나의 레코드에 대해 여러 버전으로 관리하는 방식을 MVCC라 한다.

MySQL은 레코드를 여러 버전으로 관리하는 방법으로 트랜잭션의 커밋/롤백 여부와 상관없이 변경 이전 레코드의 데이터를 Undo 로그에 남겨두는 방법을 사용한다.

Undo 로그를 남겨서 여러 버전으로 관리하면 격리 수준에 따라 Lock(잠금)을 사용하지 않고 일관된 조회가 가능하다.
만약 격리 수준을 READ_UNCOMMITED로 설정했으면 InnoDB 버퍼 풀에 있는 최신 버전의 레코드를 조회하면 되고, READ_COMMITED 이상의 격리 수준으로 설정했으면 메모리 영역인 Undo 로그에 저장된 레코드 값으로 레코드를 조회하면 굳이 레코드의 Lock을 걸 필요가 없이 일관된 읽기가 가능해진다.

단, 트랜잭션 범위가 길어져서 undo 로그 영역에 이전 버전의 레코드들이 많이 쌓여 시스템 테이블 스페이스 공간이 많이 필요로 할 수 있기 때문에 트랜잭션 범위를 최소화할 필요가 있다.

참고

https://jeong-pro.tistory.com/239