[MySQL] Storage Engine (InnoDB vs MyISAM)

생각없이 Engine을 InnoDB만 사용했지 왜 이것을 사용해야 하는지 고민해본 적이 없었다.

하지만 많은 양의 데이터를 적재하면 데이터의 수가 매우 많거나 column의 갯수가 많아지면 공간부족 현상이 나타나게 되고, 그로 인해 엔진에 대해 고민하기 시작하였다.

대략 500개에 달하는 칼럼이 필요한 상황이였다. 그래서 어떻게 테이블에 적재해야 효율적인지 고민이 필요했다.

또한 InnoDB 테이블에 많은 칼럼을 추가하니 Row size too large. 라는 오류가 발생해서 Engine을 변경하는 방법을 생각하게 되었다.

우선 각 Stroage Engine에 대해 알아보았다.

Mysql Storage Engine은 물리적 저장장치에서 데이터를 어떤 식으로 구성하고 읽어올지를 결정하는 역할을 한다.

기본적으로 8가지의 스토리지 엔진이 탑재되어 있으며 CREATE TABLE문을 사용하여 테이블을 생성할 때 엔진 이름을 추가함으로써 간단하게 설정할 수 있다.

그 중 가장 많이 쓰이는 엔진은 InnoDB, MyISAM, Archive 3가지이다.

InnoDB

테이블 생성 시, 따로 스토리지 엔진을 명시하지 않으면 default로 설정되는 스토리지 엔진이다.

InnoDB는 트랜잭션(tranjection)을 지원하고, 커밋(commit)과 롤백(roll-back) 그리고 데이터 복구 기능을 제공하므로 데이터를 효과적으로 관리할 수 있다.

InnoDB는 기본적으로 row-level locking을 제공하며, 또한 데이터를 clustered index에 저장하여 PK기반의 query의 비용을 줄인다.

또한, PK 제약을 제공하여 데이터 무결성을 보장한다.

여기서 clustered index에 저장한다는 것은 데이터를 PK순서에 맞게 저장한다는 뜻이므로 order by 등 쿼리에 유리할 수 있다. 또한 row-level locking을 제공한다는 뜻은 테이블에 CRUD할 때, 로우별로 락을 잡기 때문에 multi-thread에 보다 효율적이라는 말이다.

물론 장점만 있는 것은 아니다. InnoDB는 더욱 많은 메모리와 디스크를 사용한다. 또한 데이터가 깨졌을 때 단순 파일 백업/복구만으로 처리가 가능한 MyISAM과 달리 InnoDB의 경우 복구 방법이 어렵다. MyISAM이나 Memory 방식이 지원하지 않는 FK의 경우도 테이블 간 데이터 체크로 인한 lock, 특히 dead lock이 발생할 가능성이 있다.

InnoDB의 최대 행 저장공간

Mysql 테이블의 행 크기는 스토리지 엔진에 제약이 없다면 기본적으로 최대 65535바이트이다. 하지만 스토리지 엔진에 따라 제약이 추가되어 행 크기는 줄어들 수 있다.

BLOB, TEXT 컬럼의 내용은 행의 남은 부분이 아닌 별도의 공간에 저장되기 때문에 각각 912바이트만 영향을 준다. (포인트 저장 공간이 912바이트) 여기서 중요한 것은 위의 내용은 스토리지 엔진에 상관없이 기본적으로 이렇다는 것이다.

MyISAM

트랜잭션(tranjection)을 지원하지 않고 table-level locking을 제공한다.

따라서 1개의 ROW을 READ하더라도 테이블 전체에 락을 잡기 때문에 multi-thread 환경에서 성능이 저하될 수 있다.

하지만 InnoDB에 비해 기능적으로 단순하므로 대부분의 작업은 InnoDB보다 속도면에서 우월하다.

단순한 조회의 경우 MyISAM이 InnoDB보다 빠르지만, Order By등 정렬들의 구문이 들어간다면 InnoDB보다 느리다.

왜냐하면 InnoDB는 클러스터링 인덱스에 저장하기 때문에 PK에 따라 데이터 파일이 정렬되어 있지만, MyISAM은 그렇지 않기 때문이다.

Full text searching을 지원한다.

MyISAM 엔진의 경우 최대 행 크기가 기본 MySQL 제약을 따르므로 최대 행 크기는 65535바이트가 될 것이다.

Archive

로그 수집에 적합한 엔진이다. 데이터가 메모리상에서 압축되고 압축된 상태로 디스크에 저장되기 때문에 row-level locking이 가능하다.

다만, 한번 INSERT된 데이터는 UPDATE, DELETE를 사용할 수 없으며 인덱스를 지원하지 않는다. 따라서 거의 가공하지 않을 데이터에 대해서 관리하는데에 효율적일 수 있고, 테이블 파티셔닝도 지원한다. 다만 트랜잭션은 지원하지 않는다.