InnoDB 스토리지 엔진 아키텍처 (1)

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공한다.

그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

 

1. 프라이머리 키(PK)에 의한 클러스터링

엔진	InnoDB	MyISAM
클러스터링 키 지원	지원	미지원
물리적 주소 사용	프라이머리 키 값	레코드의 주소 값

 

InnoDB 스토리지 엔진

• 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.
  • 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이다.
• 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
  • 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리 될 수 있다.
  • 결과적으로 쿼리 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정(쿼리의 실행 계획에서 다른 보조 인덱스보다 프라이머리 키가 선택될 확률이 높음)된다.

레인지 스캔 : 인덱스를 사용하여 특정 범위의 데이터 검색 (가장 많이 사용됨)

 

MyISAM 스토리지 엔진

 

• 클러스터링 키를 지원하지 않아서, MyISAM 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다
• 프라이머리 키는 유니크 제약을 가진 세컨더리 인덱스일 뿐이다.
• 그리고 MyISAM테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적인 레코드의 주소값(ROWID)를 가진다.

 

2. 외래 키 지원

엔진	InnoDB	MyISAM	MEMORY
외래 키 지원	지원	미지원	미지원
인덱스 요구	필요	불필요	불필요
잠금 전파 여부	발생	발생 안함	발생 안함
데드락 발생 가능성	높음	낮음	낮음

 

 

외래 키는 데이터베이스 서버 운영의 불편함때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주도 있는데, 그렇다하더라도 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 할 수 있다.

 

InnoDB 스토리지 엔진 (MyISAM, MEMORY 스토리지 엔진에서는 외래키 지원이 되지 않는다.)

• 특징 : 외래키 지원하는데, 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하다
• 장점
  • 외래 키 제약 조건을 사용하면 부모 테이블의 레코드가 삭제되거나 수정될 때 자식 테이블의 무결성을 보장할 수 있다
  • 외래 키로 설정된 열에는 자동으로 인덱스가 생성되어 검색 및 조인 성능이 향상된다
• 단점 
  • 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파된다.
  • 그로 인해 데드락이 발생할 때가 많으므로 개발할 때에도 외래키의 존재는 주의하는게 좋다.
  • 부모 테이블과 자식 테이블 관계를 명확히 파악하지 못하거나, 외래 키가 복잡하게 얽힌 경우 수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다.
    • 이런 상황때문에 긴급하게 조치해야 할 상황에서 조급해질 수 있다.
    • foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다. 
    • 외래 키 체크 작업을 일시적으로 멈추면 대략 레코드 적재나 삭제 등의 작업도 부가적인 체크가 필요 없기 때문에 훨씬 빠르게 처리할 수 있다.

mysql > SET foreign_key_checks=OFF;

--// 작업 실행

mysql > SET foreign_key_checks=ON;

 

외래 키 체크를 일시적으로 해제했다고 해서 부모와 자식 테이블 간의 관계가 깨진 상태로 그대로 유지해도 된다는 것을 의미하지는 않는다. 예를 들어, 외래 키 체크를 일시적으로 중지한 상황에서 외래 키 관계를 가진 부모 테이블의 레코드를 삭제했다면 반드시 자식 테이블의 레코드도 삭제해서 맞춰준 후 다시 외래 키 체크 기능을 활성화 해야 한다.

 

foreign_key_checks 가 비활성화되면 외래키 관계의 부모 테이블에 대한 작업(ON DELETE CASCADE 와 ON UPDATE CASCASE옵션) 도 무시하게 된다.

 

3. MVCC(Multi Version Concurrency Control)

 

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있다. InnoDB는 언두 로그 (Undo log)를 이용해 이 기능을 구현한다.

 

여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미다.

해를 위해 격리 수준 (Isolation level)이 READ_COMMITED 인 MySQL서버에서 InnoDB스토리지 엔진을 사용하는 테이블의 데이터 변경을 어떻게 처리하는지 보자.

mysql> CREATE TABLE member_test (
    -> m_id INT NOT NULL,
    -> m_name VARCHAR(20) NOT NULL,
    -> m_area VARCHAR(100) NOT NULL,
    -> PRIMARY KEY (m_id),
    -> INDEX ix_area (m_area)
    -> );

mysql> INSERT INTO member_test(m_id, m_name, m_area) VALUES (12, '홍길동', '서울');

mysql> COMMIT;

 

위의 INSERT문이 실행되면 데이터베이스의 상태는 아래와 같은 사태로 바뀐다.

InnoDB의 버퍼 풀과 데이터 파일의 상태

member_test테이블에 UPDATE문장이 실행되면 아래와 같이 된다.

mysql> UPDATE member_test SET m_area='경기' WHERE m_id=12;

UPDATE 후 InnoDB 버퍼풀과 데이터 파일 및 언두영역의 변화

 

InnoDB 버퍼풀의 변경 내용은 InnoDB 스토리지 엔진의 백그라운드 스레드에 의해서 기록된다.

그래서 InnoDB 버퍼풀의 변경 내용이 디스크의 데이터 파일에 기록됐는지 여부는 시점에 따라 다를 수 있다.

 

