MYSQL IN 与 EXISTS 的优化示例介绍，mysql exists

MYSQL IN 与 EXISTS 的优化示例介绍，mysqlexists

优化原则：小表驱动大表，即小的数据集驱动大的数据集。

############# 原理 (RBO) #####################

select * from A where id in (select id from B)
等价于:
for select id from B
for select * from A where A.id = B.id

当B表的数据集必须小于A表的数据集时，用in优于exists。

select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id

当A表的数据集系小于B表的数据集时，用exists优于in。

注意：A表与B表的ID字段应建立索引。

例如：

/** 执行时间：0.313s **/
SELECT SQL_NO_CACHE * FROM rocky_member m WHERE EXISTS (SELECT 1 FROM rocky_vip_appro a WHERE m.ID = a.user_id AND a.passed = 1);
/** 执行时间：0.160s **/
SELECT SQL_NO_CACHE * FROM rocky_member m WHERE m.ID in(SELECT ID FROM rocky_vip_appro WHERE passed = 1);

not in 和not exists用法类似。

mysql exists与in 具体不同在什地方

in 是把外表和内表作hash 连接；
exists 是对外表作loop循环，每次loop循环再对内表进行查询。

一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

希望对你有帮助。
 

MYSQL NOT IN优化

算法上存在很大问题。我们先来分析该算法的执行次数。
按照你的方法，record表中的id字段要全部查询一遍，也就是2W次查询，而每次查询，最坏

情况下需要与offline_record中的rec.id进行4W次比较，这又导致offline_record表的4W次

查询（取rec_id ）。假设满足
a.* from record a where a.id not in(select b.rec_id from offline_record);
条件的记录一共有N条，那么，最坏情况下，该算法所做的查询次数为：
2W(取record.id)+2W*4W(每取一次record.id就要取一次offline_record.rec_id且offline_record的最后一条数据满足条件)+N(每

条满足条件的记录需要再在record中取该记录全部数据)
所做的比较次数为：
2W*4w

考虑最好情况下的效率，该算法所做的查询次数为：
2W(取record.id)+2W*1(每取一次record.id就要取一次offline_record.rec_id且offline_record的第一条数据满足条件)+N(N(每

条满足条件的记录需要再在record中取该记录全部数据)
所做的比较次数为：
2W*1
因此，该算法平均查询次数为：
2W+(4w*2w+1)*2w/2+N ->8*10^12
天文数字！这还不考虑将近4亿次的平均比较次数，所以你的执行效率当然低了

下面，我们对该算法来进行优化：
算法主要解决的问题是，取表record中id不等于offline_record.rec_id的数据。现假定id为record的主键(你的问题没有指明，但是你会看到无论id是否主键都不影响分析)，设计算法如下：
1、取offline_record.rec_id的结果为集合，并对该集合进行排序，设最终生成的集合为A 。则，查询数据库4w次，生成集合的算法按照O(N*ln N)的效率来算平均情况下比较O(4W*ln 4w),约等于64W次，排序次数按照O(N*ln N)的效率来算平均情况下比较O(4W*ln 4w)，约等于64W次。

2、顺序取record中的id与第一步生成的集合A进行比较，从而得出最终结果。该过程中由于record.id与A均为有序表，所以比较次数为2w次，查询次数为2w+N次。

如上算法，查询次数为 4W+2W+N=6W+N次，平均比较次数为 64W+64W+2W=130w次。
显而易见，该算法对原算法进行了最大的优化，大概将速度提高了10*8倍。
考虑到对数据库的查询时间远远大于排序比较时间，改进厚的算法在实际操作中还会有更好的表现。

至于你对mysql查询语句的优化，则是治标不治本之举，虽然有用，但毕竟是微小量变，不足与影响全局，在一个坏的算法下，几乎不能提升性能。