How to add multiple btree indexes in mysql

Currently, most database systems and file systems use B-Tree or its variant B Tree as the index structure.

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

2 , In conventional retrieval on the B-tree, the search efficiency from the root node to the leaf node is basically the same, and there will be no significant fluctuations. Moreover, during index-based sequential scanning, the bidirectional pointer can also be used to quickly move left and right, which is very efficient.

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

For example: where id=3 and price>100;//Query products with IDs above 3,100 yuan (id, price are to add indexes respectively)

Misunderstanding: You can only use Either id or price, because it is an independent index, only one can be used at the same time.

Joint index: After creating an index on multiple columns at the same time, the left prefix principle needs to be met before the index is used.

Take index (a, b, c) as an example. (Note that it depends on the order)

Statement | Whether to use index

where a=3 Yes, only column a is used

where a=3 and =5 Yes, column ab

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

where b=3 or c=4 is used No because it is skipped a

where a=3 and c =4 a uses the index, c cannot

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

The above is the detailed content of How to add multiple btree indexes in mysql. For more information, please follow other related articles on the PHP Chinese website!