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

This note mainly records the contents of the MySQL index in order to provide a reference for the actual work of SQL optimization.

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, coupled with the operation of the local principle of the system to do a lot of optimization, in short, b-tree of various characteristics or optimization techniques can guarantee: 1) query disk records, read the fewest number of times; 2) any insert and delete The effect of operation on tree structure is very small; 3) The rebalance operation of the tree itself is very efficient.

The following contents of this article mainly introduce the common scenes about MySQL index, not the B-TREE/B+TREE data structure to do in-depth introduction of the principle, interested, can refer to Wikipedia on the relevant entries (B-tree or B + tree), or refer to the " The data structure and algorithmic principle behind MySQL index "or" Understanding B+tree Indexes and how they Impact performance "These two articles, this note no longer repeat.

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) actually only uses the C1 index .
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 the Select * from Tbl_name where key_col like '%patrick% ' (starting with wildcards) and select * from Tbl_name WHERE key_col like Other_col (like condition is not a constant string) cannot Use 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, and the specific optimization strategy can refer to the MySQL document index Merge optimization

Resources
1. Data structure and algorithm principle behind MySQL index
2. Understanding B+tree Indexes and how they Impact performance
3. How MySQL Uses Indexes
4. Comparison of B-tree and Hash Indexes
5.Index Merge Optimization  

==================== EOF =================

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