How to do nest loop in MySQL without considering hash join

In this issue, the editor will bring you about how to do nest loop in MySQL without considering hash join. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Only consider nest loop, not hash join

The conditions are as follows:

A where a.id=b.id and a.id=c.id where a. Nameplate gaopengli. A nameplate gaopengche.

Suppose an is a driven table, join b first, then c, and table id have indexes.

The process is as follows:

Loop:1, access-driven table a, filter condition a.nameplate entries gaopengths, this process includes indexing to get the first piece of data recorded as row_a1, here may match multiple rows of data, need to loop loop: 2, access driven table b, join condition a.id=b.id is equivalent to conditional b.id = row_a1.id this process b.id has indexing Get the connected data a.\ * + b.\ *, here may match multiple rows of data, need to loop 3, each row of data, apply the filter condition b. Nameplate data, assume that the first piece of data is recorded as row_b1 loop: 4, access the driven table c The join condition a.id=c.id is equivalent to the condition c.id = row_b1.id. The process c.id has the index to get the connected data a.\ * + b.\ * + c.\ *, here may match multiple rows of data, need to loop 5, each row of data, apply the filter condition c.nameplate matching gaopengths, assuming that the first piece of data is recorded as row_c1

Here row_c1 is the final first piece of data. In fact, it means three layers of nested loops.

Debug is used for verification later.

The full table scan of the driven table uses A Block Nested-Loop (BNL) instead of A simple nested-loop join (NLJ). Using join buffer, step 1 reads multiple rows of data at once assuming that row_an rows are read, and the second step is equivalent to b.id in (row_a1.id,row_a2.id … Row_an.id) this will only scan the whole table once. Otherwise, each full table scan is slower, and use

The above is how to carry out nest loop in the MySQL shared by the editor without considering hash join. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.