17.10 InnoDB Row Formats

17.10 InnoDB Row Formats

테이블의 row format은 테이블의 row가 물리적으로 어떻게 저장되는지를 결정하며, 이는 다시 쿼리와 DML 작업의 성능에 영향을 줄 수 있습니다. 더 많은 row가 하나의 디스크 페이지에 들어갈수록 쿼리와 인덱스 조회가 더 빠르게 동작할 수 있고, 버퍼 풀에서 필요한 캐시 메모리가 줄어들며, 갱신된 값을 기록하는 데 필요한 I/O도 줄어듭니다.

각 테이블의 데이터는 페이지로 나뉩니다. 각 테이블을 구성하는 페이지들은 B-tree 인덱스라고 하는 트리 데이터 구조로 배열됩니다. 테이블 데이터와 세컨더리 인덱스 모두 이 타입의 구조를 사용합니다. 전체 테이블을 나타내는 B-tree 인덱스는 클러스터드 인덱스라고 하며, 프라이머리 키 컬럼에 따라 정렬됩니다. 클러스터드 인덱스 데이터 구조의 노드에는 row의 모든 컬럼 값이 들어 있습니다. 세컨더리 인덱스 구조의 노드에는 인덱스 컬럼 값과 프라이머리 키 컬럼 값이 들어 있습니다.

가변 길이 컬럼은 컬럼 값이 B-tree 인덱스 노드에 저장된다는 규칙의 예외입니다. B-tree 페이지에 들어가기에는 너무 긴 가변 길이 컬럼은 오버플로 페이지라고 불리는, 별도로 할당된 디스크 페이지에 저장됩니다. 이러한 컬럼을 오프 페이지 컬럼이라고 합니다. 오프 페이지 컬럼의 값은 오버플로 페이지의 단일 연결 리스트에 저장되며, 각 컬럼은 하나 이상의 오버플로 페이지로 구성된 고유한 리스트를 가집니다. 컬럼 길이에 따라, 저장 공간 낭비와 별도 페이지를 읽는 일을 피하기 위해 가변 길이 컬럼 값 전체 또는 프리픽스를 B-tree에 저장합니다.

InnoDB 스토리지 엔진은 네 가지 row format을 지원합니다: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED.

Table 17.12 InnoDB Row Format Overview

Row Format	Compact Storage Characteristics	Enhanced Variable-Length Column Storage	Large Index Key Prefix Support	Compression Support	Supported Tablespace Types
REDUNDANT	No	No	No	No	system, file-per-table, general
COMPACT	Yes	No	No	No	system, file-per-table, general
DYNAMIC	Yes	Yes	Yes	No	system, file-per-table, general
COMPRESSED	Yes	Yes	Yes	Yes	file-per-table, general

다음 항목에서는 row format의 스토리지 특성과 테이블의 row format을 정의하고 확인하는 방법을 설명합니다.

• REDUNDANT Row Format

• COMPACT Row Format

• DYNAMIC Row Format

• COMPRESSED Row Format

• Defining the Row Format of a Table

• Determining the Row Format of a Table

REDUNDANT Row Format

REDUNDANT format은 이전 버전의 MySQL과의 호환성을 제공합니다.

REDUNDANT row format을 사용하는 테이블은 가변 길이 컬럼 값의 처음 768 byte를 (VARCHAR, VARBINARY, BLOB, TEXT 타입) B-tree 노드 내 인덱스 레코드에 저장하고, 나머지는 오버플로 페이지에 저장합니다. 길이가 768 byte 이상인 고정 길이 컬럼은 가변 길이 컬럼으로 인코딩되어 오프 페이지에 저장될 수 있습니다. 예를 들어, 문자 집합의 최대 byte 길이가 3보다 크면(예: utf8mb4) CHAR(255) 컬럼은 768 byte를 초과할 수 있습니다.

