MySQL uses indexed scenarios and the type of SQL that really leverages the index

1. Why use an index

in the absence of an index, MySQL scans the entire table to find records that meet the SQL criteria, and its time overhead is positively correlated with the amount of data in the table. Jianjian indexes on certain words in a relational data table can greatly improve the query speed (of course, whether different fields are selective will cause the indexes of these fields to raise the query speed differently, and the index is not the more the better, because the index information needs to be updated when writing or deleting).

For MySQL's InnoDB storage engine, most types of index are stored in variant B+tree of the B-tree data structure (memory type tables also support hash type indexes). B-tree is a database or file system commonly used in a data structure, it is a kind of n-fork balance tree, this tree structure to ensure that the same layer of nodes to save the order of key, for a node, the left subtree of all keys are smaller than the key saved by the node, All keys saved by its right subtree are larger than the key saved by that node. In addition, in the implementation of the project, but also with the operating system of the local principle to do a lot of optimization, in short, b-tree of various characteristics or optimization techniques can guarantee: 1) query disk records, read the minimum number of times; 2) any insert and delete operations have little effect on the tree structure; 3) The rebalance operation of the tree itself is efficient.

2. mysql uses indexed scenarios

MySQL uses the index in the following scenario:
1) Quickly find records that meet the Where condition
2) quickly identify candidate sets. If the Where condition uses more than one indexed field, MySQL takes precedence over the index that makes the candidate recordset the smallest, so that the non-qualifying record can be eliminated as quickly as possible.
3) If there are several fields in the table that make up a federated index, the leftmost prefix match field of the Federated index will also be automatically indexed to speed up the lookup when the record is found.
For example, if you create a union index that consists of 3 fields (c1, C2, C3) for a table, then (C1), (c1, C2), (c1, C2, C3) are indexed, (C2, C3) are not indexed, and (C1, C3) are actually only used to C1 indexes.
4) Indexes are used when multiple tables do join operations (if the fields participating in the join are indexed in these tables)
5) If a field is indexed, MySQL uses the index when the field's min () or MAX () is evaluated
6) When you do a sort or group operation on an indexed field, MySQL uses the index
3. Which SQL statements will actually take advantage of the index
from the MySQL official website document "Comparison of B-tree and Hash Indexes", the following types of SQL may actually be used for indexing:

1) B-tree can be used in SQL to compare columns to the expression, such as =,,, >=, <, <= and between operations

2) If the condition of the like statement is a constant string that does not begin with a wildcard, MySQL will also use the index
For example, select * from Tbl_name where key_col like ' patrick% ' or select * from Tbl_name where key_col as ' pat%_ck% ' can take advantage of the index, while SE Lect * from Tbl_name where key_col like '%patrick% ' (starting with wildcards) and select * from Tbl_name WHERE key_col like other_col (like condition not constant String) cannot take advantage of the index.
for SQL statements that look like '%string% ', if the string after the wildcard is longer than 3, MySQL uses the turbo Boyer-moore algorithm algorithm to find it.

3) If the column named Col_name has been indexed, SQL that is shaped as "col_name is null" uses the index

4) for a federated index, the leftmost prefix match field in the SQL condition is used for the index, and the example refers to the description of the Federated index in section 2nd, article 3rd

5) If the Where condition in the SQL statement is not more than 1 conditions, MySQL will perform an index merge optimization to narrow the candidate set range

MySQL uses indexed scenarios and the type of SQL that really leverages the index