Shulou(Shulou.com)06/01 Report--

Cdn.shimo.imposure wLa8Ccuo2bAFpz1G image. PngAFpz1G image.pnglength thumbnail "}," 29:1 | 30:0 | 3 613px. "" | 4 frame:.. "" 73.275px. "| 7:1 | crop:.". | frame:. "none." | ori-height:. "104." | ori-width:. "870."], [20, "the header is a list of field length offsets, placed in reverse order in the order of the columns (if the length of the column is less than 255bytes, 1 byte is used to represent it. "], [20," .n "," text-indent:. "1."], [20," record header information: occupies 6 bytes The meaning is shown in the figure: "], [20," .n "," text-indent:. "1."], [20, {" gallery-block ":" https://images-cdn.shimo.im/RrR00mMkB0E9w1FP/image.png!thumbnail"},"29:1|30:0|3:."613px."|4:."215.738px."|7:1|crop:."."|frame:."none."|ori-height:."353."|ori-width:."1003.""],[20, " "description: the n _ fields value represents the number of columns in a row. The largest column in a row of mysql is 1023. 1byte_offs_flag: defines whether the offset list takes 1 byte or 2 bytes. The last part of .n is the actual stored data for each column "], [20," .n "," text-indent:. "1."], [20," Note: for null values of type varchar, no storage space is required, while null values of type char require space .n "], [20," compact. Default line format after 5.0.3 "," 27 gallery-block. "12." | 8:1 "], [20," .n "," text-indent:. "1."], [20, {" gallery-block ":" https://images-cdn.shimo.im/TWwEzwRWPCYq30ni/image.png!thumbnail"}, " "29:1 | 30:0 | 3none." 613px. "| 4frame:.." 74.0625px. "| 7:1 | crop:.". "| frame:." none. "| ori-height:." 116." | ori-width:. "960."], [20, "description: .n first part is a"], [20, "non-variable length field," 8:1 "], [20," length list. It is placed in reverse order in the order of the columns, and its length is (if the length of the column is less than 255 bytes, it is represented by 1 byte. If it is greater than 255bytes, it is expressed as 2 bytes) "], [20," .n "," text-indent:. "1."], [20, "NULL flag bit: whether the row of data has a null value, if so, it takes 1 byte"], [20, ".n", "text-indent:." 1. ""], [20, "record header information: fixed occupation of 5 bytes. The meaning is shown in the figure: "], [20," .n "," text-indent:. "1."], [20, {" gallery-block ":" https://images-cdn.shimo.im/WYtqxD4zit0IzMK6/image.png!thumbnail"},"29:1|30:0|3:."613px."|4:."217.913px."|7:1|crop:."."|frame:."none."|ori-height:."353."|ori-width:."993.""],[20, " "description: available through record_type analysis In the innodb storage engine, the interior of the page concatenates row records through a linked list structure, 8:1], [20, ". The last part of .n is the actual storage of data for each column (Note: 1. No matter it is char type or varchar type, NULL does not occupy any space in this part, only occupies NULL mark bit; 2. Fixed-length char fields are populated with Ox20 when they cannot fully occupy their length space) "], [20," .n "," text-indent:. "1."], [20, "each row of data, in addition to user-defined columns, has two hidden columns (transaction ID (DB_TRX_ID) 6 bytes, rollback pointer column (DB_ROLL_PTR), 7 bytes), if no primary key is defined A rowid (DB_ROW_ID) 6-byte "], [20," .n "," text-indent:. "1."], [20, "Antelope file format includes compact and Redundant line record formats .nBarracuda file format includes dynamic and compressed line record format .n"], [20, ".n", "text-indent:." 1. "], [20," row overflow data "," 27 Antelope. "12." | 8:1 "], [20 ".ncompact and redundant store BLOB row overflow data storage format as shown in figure: .n"], [20, {"gallery-block": "https://images-cdn.shimo.im/k5zhzI2JVPwQiPIz/image.png!thumbnail"},"29:1|30:0|3:."613px."|4:."145.575px."|7:1|crop:."."|frame:."none."|ori-height:."166."|ori-width:."699.""],[20," "description: if you can store at least two rows of data in one page Then data of type varchar will not be stored in the BLOB page When the length exceeds 8098, the overflowed data is stored in the BLOB page. N "], [20," varchar (N) N refers to "," 27 varchar. "12."], [20, "length of characters", "27." 12. "| 8:1"], [20, " In the documentation, the varchar type is most supported "," 27 varchar. "12."], [20, "65535 units are words", "27." 12. "| 8:1"], [20, ".n"], [20, "section also refers to the sum of the lengths of all varchar columns." , "27 gallery-block." 12. "| 8:1"], [20, ".ndynamic and compressed data stored in BLOB using row overflow mode as shown in figure: .n"], [20, {"gallery-block": "https://images-cdn.shimo.im/inEA6FCjrUIgZABG/image.png!thumbnail"}," "29:1 | 30:0 | 3crop:.." 613px. "| 4frame:.." 145.587px. "| 7:1 | crop:.". "| frame:." none. "| ori-height:." 176. | ori-width:. "741."], [20, "description: a pointer that only stores 20 bytes in the data page. The actual data is stored in off page. N rows overflow summary:. N1. When the row record length exceeds about half of the page, the longest column is split and distributed to multiple page stores in turn until it no longer exceeds half of the page; "], [20," .n "," text-indent:. "1."], [20, "2. If the overflow column is not enough in one page, continue to put it in the new page "], [20," .n "," text-indent:. "1."], [20, "in 3.compact format, only the first 768 bytes are stored in the overflow column"], [20, ".n", "text-indent:." 1. "], [20," under 4.dynamic format. Overflowed columns only store the first 20 bytes (pointer), [20, ".n", "text-indent:." 1. "], [20," 5.select * will read these overflowed columns at the same time, at a high cost "], [20," .n "," text-indent:. "1."], [20, "6. When filesort or temporary table appears in the execution plan, it generally cannot be placed in memory, and it is more expensive to become disk tmp table,IO. "] , [20, ".n", "text-indent:." 1. "], [20," dynamic "," 27. "12." | 8:1 "], [20," Fully off-page long fields "], [20," .n "," text-indent:. "1."], [20, "compressed", "27 index." 12. "| 8:1"], [20, "(the stored row data will be compressed by zlib algorithm)," 27 compressed. "12."], [20, "compress data, index, pages. But pages in buffer pool does not compress "], [20," .n "," text-indent:. "1."], [20, "CHAR Row structure Storage", "8:1"], [20, ".nchar (N) refers to the length of characters, so under different character sets, char type columns may not store fixed-length data."] " Row record format

You can view the row format (row_format field) used by the current table through the show table status like 'table_name' command

Row_format

Redundant, the earliest line format

The first part is the field length offset list, which is placed in reverse order according to the column order. Its length is (1 byte if the length of the column is less than 255 bytes; 2 bytes if the column length is greater than 255 bytes) record header information: occupies 6 bytes, meaning as shown in the figure:

Note: the n _ fields value represents the number of columns in a row, and the largest column in a mysql row is 1023th 1 byte fields flag: defines whether the offset list occupies 1 byte or 2 bytes. The last part is the actual stored data for each column.

Note: null values of type varchar do not take up any storage space, while null values of type char require space

Default line format after compact,5.0.3

Description: the first part is a non-NULL variable length field length list, which is placed in reverse order according to the column order, and its length is (if the length of the column is less than 255bytes, 1 byte; if more than 255bytes, 2 bytes).

NULL flag bit: whether this line of data has a null value, which is represented by 1 and occupies 1 byte

Record header information: fixed occupation of 5 bytes, as shown in the figure:

Description: through record_type analysis, in the innodb storage engine inside the page is through a linked list structure to concatenate each row record. The last part is the actual storage of data for each column (Note: 1. No matter it is char type or varchar type, NULL does not occupy any space in this part, only occupies NULL mark bit; 2. Fixed-length char fields will be populated with Ox20 when their length space is not fully occupied)

In addition to user-defined columns, each row of data has two hidden columns (transaction ID (DB_TRX_ID) 6 bytes, rollback pointer column (DB_ROLL_PTR), 7 bytes). If no primary key is defined, a rowid (DB_ROW_ID) 6 bytes will be added

Antelope file formats include compact and Redundant line record formats

Barracuda file formats include dynamic and compressed line record formats

Row overflow data

Compact and redundant store the row overflow data of BLOB as shown in the figure:

Note: if you can store at least two rows of data in a page, then the data of varchar type will not be stored in the BLOB page; when the length exceeds 8098, the overflow data will be stored in the BLOB page. N in varchar (N) refers to the length of characters, while the maximum varchar type supported in the documentation is 65535 bytes and refers to the sum of the lengths of all varchar columns.

The row overflow mode for the data stored by dynamic and compressed in BLOB is shown in the figure:

Note: only 20-byte pointers are stored in the data page, and the actual data is stored in the off page row overflow summary:

1. When the row record length exceeds approximately half of the page, the longest columns are split and distributed to multiple page stores in turn until they no longer exceed half of the page

two。 If the overflow column is not enough in a page, continue to put it in the new page

In 3.compact format, the overflowed column stores only the first 768 bytes

In 4.dynamic format, overflowed columns store only the first 20 bytes (pointer)

5.select * reads these overflowed columns at the same time, which is very expensive

6. When filesort or temporary table appears in the execution plan, it generally cannot be placed in memory, and it is more expensive to become disk tmp table,IO.

Dynamic, full off-page storage of long fields

Compressed (the stored row data is compressed using the zlib algorithm) compresses data, index, and pages, but pages in buffer pool does not compress

CHAR row structure storage

N in char (N) refers to the length of characters, so the internal storage of char type columns may not be fixed-length data under different character sets.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.