컬럼 값이 768 byte 이하인 경우 오버플로 페이지는 사용되지 않으며, 값이 전부 B-tree 노드에 저장되므로 I/O가 일부 절약될 수 있습니다. 이는 상대적으로 짧은 BLOB 컬럼 값에는 잘 동작하지만, B-tree 노드가 키 값 대신 데이터로 채워져 효율성이 떨어질 수 있습니다. 많은 BLOB 컬럼을 가진 테이블은 B-tree 노드가 너무 가득 차 row 수가 너무 적어져, row가 더 짧거나 컬럼 값이 오프 페이지에 저장되는 경우보다 전체 인덱스 효율성이 떨어질 수 있습니다.

REDUNDANT Row Format Storage Characteristics

REDUNDANT row format은 다음과 같은 스토리지 특성을 가집니다:

• 각 인덱스 레코드는 6 byte 헤더를 포함합니다. 이 헤더는 연속 레코드들을 서로 연결하고 row 레벨 잠금에 사용됩니다.

• 클러스터드 인덱스의 레코드에는 모든 사용자 정의 컬럼에 대한 필드가 들어 있습니다. 추가로, 6 byte 트랜잭션 ID 필드와 7 byte 롤 포인터 필드가 있습니다.

• 테이블에 프라이머리 키가 정의되지 않은 경우, 각 클러스터드 인덱스 레코드는 6 byte row ID 필드도 포함합니다.

• 각 세컨더리 인덱스 레코드에는 세컨더리 인덱스에 없는, 클러스터드 인덱스 키에 대해 정의된 모든 프라이머리 키 컬럼이 포함됩니다.

• 레코드에는 레코드의 각 필드에 대한 포인터가 들어 있습니다. 레코드 내 필드의 전체 길이가 128 byte보다 작으면 포인터는 1 byte이고, 그렇지 않으면 2 byte입니다. 포인터 배열을 레코드 디렉터리라고 합니다. 포인터가 가리키는 영역이 레코드의 데이터 부분입니다.

• 내부적으로, CHAR(10) 같은 고정 길이 문자 컬럼은 고정 길이 포맷으로 저장됩니다. VARCHAR 컬럼에서는 뒤쪽 공백이 잘리지 않습니다.

• 길이가 768 byte 이상인 고정 길이 컬럼은 가변 길이 컬럼으로 인코딩되어 오프 페이지에 저장될 수 있습니다. 예를 들어, 문자 집합의 최대 byte 길이가 3보다 크면(예: utf8mb4) CHAR(255) 컬럼은 768 byte를 초과할 수 있습니다.

• SQL NULL 값은 레코드 디렉터리에서 1 또는 2 byte를 예약합니다. SQL NULL 값이 가변 길이 컬럼에 저장될 경우 레코드의 데이터 부분에서 0 byte를 예약합니다. 고정 길이 컬럼의 경우 컬럼의 고정 길이만큼을 레코드의 데이터 부분에 예약합니다. NULL 값에 대해 고정 공간을 예약하면, 인덱스 페이지 단편화를 유발하지 않고 컬럼을 NULL에서 non-NULL 값으로 인플레이스 업데이트할 수 있습니다.

COMPACT Row Format

COMPACT row format은 REDUNDANT row format에 비해 row 스토리지 공간을 약 20% 줄이는 대신, 일부 작업에서 CPU 사용량을 증가시킵니다. 워크로드가 캐시 적중률과 디스크 속도에 의해 제한되는 전형적인 경우라면 COMPACT format이 더 빠를 가능성이 높습니다. 워크로드가 CPU 속도에 의해 제한되는 경우, compact format은 더 느릴 수도 있습니다.

COMPACT row format을 사용하는 테이블은 가변 길이 컬럼 값의 처음 768 byte를 (VARCHAR, VARBINARY, BLOB, TEXT 타입) B-tree 노드 내 인덱스 레코드에 저장하고, 나머지는 오버플로 페이지에 저장합니다. 길이가 768 byte 이상인 고정 길이 컬럼은 가변 길이 컬럼으로 인코딩되어 오프 페이지에 저장될 수 있습니다. 예를 들어, 문자 집합의 최대 byte 길이가 3보다 크면(예: utf8mb4) CHAR(255) 컬럼은 768 byte를 초과할 수 있습니다.

