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

背景:
如果想查询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表中不存在的数据。
展开阅读全文

没有更多推荐了,返回首页