Index Nested-loop Join (last) because the access is a secondary index, if the query needs to access the columns on the clustered index, then it is necessary to take the data back to the table, it seems that each record is just one more table operation, but this is the INLJ algorithm the biggest disadvantage. First, index lookup for the secondary index is a comparison of random I/O access operations. Second, the return table is a random I/O operation based on index lookup. So, the biggest drawback of INLJ is that it may require a lot of discrete operations, which is the biggest bottleneck before SSDs appear. And even if the SSD's appearance greatly increased the random access performance, but compared sequential I/O, it is still a lot slower, still not an order of magnitude. For example, the following SQL statement: SELECT COUNT (*) from part, lineitemwhere L_partkey = P_partkey and P_retailprice > 2050AND l_disc Ount > 0.04; Where P_partkey is the primary key of the table part, L_partkey is a secondary index of the table LineItem, which is a form of appearance (driver table) because of the small data in the table. However, it is necessary to determine the condition L_discount > 0.04 after the join of the inner table, this is on the clustered index, so the table needs to be read back. The execution plan for the above SQL is obtained according to explain as shown in:
The Block Nested-loop join algorithm shows that MySQL will attempt to use the index Nested-loop join algorithm in the case of an index, and in some cases the column that might Join is not indexed. Then the choice of MySQL will not be the first to introduce the simple nested-loop join algorithm, because the algorithm is too rough, can't bear to look directly. Complex SQL with a large amount of data may not run out of results for years, if you don't believe it, that's too young too. Or inside can give you some SQL to run to see. The disadvantage of the simple nested-loop join algorithm is that it has too many scans on the inner table, causing the scan to be too large. The improvement of the Block Nested-loop join algorithm compared to simple nested-loop join is that it can reduce the number of scans in the inner table, and even the same as the hash join algorithm, only need to scan the inner table once. Then inside you to take a look at the pseudo code of the block Nested-loop join algorithm: For each of the tuple R in R does store used columns as p from R in the join buffer for E Ach tuple s in s does If p and s satisfy the join condition then output the tuple can be seen compared to the simple nested-loop join algorithm, Bl The Ock Nested-loopjoin algorithm has only one more so-called join Buffer, so why is it possible to reduce the number of scans in the table? The operation of the block Nested-loop join algorithm is better explained in comparison:
You can see the join buffer to cache the columns needed for the link, and then link the data in the form of join buffer in batches to the inner table. Just look, record r1,r2 ... rt links only need to sweep the table once, if the join buffer can cache all the outer columns, then the link only need to scan the inside of each time, so that the performance of the join greatly improved. join buffer variable join_buffer_size from the previous section you can find that the join buffer is an optimization to reduce the number of internal table scans, but the join buffer is not that simple. In the previous section, inside deliberately overlooked some implementations. First, variable join_buffer_size is used to control the size of the join buffer, which can avoid multiple internal table scans to improve performance. That is, when MySQL joins are used to block Nested-loop join, then the variable join_buffer_size is meaningful. The previous index Nested-loop join if you use only the index to join, then it makes no sense to increase this variable. The default value of the variable join_buffer_size is 256K, which is obviously not enough for a slightly more complex SQL. Fortunately this is a session-level variable that can be extended before execution. Inside recommends setting at the session level instead of the global setting because it is difficult to measure a common value. In addition, this memory is allocated at the session level and is prone to downtime caused by the inability to allocate memory if the settings are not good. It is important to note that the maximum value of the variable join_buffer_size is 4g-1 before MySQL 5.1.22, and the subsequent version can request a join buffer space greater than 4G under the 64-bit operating system. join Buffer caches objects join buffer cache objects, this issue is quite critical and important. This is recorded in the official MySQL manual: only columns of interest to the join IS stored in the join buffer, not whole rows. To discover that join buffer is not the entire row of records that caches appearances, but what does columns of interest specifically refer to? Inside's first reaction was to join the column. To this end, inside again to check the next MySQL internals, the query gets the following description: we only store the used columns in the join buffer, not the whole rows. used columns is still very blurry. To this end, inside asked the friend Li Haixiang, also the official MySQL Optimizer team member, he replied to my result is: "All participate in the query column" will be saved to the join Buffer, instead of only the join column. Finally, inside debugging MySQL, in the sql_join_buffer.cc file to verify the results. such as the following SQL statement, assuming that there is no index, you need to use the link to join buffer: select a.col3 from a,b WHERE a.col1 = b.col2 and A.col2 &G T .... and b.col2 = ... Assuming that the appearance of the above SQL statement is a, the inner table is B, then the column stored in the join buffer is the column of all participating queries, and here is (A.COL1,A.COL2,A.COL3). Through the above introduction, we can now get the number of scans within the table: scaninner_table = (Rn * used_column_size)/join_buffer_size + 1 For experienced DBAs, it is possible to estimate the size of the join buffer that needs to be allocated, and then try to minimize the number of scans in the inner table, and the best case is to scan the inner table only once. join buffer allocation need to keep in mind that the join buffer is allocated prior to the join, and each join needs to be assigned a join buffer, so assume that there are n tables participating in the join, each table passing a block Nested-loop join, a total of N-1 join Buffer is required, and this memory capacity needs to be considered by the DBA. join buffer can be divided into the following two categories: Regular join Bufferincremental join Buffer regular Join buffer is a join buffer that caches all the columns participating in the query. If you use the join buffer for the first time, you must use the regular join buffer. incremental JoiThe join buffer in n buffer caches the currently used column and the previous pointer using the join buffer. This can greatly reduce the need for memory overhead for join buffer when the join operation is performed multiple times. In addition, for columns with null types, you do not need to store them in join buffer, and for varchar-type columns, you need only the smallest amount of memory, rather than the char type in the join buffer. Finally, starting with MySQL version 5.6, you can also use join Buffer for outer joins. block Nested-loop Join Summary block Nested-loop join greatly avoids the number of scans inside the table, if the join buffer can cache the appearance of data, then the internal table scan only once, which and hash Joins are very similar. However, the block Nested-loop join still does not solve the number of join comparisons, which are still compared by the join judgment. In summary, the cost comparisons of the join algorithms so far are as follows:
To be Continued ...
MySQL Join algorithm and Tuning White Paper (ii)
