[MySQL][InnoDB] 행(Row)형식

테이블의 행(Row) 형식에 따라 행이 실제로 저장되는 방식이 결정되어 쿼리 및 DML 작업의 성능에 영향을 줄 수 있습니다. 단일 디스크 페이지에 더 많은 행이 들어가므로 쿼리 및 인덱스 조회가 더 빠르게 작동 할 수 있으며 버퍼 풀에 필요한 캐시 메모리가 적고 업데이트 된 값을 기록하는 데 필요한 I/O가 적습니다.

 

각 테이블의 데이터는 페이지로 나뉩니다. 각 테이블을 구성하는 페이지는 B-트리 인덱스라고 하는 트리 데이터 구조로 정렬됩니다. 테이블 데이터와 보조 인덱스는 모두 이 유형의 구조를 사용합니다. 전체 테이블을 나타내는 B-트리 인덱스를 클러스터된 인덱스라고 하며 기본 키 열에 따라 구성됩니다. 클러스터형 인덱스 데이터 구조의 노드에는 행의 모든 ​​열(Column) 값이 포함됩니다. 보조 인덱스 구조의 노드에는 인덱스 열과 기본 키 열의 값이 포함됩니다.

 

가변 길이 열은 열 값이 B-트리 인덱스 노드에 저장되는 규칙의 예외입니다. B-트리 페이지에 맞지 않는 가변 길이 열은 오버플로 페이지라고하는 별도로 할당된 디스크 페이지에 저장됩니다. 이러한 열을 페이지 외부 열(off-page)이라고합니다. 외부 열(off-page)의 값은 단독으로 연결된 오버플로 페이지 목록에 저장되며 각 열에는 하나 이상의 오버플로 페이지 목록이 있습니다. 열 길이에 따라 가변 길이 열 값의 접두사 전체 또는 접두어가 B-트리에 저장되어 스토리지 낭비를 줄이고 별도의 페이지를 읽지 않아도 됩니다.

 

InnoDB 스토리지 엔진은 REDUNDANT, COMPACT, DYNAMIC 및 COMPRESSED의 네 가지 행 형식을 지원합니다.

 

Table 14.9 InnoDB Row Format Overview

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

 

다음 주제에서는 행 형식 스토리지 특성과 테이블의 행 형식을  정의하고 결정하는 방법에 대해 설명합니다.

+ 중복 행 형식

+ COMPACT 행 형식

+ 동적 행 형식

+ 압축 행 형식

+ 테이블의 행 형식 정의

+ 테이블의 행 형식 결정

 

 

 

■ 중복 행 형식

REDUNDANT 형식은 이전 버전의 MySQL과 호환됩니다.

 

REDUNDANT 행 형식은 InnoDB 파일 형식 (Antelope 및 Barracuda)에서 모두 지원됩니다.

 

REDUNDANT 행 형식을 사용하는 테이블은 B-트리 노드 내의 인덱스 레코드에 첫 번째 768 바이트의 가변 길이 열 값 (VARCHAR, VARBINARY 및 BLOB 및 TEXT 유형)을 저장하고 나머지는 오버 플로우 페이지에 저장합니다. 768 바이트 이상의 고정 길이 열은 가변 길이 열로 인코딩되어 페이지 외부에 저장할 수 있습니다. 예를 들어, 문자 세트의 최대 바이트 길이가 utf8mb4에서와 같이 3보다 큰 경우 CHAR(255) 열은 768 바이트를 초과할 수 있습니다.

 

열의 값이 768 바이트 이하인 경우 오버 플로우 페이지가 사용되지 않으며 값이 완전히 B-트리 노드에 저장되므로 I/O가 약간 절약 될 수 있습니다. 이는 비교적 짧은 BLOB열 값에는 효과적이지만 B-트리 노드가 키 값이 아닌 데이터로 채워져 효율성이 떨어질 수 있습니다. BLOB열이 많은 테이블은 B-트리 노드가 너무 가득 차고 행이 너무 적어 행이 더 짧거나 열 값이 페이지 외부에 저장된 경우보다 전체 인덱스의 효율성이 떨어질 수 있습니다.

 

▶ 중복 행 형식 저장 특성

REDUNDANT 행 형식에는 다음과 같은 저장 특성이 있습니다.

 

