preface

Today we will talk about why MySQL indexes fail. Many articles and tutorials will only tell you when indexes fail.

For example: not following the best left prefix rule, the right side of a range query is invalidated, like queries don’t use indexes, and so on

But no one told you, index failure principle is what, the elder brother will tell you today, so that you know, but also know why.

Single-valued index B+ tree graph

Single-valued index In a B+ tree structure, there is only one key-value pair per node

Joint index

Open a graph with a combined index of the database’s A and B fields.

InnoDB Storage engine version 2

In essence, a federated index is also a B+ tree. Unlike a single-valued index, a federated index has more than one key-value pair, rather than one.

A, B rank analysis

A order: 1,1,2,2,3,3

B order: 1,2,1,4,1,2

You can see that a is ordered and B is unordered (because b + trees can only pick one field to build an ordered tree).

If you’re not careful, you’ll see that b is ordered if A is equal.

If you want to sort two fields, do you want to sort by the first field, and if the first field is equal, use the second field. This sort is also applied to B+ trees.

Analyze the principle of optimal left prefix

Let’s start with an example that follows the optimal left prefix rule

select * from testTable where a=1 and b=2
Copy the code

Analysis is as follows:

Firstly, the a field is ordered in the B+ tree, so we can locate the position of a=1 by binary search method.

Secondly, in the case that A is determined, B is relatively orderly. Because it is orderly, the position of b=2 can also be found by binary search method.

Let’s look at examples that don’t follow the optimal left prefix

select * from testTable where b=2
Copy the code

Analysis is as follows:

Let’s recall that b has an order: if A is certain.

Now that your A’s are flying, b’s must be in indeterminate order, and you can’t use binary lookup to locate the B field in an unordered B+ tree.

So at this point, you don’t need an index. Does that make sense?

Scope query right failure principle

For example,

select * from testTable where a>1 and b=2
Copy the code

Analysis is as follows:

First, the a field is ordered in the B+ tree, so we can use binary search up to 1, and then extract all data greater than 1, A can use the index.

B is ordered if A is a definite value, so now A is greater than 1, maybe there are 10 as greater than 1, maybe there are a hundred as.

If a is greater than 1, the B field in the B+ tree is unordered, so B cannot be queried by binary search in the unordered B+ tree. B does not use an index.

Mechanism of like index failure

where name like "a%"

where name like "%a%"

where name like "%a"
Copy the code

Let’s see what % is used for

  • % on the rightTo query the data starting with “A”, for example, ABC
  • Two % %Is the query data containing “A”, for example, CAB, CBA, and ABC
  • % on the left, represents to query data ending in “A”, such as CBA

Why is % on the right sometimes used for indexing

  • % to the right is called:The prefix
  • % % is called:infix
  • % on the left is called:The suffix

Yes, this is still the concept of the best left prefix rule

As you can see, the B+ tree above is made up of strings.

If the first letter is the same, the second letter is the same. And so on

To analyze

1. Put the % sign on the right (prefix)

Since the index order of the B+ tree is sorted by the size of the first letter, prefix matches match the first letter. So you can do an ordered search on the B+ tree, looking for data that starts with the right letter. So sometimes you can use indexes.

2. Put % on the left

It matches the data at the end of the string. We said above that the characters at the end of the string are not in order, so it cannot be queried in index order, so it does not use the index.

Three, two %%

Only the first letter is sorted by index. The letters in other positions are relatively unordered, so the search for letters in any position is not used by index.

conclusion

Here are some classic index failure cases for you to analyze, I hope to trigger you to think, can through these cases, understand the principle of index failure in other cases.

Later we will talk about how to query data through indexes and how InnoDB and MyISAM implement the underlying indexes differently.

It is better to teach a man to fish than to teach a man to fish. At this moment, my brother felt special shuai.

Follow wechat public account: IT elder brother

Java actual combat project video tutorial: you can get 200G, 27 sets of actual combat project video tutorial

Reply: Java Learning Route, you can get the latest and most complete learning roadmap in 2020

Re: Java ebooks, get 13 must-read books for top programmers

Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes