What kinds of indexes does MySQL support

From the data structure angle

1. B + Tree index (O (log (n))): for the B + Tree index, you can refer to the data structure behind the MySQL index and the algorithm principle

2. Hash index:
A can only satisfy "=", "in" and "<=>" queries, and cannot use range queries
b Its retrieval efficiency is very high, index retrieval can be located at once, unlike the B-tree index need from the root node to the side point, and finally access to the page node so many IO access, so the Hash index query efficiency is much higher than the B-tree index
c Only memory storage engine display supports hash index

3. Fulltext INDEX (now supported by MyISAM and InnoDB engines)

4. R-tree index (used to create spatial indexes on GIS data types)

From the physical storage perspective

1. Clustered index (clustered)

2. Nonclustered indexes (non-clustered index)

From a logical point of view

1. Primary KEY index: Primary key index is a special unique index, no null value allowed

2, ordinary index or single-column index

3. Multi-column index (composite index): A composite index is an index created on multiple fields, and the index is used only if the first field when the index is created is used in the query criteria. To follow the leftmost prefix collection when using a composite index

4. Unique index or non-unique index

5. Spatial index: Spatial index is the index of the field of spatial data type, there are 4 kinds of spatial data types in MySQL, namely geometry, point, LINESTRING, POLYGON. MySQL uses the spatial keyword to extend the syntax to create spatial indexes that can be used to create regular index types. A column that creates a spatial index must be declared as not NULL, and the spatial index can only be created in a table where the storage engine is MyISAM

CREATE TABLE table_name[col_name data type][unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1. Unique|fulltext|spatial is an optional parameter, which represents a unique index, a full-text index and a spatial index, respectively;

2, index and key are synonyms, both function the same, used to specify the creation index

3. Col_name is the field column that needs to be indexed, and the column must be selected from multiple columns of that definition in the datasheet;

4, index_name the name of the specified index, optional parameters, if not specified, MySQL default col_name is the index value;

5, length is an optional parameter, indicating the length of the index, only the string type of the field to specify the index length;

6, ASC or desc Specify an ascending or descending index value store

What kinds of indexes does MySQL support