Yanglinshan little wild boar 2022-05-22 12:37:01 阅读数:820
constitute MySQL Database and InnoDB Various types of files that store engine tables ：
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 ：
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 .
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 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 The design goal of row record format is Store data efficiently .
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 .
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 .
For storage in BLOB The data in adopts the method of complete row overflow ：
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 ：
There are two ways to create constraints ：
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 .
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 .
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 .
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 .
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 ：
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 ：
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. https://en.javamana.com/2022/142/202205211828472237.html