MySQL의 스토리지 엔진(InnoDB, MyISAM)이란?

지난번 포스팅에서는 MySQL의 쿼리 작성 문법 및 쿼리 실행 계획, 내부적으로 실행되는 쿼리 구조에대해 알아보았습니다.

※ MySQL의 쿼리 실행 구조

이번 포스팅에서는 MySQL 쿼리 실행시 실행되는 스토리지엔진 (InnoDB,MyISAM)이란 무엇인지, 그리고 각각의 장단점 및 차이점에 대해 알아보겠습니다.

 

스토리지 엔진

서버 엔진이 필요한 물리적인 데이터를 가져오는 장치.

MySQL에서 테이블을 생성시에 서버에서는 frm확장자의 파일을 생성하는데, 이 파일에 접근시 속성을 설정하는 것을 담당한다. MySQL에서는 대표적으로 MyISAM과 InnoDB이 있으며, 동일한 데이터베이스 일지라도 테이블마다 스토리지 엔진을 다르게 설정할 수 있다.

 

InnoDB

MySQL의 바이너리에 내장되어있는 스토리지 엔진이다. 또한 트랜잭션을 지원하며 CPU와 메모리 사이에 존재한다.

 

특징 및 장단점

• 기본적으로 PK를 기준으로 클러스터링되어 PK 키값의 순서대로 디스크에 저장된다.
• 외래키(FK)를 지원한다.
• Inno Buffer Pool
  
  • 엔진 내 가장 핵심적인 부분으로 디스크의 데이터 파일이나 정보를 메모리에 캐시해두는 역할을 담당한다.
  • ex) SELECT시 해당 결과가 버퍼에 존재하지 않을시 버퍼풀에 기록하고 결과값을 반환한다.
• MVCC(Multi Version Concurrency Control)
  • 하나의 레코드에 대해 여러개의 버전이 동시에 관리된다.
  • 시스템 변수에 설정된 격리수준에 따라 레코드 조회시 어디에 있는 데이터를 조회하는지에 다르다.
• Non-Locking Consistent Read
  • Lock을 하지않음으로써 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지않고 읽기 작업이 가능하다.
  • 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지않고바로 실행된다.
  • InnoDB에서는 변경되기 전의 데이터를 읽기위해 Undo로그를 사용한다.
• 데이터 안전성 및 무결성을 보장한다.
  • 트랜잭션, 외래키, 제약조건, 동시성, ACID를 제공함으로써 무결성 및 안전성을 보장할 수 있다.
• 장애 복구 기능 및 자동 데드락 감지
  • 논리적으로 장애 복구를 수행하며 교착상태 발생시 감지하여 자동으로 해제한다.
• 대용량 처리를 하게된다면 순간적으로 많은 자원을 소모한다.
• 단순한 파일 백업 복구방식이 아닌, 특정한 방법을 사용하여 복구를 수행한다.

 

사용 예시

• 데이터의 일관성과 무결성을 중시하는 어플리케이션 (주문처리 또는 금융 어플리케이션)
• 다수의 동시 사용자가 데이터베이스에 엑세스하는 웹 어플리케이션

 

MyISAM

운영체제와 메모리 내부에 존재하며 데이터의 조회시 성능이 최적화 되어있는 엔진이다.

 

특징 및 장단점

• 구조가 단순하며, 속도가 빠르다.
• Lock 발생시 테이블 레벨에서 잠금처리된다.
  • 테이블 작업시 특정 행을 수정하려고 할 시 테이블 전체에 락이 걸려 다른 사람이 작업할 수 없다.
• 트랜잭션에 대한 지원이 없기때문에 작업도중 문제가 생겨도 DB안으로 데이터가 들어간다.
  • 데이터 무결성이 보장되지 않는다.
• SELECT작업에 자주 사용된다. 즉, READ작업에 효과적이다.
• InnoDB의 BufferPool과 같은 기능을하는 키 캐시(또는 키 버퍼),가 존재한다.
  • 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 수행한다.

사용 예시

• 테스트 및 개발환경
• 읽기 중심의 웹 어플리케이션
  • 블로그 및 포럼과 같은 읽기 중심 어플리케이션에서 주로 사용한다.

 

MyISAM vs InnoDB

출처 - https://velog.io/@sweet_sumin/DB-%EC%8A%A4%ED%86%A0%EB%A6%AC%EC%A7%80-%EC%97%94%EC%A7%84-%EB%B3%84-%EC%B0%A8%EC%9D%B4

 

정리

InnoDB

• MyISAM보다 무겁고 느리지만, 트랜잭션이 지원되며 데이터 무결성과 안전성을 보장한다.
• Lock은 Row-Level로 잠금처리되며, 다른 사용자가 같은 Row를 접근할 시에는 Undo로그에서 읽어온다.
• 논리적으로 데이터를 복구하며, 교착상태 발생시에도 자동으로 감지하여 해제한다.
• Inno Buffer Pool을 사용하여 데이터를 캐싱처리한다.
• 무결성과 안전성이 필요한 경우에 주로 사용할 수 있어 주문처리 또는 금융관련 어플리케이션에서 유용하게 사용할 수 있다.

MyISAM

• InnoDB에 비해 가벼우며, Read작업이 많을경우에 사용한다.
• Lock은 테이블 단계에서 잠금처리되며, 작업이 진행중일 경우 다른 사용자는 작업할 수 없다.
• 트랜잭션이 처리되지않아 데이터 무결성을 보장하지 않는다.
• Inno Buffer Pool과 비슷한 캐싱을하는 키 캐시(또는 키 버퍼)가 존재한다.
• 읽기 작업중심의 어플리케이션에 유용하다. (블로그, 뉴스 등의 읽기 중심의 포럼 등)