好开发

部分同学可能不明白什么是in list的SQL，这里先说明一下，所谓in list的SQL就是指使用了in来进行查询，绑定变量个数不确认的SQL，如:

select * from test where id in (:1,:2,:3)

对于这一类的查询，由于in的查询条件中绑定变量个数的不同，会导致SQL版本变多，从而导致PreparedStatementCache的命中率下降。（因为JBOSS/oracle中都是以SQL文本完全一致来匹配PreparedStatementCache）。

因为PreparedStatementCache在MYSQL中没有作用（根本原因是MYSQL不支持绑定变量），所以MYSQL不需要考虑in list在PSCACHE中的优化，下面给出oracle中两种in list sql的解决方案：

方案1：使用oracle中的pipelined function（可以认为是一种性能较好的存储过程），实现如下函数：

create or replace type t_array
    as table of varchar2(4000)
/
--The PIPELINED keyword on line 4 allows this function
-- to work as if it were a table: 

create or replace function
  str2varlist(p_string in varchar2)
  return t_array
  PIPELINED
  as
   v_str    VARCHAR2 (4000)          DEFAULT p_string || ',';
   v_n      number(11);
  begin
        LOOP
      v_n                        := INSTR (v_str, ',');
      EXIT WHEN (NVL (v_n, 0) = 0);
      pipe row(LTRIM (RTRIM (SUBSTR (v_str, 1, v_n - 1))));
      v_str                      := SUBSTR (v_str, v_n + 1);
   END LOOP;

     return;
  end;
/

这个函数的作用是将以逗号分隔的字符串转换成一个table，如下:

zhoucang@zhoucang>select * from TABLE(str2varlist('123,456,789,012,345'));

COLUMN_VALUE
---------------------------------------------------------------------------
123
456
789
012
345

5 rows selected.

这样，每次我们的SQL执行时，只要传入一个固定长度的字符串即可,如：

select * from test where id in(select * from TABLE(str2varlist(:1)))
或者写成join的方式：
select /*+ use_nl(a b) ordered*/ b.* from
TABLE(str2varlist(:1)) a,test b
 where a.column_value = b.id

这也不失为一种好方法，至少使用起来非常的方便。

方案2：这是一种看起来很土的方法，同事建议的，真的很土，即固定in后面条件的个数，每次传入不同的值，不足的可以使用一个不存在的值来进行补充。如：

select  * from test where id in(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
固定10个ID的查询，不足10个以-1来补充（如果ID不为负数）。

因为oracle中的in限制最多为999个，所以，极端情况下，也只需要固定999个ID即可。

如何选择方案1还是方案2？

专门测试了一下，测试过程主要逻辑如下：

方案1，创建pipelined function,执行20000次查询：

	sql = " select /*+ use_nl(a b) ordered*/ b.* from
				TABLE(str2varlist(:1)) a,test b
 					where a.column_value = b.id ";
for (int i = 0; i < 20000; i++) {
				stmt1.setString(1,"1111,2222,3333");
}

方案2，固定绑定变量个数，执行20000次查询：

	sql = "select  * from test where id in(?,?,?,?,?,?,?,?..........) ";
	for (int i = 0; i < 20000; i++) {
				stmt.setString(1, "1111");
				stmt.setString(2, "2222");
				stmt.setString(3, "3333");
				stmt.setString(4, "-1");
				stmt.setString(5, "-1");
				stmt.setString(6, "-1");
                 ……
}

测试主要以数据库的性能损耗为主要考虑指标（因为应用上带来的好处对我们来说诱惑力实在太大了，基本上没什么坏处，性价比很高），测试结果如下（给出数据库中单个SQL主要的性能参考指标）：

说明:	应用总执行时间：秒	内存读（块）	返回记录数	单条SQL CPU时间(us)	单条SQL响应时间
3个绑定变量，传3个id	38	9	3	75.84365	75.84365
20个绑定变量，传3个id	39	10.06	3	94.48995	94.48995
100个绑定变量, 传3个id	43	10.05955	3	112.8007	112.8007
PPLINE,3个id	41	9.01435	3	149.5074	149.5074
100个绑定变量，传10个id	53	31.05955	10	220.55765	220.55765
10个绑定变量,传10个id	43	30	10	174.7577	174.7577
PPLINE,10个id	51	30.03795	10	358.46375	358.46375

PPLINE方案（方案1）：

1． CPU：会增加CPU的消耗1倍左右，原因是数据库需要对字符串进行解析。
2． IO:对IO基本无损耗。
3． 响应时间:数据库响应时间增加一倍。

固定绑定变量个数（方案2） ：

1． CPU：CPU的消耗，增加幅度在20%~60%，之间，具体因in的个数而定。
2． IO:增加了一个逻辑读，这一个逻辑读是由于查询-1时产生的，主要是由于索引branch节点查询引起，由于root节点一定需要查询，索引一般为3-4层，可以认为，这个逻辑读开销在1-3个左右，基本上可以认为在1个左右（这个可以结合B树索引结构得知）。并且这个branch节点非常热，IO开销肯定是逻辑读。
3． 响应时间: 增加幅度在20%~60%，之间，具体因in的个数而定。

显然，选择方案2会更加节省数据库的资源。

从测试结果可以看出，当传入3个ID的时候，使用100个绑定变量和20个绑定变量，还是存在一定的差异。因此，对于in的SQL我们可以再进行分级，根据执行频率，评估适当的多给出几个版本，如：
Id=:1 :适用于1个ID的查询
Id in(20个绑定变量) :适用于ID个数据在2-20个之前的查询。
Id in (1000绑定变量) :适用于ID个数在20个以上的查询。