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

Xiaobian to share with you how to optimize Mysql index, I believe most people still do not know how, so share this article for everyone's reference, I hope you have a lot of gains after reading this article, let's go to understand it together!

Programming Meditations

B+Tree is characterized by data stored in leaf nodes, and each leaf node data is stored in the same order (ascending or descending order), and adjacent leaf nodes are connected by pointers at a point, this structure is very suitable for range lookup.

B-Tree index can significantly speed up the speed of accessing data, because the storage engine no longer needs to scan the whole table to obtain the required data, but searches down from the root node of the index, which greatly reduces the scope of the storage engine scanning data, so the query speed is very obvious.

2) Hash index

Hash index, as the name implies, is an index implemented by hash table. It is characterized by the fact 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 index columns, and the Hash index stores the hash code in the index while keeping a pointer to each row of data in the hash table.

In Mysql, only Memory engine explicitly supports Hash index, and because Hash index does not support range lookup, sorting, and partial index column matching lookup, Hash index is used less.

The following sections highlight the use of B-Tree indexes.

For the sake 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 and 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

All-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' is available to all columns in the index.

2) Match leftmost prefix

Matching the leftmost prefix means using only the columns to the left of a multi-column index. For example, query the user table above where name = 'aaa' is available to the index, and only use the first column of the index.

3) Match column prefix

Matching column prefix refers to matching only the beginning of a column. For example, querying where name like 'aaa%' for the above user table 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

For example, query the above user table where name > 'aaa' and name

10, can be used up to the index and up to the first 2 columns of the index.

3. Limitations of B-Tree Index

1) An index cannot be used if it does not start with the leftmost column of the index.

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

2) Columns in the index cannot be skipped.

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

The above is "Mysql index how to optimize" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.