컬럼 값이 768 byte 이하인 경우 오버플로 페이지는 사용되지 않으며, 값이 전부 B-tree 노드에 저장되므로 I/O가 일부 절약될 수 있습니다. 이는 상대적으로 짧은 BLOB 컬럼 값에는 잘 동작하지만, B-tree 노드가 키 값 대신 데이터로 채워져 효율성이 떨어질 수 있습니다. 많은 BLOB 컬럼을 가진 테이블은 B-tree 노드가 너무 가득 차 row 수가 너무 적어져, row가 더 짧거나 컬럼 값이 오프 페이지에 저장되는 경우보다 전체 인덱스 효율성이 떨어질 수 있습니다.

COMPACT Row Format Storage Characteristics

COMPACT row format은 다음과 같은 스토리지 특성을 가집니다:

• 각 인덱스 레코드는 5 byte 헤더를 포함하며, 그 앞에 가변 길이 헤더가 붙을 수 있습니다. 이 헤더는 연속 레코드들을 서로 연결하고 row 레벨 잠금에 사용됩니다.

• 레코드 헤더의 가변 길이 부분에는 NULL 컬럼을 나타내는 비트 벡터가 포함됩니다. 인덱스 내 NULL이 될 수 있는 컬럼 수가 _N_이면, 비트 벡터는 CEILING(N/8) byte를 차지합니다. (예를 들어, NULL이 될 수 있는 컬럼이 9~16개이면 비트 벡터는 2 byte를 사용합니다.) NULL인 컬럼은 이 벡터의 비트 이외의 공간을 차지하지 않습니다. 가변 길이 헤더 부분에는 가변 길이 컬럼의 길이도 포함됩니다. 각 길이는 컬럼의 최대 길이에 따라 1 또는 2 byte를 사용합니다. 인덱스의 모든 컬럼이 NOT NULL이며 고정 길이인 경우 레코드 헤더에는 가변 길이 부분이 없습니다.

• non-NULL 가변 길이 필드마다 레코드 헤더에는 1 또는 2 byte의 컬럼 길이가 들어 있습니다. 컬럼의 일부가 오버플로 페이지에 외부 저장되거나, 최대 길이가 255 byte를 초과하고 실제 길이가 127 byte를 초과하는 경우에만 2 byte가 필요합니다. 외부에 저장된 컬럼의 경우, 2 byte 길이는 내부에 저장된 부분의 길이와 외부에 저장된 부분을 가리키는 20 byte 포인터의 길이를 합한 값을 나타냅니다. 내부에 저장되는 부분은 768 byte이므로 길이는 768+20입니다. 20 byte 포인터는 컬럼의 실제 길이를 저장합니다.

• 레코드 헤더 다음에는 non-NULL 컬럼의 데이터 내용이 옵니다.

• 클러스터드 인덱스의 레코드에는 모든 사용자 정의 컬럼에 대한 필드가 들어 있습니다. 추가로, 6 byte 트랜잭션 ID 필드와 7 byte 롤 포인터 필드가 있습니다.

• 테이블에 프라이머리 키가 정의되지 않은 경우, 각 클러스터드 인덱스 레코드는 6 byte row ID 필드도 포함합니다.

• 각 세컨더리 인덱스 레코드에는 세컨더리 인덱스에 없는, 클러스터드 인덱스 키에 대해 정의된 모든 프라이머리 키 컬럼이 포함됩니다. 프라이머리 키 컬럼 중 하나라도 가변 길이이면, 세컨더리 인덱스가 고정 길이 컬럼에 대해 정의되었더라도 각 세컨더리 인덱스의 레코드 헤더에는 그 길이를 기록하기 위한 가변 길이 부분이 있습니다.

• 내부적으로, 비가변 길이 문자 집합에 대해서는 CHAR(10) 같은 고정 길이 문자 컬럼이 고정 길이 포맷으로 저장됩니다.

뒤쪽 공백은 VARCHAR 컬럼에서 잘리지 않습니다.

