Mybatis+SQL Server使用过程中的优化

最近在数据库的使用和优化方面遇到很多问题。

不但将数据库从MySQL改到了SQL Server，在SQL语句的调优方面也下了很多功夫。

在这里将遇到的问题做个总结。

一. 查询速度优化

影响查询速度的原因很多，查询的列数，Join表，索引，检索条件等。

1.首先，我减少了查询的字段。

比如某个表有ABCD4个字段，一个画面需要AB，另一个画面需要ABCD，其他检索条件都一样。

如果你做成1个SQL，每次都取出ABCD4个字段的话，速度会慢。

所以如果改成根据不同画面，获取不同字段，或者分2个SQL，会加快查询速度。

2.索引

对一些常用字段建索引可以大幅提高查询速度。

但是不宜过多，如果对一个表中所有字段建索引，其实并没有效果，仍然是全表遍历。

3.JOIN

当A表 JOIN B表时，A表数据>B表数据的时候，查询比较快。反之则慢。

4.传值：

Mybatis的标准写法是：

SELECT a.*FROM A a WHERE a.del_flag = #{delFlag}and a.year =#{year}

这种写法在SQL语句里面执行很快（4s），但是在代码里面跑的时候很慢（40s）。

后来改成这种写法就快很多了：

SELECT a.*FROM A a WHERE a.del_flag = '${delFlag}'and a.year ='${year}'

二. 分页

我用了2种方法Top和row_number：

1. Top的写法：

SELECT TOP ${pageFrom} *FROM fp_pkr a

ORDER BY a.type

比如想要获取90-100条数据，pageFrom设为100，取得100条以后在程序中截取90-100的数据。

2.row_number的写法：

SELECT *FROM (SELECT *,row_number() over( order by type) AS "rowNumber"FROM (SELECT DISTINCT*FROM fp_pkr a

LEFT JOIN b on a.type = b.type) s ) ssWHERE ss.rowNumber between ${pageFrom}' and ${pageTo}'

（rowNumber不放在里面是因为里面必须要做DISTINCT，有人说可以用partition by去重复，

不使用partition by是因为partition by对分组内的数据赋予行号，而不是对所有数据，因此会导致无法分页）

我用100w的数据测试下来，row_number的数据获取稳定在12-18s左右，

top的方法在获取前10w的时候，比row_number快很多，在5s左右，

但是当top 30w的时候，速度就跟row_number差不多了，

当top 100w的时候，需要45s。

鉴于用户不可能在几百万数据的时候，一页一页翻到最后，我还是采用了top的方法，推荐用户增加检索条件减少数据量。

不过根据不同的场合，用row_number也可以。

三. 查询大数据时OOM

在查询数据量很大，比如百万条数据或者列很多的数据的时候，查询虽然不慢，但是读到内存里面的时候可能会OOM。

网上比较多的建议是使用fetchSize，设置每次读取的数据行数，分多次读取以防止一次读取的时候数据量过大。

可是我这么加的：

<select id="findListByCity" fetchSize="10000" resultType="FpPkr">

但好像不起作用，可能是因为我本地调试，开发机器性能不高，看了下CPU和内存，都接近100%的使用率了。

不过没关系，我改用将一个SQL手动分批获取，可以实现同样的效果：

比如一个表里有100w条数据，有不同的type10种。我根据type循环，将每种type的数据分开获取。

反正知道原因还是很好解决的。