MySQL's index

MySQL Index

Index: is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. It can speed up data read operations, but it slows down data writes , and should be built on fields that are used as query criteria.

　　Type of index in MySQL:

1.b+tree Index

Balance tree is a data structure that, due to its high overall efficiency, significantly reduces the intermediate processes that are experienced in locating records, thus speeding up the access speed.

Types that can be used for queries:

(1) Full value matching

(2) match the leftmost prefix

(3) Match column prefix

(4) Match range value

But there are some limitations:

(1) You must start from the left-most query

(2) cannot skip an index

(3) If a column in a query is a range query, the columns on its right can no longer use the Index optimization query, (the exact range is left, the scope query is placed to the right, and the index query is no longer available on the right side.) )

2.Hash Index

Based on a hash table, suitable for exact matching of all columns in the index, but only the memory storage engine supports explicit hash indexes, fuzzy matching is not supported, not for sequential queries;

3. Spatial index (R-TREE):

Only the MyISAM storage engine supports spatial indexes in MySQL;

4. Full-Text Indexing (fulltext):

Find keywords in text, similar to search engines;

　　Management of indexes in MySQL:

/* Create an index */ CREATE INDEX index_name on Tbl_name (col1,col2,...); /* Delete Index */ DROP [ONLINE| OFFLINE] INDEX index_name on Tbl_name; /* View Index */ SHOW {INDEX| indexes| KEYS} {form| in} tbl_name [{from| in} db_name] [WHERE expression];

　　How do I analyze index validity?

　　　　EXPLAIN SELECT Clause ...

The output is:

The explanations of the indicators are as follows:

ID: The number of the SELECT statement in the current query

Select_type: Query type

/* type of correlation */ Simple   // Simple Query Subquery  // simple subquery DERIVED  // sub-query for from;UNION  // The first SELECT statement after Union in a union statement  total temporary virtual table after Union RESULT/union

Table: The tables to which the query is associated

Type: association type, that is, how the MySQL query table

/* type of correlation */ all   // full table Scan index  // full table scan according to the order of index, if "Using index" appears in extra, it means overwrite index is used instead of full table scan range   // Limited range based on index implementation Scan ref  // returns all rows in the table that match a single value according to the index eq_ref  // only one row is returned, but additional const is required for a reference value ,system  // returns a single row directly; This type is the best query;

Possible_keys: Indexes that may be used in a query

Key: The index used in the query

Key_len: Number of bytes using the index

Ref: All columns or a constant value when the query is completed using the index represented by key

Row:mysql to find the number of rows read by the target

Extra: Additional Information

/* Additional Information */ Using Index   // MySQL will use the overlay index to avoid accessing the table; The using where  //MySQL server will be filtered once after the storage engine is retrieved;using temporary  //  MySQL uses temporary tables when sorting results, using Filesort  // to sort results with an external index (poor performance);

　　　　

MySQL's index