Mysql 테이블 연결 실행 과정은 어떻게 되나요?

1. 서문

운전 테이블과 피동 테이블의 연관 조건을 on 뒤에 넣습니다. 운전 테이블과 피동 테이블에 대한 추가 필터 조건을 추가할 경우 on 또는 뒤에 넣습니다. 오류는 보고되지 않지만 얻은 결과 세트가 다릅니다. ? ?

1.1 mysql 연결의 원리

우리 모두 알고 있듯이 mysql은 Nested-Loop Join(최적화 알고리즘과 상관없이 Nested-Loop Join) 알고리즘을 기반으로 테이블 간 연결 작업을 수행하는데 일반적인 과정은 다음과 같습니다. :

• 드라이버 테이블을 선택하고 드라이버 테이블과 관련된 필터 조건을 사용하여 드라이버 테이블에 대한 단일 테이블 쿼리를 실행합니다.

• 쿼리된 드라이버 테이블의 각 레코드에 대해 다음에서 일치하는 레코드를 검색합니다. 구동 테이블.

의사 코드는 다음과 같습니다.

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 명령

우리가 작성한 sql 문은 옵티마이저에 의해 최적화된 후 실행을 위해 executor로 전달되며, show warnings 명령이 도움이 될 수 있습니다. 우리는 최적화된 SQL을 얻습니다.

2. 준비 작업

테이블 구조는 다음과 같습니다.

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

테이블 데이터는 다음과 같습니다.

3 내부 조인

sql에서 on과 where의 차이점은 다음과 같습니다. 다음과 같이:

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

explain+sql 명령 실행:

show warnings 명령 실행:

분석: show warnings 분석에서 내부에 대한 Join Connection, 옵티마이저에 의한 최적화 후, 연결 조건은 where!로 변환됩니다! 즉, 내부 연결의 where와 on은 동일합니다. 对于inner join连接，经过优化器优化后，on连接条件会转化为where！也就是说内连接中的where和on是等价的。

4. left join左连接on、where的区别

4.1 where驱动表过滤条件

sql如下：

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

执行explain+sql命令： 

执行show warnings命令： 

结果集： 

分析：从explain分析看出，student作为驱动表，把student.stu_code >= 3作为过滤条件进行全表扫描，然后把查询到的每条纪录的student.stu_code（也就是on条件里面的）分别作为过滤条件让被驱动表course做单表查询。

4.2 on驱动表过滤条件

sql如下：

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

执行explain+sql命令： 

执行show warnings命令： 

结果集： 

从结果集来看，student.stu_code >= 3并未生效，为什么？

分析：从explain分析看出，student作为驱动表，做全表扫描，然后把查询到的每条记录的student.stu_code和student.stu_code >= 3（也就是on条件里面的）分别做为过滤条件让被驱动表做单表查询；此时student.stu_code >= 3对驱动表是不过滤的，仅在连接被驱动表时生效，查询不到符合纪录而返回NULL！

4. 드라이버 테이블 필터링 조건

sql이 있는 경우와 4.1의 차이점은 다음과 같습니다.

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

explain+sql 명령을 실행합니다.

show warnings 명령 실행:

결과 세트:

분석: 🎜Explain 분석 결과에서, Student를 드라이버 테이블로 사용하고, Student.stu_code >= 3을 필터 조건으로 사용하여 전체 테이블 스캔을 수행한 후, Student.stu_code(즉, on 조건)를 사용합니다. 쿼리된 각 레코드는 필터 조건으로 사용되어 구동됩니다. 테이블 코스에 대해 단일 테이블 쿼리를 만듭니다. 🎜🎜4.2 드라이버 테이블 필터 조건 🎜🎜🎜sql은 다음과 같습니다. 🎜🎜rrreee🎜🎜 explain+sql 명령 실행: 🎜🎜🎜🎜🎜🎜show warnings 명령 실행: 🎜🎜🎜🎜🎜🎜결과 세트: 🎜🎜🎜🎜🎜결과 집합에서 Student.stu_code >= 3은 적용되지 않습니다. , 왜? 🎜🎜🎜분석: 🎜설명 분석에서 전체 테이블 스캔을 수행하기 위해 학생이 드라이버 테이블로 사용된 다음 쿼리된 각 레코드의 Student.stu_code 및 Student.stu_code가 >= 3임을 알 수 있습니다(즉 on 조건에서) 이를 필터 조건으로 사용하여 구동 테이블이 단일 테이블 쿼리를 수행하도록 합니다. 이때, Student.stu_code >= 3은 드라이버 테이블에 대해 필터링되지 않습니다. 쿼리에서 일치하는 레코드를 찾을 수 없으면 NULL이 반환됩니다. 🎜🎜4.3 구동 테이블 필터 조건 🎜🎜🎜sql은 다음과 같습니다. 🎜🎜rrreee🎜🎜 explain+sql 명령 실행: 🎜🎜🎜🎜🎜🎜🎜show warnings 명령 실행: 🎜🎜🎜🎜 🎜 🎜🎜 결과 에피소드: 🎜🎜🎜🎜🎜

분석: 설명 분석에서 Student를 드라이버 테이블로 사용하여 전체 테이블 스캔을 수행한 다음, Student.stu_code 및course.cou_score >= 80(즉, on 조건)임을 알 수 있습니다. ) 각 쿼리된 레코드는 개별적으로 구동 테이블을 필터 조건으로 사용하여 단일 테이블 쿼리를 수행합니다.

4.4 여기서 구동 테이블 필터 조건

sql은 다음과 같습니다.

explain+sql 명령 실행:

표시 경고 명령 실행:

결과 세트:

표시 경고 분석에서? 왼쪽 조인 연결이 내부 조인 연결이 되나요?

분석: 표시 경고 분석에서 구동 테이블의 where에 필터 조건이 있는 경우 왼쪽 조인이 무효화되고 내부 조인 연결로 최적화된다는 것을 알 수 있습니다. 그래서 被驱动表的过滤条件应该放在on而不是where.

위 내용은 Mysql 테이블 연결 실행 과정은 어떻게 되나요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!