The method of adding multiple btree Indexes to mysql

Xiaobian to share with you mysql add multiple btree index method, I hope you read this article after the harvest, let us discuss it together!

At present, most database systems and file systems adopt B-Tree or its variant B+Tree as index structure.

1. B+ tree is a balanced multi-fork tree, the height value from root node to each leaf node does not exceed 1, and there are pointers between nodes at the same level.

2. Regular search on B+ tree, the search efficiency from root node to leaf node is basically equal, there will be no large fluctuation, and when scanning based on index, it can also use bidirectional pointer to move left and right quickly, which is very efficient.

In mysql, indexes can effectively improve query efficiency, but in actual projects, even if indexes are added to where conditions, indexes may not be used.

For example, a chestnut: where id=3 and price>100;//query products with id above 3,100 yuan (id and price are added indexes respectively)

Mistake: only one of id or price can be used because it is an independent index, and only one can be used at the same time.

Union index: After creating indexes on multiple columns at the same time, the left prefix principle needs to be met before indexes are used.

Consider index (a,b,c). (Note related to order)

statement| Whether to use index

where a=3 Yes, only column a is used

where a=3 and =5 Yes, ab column is used

where a=3 and b=4 and c=5 Yes, abc is used

where b=3 or c=4 No because skip a

where a=3 and c =4 a uses index, c does not

where a=3 and b like 'hello%' a used, b used

After reading this article, I believe you have a certain understanding of mysql to add multiple btree index methods, want to know more related knowledge, welcome to pay attention to the industry information channel, thank you for reading!