What does mysql leftmost principle mean?

mysql's leftmost principle means that during the query process, the leftmost column of the index should be involved in filtering as much as possible. It is one of the core ideas of database index optimization, which can maximize the efficiency of index use and speed up query speed. The leftmost principle helps avoid inefficient operations such as full table scans, enables the database to process large data sets faster, helps to better design database table structures and indexes, and improves system performance.

The operating system of this tutorial: Windows 10 system, mysql version 8.0, Dell G3 computer.

MySQL The leftmost principle is one of the core ideas of database index optimization. It refers to making the leftmost column of the index (also called the "prefix") participate in filtering as much as possible during the query process. This can maximize the efficiency of using the index and speed up the query.

The following is an example:

SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2'

In this query, if there is a joint index (column1, column2), column1 will be used first for filtering according to the leftmost principle. Only when the value of column1 matches successfully, the value of column2 will be referenced for further filtering.

However, if the query only has column2 but not column1, the joint index will not work.

The leftmost principle helps avoid inefficient operations such as full table scans, allowing the database to process large data sets faster. At the same time, it also helps to better design the database table structure and indexes and improve system performance.

The principle of the leftmost matching principle

The index in MySQL can reference multiple columns in a certain order. This kind of index is called a joint index. The leftmost matching principle is For joint indexes.

We all know that the bottom layer of the index is a B-tree, so of course the joint index is still a B-tree, but the number of key values ​​​​of the joint index is not one, but multiple . Building a B-tree can only be built based on one value, so the database builds the B-tree based on the leftmost field of the joint index.

Example: If you create a joint index of (a, b), then its index tree is like this. You can see that the value of a is in order, 1, 1, 2, 2, 3, 3 , and the value of b is 1, 2, 1, 4, 1, 2 in no order. Therefore, there is no way to use the index for query conditions such as b = 2, because the joint index is first sorted by a, and b is unordered.

At the same time, we can also find that when the a values ​​are equal, the b values ​​are arranged in order, but this order is relative. Therefore, the leftmost matching principle will stop when encountering a range query, and the remaining fields cannot use the index. For example, a = 1 and b = 2, both a and b fields can use indexes, because b is relatively ordered when the a value is determined, and a>1and b=2, the a field can match the index, but the b value No, because the value of a is a range, and b is unordered in this range.

Advantages: The use of the leftmost prefix principle can also significantly improve query efficiency and is a common MySQL performance optimization method.

The above is the detailed content of What does mysql leftmost principle mean?. For more information, please follow other related articles on the PHP Chinese website!