Simple sorting of MySQL InnoDB Engine B + tree indexes, mysqlinnodb

Simple sorting of MySQL InnoDB Engine B + tree indexes, mysqlinnodb

 

Standard Source: http://www.cnblogs.com/wy123/p/7211742.html

(The Source retained is not the right of original works. My work is far from reaching this level, just to link to the original article, because some possible errors will be corrected or supplemented later, without him)

 

 

There are several index types for InnoDB Engine tables in MySQL (the indexes mentioned below are not described in detail. They all refer to InnoDB Engine table indexes .)
0 = Secondary Index, Secondary Index,
1 = Clustered Index, Clustered Index
2 = Unique Index, Unique Index
3 = Primary Index, Primary key Index
32 = Full-text Index, Full-text Index
64 = Spatial Index, Spatial Index
128 = A secondary index that includes des a virtual generated column. secondary calculation column index.

Secondary indexes, clustered indexes, unique indexes, primary key indexes, and secondary computing column indexes are stored in the B + tree according to the logical storage structure.
This is not very different from the logic storage structure of the B + tree indexes of other databases. The above indexes are logically divided.

From the perspective of physical storage, these indexes in MySQL can be divided into clustered indexes and secondary indexes (or non-clustered indexes)
Primary key indexes and clustered indexes can be classified as clustered indexes, secondary indexes, unique indexes, and non-clustered indexes for all secondary computing column indexes.

Clustered index in MySQL

MySQL clustered index logically organizes the data of the entire table into a B + tree based on the primary key. Therefore, a table can only contain one clustered index.
Non-leaf nodes store the clustered index key value. The leaf nodes store the data in the table, and the leaf nodes are connected by a two-way linked list.

Before learning about the clustered index of MySQL, first understand the MySQL primary key generation mechanism.
MySQL InnoDB must have a primary key,
If a primary key is specified during table creation, this primary key is the primary key of the table (it sounds so awkward, mainly compared with the automatically generated primary key when no primary key is specified)
If no primary key is specified during table creation, the storage engine automatically creates a primary key column for the table.
1) For tables with primary keys specified, the index generated by the primary key is "primary key index ",
2) For tables with no primary key specified, if there are (one or more) unique non-null indexes, the (first) non-null unique constraint is used as the primary key.
3) For tables with no primary key specified and without unique constraints, a primary key is generated by default. The index generated on the primary key is a clustered index ",
In fact, both the "primary key index" of the former and the "clustered Index" of the latter can belong to the clustered index in physical storage.

1. Explicit primary key index (clustered index)

Test_index_type_1 is created as follows:
If a primary key is specified during table creation, the primary key index is generated by default. The index type is 3 (from the perspective of physical storage, it is a clustered index)
An index is created after the table is created. Secondary indexes are generated and the index type is 0 (from the perspective of physical storage, non-clustered indexes)

2. Primary Key Index (clustered index) generated with unique non-null Constraints)

Test_index_type_2 is created,
If a primary key is not specified during table creation, but a unique non-null constraint is specified, this field is used as the primary key and the generated index type is 3 (from the perspective of physical storage, is a clustered index)

3. The clustered index (clustered index) generated by the system's default primary key)

Table test_index_type_3 is created as follows,
When creating a table, no primary key is specified, and no unique non-null constraint is specified. The InnoDB engine automatically generates a 6-byte pointer, And the generated index type is clustered index, the type is 1 (from the perspective of physical storage, it is a clustered index)

 

Non-clustered Index

Non-clustered indexes, non-clustered indexes are also the structure of the B + tree to store data,
The big difference from clustered indexes is that the leaf node of the non-clustered index stores only the index key value + the clustered index key value, but does not include all the non-index key values.

1. Unique index generated by the unique index constraint (non-clustered index)

The test_index_type_4 table is created as follows,
If the id is unique, a unique index is automatically created on the id column.

 

2. Unique index manually created (non-clustered index)
Table test_index_type5 is created as follows,
If you manually create a unique index, the index type is unique.

3. Manually created secondary indexes (non-clustered indexes)
Table test_index_type6 is created as follows,
If you manually create an index (unique is not specified), the index type is secondary index.

　　

 

4. Calculate the column index. manually create an index on the calculated column (non-clustered index)
Table test_index_type7 is created as follows,
There is a computing column on test_index_type7. After creation, an index is added to the computing column. The index is the calculation column index.

 

Summary:

On the whole, the indexes of several types of B + trees in MySQL are easy to understand, and are similar to those in SQL Server.
MySQL InnoDB Engine tables, primary key indexes, clustered indexes generated by non-null unique constraints, clustered indexes, and data clustered indexes from the perspective of physical storage.
Primary Key Index, clustered index generated with unique non-null constraints, all of which must be non-empty and unique.
In addition, MySQL cannot explicitly create a clustered index, that is, create clustered index.

This is very different from SQL Server,
1. in SQL Server, if the primary key is not specified or the primary key is not specified but its nonclustered, the table is a heap table, and the system does not add the default field as the clustered index.
2. The primary key of SQL Server can be a primary key rather than a clustered index (the primary key is a clustered index by default ).

Clustered indexes can be specified on any column. They can be non-primary key columns, and can be non-unique, null, and repeatable columns. For example: