Mysql Custom Hash index brings a huge performance boost----[truth article]

Knock it off.

As the saying goes no Zuo no die Why do you try, this time I could not help Zuo, Chi Chi to solve the process of the blog, announced to the world, elder brother and solve a problem.

The development of the plot often looks like the protagonist completely mastered the situation, will suddenly jump out of a very cow villain, and then make the protagonist very miserable, engage in the process of the protagonist began the small universe, and then the attack. This time is no exception. The people who kicked the ball should show up.

After a crazy kan, found that my original analysis of the good, completely withstand the scrutiny. Several questions

1) before the hash index, why the large table of rows so large, equivalent to a full table scan

2) Since SN is the only index, why is it not accurate to locate the associated query?

3) After doing the hash index, the correlation query only changed the different judgment field, can do Rows=1, this is why?

Here is an explanation of how the join operation and index positioning data are done by index

In relational databases, there are several common ways to deal with joins: NESTED Loop,hash join,sort MERGE Join, what is the difference between the various joins on the wiki is described in detail. MySQL implements the nested loop, commonly known as nested loops.

In conjunction with the previous query, the associated fields of join are Fdevice and FSN, and the process of querying is this

For Fdevice in A:

For FSN in B:

IF A.FDEVICE=B.FSN:

Store result into record buffer

Send result

This is how nested loops are implemented.

In the IF A.FDEVICE=B.FSN: This step, when comparing the B-table data, the FSH on B has an index, first through the index lookup, get the primary key ID, and then based on the primary key ID to get the exact value of the whole row record.

In this case, the FSN of B is the unique value, A's fdevice in the B table, either does not exist, or only one record matches it, there will be no third case.

Before the custom hash index of SQL, it should be this execution process, hash once, and then in the hash field to do an index, in fact, just a change of the implementation path, and did not explain why the previous method of the full table scan, lurking in the appearance of the truth is not so.

Back to the origin of the event, why is the first SQL execution plan, rows equal to the full table, and Rows=1 after the custom hash index? Through the analysis of the principle of the index, it is used as long as the index, rows must be 1, if not used index, then key This column is bound to be null, it is this contradiction, from the beginning led me astray, mistaken for the index itself problem, and then take for granted a hash index, And then stumbled over the problem to solve.

Accidental discovery

The first thing to do is to exclude all the impossible facts, and the rest, no matter how bizarre, how unbelievable, is inevitably the indisputable fact that Sherlock Holmes.

In this case, the impossible fact is that the execution of the nested loop does not change, and the process of finding the data in the index does not change. So what's left?

1) See the rows=490w is Illusion 2) see KEY=SN is False

From the execution efficiency point of view, rows=490w is true, or not so slow, then KEY=SN is false is possible, it seems to use the index, in fact, the index does not work at all.

Once again, focus on the table structure of two related queries and find a very obscure anomaly: ' sn ' varchar (CHARACTER) set UTF8. The character set of this field is utf8, and the character set of the field associated with it is UTF8MB4, and two different field comparisons are not causing the index to fail?

Soon the results of the experiment came out, and this time, is the truth behind the incident.

When the character set of the two associated fields is the same, the index runs as expected.

When I have only one hammer, it's natural to think of any problem as a nail, and preconceived thinking makes some simple and important details overlooked, and these clues can lead us to find out the truth of the matter. After a round of detours suddenly look back, in fact, the search for the perfect world is in front of us, but we are blinded by prejudice and ignorance.