Record

for each record, MySQL will add other default three column. But if any column could be primary key , row_id will not be added.

There are totally four kinds of formats in MySQL

• compact

• redundant

• dynamic

• compressed

Current default is compact. We can indicate it.

create table tb (
	...
) ROW_FORMAT=COMPACT

Format

Compact Format

main structure is as follow.

when we want to locate a record, we find its record header’s position. Then parser NULL list and variables list in reverse order.(Because it store reversely).

Recorder Header

for compact format, it take fixed 5 bytes.

NULL list

Null list is stored as a bit for every column, which is not declare NOT NULL.

For not full a byte condition, fulfill it with zero bit.

Variable list

In this list, use one or two bytes to express data length.

set W bytes is the longest bytes of the table’s dataset.

set M bytes is the longest characters it can store.

set L is the actual bytes to store this record.

• if M * W < 255, use one byte.

• if M * W > 255

  • L <= 127 use one byte
  • L > 127 use two byte

CHAR(M) column

• if its fixed encoding set, it will be seen as other fixed column.
• else, it will be add to variable list and restrain its length at least M bytes for later convenient expansion.

Redundant Format

main structure is as follow.

Recorder Header

It take fixed 6 bytes.

Compared to compact format, it add n_filed and 1byte_offs_flag and lose record_type

Variable list

It’s use one byte or two bytes depends on total record size rather than single column size.

It use offset rather than length to express its length, meaning that length is its difference.

1byte_offs_flag is used to express it.

• 1 means 1 byte.
• 0 means 2 bytes.

set S as total record size.

• if S <= 127, it’s 1.
• if S > 127 but S < 32767, it’s 0.
• if S > 32767, it’s overflow page, will explain it later.

NULL list

Null list is stored as a bit for every column, which is not declare NOT NULL.

but this bit is moved to Variable list. every first byte’s last bit.

Dynamic Format And Compressed Format

These two are basically same as compact. But it is different in overflow page.

compact format will a store part of data and a overflow page address in origin page.

while Dynamic format will not store data, only overflow page address.

and Compressed Format will compress data.

Layout in Page

Records are linked by a single forward linked list.

A few records are seen as a ‘group’, every slot remember its group main record’s offset in page.

Slot is used to binary search.

Other information

• every slot is 2 bytes, enough to store offset in page(default 16 KB, less than 2^16).

Questions

Why set record header in middle of record and set NULL list and variable list in reverse order ?

when we reach a record, we want to parse it, read its information and data simultaneously.

On the one hand, it better to code to parse it, read one column’s null value and length and move to get it .On the other hand, it’s make closed column get more possibility to be cached, increasing cache hit rate.

Why there are at least two record in a page ?

To make sure search more efficient.

In a page, there are some slots store some record. About 7~8 records per page.

Why page are double linked list ?

for range traversal.