What is the execution process of Mysql table connection?

1. Foreword

For connection operations, we put the association conditions between the driving table and the driven table after on. If additional conditions for the driving table and the driven table are added, If the filter conditions of the driven table are placed after on or where, no error will be reported, but the result set obtained is different? ? ?

1.1 The principle of mysql connection

As we all know, mysql is based on the Nested-Loop Join (Nested-Loop Join, not considering the optimization algorithm for the time being) algorithm to perform connection operations between tables The general process is as follows:

• Select the driver table and use the filter conditions related to the driver table to execute a single-table query on the driver table;

• For each record in the queried driver table, search for matching records in the driven table.

The pseudo code is as follows:

for each row in t1 {      // 遍历满足对t1单表查询结果集中的每一条纪录
    for each row in t2 {  // 对于某条t1纪录，遍历满足对t2单表查询结果集中的每一条纪录
        if row satisfies join conditions, send to client
    }
}

1.2 show warnings command

The sql statement we wrote is optimized by the optimizer It will then be handed over to the executor for execution, and the show warnings command can help us obtain the SQL optimized by the optimizer.

2. Preparation

The table structure is as follows:

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stu_code` varchar(20) NOT NULL DEFAULT '',
  `stu_name` varchar(30) NOT NULL DEFAULT '',
  `stu_sex` varchar(10) NOT NULL DEFAULT '',
  `stu_age` int(10) NOT NULL DEFAULT '0',
  `stu_dept` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uq_stu_code` (`stu_code`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4

CREATE TABLE `course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cou_code` varchar(20) NOT NULL DEFAULT '',
  `cou_name` varchar(50) NOT NULL DEFAULT '',
  `cou_score` int(10) NOT NULL DEFAULT '0',
  `stu_code` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_stu_code_cou_code` (`stu_code`,`cou_code`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4

The table data is as follows:

3. The difference between inner join inner join on and where

sql is as follows:

select * from student
inner join course on student.stu_code = course.stu_code
and student.stu_code >= 3 and course.cou_score >= 80;

Execute explain sql command:

Execute show warnings command:

Analysis: From the analysis of show warnings, For inner join connections, after optimization by the optimizer, the on connection conditions will be converted into where! In other words, where and on in inner connections are equivalent .

4. The difference between left join left join on and where

4.1 where driver table filter conditions

sql is as follows:

select * from student
left join course on student.stu_code = course.stu_code
where student.stu_code >= 3;

Execute explain sql command:

Execute show warnings command:

Result set:

Analysis: From the explain analysis, student is used as the driver table, and student.stu_code >= 3 is used as a filter condition to perform a full table scan, and then the student.stu_code (that is, in the on condition) of each queried record is used as a filter condition to perform a single table query on the driven table course.

4.2 on driver table filter conditions

sql is as follows:

select * from student
left join course on student.stu_code = course.stu_code 
and student.stu_code >= 3;

Execute explain sql command:

Execute show warnings command:

## Result set:

From the result set, student.stu_code >= 3 does not take effect. Why?

Analysis: As can be seen from the explain analysis, student is used as the driver table to perform a full table scan, and then the student.stu_code and student.stu_code of each queried record are >= 3 (That is, in the on condition) are used as filter conditions to allow the driven table to perform single-table queries; At this time, student.stu_code >= 3 is not filtered for the driver table, and only takes effect when connecting to the driven table , the query cannot find matching records and returns NULL!

4.3 On driven table filter conditions

sql is as follows:

select * from student
left join course on student.stu_code = course.stu_code 
and course.cou_score >= 80;

Execute explain sql command:

Execute show warnings command:

##Result set:

Analysis: As can be seen from the explain analysis, student is used as the driving table to perform a full table scan, and then the student.stu_code and course.cou_score of each queried record are >= 80 (that is, ) in the on condition are used as filtering conditions to allow the driven table to perform single-table queries;

4.4 where driven table filtering conditions

sql is as follows:

Execute the explain sql command:

Execute the show warnings command:

Result set:

From the analysis of show warnings? Left join connection becomes inner join connection?

Analysis: From the show warnings analysis, it can be seen that if the driven table has filter conditions in where, then the left join will be invalidated and optimized into an inner join connection. Therefore, the filter conditions of driven tables should be placed on instead of where.

The above is the detailed content of What is the execution process of Mysql table connection?. For more information, please follow other related articles on the PHP Chinese website!