Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to use the SQL type of index in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. Why use an index?

Without 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. Indexing some fields in a relational data table can greatly improve the query speed (of course, whether different fields selective will cause the index built by these fields to improve the query speed differently, and the more indexes, 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 B+Tree, a variant of the B-Tree data structure (tables of type MEMORY also support indexes of type hash). B-Tree is a commonly used data structure in database or file system. It is an N-ary balanced tree. This tree structure ensures that the key saved by the nodes in the same layer is orderly. For a node, all the key stored in the left subtree is smaller than the key saved by the node, and all the key stored in the right subtree is larger than the key saved by the node. In addition, in the engineering implementation, also combined with the local principle of the operating system to do a lot of optimization, in short, the various characteristics or optimization skills of b-tree can ensure: 1) when querying disk records, the number of disk reads is the least; 2) any insert and delete operations have little impact on the tree structure; 3) the rebalance operation of the tree itself is very efficient.

2. Scenarios where MySQL uses indexes

MySQL uses indexes in the following operation scenarios:

1) quickly find records that meet the where criteria

2) quickly determine the candidate set. If the where condition uses multiple index fields, MySQL will give priority to the index that minimizes the size of the candidate recordset in order to eliminate the records that do not meet the criteria as soon as possible.

3) if there is a federated index composed of several fields in the table, when looking for a record, the leftmost prefix matching field of the federated index will also be automatically used as an index to speed up the search.

For example, if a federated index of three fields (C1, c2, c3) is created for a table, then (C1), (C1, c2), and (C1, c2, c3) will all be used as indexes, (c2, c3) will not be used as indexes, and (C1, c3) will only use C1 indexes.

4) Indexes are used when multiple tables perform join operations (if the fields participating in join are indexed in these tables)

5) if a field has been indexed, MySQL will use the index when asking for the min () or max () of the field.

6) when performing sort or group operations on indexed fields, MySQL will use the index

3. Which SQL statements actually take advantage of indexes

As you can see from the MySQL official website document "Comparison of B-Tree and Hash Indexes", the following types of SQL may actually use indexes:

1) B-Tree can be used for expressions in sql that compare columns, such as =, >, > =

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.