InnoDB storage engine primary key
InnoDB storage engine creates a primary key as follows if the table is created without a specified primary key.
The first determines whether there is a non-empty unique index in the table, and if there is, the column is the primary key
InnoDB creates a pointer (6 bytes) by default if InnoDB does not meet the above criteria.
InnoDB logical storage structure
1. Overall introduction
All InnoDB data is logically stored in a tablespace, called an. Ibd file, so it has a shared tablespace and an independent tablespace for each table.
Table Spaces are made up of segments, extents, and pages. Pages are also called blocks in some documents.
2, table space
Table space is the highest logical structure of InnoDB storage engine. All data is stored in table space, and each table can have its own independent table space through Innodb_file_per_table.
The independent tablespace of each table stores only data, index, insert buffer, and other information such as Undo information, system transaction information, and layer 2 write buffer are still stored in the original shared tablespace, which is the default IBDatA1 file.
3, paragraph
A table space is composed of segments. Common segments are data segments, index segments, and rollback segments. Since InnoDB’s storage engine is index organized, time periods are page nodes of a B+ tree (leaf node segments), and index segments are non-leaf node segments of a B+ tree.
Note that not all objects have segments, so a table space can be interpreted as consisting of discrete pages and segments.
4, area
Extents are made up of 64 consecutive pages, each page is 16KB, so each extents is 1MB. For large data segments, the InnoDB storage engine can apply for up to four extents at a time to ensure sequential data performance. (Consecutive pages, so InnoDB allocates memory space by area?)
On page 5,
A page is InnoDB’s smallest unit of disk management. Common page types are:
Data page (B-True Node)
Undo Log Page
System Page
Transaction System Page
Insert Buffer Bitmap
Insert Buffer Free List
Uncompressed BLOG Page
Compressed binary large object Page (Compressed BLOG Page)
Line 6,
InnoDB is row oriented, which means data is stored row by row. The book says that each line is also stored is regulated, the maximum is 7992 lines of record.
3. InnoDB physical storage structure
1. Overall introduction
Physically, InnoDB tables are made up of shared tablespaces (ibdata1 files), logfile groups (Redo file groups (default ib_logfile0, ib_logFILe1 files), table structure definition files (.frm files, which are associated with mysql), Other engines also have this file). With Innodb_file_pre_TABLE enabled, there is a separate table space for each table (.ibd file) that stores data, indexes, and an internal data dictionary for the table.
4. InnoDB row record format
Recording in a page holds rows of data from a table. To check this out, run the show create table XXX \G command
As you can see, the Row_format row is Dynamic(Compact is used as an example, but the core information should be pretty much the same for each format).
1. Compact row record format
1) Variable length field length list
It is used to represent variable-length fields, and is represented by 1 byte if the column length is less than 255 bytes, and by 2 bytes if the column length is greater than 255 bytes, which explains that the vARCHAR type maximum length is 65535 bytes (which is actually less than that, because there are other default overheads).
2) NULL flag
This bit indicates whether the row data has a NULL value, denoted by 1.
3) Record header information
4) Column data
This is used to store the actual data for each column. It is important to note that the NULL value should not be part of any data, and is only represented by the NULL identifier bit. At the same time, in addition to the user-defined column, there are two hidden columns for each row of data, transaction ID column and rollback pointer column. And if there is no primary key for the rule, a default RowId column is added to each row.
Then there is the Redundant row record format, which is not covered.
2. Row overflow data
The InnoDB storage engine can store some data in a record outside of the actual data page, i.e. as row overflow data. Common BLOB, LOB types of large object columns may occur. It may or may not happen, depending on the size of the data store. The book uses vARCHAR as an example. Varchar can also overflow rows. If SQL_mode is set to strict mode, there may be a warning, set vARCHAR size to 65535. Sql_mode allows you to set strict mode validation for columns.
5. InnoDB data page structure
From the previous introduction, we know that the page type is B-tree node, which stores the actual data of the row in the table.
1. Data page composition
2, the File Header
The File Header is used to record the Header information of a page. It consists of eight parts:
1), FIL_PAGE_SPACE_OR_CHECKSUM
If innodb_file_per_TABLE is not enabled, all table information is stored in idBdatA1 file, so you need to create the corresponding page tablespace.
2), FIL_PAGE_OFFSET
Represents the offset of a page in a table space
FIL_PAGE_PREV, FIL_PAGE_NEXT
Represents the previous and next pages of the current page
4), FIL_PAGE_LSN
Indicates the LSN(Log Sequence Number) of the last modified Log Sequence on the page. This is used to validate page data, as described below
5), FIL_PAGE_TYPE
Indicates the type of a page. The value can be:
6), FIL_PAGE_FILE_FLUSH_LSN
This value is defined only on one page in the data file, and represents that the file has been updated to at least that LSN value
7), FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID
This value indicates which table space the page belongs to.
3, Page Header
It is used to record the status information of the data page and consists of 14 parts
1), PAGE_N_DIR_SLOTS
Number of slots in Page Directory. The relative location in the Page Directory where records are stored
2), PAGE_HEAP_TOP
Pointer to the first record in the heap.
3), PAGE_N_HEAP
Number of records in the heap
4), PAGE_FREE
Refers to the first pointer to the free list
5), PAGE_GARBAGE
The number of bytes to delete the record. The total number of record sizes in a row record structure where delete_flage is 1
6), PAGE_LAST_INSERT
The position where the record was last inserted
7), PAGE_DIRECTION
The direction of the last insertion
8), PAGE_N_DIRECTION
The number of consecutive records inserted in one direction
9), PAGE_N_RECS
The number of records on the page
10), PAGE_MAX_TRX_ID
Changes the maximum transaction ID of the current page, which is defined only in the Secondary Index
11), PAGE_LEVEL
The position of the current page in the index tree. 0x00 indicates a node
12), PAGE_INDEX_ID
Which index ID the current page belongs to
13), PAGE_BTR_SEG_LEAF
The first byte position of a file segment in a page node of a B+ tree. This value is only defined in the Root page of the B+ tree
14), PAGE_BTR_SEG_TOP
In a nonleaf node of a B+ tree, the position of the first pointer to a file segment. This value is only defined in the Root page of the B+ tree.
4. Infimun and Supremun records
Each data page has two virtual row records that define the record boundaries. Infimun records values smaller than any primary key on the page, and Supremun is larger
5. User Records and FreeSpace
User Records is the contents of the actual stored row Records. FreeSpace is FreeSpace and a linked list data structure. When a record is deleted, the creation goes to the free list.
6, the File Trailer
InnoDB storage engine has a File Trailer section in its pages to ensure that pages can be fully written to disk. This value has only the FIL_PAGE_END_LSN part, 8 bytes. The first four bytes represent the checksum value of the page, and the last four bytes are the same as FIL_PAGE_LSN in the previous File Header. Page integrity is guaranteed by comparing the two values to see if they are the same.