UPDATE문장이 실행되면 커밋 실행 여부와 관계 없이 InnoDB의 버퍼풀은 새로운 값인 '경기'로 업데이트 된다.

그리고 디스크의 데이터 파일에는 체크 포인트나 InnoDB의 Write스레드에 새로운 값으로 업데이트돼 있을 수도 있고 아닐 수도 있다(InnoDB가 ACID를 보장하기 때문에 일반적으로 InnoDB의 버퍼풀과 데이터 파일은 동일한 상태라고 봐도 무방하다)

아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 다음 같은 쿼리로 작업 중인 레코드를 조회하면 어디에 있는 데이터를 조회할까?

mysql> SELECT * FROM member_test WHERE m_id=12;

 

이 질문의 답은 MySQL 서버의 시스템 변수(transaction_isolation)에 의해 설정된 격리 수준에 따라 다를 것이다.

격리수준	READ_UNCOMMITED	READ_COMMUTED	REPEATABLE READ	SERIALIZABLE
조회되는 데이터 위치	InnoDB 버퍼풀 (커밋 여부와 상관없이 변경된 데이터)	언두 영역 (커밋 되기 전 데이터)	언두 영역 (커밋 되기 전 데이터)	언두 영역 (커밋 되기 전 데이터)

 

이러한 과정을 DBMS에서는 MVCC라고 부른다. 즉, 하나의 레코드(회원 번호가 12인 레코드)에 대해 2개의 버전이 유지되고, 필요에 따라 어떤 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조다.

 

• 언두 영역에 저장되는 데이터의 양은 트랜잭션의 길이와 변경된 데이터의 양에 따라 다르다.
• 트랜잭션이 매우 길거나 많은 양의 데이터를 변경하는 경우, 많은 언두 데이터가 생성되어 저장될 수 있다.
• 이로인해 시스템 테이블 스페이스의 공간을 많이 차지하게 되어 데이터베이스 성능에 영향을 줄 수 있기 때문에 주의해야 한다.

시스템 테이블 스페이스 ?

MySQL에서 데이터베이스 메타데이터와 시스템 테이블을 저장하는 공간이다.
이 공간에는 MySQL 서버의 내부 작동에 필요한 시스템 테이블과 인덱스, 캐시, 로그 등이 저장되기 때문에 
시스템 테이블 스페이스의 크기가 커질 수록 데이터베이스 용량이 증가하게 된다.

시스템 테이블 ?

MySQL데이터베이스의 구조와 동작을 관리하는데 사용되는 특별한 종류의 테이블이다.

 

• UPDATE 후 COMMIT 명령 실행
  • InnoDB는 더 이상 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어 버린다.
  • 커밋되고나면 언두영역에 백업된 데이터가 필요 없어져서 언두 영역 데이터가 삭제되지만, 항상 바로 삭제되는 것은 아니다.
  • 이 언두영역을 필요로 하는 트랜잭션이 더는 없을때 비로소 삭제된다.
• UPDATE 후 ROLLBACK 실행
  • InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해버린다.

4. 잠금 없는 일관된 읽기(Non-Locking Conistent Read)

 

InnoDB 스토리지 엔진은 MVCC기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.

잠금을 걸지 않기 때문에 InnoDB 에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.

격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITED나 READ_COMITTED, REPEATABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT)작업은 다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고 바로 실행된다.

격리 수준	설명
READ_UNCOMMITED	다른 트랜잭션의 변경 작업과 관계 없이 항상 잠금을 대기하지 않고, 변경되기 전의 데이터를 바로 읽을 수 있음.(InnoDB 버퍼풀 때문)
READ_COMITTED	다른 트랜잭션의 변경 작업에 대해 커밋된 데이터만을 읽음. 변경되지 않은 데이터에 대해서는 잠금을 대기하지 않고 읽을 수 있음.(언두영역 때문)
REPEATABLE_READ	같은 트랜잭션에서 반복해서 읽는 경우에도 항상 일관된 데이터를 보장함. 다른 트랜잭션에서의 변경 작업에 대해서는 잠금을 대기하지 않고 읽을 수 있음(언두영역 때문)
SERIALIZABLE	가장 높은 격리 수준으로, 모든 읽기 작업에 대해 잠금을 걸고 다른 트랜잭션의 변경 작업을 기다림. 변경되지 않은 데이터에 대해서도 잠금을 대기해야 함.

 

특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다하더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다. 이를 '잠금 없는 일관된 읽기'라고 표현하며, InnoDB 에서는 변경되기 전의 데이터를 읽기 위해 언두로그를 사용한다.

오랜 시간동안 활성 상태인 트랜잭션으로 인해 MySQL서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 바로 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제다. 따라서 트랜잭션이 시작됐다면 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 게 좋다.

 

5. 자동 데드락 감지

 

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(wait-for list)형태로 관리한다. InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션을 찾아서 그 중 하나를 강제 종료한다.

 

이때 어느 트랜잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다. 트랜잭션이 언두 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리를 해야 할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다.