Mysql index optimization method

Programming Meditations                  

code-thinker

Focus but It is not limited to technology sharing in the Java Web field. I hope this public account can become a good mentor and helpful friend on your programming journey.

                                                                                                               

This article mainly shares with you Mysql index optimization methods, hoping to help everyone. An index is a data structure used by storage engines to quickly find records. Especially when the amount of data in the table becomes larger and larger, correct indexes will significantly improve query performance. But in daily work, indexes are often ignored or even misunderstood. This article will briefly introduce the principles and precautions of Mysql index optimization.

1. Types of indexes

1) B-Tree index

B-Tree index is the most commonly used index type, and most storage engines support B -Tree index.

B-Tree itself is a data structure, which is a balanced search tree designed for disks or other direct access auxiliary devices. The B-Tree index in Mysql is usually implemented by B+Tree, a variant of B-Tree. Its structure is as follows:

The characteristic of B+Tree is that the data is stored in leaf nodes, and the data of each leaf node is in the same order (ascending or descending order) It is stored in an array, and adjacent leaf nodes are connected at one point with pointers. This structure is very suitable for range searches.

B-Tree index can significantly speed up access to data, because the storage engine no longer needs to perform a full table scan to obtain the required data, but searches layer by layer from the root node of the index, which greatly The scope of data scanned by the storage engine is reduced, so the query speed is significantly improved.

2) Hash index

Hash index, as the name suggests, is an index implemented through a hash table. Its characteristic is that only all columns that exactly match the index are valid. For each row of data, the storage engine calculates a hash code for all index columns. The Hash index stores the hash code in the index and saves a pointer to each data row in the hash table.

In Mysql, currently only the Memory engine explicitly supports Hash indexes, and because Hash indexes do not support range searches, sorting, or partial index column matching searches, Hash indexes are rarely used. .

The following will focus on the usage of B-Tree index.

For the convenience of the following description, 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 joint index index_1 is established on name, age, interest, the index order is (name ,age,interest), this index order is very important and will be mentioned later.

2. Usage of B-Tree index

1) Full value matching

Full value matching refers to matching with all columns in the index, such as The above user table query where name='aaa' and age=20 and interest='basketball' can use all columns of the index.

2) Match the leftmost prefix

Matching the leftmost prefix means only using the left few columns of the multi-column index. For example, for the above user table query where name = 'aaa', the index can be used, and only the first column of the index is used.

3) Matching column prefix

Matching column prefix means matching only the beginning of a certain column. For example, if you query where name like 'aaa%' in the above user table, you can use the index. Note that it matches the beginning of the column. If the query is where name like '%aaa', the index cannot be used.

4) Matching range value

If you query the above user table where name > 'aaa' and name < 'bbb', the index can also be used.

5) Exactly match a certain column and range match another column

If you query the above user table where name='aaa' and age >10, you can use the index and use the first 2 columns of the index.

3. Limitations of B-Tree index

1) If the search is not started from the leftmost column of the index, the index cannot be used.

If you query the above user table where age=20, the index cannot be used because age is not the leftmost data column in the index column.

2) Columns in the index cannot be skipped.

If you query where name='aaa' and interest='football' for the above user table, you can only use the first column of the index because the age is not included in the where condition. a row.

Related recommendations:

How to use mysql index optimization

MySQL Advanced Thirteen— —Optimizing SQL by Index

MySQL Order By Index Optimization Method

The above is the detailed content of Mysql index optimization method. For more information, please follow other related articles on the PHP Chinese website!