• 내부적으로, utf8mb3 및 utf8mb4 같은 가변 길이 문자 집합의 경우, InnoDB는 CHAR(N) 을 뒤쪽 공백을 잘라 N byte로 저장하려고 시도합니다. CHAR(N) 컬럼 값의 byte 길이가 N byte를 초과하면, 뒤쪽 공백은 컬럼 값 byte 길이의 최대치까지 잘립니다. CHAR(N) 컬럼의 최대 길이는 최대 문자 byte 길이 × _N_입니다.

최소 N byte가 CHAR(N) 에 대해 예약됩니다. 많은 경우 최소 공간 _N_을 예약하면 인덱스 페이지 단편화를 유발하지 않고 컬럼 업데이트를 인플레이스로 수행할 수 있습니다. 비교를 위해, REDUNDANT row format을 사용할 때 CHAR(N) 컬럼은 최대 문자 byte 길이 × _N_만큼 공간을 차지합니다.

길이가 768 byte 이상인 고정 길이 컬럼은 가변 길이 필드로 인코딩되어 오프 페이지에 저장될 수 있습니다. 예를 들어, 문자 집합의 최대 byte 길이가 3보다 크면(예: utf8mb4) CHAR(255) 컬럼은 768 byte를 초과할 수 있습니다.

DYNAMIC Row Format

DYNAMIC row format은 COMPACT row format과 동일한 스토리지 특성을 제공하지만, 긴 가변 길이 컬럼에 대한 향상된 스토리지 기능을 추가하고 큰 인덱스 키 프리픽스를 지원합니다.

테이블을 ROW_FORMAT=DYNAMIC으로 생성하면, InnoDB는 긴 가변 길이 컬럼 값 (VARCHAR, VARBINARY, BLOB, TEXT 타입)을 완전히 오프 페이지에 저장할 수 있으며, 클러스터드 인덱스 레코드에는 오버플로 페이지에 대한 20 byte 포인터만 들어 있습니다. 길이가 768 byte 이상인 고정 길이 필드는 가변 길이 필드로 인코딩됩니다. 예를 들어, 문자 집합의 최대 byte 길이가 3보다 크면(예: utf8mb4) CHAR(255) 컬럼은 768 byte를 초과할 수 있습니다.

컬럼이 오프 페이지에 저장되는지는 페이지 크기와 row 전체 크기에 따라 달라집니다. row가 너무 길면, 클러스터드 인덱스 레코드가 B-tree 페이지에 들어갈 수 있을 때까지 가장 긴 컬럼이 오프 페이지 스토리지 대상으로 선택됩니다. TEXT와 BLOB 컬럼이 40 byte 이하인 경우 인라인으로 저장됩니다.

DYNAMIC row format은 row 전체가 인덱스 노드에 들어가는 경우(이는 COMPACT 및 REDUNDANT format도 마찬가지입니다) 그 효율을 유지하지만, 긴 컬럼 데이터 byte가 대량으로 B-tree 노드를 채우는 문제는 피합니다. DYNAMIC row format은 긴 데이터 값의 일부를 오프 페이지에 저장하는 경우 전체 값을 오프 페이지에 저장하는 것이 보통 가장 효율적이라는 아이디어를 기반으로 합니다. DYNAMIC format에서는 짧은 컬럼이 B-tree 노드에 남을 가능성이 높아, 주어진 row에 필요한 오버플로 페이지 수를 최소화합니다.

DYNAMIC row format은 최대 3072 byte까지의 인덱스 키 프리픽스를 지원합니다.

DYNAMIC row format을 사용하는 테이블은 system tablespace, file-per-table tablespace, general tablespace에 저장될 수 있습니다. DYNAMIC 테이블을 system tablespace에 저장하려면, innodb_file_per_table을 비활성화하고 일반 CREATE TABLE 또는 ALTER TABLE 문을 사용하거나, CREATE TABLE 또는 ALTER TABLE과 함께 TABLESPACE [=] innodb_system 테이블 옵션을 사용합니다. innodb_file_per_table 변수는 general tablespace에는 적용되지 않으며, TABLESPACE [=] innodb_system 테이블 옵션을 사용해 DYNAMIC 테이블을 system tablespace에 저장하는 경우에도 적용되지 않습니다.

DYNAMIC Row Format Storage Characteristics

