MySQL이 바이너리 콘텐츠에서 InnoDB 행 형식을 보는 방법
- 王林앞으로
- 2023-06-03 09:55:281221검색
InnoDB는 테이블의 데이터를 디스크에 저장할 수 있는 스토리지 엔진이므로 재시작 후 서버가 종료되더라도 데이터는 계속 유지될 수 있습니다. 실제 데이터 처리 과정은 메모리에서 일어나므로 디스크에 있는 데이터를 메모리에 로드해야 하며, 쓰기나 수정 요청을 처리하는 경우에도 메모리에 있는 내용을 디스크에 새로 고쳐야 합니다. 그리고 우리는 디스크를 읽고 쓰는 속도가 매우 느리다는 것을 알고 있습니다. 이는 메모리에서 읽고 쓰는 것과는 몇 배 정도 다릅니다. 따라서 테이블에서 특정 레코드를 얻으려면 InnoDB 스토리지 엔진이 읽어야 합니다. 디스크의 레코드가 하나씩?
InnoDB에서 채택한 방식은 데이터를 여러 페이지로 나누고 페이지를 디스크와 메모리 간 상호 작용의 기본 단위로 사용하는 것입니다. InnoDB에서 페이지 크기는 일반적으로 16KB입니다. 즉, 정상적인 상황에서는 한 번에 최소 16KB의 콘텐츠가 디스크에서 메모리로 읽히고, 메모리에 있는 콘텐츠 중 최소 16KB가 한 번에 디스크로 새로 고쳐집니다.
mysql> show variables like '%innodb_page_size%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec)
우리는 일반적으로 레코드 단위로 테이블에 데이터를 삽입하는데, 이러한 레코드가 디스크에 저장되는 방식을 행 형식 또는 레코드 형식이라고도 합니다. InnoDB 스토리지 엔진은 컴팩트, 중복, 동적 및 압축 행 형식이라는 네 가지 유형의 행 형식을 설계했습니다.
행 레코드 형식의 분류 및 소개
초기 InnoDB 버전에서는 파일 형식이 하나뿐이어서 이 파일 형식에 이름을 붙일 필요가 없었습니다. 새로운 기능과 이전 버전과의 비호환성을 지원하기 위해 InnoDB 엔진은 새로운 파일 형식을 개발했습니다. 업그레이드 및 다운그레이드 상황에서 시스템 호환성을 관리하고 다양한 MySQL 버전을 실행하기 위해 InnoDB는 명명된 파일 형식을 사용하기 시작했습니다.
msyql 5.7.9 이상 버전에서 기본 행 형식은 innodb_default_row_format 변수에 의해 결정되며 기본값은 동적입니다.
mysql> show variables like "innodb_file_format"; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | innodb_file_format | Barracuda | +--------------------+-----------+ 1 row in set (0.01 sec) mysql> show variables like "innodb_default_row_format"; +---------------------------+---------+ | Variable_name | Value | +---------------------------+---------+ | innodb_default_row_format | dynamic | +---------------------------+---------+ 1 row in set (0.00 sec)
현재 테이블에서 사용되는 행 형식 보기:
mysql> show table status like 'dept_emp'\G*************************** 1. row ***************************
Name: dept_emp Engine: InnoDB
Version: 10
Row_format: Dynamic Rows: 331570
Avg_row_length: 36
Data_length: 12075008Max_data_length: 0
Index_length: 5783552
Data_free: 0
Auto_increment: NULL
Create_time: 2021-08-11 09:04:36
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: Comment:1 row in set (0.00 sec)지정 테이블의 행 형식:
CREATE TABLE 表名(列的信息) ROW_FORMAT=行格式名称ALTER TABLE 表名 ROW_FORMAT=行格式名称;
기존 테이블의 행 모드를 압축 또는 동적으로 수정하려면 먼저 파일 형식을 Barracuda로 설정해야 합니다. 전역 innodb_file_format=Barracuda;를 설정한 다음 ALTER TABLE tablename을 사용해야 합니다. ROW_FORMAT=COMPRESSED; 수정 내용을 적용합니다.
행 형식
COMPACT
가변 길이 필드 목록
MySQL은 VARCHAR(M), VARBINARY(M), 다양한 TEXT 유형, 다양한 BLOB 유형과 같은 일부 가변 길이 데이터 유형을 지원합니다. 이러한 데이터 유형은 가변 길이 필드라고도 할 수 있습니다. 가변 길이 필드에 저장된 데이터의 바이트 수는 고정되어 있지 않으므로 실제 데이터를 저장할 때 이러한 데이터가 차지하는 바이트 수도 저장해야 합니다. 일어서세요. 변수 필드에 저장할 수 있는 최대 바이트 수(M × W)가 255바이트를 초과하고 실제 저장되는 바이트 수(L)가 127바이트를 초과하는 경우에는 2바이트를 사용하여 기록하고, 그렇지 않으면 1바이트를 사용하여 기록합니다.
질문 1: 그렇다면 왜 128을 구분선으로 사용합니까? 1바이트는 최대 255까지 표현할 수 있지만, MySQL이 길이 표현을 설계할 때 길이를 나타내는 바이트인지 구별하기 위해 최상위 비트가 1이면 2바이트가 길이를 나타내고, 그렇지 않으면 길이를 나타내도록 규정되어 있다. 1바이트입니다. 예를 들어 01111111은 길이가 127임을 의미하고, 길이가 128이면 2바이트가 필요하므로 10000000 10000000이 됩니다. 첫 번째 바이트의 최상위 비트가 1이면 이는 길이를 나타내는 2바이트의 시작입니다. 두 번째 바이트는 모든 비트를 사용하여 길이를 나타낼 수 있으며 MySQL은 Little Endian 계산 방법을 채택하므로 낮은 비트가 먼저, 높은 비트가 마지막에 있으므로 129는 10000001 10000000입니다. 이 식별 방법의 최대 길이는 32767(32KB)입니다.
질문 2: 2바이트가 길이를 표현하기에 충분하지 않은 경우 어떻게 해야 하나요? 기본 innoDB 페이지 크기는 16KB입니다. 예를 들어 매우 많은 바이트를 차지하는 일부 필드의 경우 필드 길이가 16KB보다 길면 레코드를 단일 페이지에 저장할 수 없습니다. 페이지에서는 이 페이지에 남은 길이만 가변 길이 필드 길이 목록에 저장되므로 2바이트를 사용하여 저장할 수 있습니다. 이 오버플로 페이지 메커니즘은 나중에 데이터 오버플로를 참조합니다.
NULL 값 목록
테이블의 일부 열에는 NULL 값이 저장될 수 있습니다. 이러한 NULL 값이 레코드의 실제 데이터에 저장되면 많은 공간을 차지하게 되므로 이를 Compact 행 형식으로 관리합니다. NULL 값이 있는 열을 통합된 방식으로 NULL 값 목록에 저장합니다. NULL을 저장할 수 있는 각 열에는 해당하는 이진 비트가 있습니다. 이진 비트의 값이 1이면 해당 열의 값이 NULL임을 의미합니다. 바이너리 비트 값이 0이면 해당 열의 값이 NULL이 아니라는 의미입니다.
레코드 헤더 정보
레코드를 설명하는 데 사용되는 레코드 헤더 정보로, 고정 5바이트로 구성됩니다. 5바이트는 40개의 이진 비트이며, 서로 다른 비트는 서로 다른 의미를 나타냅니다.
| 字段 | 长度（bit） | 说明 |
|---|---|---|
| 预留位1 | 1 | 没有使用 |
| 预留位2 | 1 | 没有使用 |
| delete_mask | 1 | 标记该记录是否被删除 |
| min_rec_mask | 1 | B+树的每层非叶子节点中的最小记录都会添加该标记 |
| n_owned | 4 | 表示当前记录拥有的记录数 |
| heap_no | 13 | 表示当前记录在页的位置信息 |
| record_type | 3 | 表示当前记录的类型，0 表示普通记录，1 表示B+树非叶子节点记录，2 表示最小记录，3 表示最大记录 |
| next_record | 16 | 表示下一条记录的相对位置 |
隐藏列
记录的真实数据除了我们自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列（也称为隐藏列），包括：
DB_ROW_ID(row_id)：非必须，6字节，表示行ID，唯一标识一条记录
DB_TRX_ID：必须，6字节，表示事务ID
DB_ROLL_PTR：必须，7字节，表示回滚指针
InnoDB表对主键的生成策略是：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique 键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
DB_TRX_ID（也可以称为trx_id） 和DB_ROLL_PTR（也可以称为roll_ptr） 这两个列是必有的，但是row_id是可选的（在没有自定义主键以及Unique 键的情况下才会添加该列）。
其他的行格式和Compact行格式差别不大。
Redundant行格式
Redundant行格式是MySQL5.0之前用的一种行格式，不予深究。
Dynamic行格式
MySQL5.7的默认行格式就是Dynamic，Dynamic行格式和Compact行格式挺像，只不过在处理行溢出数据时有所不同。
Compressed行格式
Compressed行格式在Dynamic行格式的基础上会采用压缩算法对页面进行压缩，以节省空间。以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度数据能够进行有效的存储（减少40%，但对CPU要求更高）。
数据溢出
如果我们定义一个表，表中只有一个VARCHAR字段，如下：
CREATE TABLE test_varchar( c VARCHAR(60000))
然后往这个字段插入60000个字符，会发生什么？前边说过，MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的情况。
在Compact和Redundant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前768个字节的数据，然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用20个字节（768字节后20个字节）存储指向这些页的地址。这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。
Dynamic和Compressed行格式，不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。
实战分析行格式
准备表及数据：
create table row_test ( t1 varchar(10), t2 varchar(10), t3 char(10), t4 varchar(10) ) engine=innodb charset=latin1 row_format=compact; insert into row_test values('a','bb','bb','ccc'); insert into row_test values('d','ee','ee','fff'); insert into row_test values('d',NULL,NULL,'fff');
在Linux环境下，使用hexdump -C -v mytest.ibd＞mytest.txt，打开mytest.txt文件，找到如下内容：
0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00 |supremum........| 0000c080 2c 00 00 00 00 02 00 00 00 00 00 0f 61 c8 00 00 |,...........a...| 0000c090 01 d4 01 10 61 62 62 62 62 20 20 20 20 20 20 20 |....abbbb | 0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00 | ccc........+...| 0000c0b0 00 02 01 00 00 00 00 0f 62 c9 00 00 01 b2 01 10 |........b.......| 0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff| 0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 00 02 02 00 00 |..... ..........| 0000c0e0 00 00 0f 67 cc 00 00 01 b6 01 10 64 66 66 66 00 |...g.......dfff.|
该行记录从0000c078开始，第一行整理如下：
03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1 00 // NULL标志位，第一行没有NULL值 00 00 10 00 2c // 记录头信息，固定5字节长度 00 00 00 2b 68 00 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度 00 00 00 00 06 05 // 事务ID，固定6个字节80 00 00 00 32 01 10 // 回滚指针，固定7个字节61 // t1数据'a'62 62 // t2'bb'62 62 20 20 20 20 20 20 20 20 // t3数据'bb'63 63 63 // t4数据'ccc'
第二行整理如下：
03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1 00 // NULL标志位，第二行没有NULL值 00 00 18 00 2b // 记录头信息，固定5字节长度 00 00 00 00 02 01 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度 00 00 00 00 0f 62 // 事务ID，固定6个字节 c9 00 00 01 b2 01 10 // 回滚指针，固定7个字节64 // t1数据'd'65 65 // t2数据'ee'65 65 20 20 20 20 20 20 20 20 // t3数据'ee'66 66 66 // t4数据'fff'
第三行整理如下：
03 01 // 变长字段长度列表，逆序，t4列长度为3，t1列长度为1 06 // 00000110 NULL标志位，t2和t3列为空 00 00 20 ff 98 // 记录头信息，固定5字节长度 00 00 00 00 02 02 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度 00 00 00 00 0f 67 // 事务ID，固定6个字节 cc 00 00 01 b6 01 10 // 回滚指针，固定7个字节64 // t1数据'd'66 66 66 // t4数据'fff'
接下来更新下数据：
mysql> update row_test set t2=null where t1='a'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from row_test where t2='ee'; Query OK, 1 row affected (0.01 sec)
查看二进制内容（需要等一会，有可能只写入了缓存，磁盘上的文件并没有更新）：
0000c070 73 75 70 72 65 6d 75 6d 03 01 02 00 00 10 00 58 |supremum.......X| 0000c080 00 00 00 00 02 00 00 00 00 00 0f 68 4d 00 00 01 |...........hM...| 0000c090 9e 04 a9 61 62 62 20 20 20 20 20 20 20 20 63 63 |...abb cc| 0000c0a0 63 63 63 63 03 02 01 00 20 00 18 00 00 00 00 00 |cccc.... .......| 0000c0b0 00 02 01 00 00 00 00 0f 6a 4e 00 00 01 9f 10 c0 |........jN......| 0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff| 0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 00 02 02 00 00 |..... ..........| 0000c0e0 00 00 0f 67 cc 00 00 01 b6 01 10 64 66 66 66 00 |...g.......dfff.|
该行记录从0000c078开始，第一行整理如下：
03 01 // 变长字段长度列表，逆序，t4列长度为3，t1列长度为1 02 // 0000 0010 NULL标志位，表示t2为null 00 00 10 00 58 // 记录头信息，固定5字节长度 00 00 00 00 02 00 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度 00 00 00 00 0f 68 // 事务ID，固定6个字节 4d 00 00 01 9e 04 a9 // 回滚指针，固定7个字节61 // t1数据'a'62 62 20 20 20 20 20 20 20 20 // t3数据'bb'63 63 63 // t4数据'ccc'
第二行整理如下：
03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1 00 // NULL标志位，第二行没有NULL值20 00 18 00 00 // 0010 delete_mask=1 标记该记录是否被删除 记录头信息，固定5字节长度 00 00 00 00 02 01 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度 00 00 00 00 0f 6a // 事务ID，固定6个字节 4e 00 00 01 9f 10 c0 // 回滚指针，固定7个字节64 // t1数据'd'65 65 // t2数据'ee'65 65 20 20 20 20 20 20 20 20 // t3数据'ee'66 66 66 // t4数据'fff'
第三行数据未发生变化。
위 내용은 MySQL이 바이너리 콘텐츠에서 InnoDB 행 형식을 보는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!