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.
\nThe 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.<\/p>\n
Know the MySQL clustering index before the first look at the MySQL primary key generation mechanism.
\nMySQL InnoDB must have a primary key,
\nif 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)
\nIf the primary key is not specified, the storage engine will automatically create a primary key column for the table.
\n1) For the table that specifies the primary key, the primary key is the primary key index.
\n2) 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.
\n3) 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
\nfact, the former “primary key index” and the latter “clustered index”, the physical storage can be attributed to clustered index<\/p>\n
1, explicit primary key index (clustered index)<\/p>\n
In the following screenshot, the test_index_type_1 is created
\nwhen 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
\ncreated\u00a0)\u00a0Level index, the index type is 0 (from the physical storage point of view, is non-clustered index)<\/p>\n
<\/p>\n
<\/p>\n 2, non-empty unique constraints generated by the primary key index (clustered index)<\/p>\n The following screenshot, created test_index_type_2, <\/p>\n <\/p>\n <\/p>\n 3, the system default primary key generated clustered index (clustered index)<\/p>\n In the following screenshot, the test_index_type_3 table was created, <\/p>\n <\/p>\n Nonclustered index<\/strong><\/p>\n The non-clustered index, the nonclustered index is also the structure of the B + tree to store the data. 1, unique index generated by the unique index (non-clustered index)<\/p>\n The following screenshot, created the test_index_type_4 table, <\/p>\n <\/p>\n <\/p>\n <\/p>\n 2, the manual creation of a unique index (non-clustered index) The <\/p>\n <\/p>\n 3, manually created secondary index (non-clustered index) The <\/p>\n 4, the calculation of the column index, in the calculation of the column manually create the index (non-clustered index) The test_index_type7 there is a calculation column, after the completion of the calculation column in the calculation of the index,<\/p>\n![\"\"](\"http:\/\/rmohan.com\/wp-content\/uploads\/2017\/08\/170731091444891.png\")
<\/a><\/p>\n
\ndid not specify the primary key when the table was\u00a0created\u00a0, 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)<\/p>\n<\/a><\/p>\n
\nthe 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)<\/p>\n<\/a><\/p>\n
\nThe 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.<\/p>\n
\nspecify the id as unique, then the id will automatically create a unique index.<\/p>\n<\/a><\/p>\n
\nfollowing screenshot, created test_index_type5 table,
\nmanually create a unique index, then the index type is unique index<\/p>\n<\/a> <\/a><\/p>\n
\nfollowing screenshot, created test_index_type6 table,
\nthat manually create an index (not specified unique), then the index type for the secondary index<\/p>\n
\nfollowing screenshot, created test_index_type7 table,<\/p>\n