The type of MySQL index (single column index, combined index, BTREE index, clustered index, etc.)

One 、 Type of index
Mysql At present, there are mainly the following index types ：FULLTEXT,HASH,BTREE,RTREE.

1. FULLTEXT
   It's a full-text index , At present, only MyISAM Engine support . It can be CREATE TABLE ,ALTER TABLE ,CREATE INDEX Use , But at present, there is only CHAR、VARCHAR ,TEXT Full-text indexes can be created on columns .

Full text indexing is not about MyISAM Born together , It was invented to solve WHERE name LIKE “%word%" This kind of fuzzy query for text is inefficient .

2. HASH
   because HASH The only （ almost 100% The only ） And similar key value pairs , It's good as an index .

HASH The index can be located at one time , You don't need to look at it layer by layer like a tree index , So it's very efficient . however , This kind of efficiency is conditional , It's just in “=” and “in” Efficient under conditions , For range queries 、 Sorting and composite indexing are still inefficient .

3. BTREE
   BTREE Index is a kind of index value according to a certain algorithm , Store in a tree like data structure （ Binary tree ）, Every query is from the entrance of the tree root Start , Traverse in turn node, obtain leaf. This is a MySQL The default and most commonly used index types .

4. RTREE
   RTREE stay MySQL Rarely used , Support only geometry data type , The only storage engine that supports this type is MyISAM、BDb、InnoDb、NDb、Archive several .

be relative to BTREE,RTREE The advantage is range finding .
————————————————
Copyright notice ： This paper is about CSDN Blogger 「liutong123987」 The original article of , follow CC 4.0 BY-SA Copyright agreement , For reprint, please attach the original source link and this statement .
Link to the original text ：https://blog.csdn.net/liutong123987/article/details/79384395

Two 、 Types of indexes
1、 Single column index and combination （ Reunite with 、 Multiple columns ） Indexes
Single index
Including general index primary key Single index
primary key , No null values are allowed ,( stay B+TREE Medium InnoDB In the engine , The primary key leads to a crucial position )

The rules for establishing a primary key index are int be better than varchar, It is usually created when creating a table , It's better to have columns that are not related to other fields of the table or that are not related to business . It is usually set to int And it's AUTO_INCREMENT Self increasing type

unique index , Similar to a normal index , But the difference is that the unique index requires that the values of all classes are unique , This is the same as the primary key index . But he allows free value , If it's a composite index , The combination of column values must be unique

1、 Do not start the query by the leftmost column of the index （ Multi column index ） for example index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ No index ,where c2 = aaa and c3=sss Index cannot be used

2、 A column in the query has a range query , Then all the columns on the right cannot use the query （ Multi column query ）

Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ Changing the query will only use the first two columns in the index , because like It's range query

3、 You can't skip a field to query , It doesn't take advantage of indexes , Such as my sql yes

explain select * from award where nickname > ‘rSUQFzpkDz3R’ and account = ‘DYxJoqZq2rd7’ and created_time = 1449567822; Then he can't use his composite index at this time

Clustered index and ordinary index are a pair InnoDB The leaf node of the normal index stores the primary key value .

Return to the table for query , First locate the primary key value , Repositioning row records , Its performance is lower than scanning the index tree .

Overlay index Whether it's SQL-Server Official website , still MySQL Official website , All expressed ： It only needs to be on an index tree to get SQL All required column data , There is no need to return the form , Faster .

The difference between a primary key index and a unique index ：

1. A primary key is a constraint , The only index is an index , The two are essentially different .

2. After the primary key is created, it must contain a unique index , A unique index is not necessarily a primary key .

3. Null values are allowed for unique index columns , The primary key column is not allowed to be null .

4. When the primary key index is created , It has been defaulted to a non null value + The only index .

5. A table can only create one primary key index at most , But you can create multiple unique indexes .

6. Primary keys are more suitable for unique identities that are not easy to change , Such as auto increment column 、 ID number, etc .

7. The primary key can be referenced as a foreign key by other tables , And the only index can't .
   ————————————————
   Copyright notice ： This paper is about CSDN Blogger 「 War wound 」 The original article of , follow CC 4.0 BY-SA Copyright agreement , For reprint, please attach the original source link and this statement .
   Link to the original text ：https://blog.csdn.net/qq_38852289/article/details/80817156

Clustered index and non clustered index

1、 About the characteristics of clustered index and non clustered index

Cluster index ：
a、 A table has only one clustered index
b、 Cluster index B+ The row data stored in the leaf node of the tree
c、 Column rules for clustered index establishment , According to priority ： Primary key -> The first non empty unique column ->InnoDB Create an implicit row-id As cluster index
d、 The storage order of cluster index is consistent with that of physical data

Nonclustered index ：
a、 It is also called ordinary index or auxiliary index , A table can have 0-n Non clustered indexes
b、 Indexes B+ The leaf node of the tree stores the primary key value

Take chestnuts to illustrate the connection and difference between the two ：
Suppose the data table is as follows （ among id Primary key ,name For general index ）：

The corresponding index structure is as follows ( Here is only for example , Actually B+ The tree is stored , A node stores 0-n It's worth )：

here , If we implement where id=2, Then the index is shown by the green arrow , Directly indexed by clustering B+ Trees , Retrieve the specified location , And get the row data record

If you execute where name='wangwu', The steps to be performed are shown by the red arrow , A two-step ：

First step ： Through secondary index , retrieval name=‘wangwu’, And get the primary key value of the leaf node as 3
The second step ： Through primary key id=3, To the clustered index , Then go to the leaf node and get id=3 Row data

By comparison, we can know , The retrieval efficiency of clustered index is significantly higher than that of auxiliary index , Because the secondary index needs to be executed twice B+ Retrieval of tree index .

2、 About back table query .

Similar to the above through name=‘xxx’ such , It takes two searches , The primary key value is retrieved through the auxiliary index for the first time , Then the process of retrieving the actual row records from the cluster index through the primary key value , It is called back to table query . So write less select *

3、 About index coverage and common implementation .

It can be obtained only on an index tree SQL All the data needed without going back to the table , Called index overlay , The advantage of index coverage is fast efficiency . Common index coverage is through the establishment of joint indexes .

Take a chestnut ：
Query criteria select id,name,age from xxx where id=2, At this time, the cluster index that goes directly , The speed is very fast

Query criteria select id,name from xxx where name='zhangsan', because name、id Both exist in the secondary index , There is no need to return the form （ All the required data can be obtained directly ）

Query criteria select id,name,age from xxx name='zhangsan', At this time due to age Not in secondary index , You must return the table to get age Column , To achieve index coverage , You can create a federated index , take age Add to the union index .

matters needing attention

Cluster index , Inserting in the order of the primary keys is the fastest way , Otherwise, there will be page splitting , Seriously affect performance . therefore , about InnoDB surface , We usually define a self increasing ID List as primary key
It's expensive to update the primary key , Because it will cause the updated row to move . therefore , about InnoDB surface , We generally define the primary key as non updatable .