[MySQL] Storage Engine (InnoDB vs MyISAM)

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

하지만 어떠한 Storage Engine을 써야할지 고민할 일이 생겼다.

 

전문과 로그에 관련된 데이터를 테이블에 적재해야하는데, 전문과 로그의 경우 정규화하기도 애매하고 Key가 상당히 많았다.

 

대략 300개에 달하는 칼럼이 필요한 상황이였다.

그래서 어떻게 테이블에 적재해야 효율적인지 고민이 필요했다.

 

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

 

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

 

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

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

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

InnoDB

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

InnoDB는 트랜잭션을 지원하고, 커밋과 롤백 그리고 데이터 복구 기능을 제공하므로 데이터를 효과적으로 보호할 수 있다.

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

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

 

여기서 clustered index에 저장한다는 것은 데이터를 PK순서에 맞게 저장한다는 뜻이므로 order by 등 쿼리에 유리할 수 있다.

또한 row-level locking을 제공한다는 뜻은 테이블에 CRUD할 때, 로우별로 락을 잡기 때문에 multi-thread에 보다 효율적이라는 말이다.

InnoDB의 최대 행 저장공간

Mysql 테이블의 행 크기는 스토리지 엔진에 제약이 없다면 기본적으로 최대 65535바이트이다.

하지만 스토리지 엔진에 따라 제약이 추가되어 행 크기는 줄어들 수 있다.

BLOB, TEXT 컬럼의 내용은 행의 남은 부분이 아닌 별도의 공간에 저장되기 때문에 각각 9~12바이트만 영향을 준다.

(포인트 저장 공간이 9~12바이트)

여기서 중요한 것은 위의 내용은 스토리지 엔진에 상관없이 기본적으로 이렇다는 것이다.

 

이제 InnoDB부터 살펴보자.

InnoDB 테이블의 각 데이터는 page들안에 나눠져있다.

각 테이블을 구성하는 page들은 B-tree인덱스라는 트리 데이터 구조안에 정렬되어 있다.

위와 같이 InnoDB는 페이지에 저장하기 때문에 최대 행 크기는 page크기의 반보다 조금 작은데, page size의 기본값이 16KB이므로, 최대 행 크기는 이에 반에 조금 못미치는 8KB 남짓이 될 것이다.

 

여기서 주의할 점은 Mysql5.6이하인 버전에서 컬럼을 8KB넘도록 만들어도 테이블이 잘 만들어진다.

하지만 실제로 데이터를 넣을 때 에러가 발생한다!

 

Mysql5.7 이상에서는 애초에 8KB가 넘는 테이블이 만들어지지 않는다.

하지만 Mysql 5.7 이상에서는 페이지 크기를 늘릴 수 있다.

 

내가 에러가 났던 부분이 바로 여기 부분이였다. 우리의 운용서버는 Maria10.3버전을 사용하고 있었는데, 

Mysql 5.7과 대응되는 버전이므로 칼럼이 300개에 달하는 테이블을 생성할 수가 없었다.

 

그렇다면 MyISAM 엔진을 사용하면 어떨지 특징을 한번 살펴보자.

MyISAM

트랜잭션을 지원하지 않고 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를 사용할 수 없으며 인덱스를 지원하지 않는다.

따라서 거의 가공하지 않을 원시 로그 데이터를 관리하는데에 효율적일 수 있고, 테이블 파티셔닝도 지원한다.

다만 트랜잭션은 지원하지 않는다.