DYNAMIC row format은 COMPACT row format의 변형입니다. 스토리지 특성에 대해서는 COMPACT Row Format Storage Characteristics를 참조하십시오.

COMPRESSED Row Format

COMPRESSED row format은 DYNAMIC row format과 동일한 스토리지 특성과 기능을 제공하지만, 테이블과 인덱스 데이터 압축을 추가로 지원합니다.

COMPRESSED row format은 DYNAMIC row format과 유사한 오프 페이지 스토리지 내부 구조를 사용하지만, 테이블과 인덱스 데이터가 압축되고 더 작은 페이지 크기를 사용함에 따른 추가적인 스토리지 및 성능 고려 사항이 있습니다. COMPRESSED row format에서는 KEY_BLOCK_SIZE 옵션이 클러스터드 인덱스에 얼마나 많은 컬럼 데이터가 저장되고, 얼마나 많은 데이터가 오버플로 페이지로 이동되는지를 제어합니다. COMPRESSED row format에 대한 자세한 내용은 Section 17.9, “InnoDB Table and Page Compression”을 참조하십시오.

COMPRESSED row format은 최대 3072 byte까지의 인덱스 키 프리픽스를 지원합니다.

COMPRESSED row format을 사용하는 테이블은 file-per-table tablespace 또는 general tablespace에 생성할 수 있습니다. system tablespace는 COMPRESSED row format을 지원하지 않습니다. COMPRESSED 테이블을 file-per-table tablespace에 저장하려면 innodb_file_per_table 변수를 활성화해야 합니다. innodb_file_per_table 변수는 general tablespace에는 적용되지 않습니다. general tablespace는 compressed 및 uncompressed 테이블이 서로 다른 물리적 페이지 크기를 갖기 때문에 동일한 general tablespace 내에 공존할 수 없다는 점을 제외하면 모든 row format을 지원합니다. 자세한 내용은 Section 17.6.3.3, “General Tablespaces”를 참조하십시오.

Compressed Row Format Storage Characteristics

COMPRESSED row format은 COMPACT row format의 변형입니다. 스토리지 특성에 대해서는 COMPACT Row Format Storage Characteristics를 참조하십시오.

Defining the Row Format of a Table

InnoDB 테이블의 기본 row format은 innodb_default_row_format 변수로 정의되며, 기본값은 DYNAMIC입니다. 이 기본 row format은 ROW_FORMAT 테이블 옵션이 명시적으로 정의되지 않았거나 ROW_FORMAT=DEFAULT가 지정된 경우 사용됩니다.

테이블의 row format은 CREATE TABLE 또는 ALTER TABLE 문에서 ROW_FORMAT 테이블 옵션을 사용해 명시적으로 정의할 수 있습니다. 예를 들어:

1CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

명시적으로 정의된 ROW_FORMAT 설정은 기본 row format을 재정의합니다. ROW_FORMAT=DEFAULT를 지정하는 것은 암시적 기본값을 사용하는 것과 같습니다.

innodb_default_row_format 변수는 동적으로 설정할 수 있습니다:

1mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;

유효한 innodb_default_row_format 옵션에는 DYNAMIC, COMPACT, REDUNDANT가 있습니다. system tablespace에서 사용을 지원하지 않는 COMPRESSED row format은 기본값으로 정의할 수 없습니다. COMPRESSED는 CREATE TABLE 또는 ALTER TABLE 문에서만 명시적으로 지정할 수 있습니다. innodb_default_row_format 변수를 COMPRESSED로 설정하려 하면 오류가 반환됩니다:

1mysql> SET GLOBAL innodb_default_row_format=COMPRESSED;
2ERROR 1231 (42000): Variable 'innodb_default_row_format'
3can't be set to the value of 'COMPRESSED'

새로 생성되는 테이블은 ROW_FORMAT 옵션이 명시적으로 지정되지 않았거나 ROW_FORMAT=DEFAULT가 사용된 경우 innodb_default_row_format 변수로 정의된 row format을 사용합니다. 예를 들어, 다음 CREATE TABLE 문은 innodb_default_row_format 변수로 정의된 row format을 사용합니다.

1CREATE TABLE t1 (c1 INT);

1CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

