一个导数需求的演进过程

拼搏现实的明天。 2023-06-26 08:24 124阅读 0赞

最近有个导数的需求，下图所示，将数据库A中表A三个月大约3000万数据，导入数据库B的表B，要求尽可能快地完成。这3000万数据，是相对静态的，三个月的数据，当前不会对其作增删改查操作。表B大约15个字段，都是普通的字符串类型，行长200字节左右。

![format_png][]

![format_png 1][]

对这个需求，其实有很多种方案，例如，

1.通过spool，首先将数据库A的3000万数据，导出到文件中，然后通过sqlldr，将其导入数据库B。

2.数据库B中创建数据库A的dblink，通过insert ... select的形式导入。

3.通过程序，可以是PL/SQL，或者Java，读取数据库A，插入数据库B。

应用开发人员，选择的方案3，但是在实现的过程中，还是有些曲折。

**1. 第一次操作报错**

应用开发人员，用Java通过JDBC，读取数据库A中表A三个月的数据(支持输入开始和结束日期为参数，控制每次导入的数据量)，批量导入数据库B，每5000条记录，提交一次，但是首次执行，就提示了错误，

![format_png 2][]

搜了下，提示这个错误，可能和JDBC的jar版本相关，10g的驱动，要求一次使用的变量个数不能超过32768，经过确认，应用使用的jdbc，是ojdbc14，对应的就是Oracle 10g，而且插入的表，一共15个字段，一次提交5000条，显然已经超了32768，

> The 10g driver apparently keeps a global serialnumber for all parameters in the entire batch, with a "short" variable. So you can have at most 32768 parameters in the batch. I was having the same exception because I have a INSERT statement with 42 parameters and mybatches can be as big as 1000 records, so 42000 > 32768 and this overflowsto a negative index. I reduced the batch factor to 100 to be safe, and all iswell. I guess your update DML should have a larger number of parameters perrecord, right? (My diagnostic of the bug is just deduction from the symptoms)

P.S. Oracle-jdk-jdbc jar的对应关系，

![format_png 3][]

解决方案，

1. 缩小一次提交的个数。

2. 更新jdbc jar。

方案2更靠谱。

**2. 首次执行的性能**

解决了这个错误，可以正常执行，但经过测试，按照3000万的量，导入时间需要10个小时，还可以优化么？

**3. 第一次优化**

我们看下，这个程序是否存在优化的空间。通过向程序中加入断点，发现每次执行时，都是慢在了读取数据库A，看了下代码，豁然开朗，他写的SQL中where条件，开始和结束日期，都对左值用了to\_char函数进行了转换，这个A\_DATE是DATE类型，存在索引，当用了to\_char函数，将其转成字符串，用来和右值字符串进行比较，这个索引会失效，执行全表扫描，换句话说，每次检索数据，都会对这张千万级的表，扫所有的数据块，

select ...
    where to_char(A_DATE,'yyyy-mm-dd') >='"+BeginTime
    and   to_char(A_DATE,'yyyy-mm-dd') <='"+EndTime

这种索引字段使用函数的操作，可能是开发人员最容易忽视的一个常见错误，尤其是在没什么数据量的开发、测试环境，可能这个问题，并不会暴露出来，一旦到了生产，数据量上来了，性能问题，就会非常明显。

解决方案很简单，就是将左值的to\_char，改为右值的to\_date，

select ...
    where A_DATE >= to_date('"+BeginTime+"','yyyy-mm-dd')
    and   A_DATE >= to_date('"+EndTime+"','yyyy-mm-dd')

经过测试，这个方案导入3000万的数据，大约需要150分钟，和10个小时相比，性能提升了。

**4. 第二次优化**

还是通过程序导入执行中设置的断点信息，发现了个规律，每10条记录的插入操作，时间非常近，如图所示，虽然两个10条之间的时间间隔，只是毫秒级但是3000万记录，会存在300万个10条，累积起来，这个间隔时间，就会非常长了，这是为什么？

![format_png 4][]

当前逻辑如下，

...
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
      try {
        pstmt.setInt(1, startNum + count);
        ...
        pstmt.addBatch();
        if (count % batchNum ==0 || count == rowCount ) {
        ...
        pstmt.executeBatch();
        pstmt.getConnection().commit();
        pstmt.clearBatch();
        successNum ++;
      } catch (Exception e) {
        ...
      }
    }

其实，之前写了一篇《[JDBC读取数据优化-fetch size][JDBC_-fetch size]》，就告诉了答案，这个和JDBC的使用设置有关，《Database JDBC Developer's Guide》有一节介绍了Fetch Size，

> By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value.
> 
> Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object.
> 
> Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.
> 
> Changes made to the fetch size of a statement object after a result set is produced will have no affect on that result set.

