多表查询笔记

大兄弟 2018年06月16日0   285

关于数据库的in和join的效率问题

1.百度知道用户

JOIN表示连接两个表,分为外连接和内连接,通常用于带出关联关系的其他表的字段信息,比如我这里有用户ID想查出,用户的姓名,可以外连接用户表,带出姓名字段。
IN这个关键字有性能要求,如果IN里面的选项大于1000个,性能下降的非常快,可以用exist来代替 ,具体用法可以查一下,资料有一堆。

2.百度知道用户,给出了exists的效率比较

EXISTS、IN与JOIN,都可以用来实现形如“查询A表中在(或不在)B表中的记录”的查询逻辑。

在查询的两个表大小相当的情况下,3种查询方式的执行时间通常是:
EXISTS <= IN <= JOIN
NOT EXISTS <= NOT IN <= LEFT JOIN
只有当表中字段允许NULL时,NOT IN的方式最慢:
NOT EXISTS <= LEFT JOIN <= NOT IN

但是如果两个表中一个较小,一个较大,则子查询表大的用exists,子查询表小的用in,因为in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。而无论那个表大,用not exists都比not in要快。这是因为如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。

IN的好处是逻辑直观简单(通常是独立子查询);缺点是只能判断单字段,并且当NOT IN时效率较低,而且NULL会导致不想要的结果。
EXISTS的好处是效率高,可以判断单字段和组合字段,并不受NULL的影响;缺点是逻辑稍微复杂(通常是相关子查询)。
JOIN用在这种场合,往往是吃力不讨好。JOIN的用途是联接两个表,而不是判断一个表的记录是否在另一个表。