Talk about the optimization of MySQL optimization index

An index is a data structure that the storage engine uses to quickly find records. Especially when the data volume of the table is getting larger and higher, the correct index improves the query performance especially. But in daily work, indexes are often overlooked and even misunderstood. This article will give you a brief introduction of the MySQL Index optimization principles and considerations.

I. Types of indexes

1) B-tree Index

The B-tree index is the most indexed type, and most storage engines support the B-tree index.

The b-tree itself is a data structure that is a balanced search tree designed for disk or other directly accessible assistive devices. The B-tree index in MySQL is usually implemented as a variant of B-tree B+tree. The structure is as follows:

B+tree is characterized by the fact that the data is stored in the leaf node, and the data of each leaf node is stored in the same order (ascending or descending), and the adjacent leaf nodes are connected by pointers at a point, a structure that is well suited for range lookups.

B-tree indexes can significantly speed access to data because the storage engine no longer needs to perform a full table scan to get the data it needs, but instead searches from the root node of the index to the next level, which greatly reduces the range of data scanned by the storage engine, thus improving the query speed significantly.

2) Hash Index

A hash index, as its name implies, is an index implemented by a hash table. The feature is that only all columns that exactly match the index are valid. For each row of data, the storage engine computes a hash code for all indexed columns, the hash index stores the hashes in the index, and a pointer to each data row in the hash table.

In MySQL, only the memory engine is currently supported by the hash index, and because the hash index does not support range lookups, sorting is not supported, and partial index column matching lookups are not supported, so hash references are less.

The usage of the B-tree index is highlighted later in this article.

For convenience, we will assume that there is a user table with the following fields:

Id:bigint type, PRIMARY key

Name:varchar type

Age:int type

Interest:varchar type

and a federated index Index_1 is established on name, age, and interest, and the index order is (name,age,interest), and this index order is important and will be mentioned later.

Second, the use of B-tree index

1) Full Value matching

Full-value matching refers to matching all columns in the index, such as querying the user table above where name= ' AAA ' and age=20 and interest= ' basketball ' are all columns that can be used to index.

2) match the leftmost prefix

The match leftmost prefix refers to only a few columns to the left of the multi-column index. If the user table above is queried where name = ' AAA ' is available to the index, and only the first column to the index is used.

3) match column prefix

The match column prefix refers to only the beginning of a column, such as a query to the user table above where the name like ' aaa% ' is available to the index, note that the first part of the matching column, if the query is where the name '%AAA ' cannot be used to the index.

4) Match range value

As to the above user table query where name > ' AAA ' and name < ' BBB ' are also available to the index.

5) match a column exactly and range to another column

such as the user table above query where name= ' AAA ' and age >10, can be used to the index, and used to the first 2 columns of the index.

III. Limitations of B-tree indexes

1) You cannot use an index if you do not start the search by the leftmost column of the index.

If you query the above user table, where age=20 is not available to the index, because age is not the leftmost data column in the indexed column.

2) columns in the index cannot be skipped.

If you query the above user table where Name= ' aaa ' and interest= ' soccer ', you can only use the first column of the index, because the where condition does not contain the age column.

Summarize

This article briefly introduced the MySQL index principle and the basic use method, about MySQL index also has many knowledge points to say, because of the space limit, will be elaborated in the subsequent article. If you think this is helpful, you can scan the QR code below and follow my public number.

Talk about the optimization of MySQL optimization index