JDBC默认每执行一次检索，会从游标中提取10行记录，10就是默认的row fetch size值，通过设置row fetch size，可以改变每次和数据库交互，提取出来的记录行总数。需要注意的是，需要在获得检索结果集之前，设置fetch size，否则就是无效。

可以使用如下方法设置，

> ### Setting the Fetch Size ###
> 
> The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:
> 
>  *  void setFetchSize(int rows) throws SQLException
>  *  int getFetchSize() throws SQLException

简单来讲，Fetch相当于读缓存，默认Fetch Size值是10，读取10000条记录，一次数据库交互，即rs.next的操作，ResultSet会一次性从数据库服务器，得到10条记录，下次执行rs.next，就直接使用内存读取，不用和数据库交互了，但总计需要有1000次交互，如果使用setFetchSize设置Fetch Size为10000，则只需要一次数据库交互，本地缓存10000条记录，每次执行rs.next，只是内存操作，不会有数据库网络消耗，效率就会高些。但需要注意的是，Fetch Size值越高则占用内存越高，要避免出现OOM错误。

解决方案1：

> rs = ps.executeQuery();  
> 
> 
> rs.setFetchSize(10000);

即在执行ps.executeQuery()之后，对rs设置值10000。

解决方案2：

> ps = conn.prepareStatement(sql);  
> ps.setFetchSize(10000);

即在执行conn.prepareStatement(sql)之后，执行ps.executeQuery()之前，对rs设置值为10000范围。

这两种，总用时几乎一致，但SQL执行和rs.next遍历的用时，有些区别。

针对方案1，

> After you have run the query, you can call `setFetchSize` on the result set object to override the statement object fetch size that was passed to it. This will affect any subsequent trips to the database to get more rows for the original query, as well as affecting any later refetching of rows.

执行查询之后，对结果集设置setFetchSize，会影响任何接下来的数据库交互过程获得更多的记录行数，以及之后的fetch提取。

针对方案2，

> To set the fetch size for a query, call `setFetchSize` on the statement object prior to running the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

执行查询之前，设置setFetchSize，表示每次和数据库交互，得到记录行数。

综上所述，建议执行SQL之前，设置此值，效率提升最高。

应用修改如下，

...
    pstmt = getConn().prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    pstmt.setFetchSize(fetchSize);
    ResultSet rs = pstmt.executeQuery();
    ...

通过设置fetch size，经过测试，导入3000万数据，需要50分钟，相比150分钟，性能又提升了。

**5. 第三次优化**

此时设置的一次插入10000条提交一次，但是当执行到20万的时候，出现了内存溢出，根据生成的dump，应该和结果集有关系，

![format_png 5][]

![format_png 6][]

![format_png 7][]

再看下Code，看到prepareStatement有两个参数，他们做什么的？

ResultSet.TYPE\_SCROLL\_INSENSITIVE

ResultSet.CONCUR\_READ\_ONLY

...
    pstmt = getConn().prepareStatement(sql,
                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                      ResultSet.CONCUR_READ_ONLY);
    ...

他的通用格式为：

Statement stmt=con.createStatement(int type，int concurrency);

我们在访问数据库的时候，在读取返回结果的时候，可能要前后移动指针，比如我们先计算有多少条信息，这是我们就需要把指针移到最后来计算，然后再把指针移到最前面，逐条读取，有时我们只需要逐条读取就可以了。还有就是有只我们只需要读取数据，为了不破坏数据，我们可采用只读模式，有时我们需要望数据库里添加记录，这是我们就要采用可更新数据库的模式。

参数int type可用值，

1.ResultSet.TYPE\_FORWORD\_ONLY，结果集的游标只能向下滚动。

2.ResultSet.TYPE\_SCROLL\_INSENSITIVE，结果集的游标可以上下移动，当数据库变化时，当前结果集不变。

3.ResultSet.TYPE\_SCROLL\_SENSITIVE，返回可滚动的结果集，当数据库变化时，当前结果集同步改变。  
  
参数 int concurrency可用值，

1.ResultSet.CONCUR\_READ\_ONLY，不能用结果集更新数据库中的表。

2.ResultSet.CONCUR\_UPDATETABLE，能用结果集更新数据库中的表。

对于TYPE\_SCROLL\_INSENSITIVE，一次查询的结果可能存在数据库端的内存缓冲中，也可以直接发送到JVM的内存中，如果结果集很小，会直接发送到JVM层，然后被next定位，转换数据类型，显示，或者缓存在数据库内存中。总之查询结果已经和数据库脱离，这时如果数据库记录被其它进程更新，则结果集无法得知，还是使用缓存的记录。