ROW_FORMAT 옵션이 명시적으로 지정되지 않았거나 ROW_FORMAT=DEFAULT가 사용된 경우, 테이블을 재구축하는 작업은 innodb_default_row_format 변수로 정의된 format으로 테이블의 row format을 묵시적으로 변경합니다.

테이블 재구축 작업에는, 테이블 재구축이 필요한 ALGORITHM=COPY 또는 ALGORITHM=INPLACE를 사용하는 ALTER TABLE 작업이 포함됩니다. 자세한 내용은 Section 17.12.1, “Online DDL Operations”를 참조하십시오. OPTIMIZE TABLE 역시 테이블 재구축 작업입니다.

다음 예는 row format을 명시적으로 정의하지 않고 생성된 테이블에 대해, 테이블 재구축 작업이 row format을 묵시적으로 변경하는 동작을 보여줍니다.

1mysql> SELECT @@innodb_default_row_format;
2+-----------------------------+
3| @@innodb_default_row_format |
4+-----------------------------+
5| dynamic                     |
6+-----------------------------+
7
8mysql> CREATE TABLE t1 (c1 INT);
9
10mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
11*************************** 1. row ***************************
12     TABLE_ID: 54
13         NAME: test/t1
14         FLAG: 33
15       N_COLS: 4
16        SPACE: 35
17   ROW_FORMAT: Dynamic
18ZIP_PAGE_SIZE: 0
19   SPACE_TYPE: Single
20
21mysql> SET GLOBAL innodb_default_row_format=COMPACT;
22
23mysql> ALTER TABLE t1 ADD COLUMN (c2 INT);
24
25mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
26*************************** 1. row ***************************
27     TABLE_ID: 55
28         NAME: test/t1
29         FLAG: 1
30       N_COLS: 5
31        SPACE: 36
32   ROW_FORMAT: Compact
33ZIP_PAGE_SIZE: 0
34   SPACE_TYPE: Single

기존 테이블의 row format을 REDUNDANT 또는 COMPACT에서 DYNAMIC으로 변경하기 전에 다음과 같은 잠재적 이슈를 고려하십시오.

• REDUNDANT 및 COMPACT row format은 최대 767 byte의 인덱스 키 프리픽스 길이를 지원하는 반면, DYNAMIC 및 COMPRESSED row format은 3072 byte의 인덱스 키 프리픽스 길이를 지원합니다. 복제 환경에서, 소스에서 innodb_default_row_format 변수가 DYNAMIC으로 설정되어 있고 replica에서 COMPACT로 설정되어 있는 경우, row format을 명시적으로 정의하지 않는 다음 DDL 문은 소스에서는 성공하지만 replica에서는 실패합니다:

1CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070)));

관련 정보는 Section 17.21, “InnoDB Limits”를 참조하십시오.

• row format을 명시적으로 정의하지 않는 테이블을 import하면, 소스 서버의 innodb_default_row_format 설정이 대상 서버의 설정과 다를 경우 스키마 불일치 오류가 발생합니다. 자세한 내용은 Section 17.6.1.3, “Importing InnoDB Tables”를 참조하십시오.

Determining the Row Format of a Table

테이블의 row format을 확인하려면 SHOW TABLE STATUS를 사용합니다:

1mysql> SHOW TABLE STATUS IN test1\G
2*************************** 1. row ***************************
3           Name: t1
4         Engine: InnoDB
5        Version: 10
6     Row_format: Dynamic
7           Rows: 0
8 Avg_row_length: 0
9    Data_length: 16384
10Max_data_length: 0
11   Index_length: 16384
12      Data_free: 0
13 Auto_increment: 1
14    Create_time: 2016-09-14 16:29:38
15    Update_time: NULL
16     Check_time: NULL
17      Collation: utf8mb4_0900_ai_ci
18       Checksum: NULL
19 Create_options:
20        Comment:

또는 Information Schema의 INNODB_TABLES 테이블을 조회할 수도 있습니다:

1mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test1/t1';
2+----------+------------+
3| NAME     | ROW_FORMAT |
4+----------+------------+
5| test1/t1 | Dynamic    |
6+----------+------------+