InnoDB Engine는 형식에 따라 행이 실제로 저장되는 방식을 다르게 설정할 수 있다.
또한 행 저장방식에 따라 DML 작업 성능에도 영향을 미친다.
그 이유는 단일 디스크 페이지에 더 많은 행이 들어가게 되어 쿼리 및 인덱스 조회가 빠르게 작동하고 Buffer Pool에 필요한 Cache Memory가 적게 됩니다.
아래는 4가지 행 저장방식에 따른 개요이다.
1. REDUNDANT
A. mysql 5.0 이 전 row_format으로 이전 버전과 호환성을 제공
B. Record에 가변 길이 열(varchar, varbinary, BLOB, TExT)의 처음 768 Byte를 저장하고 나머지를 Overflow Page에 저장한다.
C. Antelope 및 Barracuda 2개 파일 형식 모두에서 지원된다.
D. 해당 형식을 사용할 때는 innodb_strict_mode 변수는 활성화 하는 것이 좋다.
i. innodb_strict_mode는 mysql에서 허용하는 SQL 구문을 제어하고 자동으로 오류를 무시하는지, 입력 구문과 데이터 값을 검증하는지 여부를 결정한다.
ii. 5.7 버전부터 기본값으로 ON되어 있다.
iii. 다음과 같은 설정으로 변경이 가능하다.
SET GLOBAL innodb_strict_mode=ON;
-- 또는
SET SESSION innodb_strict_mode=ON;
-- 또는
[mysqld]
...
innodb_strict_mode=ON
E. field offsets
i. 열 순서의 역순으로 배치됨
ii. 최대 열 길이가 255 Byte 미만이면 1 Byte로 저장됨
iii. 최대 열 길이가 255 Byte 보다 크면 2 Byte로 저장됨
F. Header는 총 6 Byte로 구성되어 있고 연속 행과 행 레벨 잠금을 연결하기 위해 사용된다.
G. 사용자 정의 열에 대한 내용이 있고 다음과 같은 숨겨진 내용이 존재합니다.
i. ROWID : Primary Key 혹은 NULL이 아닌 Unique Key를 명시하지 않았을 시 자동으로 생성되는 6 Byte의 열
ii. Transaction ID : 6 Byte의 Transaction ID
iii. Roll Pointer : 7 Byte의 롤백을 위한 역할 포인터
H. 2차 인덱스 열에는 1차 인덱스 키 열이 포함된다.
2. COMPACT
A. mysql 5.0.3에서 도입된 행 저장 방식
B. REDUNDANT 행 저장 방식에서 저장 공간을 약 20% 감소 시킨 방법
C. 일부 작업에 대한 CPU 사용 증가 비용 발생
D. 일반적으로는 REDUNDANT보다 빠름(CPU 고도화 사용에는 느림)
E. 기본적으론 REDUNDANT와 비슷한 구조를 가지나 header 정보가 다르고 Null Flag Bit를 가진다.
F. Null Flag Bit
i. 행에 bit 백터인 NULL값이 있는지 여부
ii. NULL이 허용된 열수가 N개 일 때 Flag BIt값은 CEILING(N/8) Byte 크기를 차지
G. header에 포함된 정보는 5 Byte로 다음과 같습니다.
i. infimum : 그 페이지에서 가질 수 있는 Key값 중 가장 낮은 값
ii. supermum : 그 페이지에서 가질 수 있는 Key값 중 가장 높은 값
H. 해당 형식을 사용할 때는 innodb_strict_mode 변수는 활성화 하는 것이 좋다.
3. DYNAMIC
A. mysql 5.7 부터 기본 행 저장 방식으로 사용된 방식
B. Barracuda 파일 형식이고 innodb_file_per_table가 On인 상태에서만 사용이 가능하다.
i. innodb_file_per_table 옵션이 on 일 때 .ibd 파일은 테이블 당 생성된다.
ii. 테이블스페이스를 다른 테이블 사이에서 공유하지 않는다.
iii. 각 파일은 각자의 테이블스페이스를 가진다.
iv. 테이블스페이스의 크기가 절대 감소하지 않는다.
v. 테이블스페이스 당 공간을 재사용 할 수 있다.
C. COMPACT와 유사한 형태를 가지고 있으나 긴 가변 길이 열 값을 완전히 off-page에 저장한다.
D. Large Index Key 접두사를 지원한다.
i. Compact는 Index key 접두사를 767 Byte만 지원한다.
ii. Dynamic은 설정한 Page의 크기에 따라 가변적으로 최대 값을 지정할 수 있도록 한다.
가) Page size 16KB = 3072 Byte
나) Page size 8KB = 1536 Byte
다) Page size 4KB = 768 Byte
iii. innodb_large_prefix 설정을 ON으로 변경 후 사용 가능
SET GLOBAL innodb_large_prefix=ON;
-- 또는
SET SESSION innodb_large_prefix=ON;
-- 또는
[mysqld]
...
innodb_large_prefix=ON
4. COMPRESSED
A. 해당 행 저장 방식은 mysql 5.5에서 처음 도입되었다.
B. Dynamic 행 저장 방식과 형태가 유사하나 zlib 알고리즘을 사용한 압축을 지원하여 off-page에 더 많은 데이터를 저장하도록 한다.
C. 일반적으로 약 40 %의 저장 공간을 줄일 수 있다.
D. 압축과 해제를 사용함으로 더 많은 CPU 사용을 발생 시킨다.
E. buffer pool에 압축된 버전과 해제된 버전의 데이터를 동시에 저장할 때도 있기 때문에 Memory를 더 많이 사용한다.
F. Memory에 공간이 부족하게 되면 LRU 알고리즘을 사용하여 압축되거나 해제되어있는 페이지를 버퍼에서 제거해야 하는지 여부를 결정한다.
i. 로직이 많은 경우 (CPU bound) : 압축된 페이지를 먼저 제거
ii. 파일 갱신이 많을 경우 (IO bound): 압축되지 않은 페이지를 먼저 제거
G. 압축은 KEY_BLOCK_SIZE 라는 테이블 옵션을 통해 압축률을 선택할 수 있다
CREATE TABLE row_test_tbl
(
a int PRIMARY KEY,
b int
)ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
설정 가능한 값은 [1, 2, 4, 8, 16] 이다
H. 고 압축률을 적용할 시 SELECT 속도 증가는 가능하나 INSERT, UPDATE, DELETE의 성능 저하를 발생 시킬 수 있다.
I. TEXT나 VARCHAR 형식의 컬럼이 많고 RANGE SCAN이 자주 일어난 테이블에 성능 향상을 기대해 볼 수 있다.
참조
https://myinfrabox.tistory.com/59
https://runebook.dev/ko/docs/mariadb/innodb-redundant-row-format/index
https://enterone.tistory.com/230
https://purumae.tistory.com/209
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
'Database > Mysql & Mariadb' 카테고리의 다른 글
| Mysql Connection에 대하여 (0) | 2021.11.15 |
|---|---|
| MySQL Online DDL(8) [Table Operations] (0) | 2021.11.09 |
| innodb 행 저장 방식(1) 저장 파일 형식 (0) | 2021.11.08 |
| MySQL Online DDL(7) [Foreign key Operations] (0) | 2021.11.01 |
| MySQL Online DDL(6) [Generated Column Operations] (0) | 2021.11.01 |
최근댓글