mysql子查询和连接查询的区别吗_子查询和联表查询的区别和优化

一 子查询

1 MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作

2 子查询虽然很灵活，但是执行效率并不高

3 执行子查询时，SQL语句中进行嵌套了SQL语句, 这就类似于循环了

4 例如有两张表, 我先查一张表,查一次, 然后拿这个结果去筛选查另一张表,查两次

二 连接查询(join)

1 可以使用连接查询(JOIN)代替子查询，连接查询需要建立临时表，,但因为联表操作不需要查询数据, 只需要在联表成新表之后查询一次, 因此其速度比子查询快

2 表关联是可以利用两个表的索引的，如果是用子查询，至少第二次查询是没有办法使用索引的

3 例如两张表, 我先把两张表拼在一起, 不查, 再查拼起来的新表, 查一次

总结：连接查询效率高于子查询！！！

1 扩展：多表联查性能优化

优化的本质就是(join on 和where的执行顺序)！！！

在使用left jion on时，on和where条件的区别如下：

1 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录

2 where条件是在临时表生成好后，再对临时表进行过滤的条件(这时已经和left join没关系了)，条件不为真的就全部过滤掉

2 left join on + where条件查询的索引优化实例分析

sql：select * from A left join B on A.c = B.c where A.employee_id = 3

解读： A表left join B表，并且指定A表中的employee_id为一个具体的值

1 假设A表，B表均有10000多条数据；

2 使用上面的sql查询时间达到16秒(在c字段不是任何索引，用explain分析得知，AB表都使用了全表查询，效率极低)

3 开始优化上面sql:

1 给AB表列c都加索引(仅用了0.1s，但是分析后显示表A依然进行了全表扫描)

思考：为什么全表扫描的不是表B.因为Mysql内部的优化，使用小表驱动大表，它在估算到必须有一个表要全表扫描的话，一定会选择那个数据量更小的表去全表扫描

也就是说，在这个查询中，因为on以后的where条件列并没有使用到索引，所以mysql的优化只用到了表B的c索引，没有用到表A的索引！

2 我们有where条件查询，不需要全表扫描，此时就需要where条件生效，操作及分析如下：

将A表中的索引改为employee_id+c(经验证两个所以都使用了，方案可行)

思考：sql执行 from中的on应该是优先于where语句的，为什么这里employee_id反而在c之前，有违常理

因为Mysql内部优化，这一句Sql在执行的时候首先是选择了使用表B的索引来进行优化，将表A单独放出来进行后续的操作，

然后，又发现了where语句中A.employee_id有一个聚合索引，并且employee_id处于索引头，所以这个聚合索引是可用的，所以自然使用了此索引

3 即使把聚合索引后面的列c删掉，与使用聚合索引的效果是一样的，之前全表查询，现在根据条件只查询了满足条件的，时间大幅缩短

3 扩展：mysql连接查询中索引的重要性

1 连接查询通过两张表中符合连接关系的字段来建立两张表的关联，通常包括内连接、左外连接、右外连接和全连接

2 内连接会保留两张表中共有的那部分记录，因此最后产生的连接表记录数最少；

3 全连接会保留两张表中所有的记录，因此最后产生的连接表记录数最多；

4 左外连接会保留左表的全部记录，右外连接会保留右表的全部记录，因此最后产生的连接表记录数处于内连接和外连接之间。

5 我们可以给关联表的字段添加索引来减少查询次数，提高查询效率

6 使用多表关联时，一般遵循以下规则：

6.1.左连接：一般给右边表的关联字段建立索引；

6.2.右关联：一般给左边表的关联字段建立索引；

6.3.内连接：一般给关联表的任意一边的关联字段建立索引即可

举例：学生表student(id，name)，课程表class(id,student_id,class),各有10000条数据

sql实例：SELECT a.id, name FROM student a LEFT JOIN class b ON a.id = b.student_id

这个查询的执行速度非常慢！！

1 首先用explain查看这个语句的查询执行计划，可以看到type都为ALL，即在student表和class表中都使用的全表扫描

2 改进：试着给class表的student_id字段添加索引alter table class add index class_index(student_id)

3 然后再次执行查询，发现速度非常快,这就是效率改进的关键点所在

4 当连接查询时产生的连接表过大时，为了防止查询次数过多，我们要经常使用索引来减少查询次数，提高查询效率