关于in与exists的sql优化(记录人生中第一次sql优化)

这是一个阳光明媚的下午，刚端起咖啡的我，余光瞥到了电脑右下角弹出的工作群消息，不禁心头一颤。在一番激烈的心理活动后，我还是先喝了一口咖啡，再点开了消息。因为我知道，如果先点开消息，那今天可能喝不到咖啡了，至少，喝不到热咖啡了。
“生产问题，数据超过6000条时，查询数据接口超时，@土豆，跟踪一下”
“我的偏头痛可能就是这样的消息看多了吧”，我心想着，麻利地敲下两个字：“收到”
问题定位很简单，查询的sql太复杂，优化不够，导致接口超时。
原sql如下：

<select id="selectOrderByConditon" paramType="xxx" resultMap="baseType">
	select * from customer c
	where c.del_flag = 0 and c.status != '已废弃'
	<if test="orderInfo != null and orderInfo != ''">
		and (
			c.name = #{info,jdbcType=VARCHAR} or exists (
				select 1 from order o where c.id = o.costomer_id and o.address = #{info,jdbcType=VARCHAR} or exists (
					select 1 from onlineShop s where s.order_id = o.id and c.id = o.costomer_id and (
						s.name = #{info,jdbcType=VARCHAR} or s.id = #{info,jdbcType=VARCHAR}
					) 
					or exists (
						select 1 from courier co where co.order_id = o.id and c.id = o.costomer_id and co.phone = #{info,jdbcType=VARCHAR}
					)
				)
			)
		)
	<if/>
<select/>

此查询sql的需求是，在一个文本框中，可输入：客户姓名/订单送货地址/店家名/店家id/送货员电话中任意一个，查询出数据。
为复现bug，我又向测试库中加了100000条数据，批量加入数据的sql脚本：

do $$ declare v_idx integer:=10000;
begin while v_idx<110000 loop v_idx=v_idx+1;
insert into order (,,,,,,,,,,) values (,,,,,,,,,,);
end loop;
end $$;

哦，对了，项目用到的数据库是pgsql，继续。
在经过了长达一下午的面向百度编程后，终于完成了优化，优化后的sql如下：

<select id="selectOrderByConditon" paramType="xxx" resultMap="baseType">
	select * from customer c
	where c.del_flag = 0 and c.status != '已废弃'
	<if test="orderInfo != null and orderInfo != ''">
		and (
			c.name = #{info,jdbcType=VARCHAR} or c.name in (
				select o.id from order o where o.address = #{info,jdbcType=VARCHAR} or o.id in (
					select s.order_id from onlineShop s where (
						s.name = #{info,jdbcType=VARCHAR} or s.id = #{info,jdbcType=VARCHAR}
					) 
					or o.id in (
						select co.order_id from courier co where co.phone = #{info,jdbcType=VARCHAR}
					)
				)
			)
		)
	<if/>
<select/>

优化前，查询超时（至少30秒），优化后，查询0.35s。
总结：
对于in和exists的性能区别：
如果子查询得出的结果集较少，主查询中的表现较大且又有索引时，应该用in；反之，外层表的主查询记录少，子查询中的表大，则用exists。
根本原因是，in和exists的驱动顺序不一样。如果是exists，那么以外层表为驱动表，先被访问，如果是in，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。

另外记录一下关于分析器对sql的执行原理：分析器会先看语句的第一个词，当它发现第一个词是select关键字时，它会跳到from关键字，然后通过from关键字找到表名并把表装入内存。接着是找where关键字，如果找不到则返回到select找字段解析 ，如果找到where，则分析其中的条件，完成后再回到select分析字段。最后形成一张我们要的虚表。
where关键字后面的是条件表达式。条件表达式计算完成后，会有一个返回值，即0或非0。同理where后面的条件也有一个返回值，真或假，来确定接下来执不执行select。
分析器先找到关键字select，然后跳到from关键字将customer表导入内存，并通过指针找到第一条记录，接着找到where关键字计算它的条件表达式，如果为真，那么把这条记录装到一个虚表当中，指针再指向下一条记录。如果为假，那么指针直接指向下一条记录，而不进行其它操作。一直检索完整个表，并把检索出来的虚拟表返回给用户。exists是条件表达式的一部分，它也有一个返回值。