文章目录
背景:
如果想查询A表中class_id字段在B表中的class_id中没有出现过的所对应的相关信息
table: students
id | username | sex | classify | score | class_id |
---|---|---|---|---|---|
10000 | alien | 女 | 作家 | 57 | 1 |
10001 | zhang | 男 | 词人 | 27 | 2 |
10002 | ping | 女 | 酱油 | 31 | 3 |
10003 | user-3 | 女 | 诗人 | 68 | 3 |
10004 | user-4 | 男 | 作家 | 6 | 2 |
10012 | user-12 | 女 | 词人 | 54 | 5 |
10018 | user-18 | 自由职业 | 88 | 8 |
table: classes
class_id | class_object | class_name | class_address |
---|---|---|---|
1 | python2018级 | 张三 | 1号楼208室 |
2 | java2017级 | 王五 | 8号楼606室 |
3 | go2019级 | 李四 | 9号楼168室 |
9 | 大数据2018级 | jack ma | 6号楼222室 |
1.子查询方式------效率较低
(1)查询方式
SELECT
*
FROM
students s
WHERE
s.class_id NOT IN ( SELECT c.class_id FROM classes c );
result:
id | username | sex | classify | score | class_id |
---|---|---|---|---|---|
10012 | user-12 | 女 | 词人 | 54 | 5 |
10018 | user-18 | 自由职业 | 88 | 8 |
(2)查询分析
EXPLAIN
SELECT
*
FROM
students s
WHERE
s.class_id NOT IN ( SELECT c.class_id FROM classes c );
- 主查询会过滤students表中所有的信息(共计7行),子查询也会过滤classes表中所有的信息(共计4行)
- 如果主查询和子查询的数据量都非常大的话,这种查询方式最终需要过滤2个表里面的所有信息才能找到最终需要的信息。
2.关联查询(left join)------效率较高
这种方式前提是,2个表格关联的字段都有索引效率才高,否者也会很慢
(1)查询方式
SELECT
*
FROM
students s
LEFT JOIN classes c ON s.class_id = c.class_id
WHERE
c.class_id IS NULL;
id | username | sex | classify | score | class_id | class_id | class_object | class_name | class_address |
---|---|---|---|---|---|---|---|---|---|
10012 | user-12 | 女 | 词人 | 54 | 5 | null | null | null | null |
10018 | user-18 | null | 自由职业 | 88 | 8 | null | null | null | null |
- 可以看出,最终的数据包含classes表的字段,只是对应的值都是null
(2)查询分析
EXPLAIN
SELECT
*
FROM
students s
LEFT JOIN classes c ON s.class_id = c.class_id
WHERE
c.class_id IS NULL;
- 最终查看到,虽然2个表格也都全部过滤了一遍,filtered列,我们看到第二个表的查询过滤数只有25,越接近100,说明查询效率越高。经过查询发现,classes表没有使用claas_id设置为索引。因为此种情况,第二张表格的数据比第一个少,所以效果不是很明显。
- 如果A表小,B表大,那么B表对应的字段就需要使用索引,否者会过滤所有的选项。
EXPLAIN
SELECT
*
FROM
borrow b
left join cash c on b.borrow_no=c.borrow_no
WHERE
c.borrow_no is null;
相同的代码,使用cash表中borrow_no作为索引,最终结果如下:
不使用索引,最终结果:
最终我们能看到,不使用索引,最终第二个大表会检索所有数据,最终效率会非常慢。
3.exist判断查询------效率较高
exits的基础用法:
https://www.cnblogs.com/cjm123/p/8177017.html
(1)查询方式
SELECT
*
FROM
students s
WHERE
NOT EXISTS ( SELECT * FROM classes c WHERE s.class_id = c.class_id );
result:
id | username | sex | classify | score | class_id |
---|---|---|---|---|---|
10012 | user-12 | 女 | 词人 | 54 | 5 |
10018 | user-18 | 自由职业 | 88 | 8 |
count(*) 有时候还使用count(1)代替,效果都一样
(2)查询分析
EXPLAIN
SELECT
*
FROM
students s
WHERE
NOT EXISTS ( SELECT * FROM classes c WHERE s.class_id = c.class_id );
- 通过如上分析,使用not exists 最终只过滤了一个表的全部,另一个表过滤的行数只有1,这样效率很高。
4.在where条件中使用count()------效率最高
(1)查询方式
SELECT
*
FROM
students s
WHERE
(SELECT count(1) FROM classes c where s.class_id=c.class_id)=0;
result:
id | username | sex | classify | score | class_id |
---|---|---|---|---|---|
10012 | user-12 | 女 | 词人 | 54 | 5 |
10018 | user-18 | 自由职业 | 88 | 8 |
count(*)与count(1)的效果最终都一样
(2)查询分析
EXPLAIN
SELECT
*
FROM
students s
WHERE
(SELECT count(*) FROM classes c where s.class_id=c.class_id)=0;
- 这种方式效率很高,在从students每个数据开始查询的时候,都计算一下是否有classes表里面的数据,满足s.class_id=c.class_id,如果每组则这组数据最终的count(*)为1,否者为0,所以在students表中,没有符合条件的数据,都是在students中存在且在classes表中不存在的数据。