Mysql join语法解析与性能分析

一：概述

在mysql对sql进行解析的时候，会把一个完整的sql语句拆分成多个字句，字句的执行过程会产生虚拟表，按一定规则逐步执行合并与删除，最后返回一张虚拟表。
复制代码

二：Join的执行顺序

先来看一个通用的join查询结构，并分析此语句的执行顺序
复制代码

SELECT <row_list> 
  FROM <left_table> 
    <inner|left|right> JOIN <right_table> 
      ON <join condition> 
        WHERE <where_condition>
复制代码

sql语句的执行顺序如下：

• FROM：对左右两张表执行笛卡尔积，产生虚拟表V1，行数为左表n * 右表行数m
• ON：根据on的条件进行筛选，产生虚拟表V2
• JOIN：判断连接类型，如果为left join(left outer join)，则遍历左表，对于存在左表而不存在v2表中的数据，插入到v2中，其他字段补null，形成虚拟表v3；如果为right join(right outer join)则同理；如果为inner join，则不会遍历左右表，v3=v2(inner join连接类型时，筛选条件放在on与where没区别)。
• WHERE：使用where条件进行过滤，筛除不符合数据，生成虚拟表v4
• SELECT：取出v4的字段到虚拟表v5

三：实例展示

接下来通过一个实例来展示上述sql语句的执行过程，首先创建一个班级表    

CREATE TABLE `class`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级唯一标识',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '班级名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '班级表'
复制代码

然后创建一个学生表   

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生唯一标识',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  `class_id` int(11) NOT NULL COMMENT '班级id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学生表' 

复制代码

然后为班级表导入3条数据，为学生表导入5条数据，数据结果如下所示   

select * from class;
+--------+------+
|    id  | name |
+--------+------+
|   1    | 一班 |
|   2    | 二班 |
|   3    | 三班 |
+--------+------+
3 rows in set (0.00 sec)

select * from student;
+--------+-------+----------+
|   id   | name  | class_id |
+--------+-------+----------+
|   1    |  张三 |    1     |
|   2    |  李四 |    1     |
|   3    |  王五 |    2     |
|   4    |  赵六 |    2     |
|   5    |  李七 |    5     |
+--------+-------+----------+
5 rows in set (0.00 sec)
复制代码

我们取出class_id为2的班级名称和班级学生姓名，sql语句如下，并对执行步骤进行分析   

SELECT c.name as c_name, s.name as s_name 
  FROM class as c 
    LEFT JOIN student as s 
      ON c.id = s.class_id 
        WHERE c.id = 2;
复制代码

第一步：对班级表和学生表执行笛卡尔积操作，班级表3行数据，学生表5行数据，那么生成的虚拟表v1即3*5行数据

第二步：根据on条件c.id=s.class_id进行过滤，生成虚拟表v2

第三步：添加外部行，连接类型为left join类型，则会对左表class进行搜索，把存在于表class中记录，而不存在于虚拟表v2的记录插入v2，剩余字段填充null或默认值，生成虚拟表v3

第四步：使用where条件进行过滤，生成虚拟表v4

第五步：使用select进行字段过滤

转载于:https://juejin.im/post/5ba377bff265da0a87265765