取得一张表的数据不在另一张表中的最优秀方法(JOIN与EXISTS的效率研究)
来源:互联网 发布:linux 脚本usleep 编辑:程序博客网 时间:2024/06/03 19:11
公司有个CRM经纪人管理系统,前天遇到的比较棘手的问题是,要查询一张表中不在另一张表中的所有用户并分页显示,但问题是外表的数据量很大,如果用not in(select ..),not exists(select ...)之类的子查询 的话需要对子查询表进行所有信息的调用过滤才行,一单子查询数据量过大效率问题就来了,于是就私下里查了一点资料,并根据资料中提供的例子建立了对应的测试表做了一些测试,随后将解决问题的方法移交给了技术部的邹鸿同事去解决问题,因为是前天的事情了,本来是事情忙没时间写日志的,就是这个事情没有做个笔记已经两天没睡好觉了,于是乎今天来了还是做一下笔记,分享给大家的好,如果大家有更好的办法 或者 说有bug疑问,请随时Email联系 摘取天上星 happy.yin@qq.com
下面是一组测试的JOIN 、EXISTS 效率对比
先将profiling打开,用来一会查看sql执行时间
set profiling=1;
exists 子查询与 join联接效率的对比
EXPLAIN SELECT film_id, language_id FROM sakila.filmWHERE NOT EXISTS( SELECT * FROM sakila.film_actor WHERE film_actor.film_id = film.film_id);
EXPLAIN SELECT film_id, language_id FROM sakila.film LEFT JOIN sakila.film_actor USING(film_id)WHERE film_actor.actor_id IS NULL;
他们只有一点细微的区别,一个select_type,还有就是Extra里后者用了Not exists,也就是提前终止算法,当遇到第一个actor_id不是null的时候,就舍弃这个筛选.
最后来看看二者的效率
可知使用子查询Exists的效率要低一些.
SELECT DISTINCT film_id FROM sakila.film JOIN sakila.film_actor USING(film_id);SELECT film_id FROM sakila.film WHERE EXISTS ( SELECT * FROM sakila.film_actor WHERE film.film_id = film_actor.film_id);SHOW PROFILES;
可以看出.依然EXISTS效率要低
PS:“摘取天上星” 的小插曲,有人说在使用JOIN方法对 1000万条数据的表进行操作时速度会变得很慢,其实不是SQL语句本身的问题,单表1000万条数据慢的问题是磁盘IO带来的硬伤,是任何优化无法避免的,使用任何软件层优化都无法避免磁盘IO问题对数据库带来的硬伤。。
总结:判断一个表的数据不在另一个表中最优秀方法如下可选(前提条件:a表的aid和b表的aid必须有索引,b.aid不能有NULL值):
SELECT aid FROM a LEFT JOIN b ON a.aid = b.aid WHERE b.aid IS NULL LIMIT 0,100;
SELECT aid FROM a LEFT JOIN b USING(aid) WHERE b.aid IS NULL LIMIT 0,100;(此语法实际效果同上,是上面SQL语句的简写形式,USING语法参见本文末尾↓)
实现原理其实很简单,left join在进行 链表时,如果外表数据不存在 的情况下 返回数据为NULL,
而 SELECT aid FROM a LEFT JOIN ON a.aid=b.aid WHERE b.aid IS NULL 恰恰利用了 a表aid和b表aid相等同时b表的aid又为NULL的情况下才返回数据,而满足这个条件的数据只有一种情况,就是b表中没有这个数据的情况下才满足b.aid即为NULL,同时又和a.aid相等,也就是我们要找的在a表中但不在b表中的数据。
USING用法以及使用效果如下:
用于表连接时给定连接条件(可以理解为简写形式)
SELECT * FROM a JOIN b ON a.id = b.id;
使用 USING 可以写为
SELECT * FROM a JOIN b USING(id);
- 取得一张表的数据不在另一张表中的最优秀方法(JOIN与EXISTS的效率研究)
- 查询一张表不在另一张表中的记录
- oracle 把一张表的数据放入另一张表
- mysql 一张表的数据存入另一张表
- 根据一张表去更新另一张表的数据
- SQL使用一张表的数据更新另一张表
- 查询一张表的数据去更新另一张表
- mysql插入一张表里的数据到另一张表
- 把一张表中的数据即一个List作为查询另一张表的条件参数
- 一张表中数据几个字段的值添加到另一张表中的值
- 两张表主键相同,根据主键一张表主键更新另一张表数据的方法
- JOIN与EXISTS(子查询)的效率研究
- 在一张表中,而不在另一张表中的记录
- sql之查询一张表的数据同时插入到另一张表的几种方法
- 查询一张表的一个字段数据,修改另一张表的字段数据
- MySql中用一张表的数据更新另一张表的部分数据
- oracle中用一张表的数据更新另一张表的数据(快速稳定)
- 循环将一张表的数据添加到另一张表,重复数据不添加
- 第二节 Lookup 缓存 之 使用永久查找高速缓存
- 系统中参数的初始化 参数在系统中配置
- rfid作业
- C#从listview中移动数据到其它控件中,避免出现虚线框。
- 从一列数中筛除尽可能少的数使得从左往右看,这些数是从小到大再从大到小的
- 取得一张表的数据不在另一张表中的最优秀方法(JOIN与EXISTS的效率研究)
- DataTable去掉重复行
- Android通信方式(三)------- Webservice概述
- OpenGL--图像
- $gopath not set的解决办法
- Java解析HTML之HTMLParser使用与详解
- modbus 入门篇,不冗长,很好理解!
- 白话IoC
- WordCount运行详解