而对于TYPE\_SCROLL\_SENSITIVE，一次查询的结果并不是直接的记录被缓存下来，只是符合条件的记录的“原始ROWID”被缓存了，这个原始ROWID并非特指ORACLE的ROWID，而是数据库底层定位记录的索引值。对于更新操作，如果你先查询，然后数据被其它进程更新掉了，然后next到这条记录时肯定没有问题，会取出最新的内容，但对于删除操作。因为数据库删除记录只是记录上做一个标记，不再被检索，但原来被缓存的ROWID还在，根据它还可以通过数据库自己的底层操作正确地把数据提取出来，所以你看到的已经被手工删除的数据又被显示出来了。同样插入操作因为查询的时候结果集中还没有要插入的操作，所以不可能缓存了它的ROWID。

通过和开发人员的确认，此处使用ResultSet.TYPE\_SCROLL\_INSENSITIVE，是为了当批量操作的时候，如果中间有一条提示错误，想根据这种操作，能够跳过这条，继续执行其他的。此处提示溢出，还是因为他会缓存一次读取数据库A的数据集太大了，超过了JVM设置的内存大小。

解决方案1，

调小批量提交的数量。

解决方案2，

不再使用type和concurrency参数，

pstmt = getConn().prepareStatement(sql)

从这个案例中，能得到很多经验，

1.从应用设计看，对重要的操作，一定要记录日志，或者输出控制台，例如执行可疑SQL的时间，否则像上面这个问题，如果没记录时间，我怎么知道什么操作导致导数缓慢？此外，应用支持的灵活性上，也是需要考虑的，例如导数程序，支持时间段作为参数，可以人为控制一次导入数据量，不是只支持一次性导入全部的数据。

2.优化，往往难的不是从10小时降低到150分钟，而是150分钟降到50分钟，幅度越小，可能隐藏的问题就会越深，你要了解的就会越多，无论是数据库，还是应用层面，都是这个理儿。

3.对开发来说，一些SQL的基本性能问题，或者易错点，应该有意识积累，例如索引字段不能使用函数，但是从另个角度，术业有专攻，我们能通过一些技巧，帮助他们发现这些问题，这个最近有所实践了，找个机会，可以和各位分享。

4.技术问题，有时候就靠积累，或者说碰到的越多积累就越多，不怕掉坑，就怕掉到同一个坑两次。

5.有时候，一个问题，没有最佳方法，只是有最合适的方法，根据不同的场景，选择最合适的，才是需要的。

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWwyZmdRandJb0lnaWM1VWMzZzMxVUxpYXlIaWJPb1ZCV1hBc1RxQm5pYWV5T3I4ZWY4WUhpYmt4S2MwdjZHYk5ZcGxyWXRLU0N0OEhVcUdsdy82NDA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWwyZmdRandJb0lnaWM1VWMzZzMxVUxpYVhhTlNRYVJxVVJ3MDVpYVZBbDB3a3NZNlNSQzluWlpuSXlGamlhb1VCam9NOUtGQndpYU1YQVdpY3cvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW4yWlgxSEFEaWNKdEtmWnJ0UTdBSkhzUTVHbU93T2pmUUg2UlpTSGNpYm1ET3VBck5wZWVRc29RZFB5ZEZJNVFSMzA1ODZQNGtaT2E4Zy82NDA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW4yWlgxSEFEaWNKdEtmWnJ0UTdBSkhzUHBkNkw2cm1VRUwwRXhnSDhxQjlXVHFpY01WUjMxRkxaRnVtMG15Z0h2Q0xBNDFFSEgzWmliNGcvNjQw?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWxvbjlFOHE0cFc5Z1VhZGpZcFhpY0phOUJpY0ZXRXMyZWVoTjBtN0N4aWFFTTVEckUwZFRPRTFpY05FU2lhdWNOdEluVHM4MkcxdTBBd0hkZy82NDA?x-oss-process=image/format,png
[JDBC_-fetch size]: https://blog.csdn.net/bisal/article/details/82735614
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWxvbjlFOHE0cFc5Z1VhZGpZcFhpY0phVkFpYVg0R2x0NXpxNnJEaWJXUnV4Z09vdkFsM0pQZXRUbGliNWJxeUxVd2ljMHNYVGliSjZOb29vZHcvNjQw?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWxvbjlFOHE0cFc5Z1VhZGpZcFhpY0phMVd2V0VMNVVqakl1WmNNV1dqVmQ3MEJ1Rlk5ckhiYzBNZTIxYjZHRndTRGd5SVhjZTF3d0tnLzY0MA?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWxvbjlFOHE0cFc5Z1VhZGpZcFhpY0phaWJCY0Z1UWlhTXVXTnFpY1luc0FGMENRTktKZ2FQZHRIWEo5THVqU2liMDh0Nmljb0hMWUI5OVNsblEvNjQw?x-oss-process=image/format,png