Normal index, unique index, primary index, foreign key index, composite index, non-primary index, clustered main code (clustered index), single-column index, multicolumn index

It is strongly recommended that you read the first reference to see this blog post, because the implementation of the underlying data structure is not prepared here.

Index: index is the data structure that helps MySQL to get data efficiently. by extracting the skeleton of a sentence, you can get the essence of the index: The index is the data structure. It expresses the scope of the storage engine, meaning that it is only meaningful to talk about indexes at the storage engine level. MyISAM, InnoDB, memory and so on. The InnoDB storage engine is simply discussed here.

B-tree, B+TREE data structure.

　　In addition to the data, the database system maintains a specific lookup calculation

Data structures that reference (point to) data in some way, so that advanced search algorithms can be implemented on these data structures. This data structure is the index

1, the main code index: is the main code based on the establishment of the index. Primary key. In InnoDB, the table data file itself is an index structure organized by B+tree, and the tree's leaf node data field holds the complete data record. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index. Also called the main code index, gather the main code.

The main index here is divided into an ordered master index (for example, id increment), and an index that is not ordered (for example, the Name-varchar type), which has a particularly large effect on the fill factor.

2, gather the main code. Why the main code index (primary index) is also called the clustered Master code. The aggregation here refers to the fact that the data file and index are clustered in the leaf node, not the address of the data file that is stored in the leaf node like MyISAM. You can see that the leaf node contains the complete data record. This index is called a clustered index. Because the InnoDB data file itself is clustered by the primary key, the INNODB requires that the table must have a primary key (MyISAM can not), and if it is not explicitly specified, the MySQL system will automatically select a column that uniquely identifies the data record as the primary key, and if no such column exists, MySQL automatically The InnoDB table generates an implicit field as the primary key, which is a length of 6 bytes, and the type is long shaped.

　　

3, secondary index. The secondary index is relative to the main code index. In MyISAM, the structure of the secondary index and the structure of the main code index are the same, the use of B + tree structure, and the leaf node is stored in the data record address. While InnoDB also uses B + Tree storage, the leaf node of the secondary index stores the primary key corresponding to the main code index. That is, if you are looking for data through a secondary index, look for the primary key in the B + tree, and then find the corresponding record based on the primary index two times .

4, non-main code index. Also called the non-primary index and secondary index, the same as the above secondary index.

Note: The main code column data length must not be too long because the leaf node in the secondary index stores the primary key value, too long will cause the secondary index to be too large for extra storage space.

　　

5, unique index. A unique index is a unique, indexed, and data integrity check of the value of a column. The primary index must be a unique index. A table can have multiple unique indexes, and nullable columns can also establish a unique index (null! =null), but there can be only one primary index (primary key unique) in a table, and the column cannot be NULL, and if the Auto increment column is defined, the column must be part of the primary index.

Normal index, unique index, primary index, foreign key index, composite index, non-primary index, clustered main code (clustered index), single-column index, multicolumn index

6, foreign key index. The foreign key index is actually the primary index or the secondary index, which is mainly used for operations related to foreign keys such as connection operations between tables. If you define a FOREIGN key constraint for a foreign key field, MySQL defines an internal index to help you manage and use foreign key constraints in the most efficient way. currently in MySQL's default storage engine, only INNODB supports foreign key constraints and does not require an index of corresponding columns, but it is generally considered that efficiency should be added.

7.

Reference documents:

1. Data structure and algorithm principle behind MySQL index

2. mysql index type and pros and cons

3.

Normal index, unique index, primary index, foreign key index, composite index, non-primary index, clustered main code (clustered index), single-column index, multicolumn index