InnoDB storage engine: files and tables

Yanglinshan little wild boar 2022-05-22 12:37:01 阅读数:820



constitute MySQL Database and InnoDB Various types of files that store engine tables :

  1. Parameter file ( tell MySQL Where can I find the database file when the instance starts , And specify some initialization parameters )
  2. Log files ( Used to record MySQL The file that an instance writes in response to a condition )
  3. socket file ( When used UNIX The file needed to connect in the domain socket mode )
  4. pid file (MySQL Process of instance ID file )
  5. MySQL Table structure file ( For storage MySQL Table structure definition file )
  6. Storage engine files ( Data such as records and indexes are stored )


Index organization table

The tables that are organized and stored according to the primary key order are called index organization tables .

If the primary key is not explicitly defined when the table is created , be InnoDB The storage engine will select or create a primary key as follows :

  1. First, determine whether there is a non empty unique index in the table , If there is , The column is the primary key .
  2. If the above conditions are not met ,InnoDB The storage engine automatically creates a 6 Byte size pointer .

When there are multiple non empty unique indexes in the table ,InnoDB The storage engine will choose the first... When creating a table Definition The non empty unique index of is the primary key .

_rowid The primary key of the table can be displayed , However, it can only be used to view the case where a single column is the primary key , For the primary key composed of multiple columns, there is nothing to do .

InnoDB Logical storage structure

 Insert picture description here

Table space :InnoDB The highest level of the logical structure of the storage engine , All the data is stored in the table space .
paragraph : Segments make up the tablespace , There are common passages Data segment (B+ The leaf node of the tree )、 Index segment (B+ The non leaf node of a tree )、 Rollback segment etc. .

District : A space consisting of consecutive pages , In any case, the size of each zone is 1MB.
page :InnoDB Minimum unit of disk management .
That's ok :InnoDB The storage engine is row oriented , That is to say, the data is stored by row .

InnoDB Line record format

InnoDB 1.0.x Before the release ,InnoDB The storage engine provides Compact and Redundant Two formats to store row record data ;1.0.x The version began to introduce a new file format , I used to support Compact and Redundant The format is called Antelope File format , The new file format is called Barracuda File format , There are two new line record formats under this file format Compressed and Dynamic.

Compact Line record format

Compact The design goal of row record format is Store data efficiently .

 Insert picture description here
Not NULL Variable length field length list : In the order of columns The reverse Placed , The maximum length can not exceed 2 byte .
NULL Sign a : Indicates whether there is... In the row data NULL value .
Record header information : Fixed occupancy 5 byte .
The following column data has two hidden columns : Business ID Column and Rollback pointer column , Respectively 6 Byte and 7 The size of bytes . if InnoDB Table has no primary key defined , One more per line 6 Bytes of rowid Column .

Whether it's CHAR Type or VARCHAR type , stay compact Under format NULL Values do not take up any storage space .

Redundant Line record format

 Insert picture description here
Record header information : Fixed occupancy 6 byte .

The rest are the same as Compact

about CHAR type , stay Redundant Under format NULL Value takes up storage space ; And for VARCHAR type , It doesn't take up any storage space .

Compressed and Dynamic Line record format

For storage in BLOB The data in adopts the method of complete row overflow :

 Insert picture description here

InnoDB Data page structure

 Insert picture description here
Infimum and Supremum Record: Virtual line record , Used to define the boundaries of records .
Page Directory: Where records are kept The relative position .


Constraint mechanism provides a powerful and simple way to ensure the integrity of data in the database .

about InnoDB For the storage engine itself , The following constraints are provided :

  1. Primary Key( The constraint name is PRIMARY)
  2. Unique Key( The default constraint name is the same as the column name )
  3. Foreign Key( To ensure referential integrity )
  4. Default( Used to specify the default value of a column )
  5. NOT NULL( The specified constraint field must have a value )

Constraint creation

There are two ways to create constraints :

  1. Constraints are defined when a table is created
  2. utilize ALTER TABLE Command to create constraints

The difference between constraints and indexes

When a user creates a unique index, a unique constraint is created . But the concepts of constraint and index are still different , Constraint is a logical concept , To ensure the integrity of data ; Index is a data structure , Existing logical concepts , It also represents the way of physical storage in the database .

Constraints on wrong data

If the user wants to insert or update illegal data in the database through constraints , namely MySQL The database prompts an error instead of a warning , Then the user must set parameters sql_mode, Used to strictly approve the input parameters .

ENUM and SET constraint

MySQL The database does not support traditional CHECK constraint , But by ENUM and SET Type can solve some of these constraint requests . But it is limited to the constraints of discrete values , For tradition CHECK Constraints support continuous value range constraints or more complex constraints , You need to use triggers to constrain the range of values .

Triggers and constraints

The function of trigger is to execute INSERT、DELETE and UPDATE Automatically call before or after the command SQL A command or stored procedure .( At most one table can be created 6 Trigger


A view is a named virtual table , It consists of a SQL Query to define , Can be used as a table . The data in the view has no actual physical storage .

One of the main uses of view is to be used as an abstract device , To some extent, it plays a role Security layer The role of .

Partition table

MySQL The partition type supported by the database is Horizontal zoning , Vertical partitioning is not supported .

MySQL The partition of the database is Local partition index , A partition holds both data and indexes .

Partition is mainly used for the management of high availability of database .

The partition types are as follows :

  1. RANGE Partition ( The values of partition columns are continuous )
  2. LIST Partition ( The value of the partition column is discrete )
  3. HASH Partition ( Partition using user-defined functions )
  4. KEY Partition ( Use MySQL The functions provided by the database are partitioned )
  5. COLUMNS Partition ( You can partition directly with non integer data )

Subarea Partition is based on partition ,MySQL Database allowed on RANGE and LIST We're going to do it again HASH or KEY Sub divisions of .

The following points should be paid attention to in the proposal of sub partition :

  1. The number of subareas must be the same .
  2. To use... On any partition of a partitioned table SUBPARTITION To clearly define any subarea , You have to define all the subareas .
  3. Every SUBPARTITION Clause must include a name for the child partition .
  4. The name of the subarea must be unique .

MySQL The database allows for NULL It's worth zoning , Its partitions are always viewed as NULL The value is less than any non NULL value .

RANGE The partition will NULL Put the value in the leftmost partition ;LIST The partition must explicitly indicate which partition to put NULL value ;HASH and KEY Any partition function of the partition will NULL The record of the value is returned as 0.

The last thing to notice is , Partitions are not always suitable for OLTP application , Users should plan their own partition design according to their own applications ; about OLAP application , Partitioning can improve query performance .

copyright:author[Yanglinshan little wild boar],Please bring the original link to reprint, thank you.