Mysql join table and id auto-increment example analysis

How to write join

If you use left join, is the table on the left necessarily the driving table? When the join of two tables contains multiple equal matching conditions, should we write on for all of them or only one of them and write the rest in the where part?

create table a(f1 int, f2 int, index(f1))engine=innodb;
 create table b(f1 int, f2 int)engine=innodb;
 insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
 insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/
 select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/

Execution Result:

Since table b has no index, the Block Nexted Loop Join (BNL) algorithm is used.

• Read the contents of table a into join_buffer. Because of select *, fields f1 and f2 are both put into the join_buffer.
• Scan b sequentially. For each row of data, determine whether the join condition is met. The records that meet the condition are taken as a row of the result set. If there is a where clause, determine whether the where part meets the condition before returning.
• After the scan of table b is completed, the rows of table a that have no matches are filled with null and put into the result set.

In the Q2 statement, explain the result:

b is the driving table. If there is nothing in the EXTRA field of a statement, it is the Index Nested_Loop Join algorithm, so the process is:

Scan b sequentially, use b.f1 to check each row in a, and match whether a.f2=b.f2 is satisfied, and return it as the result set.

The difference between the execution process of Q1 and Q2 is because the optimizer optimizes based on the query semantics of Q2: in MySQL, the result of equality and inequality judgment between null and any value is null, including select null = null, which also returns null.

In Q2, where a.f2 = b.f2 means that the query result will not include rows where b.f2 is null. The semantics of left join is to find the same rows in the two tables where f1 and f2 correspond to each other. If a exists but b does not match, then give up. Therefore, the optimizer rewrites the left join of this statement into a join. Because f1 of a has an index, b is used as the driving table, so that the NLJ algorithm can be used. Therefore, when using left join, the table on the left is not necessarily the driving table.

If the semantics of left join are required, the fields of the driven table cannot be placed in the where condition for equality or inequality judgment. They must be written in the on condition.

Performance issues with Nested Loop Join

BLN algorithm execution logic

• Read all the data in the driver table into join_buffer, which is an unordered array.
• Sequentially traverse all rows of the driven table, matching each row with join_buffer, and returning it as part of the result set if successful.

The logic of the Simple Nested Loop Join algorithm is to sequentially remove each row of data in the driving table and perform a full table match in the driven table.

Differences between the two:

When performing a full table scan on the driven table, if the data is not in the buffer pool, it is necessary to wait for some data to be read from the disk. It will affect the buffer pool hit rate of normal business and will make multiple visits to the driven table, making it easier to put these data pages at the head of the buffer pool. So the BNL algorithm will perform better. Auto-increment id

The auto-increment ID in MySQL defines an initial value, which keeps growing, but has an upper limit, 2^32-1. What happens when the auto-increment ID is used up?

When the auto-increment value defined in the table reaches the upper limit, the value obtained when applying for the next ID remains unchanged. When inserting again, a primary key conflict error will be reported. Therefore, when creating a table, if there are frequent additions, deletions, and modifications, you should create an 8-byte bigint unsigned.

Innodb system automatically increases row_id

If an Innodb table is created without specifying a primary key, Innodb will create an invisible row_id with a length of 6 bytes. For all Innodb tables without a primary key, each time a row of data is inserted, the current dict_sys.row_id value is used as the row_id of the data to be inserted, and then incremented by 1.

In fact, when the code is implemented, row_id is an unsigned long integer with a length of 8 bytes, but when innodb is designed, row_id is only 6 bytes long, so only the last 6 bytes are placed when writing data. so:

• The range of row_id written to the table is 0 to 2^48-1;
• When the maximum is reached, if there is another act of inserting data to apply for row_id, the last 6 bytes will be 0 after obtaining it, and then the cycle will continue.
• In the logic of InnoDB, after reaching the maximum loop, new data will overwrite the existing data.

From this perspective, we should actively create an auto-increment primary key so that when the upper limit is reached, an error will be reported when inserting data. The reliability of the data will be more guaranteed.

XID

When redo log and binlog work together, they have a common field called xid, which corresponds to transactions in MySQL. The maximum value of xid is 2^64, and it only exists in theory when it is exhausted.

thread_id

The system saves the global variable thread_id_counter. Every time a new connection is created, thread_id_counter is assigned to the thread variable of the new connection. The size of thread_id_counter is defined as 4 bytes, so it will be reset to 0 when it reaches 2^32-1, and then continue to increase. However, you will not see two identical thread_ids in show processlist. This is because MySQL has designed a unique array logic to assign thread_ids to new threads:

do {
 		new_id= thread_id_counter++;
 } while (!thread_ids.insert_unique(new_id).second);

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.