각 인덱스 레코드는 6바이트 헤더를 포함합니다. 헤더는 연속 레코드를 함께 연결하고 행 레벨 잠금에 사용됩니다.

 

클러스터형 인덱스의 레코드에는 모든 사용자 정의 열에 대한 필드가 포함됩니다. 또한 6바이트 트랜잭션 ID 필드와 7바이트 롤 포인터 필드가 있습니다.

 

테이블에 기본키가 정의되지 않은 경우 각 클러스터형 인덱스 레코드에는 6바이트 행ID 필드도 포함됩니다.

 

각 보조 인덱스 레코드에는 보조 인덱스에 없는 클러스터형 인덱스 키에 대해 정의된 모든 기본키 열이 포함됩니다.

 

레코드에는 레코드의 각 필드에 대한 포인터가 포함됩니다. 레코드에서 필드의 총 길이가 128바이트보다 작 으면 포인터는 1바이트입니다. 그렇지 않으면 2바이트입니다. 포인터 배열을 레코드 디렉토리라고합니다. 포인터가 가리키는 영역은 레코드의 데이터 부분입니다.

 

내부적으로 CHAR(10)과 같은 고정 길이 문자 열은 고정 길이 형식으로 저장됩니다. VARCHAR열에서 후행 공백이 잘리지 않습니다.

 

768바이트 이상의 고정 길이 열은 가변 길이 열로 인코딩되어 페이지 외부에 저장할 수 있습니다. 예를 들어, 문자 세트의 최대 바이트 길이가 utf8mb4에서와 같이 3보다 큰 경우 CHAR(255) 열은 768 바이트를 초과 할 수 있습니다.

 

SQL NULL 값은 레코드 디렉토리에 1-2바이트를 예약합니다. 가변 길이 열에 저장된 경우 SQL NULL 값은 레코드의 데이터 부분에 0바이트를 예약합니다. 고정 길이열의 경우 고정열 길이는 레코드의 데이터 부분에 예약됩니다. NULL 값의 고정 공간을 예약하면 인덱스 페이지 조각화를 발생시키지 않고 열을 NULL에서 NULL이 아닌 값으로 업데이트 할 수 있습니다.

 

■ COMPACT 행 형식

COMPACT행 형식은 일부 작업에서 CPU 사용이 증가함에 따라 REDUNDANT행 형식에 비해 행 저장 공간을 약 20% 줄입니다. 작업 부하가 캐시 적중률과 디스크 속도에 의해 제한되는 일반적인 작업인 경우 COMPACT형식이 더 빠를 수 있습니다. 워크로드가 CPU 속도에 의해 제한되면 컴팩트 형식이 느려질 수 있습니다.

 

COMPACT행 형식은 InnoDB 파일 형식 (Antelope 및 Barracuda)에서 모두 지원됩니다.

 

COMPACT 행 형식을 사용하는 테이블은 B-트리 노드 내의 인덱스 레코드에 첫 번째 768바이트의 가변 길이 열 값(VARCHAR, VARBINARY 및 BLOB 및 TEXT 유형)을 저장하고 나머지는 오버 플로우 페이지에 저장합니다. 768바이트 이상의 고정 길이 열은 가변 길이 열로 인코딩되어 페이지 외부에 저장할 수 있습니다. 예를 들어, 문자 세트의 최대 바이트 길이가 utf8mb4에서와 같이 3보다 큰 경우 CHAR(255) 열은 768 바이트를 초과 할 수 있습니다.

 

열의 값이 768바이트 이하인 경우, 오버 플로우 페이지가 사용되지 않으며 값이 완전히 B-트리 노드에 저장되므로 I/O가 약간 절약 될 수 있습니다. 이는 비교적 짧은 BLOB열 값에는 효과적이지만 B-트리 노드가 키 값이 아닌 데이터로 채워져 효율성이 떨어질 수 있습니다. BLOB 열이 많은 테이블은 B-트리 노드가 너무 가득 차고 행이 너무 적어, 행이 더 짧거나 열 값이 페이지 외부에 저장된 경우보다 전체 인덱스의 효율성이 떨어질 수 있습니다.

 

▶ COMPACT행 형식 저장 특성

COMPACT행 형식에는 다음과 같은 저장 특성이 있습니다.

 

