Shulou(Shulou.com)05/31 Report--

This article mainly introduces several algorithms of join in MySQL, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

We often use join to join multiple tables when querying multiple tables, in fact, the efficiency ratio of join is not good or should be avoided as far as possible, its essence is circular matching between tables, MySQL only supports a join algorithm Nested-Loop Join (circular nested join), but it has many variants of the algorithm, in fact, it is to improve the efficiency of join implementation.

1. Simple Nested-Loop Join (simple nested loop join)

The Simple Nested-Loop join (NLJ) algorithm reads one row at a time from the first table in the loop, passing each row to a nested loop that matches whether the data is consistent. For example, the sql of the driven table User and the driven table UserInfo is select * from User u left join User_info info on u.id = info.user_id, which is actually our commonly used for loop. The logic of the pseudocode should be

For (User u:Users) {for (UserInfo info:UserInfos) {if (u.id = = info.userId) {/ / get matching data}

A simple and crude algorithm that takes one piece of data from the User table at a time, then scans all the records in the User_info to match, and finally merges the data back.

If the driven table User has 10 pieces of data, and the driven table UserInfo also has 10 pieces of data, then in fact, the driven table User will be scanned 10 times, while the driven table will be scanned 10 times 10 times, 100 times (every time you scan the driven table, all the driven tables will be scanned), this efficiency is very low, and the cost to the database is relatively large, especially the driven table. Each scan actually reads data from the hard disk and loads it into memory, that is, an IO. At present, IO is the biggest bottleneck.

2. Index Nested-Loop Join (index nested loop join)

Index nesting loops use indexes to reduce the number of scans to improve efficiency, so it is required that there must be indexes on undriven tables.

During the query, the driven table (User) will query according to the index of the associated field, and the query back to the table will only be carried out when the matching value is found on the index. If the associated field (user_id) of the non-driven table (User_info) is the primary key, the query efficiency will be very high (the leaf node of the primary key index structure contains the complete row data (InnoDB). If it is not the primary key, you need to do a query back to the table (according to the primary key ID of the secondary index (non-primary key index) after matching to the index, so the performance is definitely weaker than that of the primary key.

The index query in the above figure does not necessarily return the table, and under what circumstances it will return to the table. This depends on whether the fields queried by the index can meet the needs of the query. Please refer to the previous article: some basic index knowledge you need to know and the index knowledge of B+ tree.

3. Block Nested-Loop Join (cache block nested loop connection)

If there is an index, then index will be used for join. If the column of join does not have an index, the driven table will be scanned too many times. Each time the driven table is accessed, the records in its table will be loaded into memory, and then a match will be taken from the driven table. After the matching, the memory will be cleared, and then a record will be loaded from the driven table and then the record of the driven table will be loaded into memory. This cycle greatly increases the number of IO. In order to reduce the number of IO of driven tables, the way of Block Nested-Loop Join appears.

Instead of getting the data of the driven table one by one, the join buffer buffer is introduced, and some data columns related to the join of the driven table (the size is the limit of join buffer) are cached into join buffer, and then the whole table is scanned. Each record of the driven table is matched with all the records of the driven table in join buffer at one time (memory operation), merging multiple comparisons in a simple nested loop into one. The access frequency of undriven tables is reduced.

Whether the driver table can be loaded at one time depends on whether join buffer can store all the data. By default, join_buffer_size=256k, when querying, Join Buffer caches all the columns participating in the query instead of just join. In a sql with N join associations, one join buffer is allocated. So minimize unnecessary fields when querying, so that more columns can be stored in the join buffer.

You can adjust the cache size of join_buffer_size show variables like'% join_buffer%' this value can be changed according to the actual situation.

To use the Block Nested-Loop Join algorithm, you need to enable the optimizer_switch managed by the optimizer. Set block_nested_loop to on, which is enabled by default. You can view the block_nested_loop status through show variables like'% optimizer_switch%'.

Thank you for reading this article carefully. I hope the article "several algorithms of join in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.