Sql优化案例分析

1. 目的
   本文以多编号查询为例，结合以往sql优化的经验与互联网上主流的观点，从原理层面对其进行批判分析与总结，并通过实际的比对测试进行验证。本文的目的是以案例的形式，提供一种探究分析的思路和方法，在面对截然不同的环境与观点时，能够快速看清本质，给出最优方案。
2. 背景
   使用多个编号作为sql查询条件时，一般的选择是使用in和exists。
   下面列举一些开发中实际的案例：
3. 2016年，由于现场业务数据的增加，本人维护的某个平台（使用oracle数据库）很多模块出现了查询效率慢的问题，而后针对这些模块进行了一系列的sql优化，其中，效率提升最明显的两个优化：使用exists替代in，使用连接查询替换自定义函数。
4. 2019年上旬，公路EW3.0（使用pg数据库）测试阶段，多个模块的查询效率较低，后来扩展了Jpa的功能，使之支持自定义分页条件查询，自定义sql相比Jpa框架创建的sql，最大的变化是使用exists替代in。
5. 2019年中旬，港口组某项目出现编号过多时查询过慢的问题，同样是使用 exists替代in 进行处理。
6. 2019年下旬，公路网某平台出现客户端无法登录问题，经查是由于数据堆积导致年老代内存占用过大，系统响应超时。而数据消费慢的原因也是也是由于使用了in语句进行多编号查询。为防止后续再出现此类问题，公路网内部通过邮件阐述了这一问题，并发了一个规范，邮件名为“使用in查询多编号效率低问题”。
7. 在规范发出来后，不少同事对此感到困惑，进行了私下咨询，因为此规范与互联网上可以查询到的观点大相径庭。

互联网主流观点：
IN 是把外表和内表作hash连接，而 EXISTS 是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为 EXISTS 比IN 的效率高的说法是不准确。如果查询的两个表大小（数据量）相当，EXISTS 和 IN 的效率差别不大。
如果两个表中，其中一个表大(A表)，另一个表较小(B表)，则子查询表大的用 EXISTS，子查询表小的用 IN。

通过比对实际优化的案例，会发现跟互联网的主流观点几乎完全相悖。在优化的案例中，主表的数据一般是100w到3000w这个级别，使用多编号查询时，编号数量一般在0-10000的范围内。按子查询表小使用IN的说法，这种情况下使用IN的效率应该更高，但实际情况却恰好相反，拿一段优化前后的sql（简化后，使用pg数据库）进行比对：

优化前：
select t.* from t_highway_traffic_statistics t where t.crossing_index_code in (‘src1’,‘src2’,…,‘src200’)
优化后：
select t.* from t_highway_traffic_statistics t where exists ( select 1 from (
select ‘src1’ ::text as crossno union all select ‘src2’ union all … union all select ‘src200’
) t1 where t1.crossno = t.crossing_index_code )
为了解释上述现象，接下来，以实际环境对多编号查询的sql进行测试和分析，逐步解开多编号查询sql的优化原理，以及主流观点与实际应用产生分歧的本质。

3. 测试准备
   数据库版本： PostgreSQL 9.4.5
   测试表结构：
   CREATE TABLE public.t_highway_traffic_statistics ( --交通参数表
   traffic_statistics_id bigserial NOT NULL, – 主键(取序列递增)
   crossing_index_code varchar(256) NULL, – 卡口编号
   lane_no int4 NULL, – 车道号
   begin_time timestamp NULL, – 统计开始时间
   end_time timestamp NULL, – 统计结束时间
   speedaverage int4 NULL, – 车道速度
   CONSTRAINT t_highway_traffic_statistics_pkey PRIMARY KEY (traffic_statistics_id)
   );
   CREATE TABLE public.t_cross ( --卡口表
   crossing_index_code varchar(256) NOT NULL, – 卡口编号
   crossing_name varchar(256) NOT NULL – 卡口名称
   );
   表数据：
   t_highway_traffic_statistics 表插入600w数据（crossing_index_code 取值src1-src200，用于模拟200卡口，每个卡口插入数据的概率相同），
   t_cross 表插入200数据（crossing_index_code 分别为src1-src200，用于模拟200卡口信息）。
   场景模拟：
   查询交通参数时，用户勾选全部卡口作为查询条件，常规的查询条件为：
   crossing_index_code in (‘src1’,‘src2’,…,‘src200’)
   测试环境准备好之后，开始进行优化前后sql的比对测试和分析。
4. 测试和分析
   4.1 多编号列表查询
   优化前sql多编号查询：
   

优化后sql多编号查询：<