This page contains:
After reading this page, you will know how MySQL/InnoDB stores a physical record.
The chart below shows the three parts of a physical record.
Legend: The letter 'F' stands for 'Number Of Fields'.
The meaning of the parts is as follows:
An Important Note About The Word "Origin"
The "Origin" or "Zero Point" of a record is the first byte of the Field Contents --- not the first byte of the Field Start Offsets. If there is a pointer to a record, that pointer is pointing to the Origin. Therefore the first two parts of the record are addressed by subtracting from the pointer, and only the third part is addressed by adding to the pointer.
FIELD START OFFSETS
The Field Start Offsets is a list in which each entry is the position, relative to the Origin, of the start of the next field. The entries are in reverse order, that is, the first field's offset is at the end of the list. An example: suppose there are three columns. The first column's length is 1, the second column's length is 2, and the third column's length is 4. In this case, the offset values are, respectively, 1, 3 (1+2), and 7 (1+2+4). Because values are reversed, a core dump of the Field Start Offsets would look like this: 07,03,01.
There are two complications for special cases:
It is unlikely that the "field and offset are on different pages" unless the record contains a large BLOB.
The Extra Bytes are a fixed six-byte header.
Total size is 48 bits, which is six bytes.
If you're just trying to read the record, the key bit in the Extra Bytes is 1byte_offs_flag — you need to know if 1byte_offs_flag is 1 (i.e.: "short 1-byteoffsets") or 0 (i.e.: "2-byte offsets").
Given a pointer to the Origin, InnoDB finds the start of the record as follows:
The Field Contents part of the record has all the data. Fields are stored in the order they were defined in.
There are no markers between fields, and there is no marker or filler at the end of a record. Here's an example.
To understand what follows, you must know that table T has six columns — not three — because InnoDB automatically added three "system columns" at the start for its own housekeeping. It happens that these system columns are the row ID, the transaction ID, and the rollback pointer, but their values don't matter now. Regard them as three black boxes.
Here is an extract of the dump:
A reformatted version of the dump, showing only the relevant bytes, looks like this (I've put a line break after each field and added labels):
Reformatted Hexadecimal Dump
You won't need explanation if you followed everything I've said, but I'll add helpful notes for the three trickiest details.
Notice that the sizes of the record's fields, in forward order,are:6, 6, 7, 2, 2, 2.Since each offset is for the start of the "next"field,the hexadecimal offsets are 06,0c(6+6),13(6+6+7),15(6+6+7+2),17(6+6+7+2+2),19(6+6+7+2+2+2). Reversing the order,the Field Start Offsets of the first record are: 19,17,15,13,0c,06.
Look at the Extra Bytes of the first record: 00 00 78 0D 02 BF. The fourth byte is 0D hexadecimal, which is 1101 binary ... the 110 is the last bits of n_fields (110 binary is 6 which is indeed the number of fields in the record) and the final 1 bit is 1byte_offs_flag. The fifth and sixth bytes, which contain 02 BF, constitute the "next" field. Looking at the original hexadecimal dump, at address 0D42BF (which is position 02BF within the page), you'll see the beginning bytes of System Column #1 of the second row. In other words, the "next" field points to the "Origin" of the following row.
For the third row, I inserted NULLs in FIELD2 and FIELD3. Therefore in the Field Start Offsets the top bit is on for these fields (the values are 94 hexadecimal, 94 hexadecimal, instead of 14 hexadecimal, 14 hexadecimal). And the row is shorter because the NULLs take no space.
Where to Look For More Information References:
The most relevant InnoDB source-code files are rem0rec.c, rem0rec.ic, and rem0rec.h in the rem ("Record Manager") directory.
MySQL Related Interview Questions
|PHP Interview Questions||MySQL Interview Questions|
|PHP+MySQL Interview Questions||Drupal Interview Questions|
|MYSQL DBA Interview Questions||PHP5 Interview Questions|
|WordPress Interview Questions||Joomla Interview Questions|
|CakePHP Interview Questions||CodeIgniter Interview Questions|
|PHP7 Interview Questions|
A Guided Tour Of The Mysql Source Code
Important Algorithms And Structures
How Mysql Performs Different Selects
How Mysql Transforms Subqueries
Mysql Client/server Protocol
Prepared Statements And Stored Routines
Myisam Storage Engine
Innodb Storage Engine
Writing A Custom Storage Engine
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.