+ 각 인덱스 레코드에는 가변 길이 헤더가 앞에 오는 5바이트 헤더가 포함됩니다. 헤더는 연속 레코드를 함께 연결하고 행레벨 잠금에 사용됩니다.

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

+ NULL이 아닌 가변 길이 필드 각각에 대해 레코드 헤더에는 열 길이가 1-2바이트로 포함됩니다. 열의 일부가 오버 플로우 페이지에 외부 적으로 저장되거나 최대 길이가 255 바이트를 초과하고 실제 길이가 127 바이트를 초과하는 경우에만 2바이트가 필요합니다. 외부 저장 열의 경우 2바이트 길이는 내부 저장 부품의 길이에 외부 저장 부품에 대한 20바이트 포인터를 더한 값을 나타냅니다. 내부 부분은 768 바이트이므로 길이는 768+20입니다. 20바이트 포인터는 열의 실제 길이를 저장합니다.

+ 레코드 헤더 다음에는 NULL이 아닌 열의 데이터 내용이 옵니다.

+ 클러스터형 인덱스의 레코드에는 모든 사용자정의 열에 대한 필드가 포함됩니다. 또한 6바이트 트랜잭션 ID 필드와 7바이트 롤 포인터 필드가 있습니다.

+ 테이블에 기본키가 정의되지 않은 경우 각 클러스터형 인덱스 레코드에는 6바이트 행ID 필드도 포함됩니다.

+ 각 보조 인덱스 레코드에는 보조 인덱스에 없는 클러스터형 인덱스키에 대해 정의된 모든 기본키 열이 포함됩니다. 기본키 열 중 하나가 가변 길이인 경우 보조 인덱스가 고정 길이 열에 정의되어 있어도 각 보조 인덱스의 레코드 헤더에는 길이를 기록하는 가변 길이 부분이 있습니다.

+ 내부적으로 가변 길이 문자 세트의 경우, CHAR 10)과 같은 고정길이 문자열은 고정길이 형식으로 저장됩니다.

VARCHAR열에서 후행 공백이 잘리지 않습니다.

+ 내부적으로 utf8mb3 및 utf8mb4와 같은 가변 길이 문자 세트의 경우 InnoDB는 후행공백을 잘라서 CHAR(N)을 N바이트로 저장하려고 시도합니다. CHAR(N)열 값의 바이트 길이가 N바이트를 초과하면 후행 공백이 최소 열값 바이트 길이로 잘립니다. CHAR(N)열의 최대 길이는 최대 문자 바이트 길이 × N입니다.

CHAR(N)에는 최소 N바이트가 예약되어 있습니다. 대부분의 경우 최소 공간 N을 예약하면 인덱스 페이지 조각화없이 열 업데이트를 수행할 수 있습니다. REDUNDANT행 형식을 사용할 때 CHAR(N)열은 최대 문자 바이트 길이 × N을 차지합니다.

768 바이트 이상의 고정 길이 열은 가변 길이 필드로 인코딩되어 페이지 외부에 저장할 수 있습니다. 예를 들어, 문자 세트의 최대 바이트 길이가 utf8mb4에서와 같이 3보다 큰 경우 CHAR(255) 열은 768바이트를 초과 할 수 있습니다.

 

■ DYNAMIC 행 형식

DYNAMIC 행 형식은 COMPACT행 형식과 동일한 스토리지 특성을 제공하지만 가변길이의 긴 열에 향상된 스토리지 기능을 추가하고 큰 인덱스키 접 두부를 지원합니다.

 

바라쿠다 파일 형식은 DYNAMIC행 형식을 지원합니다.

 

ROW_FORMAT = DYNAMIC으로 테이블을 만들면 InnoDB는 긴 가변 길이 열 값 (VARCHAR, VARBINARY 및 BLOB 및 TEXT 유형의 경우)을 20 바이트 포인터에서 오버플로 페이지까지만 포함하는 클러스터형 인덱스 레코드와 함께 페이지 외부에 완전히 저장할 수 있습니다. 768바이트 이상의 고정 길이 필드는 가변 길이 필드로 인코딩됩니다. 예를 들어, 문자 세트의 최대 바이트 길이가 utf8mb4에서와 같이 3보다 큰 경우 CHAR (255) 열은 768 바이트를 초과할 수 있습니다.

 

