Analysis of implementation principle of join algorithm in MySQL

In MySQL, there is only one join algorithm, the famous Nested Loop Join, which does not have the Hash join provided by many other databases, nor does the Sort Merge join. As the name suggests, the Nested loop Join actually uses the result set of the driver table as the cyclic base data, and then a single piece of data in the result set is used as a filter condition to query the data in the next table and then merge the results. If there is a third participation join, then the join result set of the first two tables is used as the circular base data, and then the query data is queried again through the loop query condition to the third table.

This is illustrated by examples and illustrations, followed by a Join query in the three tables in my personal database test environment, which is a example (self-designed, non-mysql-provided) database.

Note: Since some of this content needs to be reflected in the MySQL 5.1.18 version, the MySQL version of this test is 5.1.26

Table structure:

1 sky@localhost:example 11:09:32> Show CREATE TABLE User_group\g
2
3 *************************** 1. Row ***************************
4
5 Table:user_group
6
7 Create table:create table ' User_group ' (
8
9 ' user_id ' int (one) not NULL,
10
One ' group_id ' int (one) is not NULL,
12
' User_type ' int (one) is not NULL,
14
' Gmt_create ' datetime not NULL,
16
' Gmt_modified ' datetime not NULL,
18
' Status ' varchar not NULL,
20
KEY ' Idx_user_group_uid ' (' user_id ')
22
) Engine=myisam DEFAULT Charset=utf8
24
1 row in Set (0.00 sec)
26
Sky@localhost:example 11:10:32> Show CREATE TABLE Group_message\g
28
29 *************************** 1. Row ***************************
30
Table:group_message
32
Create table:create table ' Group_message ' (
34
' id ' int (one) not NULL auto_increment,
36
Panax Notoginseng ' gmt_create ' datetime not NULL,
38
The ' gmt_modified ' datetime is not NULL,
40
The ' group_id ' int (one) is not NULL,
42
The ' user_id ' int (one) is not NULL,
44
VarChar ' author ' is not NULL,
46
The ' subject ' varchar (128) is not NULL,
48
PRIMARY KEY (' id '),
50
Wuyi KEY ' idx_group_message_author_subject ' (' Author ', ' Subject ' (16)),
52
The KEY ' Idx_group_message_author ' (' Author '),
54
KEY ' Idx_group_message_gid_uid ' (' group_id ', ' user_id ')
56
) Engine=myisam auto_increment=97 DEFAULT Charset=utf8
58
1 row in Set (0.00 sec)
60
Sky@localhost:example 11:10:43> Show CREATE TABLE Group_message_content\g
62
63 *************************** 1. Row ***************************
64
Table:group_message_content
66
Create table:create table ' Group_message_content ' (
68
The ' group_msg_id ' int (one) is not NULL,
70
The ' content ' text is not NULL,
72
KEY ' group_message_content_msg_id ' (' group_msg_id ')
74
Engine=myisam DEFAULT Charset=utf8
76
1 row in Set (0.00 sec)