MySQL优化------A表的某字段在B表的没有出现的相关信息

背景:
如果想查询A表中class_id字段在B表中的class_id中没有出现过的所对应的相关信息

table: students

idusernamesexclassifyscoreclass_id
10000alien作家571
10001zhang词人272
10002ping酱油313
10003user-3诗人683
10004user-4作家62
10012user-12词人545
10018user-18自由职业888

table: classes

class_idclass_objectclass_nameclass_address
1python2018级张三1号楼208室
2java2017级王五8号楼606室
3go2019级李四9号楼168室
9大数据2018级jack ma6号楼222室


1.子查询方式------效率较低

(1)查询方式
SELECT
	* 
FROM
	students s 
WHERE
	s.class_id NOT IN ( SELECT c.class_id FROM classes c );

result:

idusernamesexclassifyscoreclass_id
10012user-12词人545
10018user-18自由职业888
(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;

idusernamesexclassifyscoreclass_idclass_idclass_objectclass_nameclass_address
10012user-12词人545nullnullnullnull
10018user-18null自由职业888nullnullnullnull
  • 可以看出,最终的数据包含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:

idusernamesexclassifyscoreclass_id
10012user-12词人545
10018user-18自由职业888

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:

idusernamesexclassifyscoreclass_id
10012user-12词人545
10018user-18自由职业888

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表中不存在的数据。
  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

hello-alien

您的鼓励,是我最大的支持!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值