열이 페이지 외부에 저장되는지 여부는 페이지 크기와 행의 총 크기에 따라 다릅니다. 행이 너무 길면 클러스터형 인덱스 레코드가 B-트리 페이지에 맞을 때까지 오프 페이지 스토리지에 가장 긴 열이 선택됩니다. 40 바이트 이하인 TEXT 및 BLOB 열은 줄로 저장됩니다.

 

DYNAMIC행 형식은 COMPACT 및 REDUNDANT형식과 같이 인덱스 행에 전체 행을 저장하는 효율성을 유지하지만 DYNAMIC행 형식은 많은 수의 데이터 바이트 채워진 긴 열이 B-트리 노드를 채우는 문제를 피합니다. DYNAMIC 행 형식은 긴 데이터 값의 일부가 페이지 외부에 저장되는 경우 일반적으로 전체 값을 페이지 외부에 저장하는 것이 가장 효율적이라는 아이디어를 기반으로합니다. DYNAMIC 형식을 사용하면 B- 트리 노드에 더 짧은 열이 남아있을 수 있으므로 주어진 행에 필요한 오버플로 페이지수가 최소화됩니다.

 

DYNAMIC 행 형식은 최대 3072 바이트의 색인키 접두부를 지원합니다. 이 기능은 기본적으로 사용 가능한 innodb_large_prefix 변수에 의해 제어됩니다.

 

DYNAMIC 행 형식을 사용하는 테이블은 시스템 테이블 스페이스, 테이블 당 파일 테이블 스페이스 및 일반 테이블 스페이스에 저장 될 수 있습니다. 시스템 테이블 스페이스에 DYNAMIC 테이블을 저장하려면 innodb_file_per_table을 사용하지 않도록 설정하고 일반 CREATE TABLE 또는 ALTER TABLE 문을 사용하거나 CREATE TABLE 또는 ALTER TABLE과 함께 TABLESPACE [=] innodb_system table 옵션을 사용합니다. innodb_file_per_table 및 innodb_file_format 변수는 일반 테이블 스페이스에는 적용되지 않으며 TABLESPACE [=] innodb_system 테이블 옵션을 사용하여 DYNAMIC테이블을 시스템 테이블 스페이스에 저장할 때도 적용할 수 없습니다.

 

▶ DYNAMIC 행 형식 저장 특성

DYNAMIC 행 형식은 COMPACT 행 형식의 변형입니다. 스토리지 특성에 대해서는 COMPACT 행 형식 스토리지 특성을 참조하십시오.

 

■ 압축 행 형식

COMPRESSED행 형식은 DYNAMIC행 형식과 동일한 스토리지 특성 및 기능을 제공하지만 테이블 및 인덱스 데이터 압축을 지원합니다.

 

바라쿠다 파일형식은 COMPRESSED행 형식을 지원합니다.

 

COMPRESSED행 형식은 DYNAMIC행 형식과 유사한 오프 페이지 스토리지에 대한 내부 세부 사항을 사용하며, 테이블 및 인덱스 데이터의 압축 및 작은 페이지 크기에서 추가 스토리지 및 성능 고려 사항을 사용합니다. COMPRESSED행 형식을 사용하면 KEY_BLOCK_SIZE 옵션은 클러스터형 인덱스에 저장되는 열 데이터 양과 오버플로 페이지에 배치되는 양을 제어합니다.

 

COMPRESSED 행 형식은 최대 3072바이트의 색인키 접두부를 지원합니다. 이 기능은 기본적으로 사용 가능한 innodb_large_prefix 변수에 의해 제어됩니다.

 

COMPRESSED행 형식을 사용하는 테이블은 테이블당 파일 테이블스페이스 또는 일반 테이블스페이스에서 작성할 수 있습니다. 시스템 테이블 스페이스는 COMPRESSED행 형식을 지원하지 않습니다. 테이블 당 파일 테이블스페이스에 COMPRESSED테이블을 저장하려면 innodb_file_per_table 변수를 사용 가능하게하고 innodb_file_format을 Barracuda로 설정해야합니다. innodb_file_per_table 및 innodb_file_format 변수는 일반 테이블 스페이스에는 적용되지 않습니다. 일반 테이블 스페이스는 물리적 페이지 크기가 다르기 때문에 압축된 테이블과 압축되지 않은 테이블이 동일한 일반 테이블 스페이스에 공존 할 수 없다는 경고와 함께 모든 행 형식을 지원합니다.

 

