There are several types of InnoDB engine tables in MySQL (the following index, no special instructions, refers to the InnoDB engine table index.)
0 = Secondary Index, 2-level index,
1 = Clustered Index, clustered index
2 = Unique Index, Unique Index
3 = Primary Index, Primary Key Index
32 = Full-text Index, Full Index
64 = Spatial Index
128 = A secondary index that includes a virtual generated column.
The second-level index, the clustered index, the unique index, the primary key index, the second-level computed column index, and the logical storage structure, are all stored in the B + tree to store
this point with the B + tree index logical storage structure of the other database Not too different, the above several indexes are divided from the logical point of view.
If the physical storage from the point of view, MySQL in these types of index can be divided into clustered index and secondary index (or non-clustered index)
Among them, the primary key index and clustered index, can be classified as clustered index, Unique index, secondary computed column index data nonclustered index.
The clustered index in MySQL
MySQL clustered index is based on the primary key, the entire table of data, logically organized into a B + tree, so a table can only be a clustered index.
The non-leaf node stores the clustered index key value, and the leaf node stores the data itself in the table, and the leaf node and the leaf node are connected together by a two-way list.
Know the MySQL clustering index before the first look at the MySQL primary key generation mechanism.
MySQL InnoDB must have a primary key,
if the time specified in the construction of the primary key, then the primary key is the main key of the table (sounds so awkward, mainly with the primary key is not specified, the automatic generation of the main key for comparison)
If the primary key is not specified, the storage engine will automatically create a primary key column for the table.
1) For the table that specifies the primary key, the primary key is the primary key index.
2) For tables that do not specify a primary key, If there is a unique index (one or more) that is not empty, the (first) non-null unique constraint is the primary key.
3) For a table that does not specify a primary key and there is no unique constraint, a primary key is generated by default. The index is “clustered index”, in
fact, the former “primary key index” and the latter “clustered index”, the physical storage can be attributed to clustered index
1, explicit primary key index (clustered index)
In the following screenshot, the test_index_type_1 is created
when the primary key is specified in the table, the primary key defaults to the primary key index. The index type is 3 (from the physical storage point of view, the index is
created ) Level index, the index type is 0 (from the physical storage point of view, is non-clustered index)
2, non-empty unique constraints generated by the primary key index (clustered index)
The following screenshot, created test_index_type_2,
did not specify the primary key when the table was created , but specified a unique non-null constraint, then the field would be used as the primary key, and the resulting index type would be 3 (from the physical storage point of view index)
3, the system default primary key generated clustered index (clustered index)
In the following screenshot, the test_index_type_3 table was created,
the primary key was not specified at the time of the table, and the unique non-null constraint was specified. The InnoDB engine automatically generates a 6-byte pointer. The generated index type is a clustered index, (From the physical storage point of view, is the clustered index)
Nonclustered index
The non-clustered index, the nonclustered index is also the structure of the B + tree to store the data.
The difference from the clustered index is that the leaf node of the nonclustered cable stores only the key value of the index + the key value of the clustered index, but does not include All non-index key values.
1, unique index generated by the unique index (non-clustered index)
The following screenshot, created the test_index_type_4 table,
specify the id as unique, then the id will automatically create a unique index.
2, the manual creation of a unique index (non-clustered index) The
following screenshot, created test_index_type5 table,
manually create a unique index, then the index type is unique index
3, manually created secondary index (non-clustered index) The
following screenshot, created test_index_type6 table,
that manually create an index (not specified unique), then the index type for the secondary index
4, the calculation of the column index, in the calculation of the column manually create the index (non-clustered index) The
following screenshot, created test_index_type7 table,
test_index_type7 there is a calculation column, after the completion of the calculation column in the calculation of the index,
to sum up:
Overall, MySQL several types of B + tree index is still relatively easy to understand, with the SQL Server index is also similar.
MySQL’s InnoDB engine table, the primary key index, non-null unique constraint generated by the clustered index, clustered index, from the physical storage point of view are data clustered index.
Primary key index, non-empty unique constraint generated clustered index, clustered index, the three have a distinct feature, all require that the column is non-empty and unique.
The other is that MySQL can not explicitly create a clustered index, that is, create clustered index.
This is very different from SQL Server,
1, in SQL Server, if you do not specify the primary key, or specify the primary key is not specified but nonclustered, then the table is for the heap table, the system will not add the default field as a clustered index
2 , SQL Server primary key can only be the primary key, you can not clustered index (by default the primary key is clustered index).
The clustered index can be specified on any column, either a non-primary key column, a non-unique, nullable, repeatable column, such as
Recent Comments