Mysql - MyiSAM 로우 저장 방식 비교

MySQL을 개인이 일반용도로 사용한다면 트랜잭션을 관리하는 InnoDB 저장엔진보다는 MyISAM 저장엔진을 많이 사용하게 된다.

1. 저장옵션의 종류
MyISAM 저장엔진에서 하나의 로우를 저장할 때 사용하는 방법은 2가지가 있는데, 하나는 고정식(fixed) 그리고 다른 하나는 가변식(dynamic)이 있다.

1.1 고정식(fixed)
가변길이 문자열(VARCHAR)을 고정길이 문자열(CHAR)로 변환하여 저장 한다.

예제> CREATE TABLE dgs10_fix(bday CHAR(8) NOT NULL,indx VARCHAR(13) NOT NULL) ROW_FORMAT = FIXED;


1.2 가변식(dynamic)
고정길이 문자열(CHAR)을 가변길이 문자열(VARCHAR)로 변환하여 저장 한다.

예제> CREATE TABLE dgs10_dyn(bday CHAR(8) NOT NULL,indx VARCHAR(13) NOT NULL) ROW_FORMAT = DYNAMIC;

위와 같이 테이블을 생성한 후 Load Data Infile 명령어를 이용하여 데이터를 입력하고서 데이터 파일의 크기를 비교하면 다음과 같다.

dgs10_fix.myd    233,320bytes           
dgs10_dyn.myd  758,290bytes        

두개의 옵션을 비교해보면 각각의 장단점이 있다. 먼저 고정식 옵션은 테이블에 저장되는 로우가 순수한 데이터 타입의 길이만 저장한다는 것이다. 즉, 위에서 생성한 dgs10_fix 테이블의 경우를 보면, 두번째 칼럼인 indx에 들어오는 값이 선언한 문자열의 길이인 13개의 자릿수가 아니라 순수한 데이터의 길이가-최대길이가 4인- 저장되었음을 알 수 있다. 그래서 얻는 잇점은 작은 I/O를 통해서 원하는 데이터를 가져올 수 있기 때문에 읽기 전용의 데이터에 대해 가변식보다 우위에 있는 저장방식이라고 볼 수 있다.
장점 : 가변식보다 적은 저장소를 요구,  이에 따른 적은 I/O유발, 읽기전용 데이터에 좋음
단점 : 만약, 데이터에 대한 업데이트시가 있다면 저장한 데이터가 조각나므로 가변식보다 더 많은 I/O가 필요해진다.

가변식을 보면 원래 선언한 13개의 자릿수가 그대로 저장-공백으로-되어 고정식보다는 더 많은 I/O가 필요하지만 값의 업데이트가 이루어지면 미리 확보한 13개의 자릿수 안에서만 작동하므로 데이터가 조각나지 않는다.
장점 : 로우를 저장할 때 업데이트시에 필요한 저장공간을 미리 가지고 있기 때문에 데이터가 변경이 되어도 동일하게 옵티마이저가 작용을 하여 튜닝시에 도움이 되며 데이터 저장이 조각나지 않기 때문에 추후 테이블에 대한 옵티마이징 작업을 줄여도 된다.
단점 : 고정식보다는 많은 저장소를 요구한다. 이에 따른 I/O가 증가 한다.

다음 그림은 dgs10_dyn.myd 파일을 울트라 에디트에서 헥사모드로 열어 본 것이다.


다음은 dgs10_fix.myd 파일을 열어 본 것이다.



그리고 MySQL에서 제공하는 인터널 문서의 내용 일부를 발췌해서 보면

For example, here is a dynamic row:

03, 00             start of header
04                 actual length
0c                 unused length
01, fc             flags + overflow pointer
****               data in the row
************       unused bytes
                   <-- next row starts here)

그림을 놓고 비교해보면 추가설명이 필요없을 정도로 그 차이가 명확해진다. 고정식은 테이블을 생성할 때 그대로 그 구조를 보존하고 있고, 가변식은 저장되는 데이터의 길이이 따라 저장한다는 것을 알 수 있다.

...

------------------------------------------------update 1

글을 지울까 하고 생각할 정도로 윗 글은 오류투성이이다.

간단하게 윗글을 바로 잡는 다면 가변식과 고정식의 설명이 뒤바뀌었다는 것이다.

고정식(fixed)은 테이블의 구조가 설계대로 유지되기 때문에 잦은 DML작업에 효과적이며,
가변식(dynamic)은 입력되는 데이터의 길이대로 저장되기 때문에 고정식 보다 더 적은 공간을 이용하게 된다. 이러한 더 적은 공간은 작은 I/O를 통해서 데이터를 가져올 수 있으므로 그 만큼 효과적인 로우 저장방법이 되는 것이다.

1.1 고정식(fixed)
가변길이 문자열(VARCHAR)을 고정길이 문자열(CHAR)로 변환하여 저장 한다.

예제> CREATE TABLE dff_fix(bday date NOT NULL,indx VARCHAR(13) NOT NULL) ROW_FORMAT = FIXED;

1.2 가변식(dynamic)
고정길이 문자열(CHAR)을 가변길이 문자열(VARCHAR)로 변환하여 저장 한다.

예제> CREATE TABLE dff_dyn(bday date NOT NULL,indx VARCHAR(13) NOT NULL) ROW_FORMAT = DYNAMIC;

mysql> load data infile 'L:/Projects/Perl/dff.csv'
    -> into table dff_fix
    -> fields terminated by ','
    -> LINES TERMINATED BY '\r\n'
    -> ignore 1 lines
    -> (bday, @v2)
    -> set indx = rtrim(ltrim(@v2));

mysql> load data infile 'L:/Projects/Perl/dff.csv'
    -> into table dff_dyn
    -> fields terminated by ','
    -> LINES TERMINATED BY '\r\n'
    -> ignore 1 lines
    -> (bday, @v2)
    -> set indx = rtrim(ltrim(@v2));

입력 후 파일용량
dff_dyn.myd       381,480bytes
dff_fix.myd         839,256bytes

확인
show table status