▶ 압축 행 형식 저장 특성

COMPRESSED 행 형식은 COMPACT 행 형식의 변형입니다. 스토리지 특성에 대해서는 COMPACT 행 형식 스토리지 특성을 참조하십시오.

 

 

 

■ 테이블의 행 형식 정의

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

 

CREATE TABLE 또는 ALTER TABLE 문에서 ROW_FORMAT 테이블 옵션을 사용하여 테이블의 행 형식을 명시적으로 정의 할 수 있습니다. 예를 들면 다음과 같습니다.

 

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

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

 

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

mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;

 

유효한 innodb_default_row_format 옵션에는 DYNAMIC, COMPACT 및 REDUNDANT가 있습니다. 시스템 테이블 스페이스에서 사용하도록 지원되지 않는 COMPRESSED행 형식을 기본값으로 정의할 수 없습니다. CREATE TABLE또는 ALTER TABLE 문에서만 명시적으로 지정할 수 있습니다. innodb_default_row_format 변수를 COMPRESSED로 설정하려고 하면 오류가 리턴됩니다.

 

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

 

새로 작성된 테이블은 ROW_FORMAT옵션이 명적으로 지정되지 않았거나 ROW_FORMAT = DEFAULT가 사용될 때 innodb_default_row_format 변수에 의해 정의된 행 형식을 사용합니다. 예를 들어, 다음 CREATE TABLE 문은 innodb_default_row_format 변수로 정의 된 행 형식을 사용합니다.

mysql> CREATE TABLE t1 (c1 INT);
mysql> CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

ROW_FORMAT 옵션을 명시적으로 지정하지 않거나 ROW_FORMAT = DEFAULT를 사용하는 경우 테이블을 다시 작성하는 조작은 테이블의 행 형식을 자동으로 innodb_default_row_format 변수에 의해 정의 된 형식으로 변경합니다.

 

테이블 재구축 작업에는 테이블 재구축이 필요한 ALGORITHM = COPY 또는 ALGORITHM = INPLACE를 사용하는 ALTER TABLE 작업이 포함됩니다.

 

다음 예제는 명시적으로 정의된 행 형식없이 작성된 테이블의 행 형식을 자동으로 변경하는 테이블 재구축 조작을 보여줍니다.

mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic                     |
+-----------------------------+

mysql> CREATE TABLE t1 (c1 INT);
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 54
         NAME: test/t1
         FLAG: 33
       N_COLS: 4
        SPACE: 35
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

mysql> SET GLOBAL innodb_default_row_format=COMPACT;
mysql> ALTER TABLE t1 ADD COLUMN (c2 INT);
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 55
         NAME: test/t1
         FLAG: 1
       N_COLS: 5
        SPACE: 36
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

기존 테이블의 행 형식을 REDUNDANT또는 COMPACT에서 DYNAMIC으로 변경하기 전에 다음 잠재적 문제를 고려해야 합니다.

 

REDUNDANT 및 COMPACT행 형식은 최대 인덱스 키 접두사 길이 767바이트를 지원하는 반면 DYNAMIC 및 COMPRESSED행 형식은 3072 바이트의 인덱스 키 접두사 길이를 지원합니다. 복제 환경에서 innodb_default_row_format 변수가 마스터에서 DYNAMIC으로 설정되고 슬레이브에서 COMPACT로 설정되면 행 형식을 명시적으로 정의하지 않는 다음 DDL 문은 마스터에서 성공하지만 슬레이브에서 실패합니다.

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

 

소스 서버의 innodb_default_row_format 설정이 대상 서버의 설정과 다른 경우 행 형식을 명시 적으로 정의하지 않은 테이블을 가져 오면 스키마 불일치 오류가 발생합니다.

 

■ 테이블의 행 형식 결정

테이블의 행 형식을 확인하려면 SHOW TABLE STATUS를 사용하십시오.

mysql> SHOW TABLE STATUS IN test1\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2016-09-14 16:29:38
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment:

 

또는 INFORMATION_SCHEMA.INNODB_TABLES 테이블을 조회합니다.

mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME     | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic    |
+----------+------------+

 

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※