MySQL InnoDB Index Organization table & PRIMARY KEY function

InnoDB Index Organization table

I. Index organization TABLE definition

In the InnoDB storage engine, tables are organized according to the primary key order , which is called the Index Organization table (Index organized table IOT).

In the InnoDB storage engine, each table has a primary key (Primary key), and if a primary key is not defined when the table is created, the InnoDB storage engine chooses the qualifying column in the table to create the primary key .

Conditions:

1. First determine if there is a unique index (unique NOT NULL) in the table, and if so, the column is the primary key.

2. If the above conditions are not met, the InnoDB storage engine automatically creates a 6-byte size pointer.

When there are multiple, non-null, unique indexes in a table, the InnoDB storage engine acts as a primary key based on the first non-null unique index created when the table is built.

Second, the case

Create Test table

CREATE TABLETest (aINT  not NULL, bINT NULL, CINT  not NULL, DINT  not NULL ,    UNIQUE KEY(b),UNIQUE KEY(d),UNIQUE KEY(c))

　　Initializing data

INSERT  into SELECT 1,2,3,4; INSERT  into SELECT 5,6,7,8;

The main key value of the strap can be judged by the following statement (_rowid primary key)

Select *  from Test;

Although the order of the B-field indexes precedes D, the D field is selected as the primary key because the B field allows null values.

PS, if the joint index, so long helpless!

Third, why do you need a unique primary key?

The primary key (primary key) is a column (or set of columns) whose value uniquely distinguishes each row in the table. This column (or set of columns) that uniquely identifies each row in the table is called the primary key. Without a primary key, it is difficult to update or delete a particular row in a table because there is no secure way to ensure that only related rows are designed. Simply speaking, the purpose of the primary key is to index.

The InnoDB engine uses a clustered index, and the data record itself is stored on the leaf node of the primary index (one b+tree). This requires that each data record in the same leaf node (the size of a memory page or a disk page) be stored in the primary key order , so that whenever a new record is inserted, MySQL inserts it into the appropriate node and position according to its primary key. If the page reaches the load factor (InnoDB defaults to 15/16), a new page (node) is opened.

1. If the table uses the self-increment primary key, each time a new record is inserted, the record is added sequentially to the subsequent position of the current index node, and when a page is full, a new page is automatically opened. As shown in the following:

This creates a compact index structure that fills in the approximate order. because there is no need to move existing data each time it is inserted, it is highly efficient and does not add much overhead to maintaining the index.

2. If you are using a non-self-increasing primary key ( such as a social security number or a school number, etc. ), each time a new record is inserted into the existing index page, because the value of each insert primary key is approximate to random. at this point, MySQL had to move the data in order to insert the new record in the appropriate location, even the target page may have been written back to disk and cleared from the cache, and then read back from the disk, which adds a lot of overhead, while the frequent movement, paging operations caused a lot of fragmentation , The index structure is not sufficiently compact, and subsequent tables have to be rebuilt by optimize table and the fill page is optimized.

MySQL InnoDB Index Organization table & PRIMARY KEY function