1、 What is index

In a relational database , The index is a separate 、 A storage structure that physically sorts the values of one or more columns in a database table , It is a collection of one or more column values in a table and a corresponding list of logical pointers to the data pages in the table that physically identify these values . The function of index is equivalent to the catalogue of books , You can quickly find the required content according to the page number in the table of contents .

2、 Classification of indexes

a、 primary key ： When set as the primary key, the data will be indexed automatically ,innodb Index for clustering .
b、 Single value index ： That is, an index contains a single column , A table can have multiple single-column indexes , Also called general index
c、 unique index ： The value of the index must be unique , But you can have an empty value , The biggest difference between it and the primary key index is that the primary key index cannot have a null value , But a unique index is allowed to have a null value
d、 Composite index ： That is, an index contains multiple columns
e、 Full-text index ：innodb I won't support it ,fulltext type .

3、 The principle of indexing

The bottom layer is B+ Tree structure , Only leaf nodes can store key And data , Non leaf nodes can only store key.
B+ Each node of the tree can contain more nodes , This reduces the height of the tree , The data range becomes multiple intervals , The more intervals , Faster data retrieval . Leaf nodes are connected with two pointers , Conforms to the read ahead feature of the disk , Higher performance of sequential query .
Why there is no choice B Trees , because B The tree requires that non leaf nodes must store data .

4、 The difference between clustered index and non clustered index

Clustered index puts data storage and index together , The leaf node of the index structure holds the data , The primary key index must be a clustered index ;
Non clustered indexes store data separately from indexes , The leaf node of the index structure points to the corresponding position of the data .
stay innodb in , An index created on a clustered index is called a secondary index , A non clustered index is called a secondary index .

5、 Advantages of clustering index , What should cluster index pay attention to , Why do primary keys usually suggest self increment id

6、 Under what circumstances, indexes cannot be used

7、Innodb And Myisam The difference between