MySQL uses the index scenario and the SQL type that actually uses the index, mysqlsql

MySQL uses the index scenario and the SQL type that actually uses the index, mysqlsql

1. Why are indexes used?

Without an index, MySQL scans the entire table to find records that meet the SQL conditions. The time overhead is positively related to the data volume in the table. Creating an index for some fields in a relational data table can greatly improve the query speed (of course, whether different fields are selective will lead to different index creation for these fields to increase the query speed, in addition, the more indexes, the better, because the index information needs to be updated during writing or deletion ).

For MySQL's Innodb Storage engine, most types of indexes are stored using B-Tree Data Structure Variant B + Tree (MEMORY tables also support hash indexes ). B-Tree is a common data structure in databases or file systems. It is an N-cross balancing Tree. This Tree structure ensures that keys stored at the same layer are ordered. For a node, all the keys saved in the left subtree are smaller than the keys saved by the node, and all the keys saved in the right subtree are greater than the keys saved by the node. In addition, in terms of engineering implementation, we have made a lot of optimizations Based on the operating system's locality principle. In short, the various features or optimization techniques of B-tree can ensure that: 1) When querying disk records, the minimum number of disk reads; 2) Any insert or delete operation has little impact on the tree structure; 3) the rebalance operation of the tree itself is very efficient.

2. Use of indexes in MySQL

MySQL uses indexes in the following scenarios:
1) Quickly search for records that meet the where Condition
2) quickly determine candidate sets. If the where condition uses multiple index fields, MySQL will give priority to the index that minimizes the size of the candidate record set to eliminate non-conforming records as soon as possible.
3) if the table contains a joint index composed of several fields, the leftmost prefix matching field of the joint index will be automatically used as an index to accelerate the search.
For example, if you create a joint index composed of three fields (c1, c2, c3) for a table, (c1), (c1, c2), (c1, c2, c3) and (c2, c3) will not be used as an index, while (c1, c3) actually only uses the c1 index.
4) indexes will be used for join operations on multiple tables (if the fields involved in join are indexed in these tables)
5) If an index has been created for a field, MySQL will use the index to evaluate the min () or max () of the field.
6) when you perform sort or group operations on the fields with indexes, MySQL uses indexes.
3. Which SQL statements will actually use indexes?
According to the MySQL official documentation "Comparison of B-Tree and Hash Indexes", the following types of SQL statements may actually use Indexes:

1) B-Tree can be used to compare column expressions in SQL, such as =,>, >=, <, <= and between operations.

2) If the like statement is a constant string that does not start with a wildcard, MySQL will also use an index
For example, SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick % 'or SELECT * FROM tbl_name WHERE key_col LIKE 'pat % _ ck %' can use indexes, SELECT * FROM tbl_name WHERE key_col LIKE '% Patrick %' (starting with a wildcard) and SELECT * FROM tbl_name WHERE key_col LIKE other_col (the like condition is not a constant string) cannot use indexes.
For SQL statements LIKE '% string %', if the string length after the wildcard character is greater than 3, MySQL will use the Turbo Boyer-Moore algorithm for search.

3) If you have created an index for a column named col_name, the index will be used for SQL statements like "col_name is null ".

4) for the Union index, the leftmost prefix matching field in the SQL condition will use the index. For examples, see section 2nd 3rd about the Union index.

5) if there are more than one where condition in the SQL statement, MySQL performs Index Merge optimization to narrow the range of candidate sets.