mysql中sql的执行顺序的坑

目录

场景还原

where与join on

sql顺序

---

今天工作中碰到一个sql问题，关于left join的，后面虽然解决了，但是通过此问题了解了一下sql的执行顺序

场景还原

为避免安全纠纷，把场景模拟。

有一个学生表-S，一个成绩表G

CREATE TABLE `test_student` (
  `id` bigint(20) NOT NULL COMMENT '学号',
	`sex` TINYINT DEFAULT '0' COMMENT '性别 0-男 1-女',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='学生表';

CREATE TABLE `test_score` (
  `id` bigint(20) NOT NULL COMMENT '学号',
  `score` int NOT NULL COMMENT '分数',
	`level` TINYINT COMMENT '成绩 0-不及格 1-及格 2-优良 3-优秀'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='成绩表';

-- 初始化学生
INSERT INTO test_student VALUES(1, 0, '张三'), (2, 0, '李四'), (3, 1, '王X芳');	`sex` TINYINT DEFAULT '0' COMMENT '性别 0-男 1-女',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='学生表';

CREATE TABLE `test_score` (
  `id` bigint(20) NOT NULL COMMENT '学号',
  `score` int NOT NULL COMMENT '分数',
	`level` TINYINT COMMENT '成绩 0-不及格 1-及格 2-优良 3-优秀'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='成绩表';

-- 初始化学生
INSERT INTO test_student VALUES(1, 0, '张三'), (2, 0, '李四'), (3, 1, '王X芳');

-- 初始化成绩
INSERT INTO test_score VALUES(1, 10, 0), (2, 20, 0), (3, 100, 3);

现在有一个需求，查出学生的考试成绩，可能的sql

SELECT ts.name AS '名字', tc.score AS '分数'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id;LEFT JOIN test_score tc
ON ts.id = tc.s_id;

,一切都很顺利

突然，刚考完试，就转来一个学生

INSERT INTO test_student VALUES(4, 0, '新来的');

他没成绩,还是刚才的sql查

，老师只要看参加考试的学生的成绩，怎么办

1.用inner join

SELECT ts.name AS '名字', tc.score AS '分数'
FROM test_student ts
INNER JOIN test_score tc
ON ts.id = tc.s_id;INNER JOIN test_score tc
ON ts.id = tc.s_id;

2.加条件过滤

SELECT ts.name AS '名字', tc.score AS '分数'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id
AND tc.score is NOT NULL
;AND tc.score is NOT NULL
;

，发现加了条件过滤还是不对，额，改用where呢

SELECT ts.name AS '名字', tc.score AS '分数'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id
WHERE tc.score is NOT NULL
;

,bingo，那where为什么就对了呢。

这就涉及到sql的执行顺序了

where与join on

从上面的例子推断

• where会从结果集中过滤出符合条件的记录，不符合条件的丢弃

join操作：有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。

除了我们在上面的例子中使用的 INNER JOIN（内连接），我们还可以使用其他几种连接。

下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行(mysql不支持，可以考虑用视图实现）

这里推荐一个写的通俗易懂的关于joinSQL的各种连接Join详解

sql顺序

从上面看到，在sql中，on是写在了where条件之前，那么数据库引擎分析执行sql时，是否on也是在where前面呢?

一般sql的写法顺序

1. SELECT [列名称 *代表所有的列]
2. FROM [表名称]
3. join_type JOIN [表名称]
4. ON [join条件]
5. WHERE [过滤条件]
6. GROUP BY [分组字段]
7. HAVING [分组条件]
8. ORDER BY [排序字段]

那么sql在执行时，顺序是怎样的呢？

标准的sql解析顺序为：

1. FROM    组装数据，来自不同数据源(表）
2. WHERE    根据条件过滤记录
3. GROUP BY 对数据分组
4. 计算聚集函数，如avg,sum
5. 使用HAVING子句筛选分组
6. 计算所有表达式
7. 使用ORDER BY对结果排序

那么sql的执行顺序呢？

1. FROM: 对前2个表执行笛卡尔积，生成虚表vt1
2. ON: 对vt1应用on条件，只有满足join_condition条件的才能插入虚表vt2
3. OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2，生成t3，如果from包含两个以上表，则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束
4. WHERE: 对vt3进行where筛选，只有满足where条件的才能插入vt4
5. GROUP BY: 对vt4按group by字段分组，得到vt5
6. HAVING：对vt5应用HAVING筛选器只有使 having_condition 为true的组才插入vt6
7. SELECT：处理select列表产生vt7
8. DISTINCT：将重复的行从vt7中去除产生vt8
9. ORDER BY：将vt8的行按order by子句中的列 列表排序生成一个游标vc9
10. LIMIT（Mysql）: 从vc9的开始处选择指定数量的行生成vt10 并返回调用者

到了这里，应该发现，要写好sql不容易。但是了解了sql的执行顺序，能在开发的同理，更好的帮助写出好的程序。

比如join表不能太多（先过滤条件然后再根据表连接 同时在表中建立相关查询字段的索引这样在大数据多表联合查询的情况下速度相当快），这种sql优化问题，下次再学习整理下。

实践之！