最近在数据库的使用和优化方面遇到很多问题。
不但将数据库从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
) ss
WHERE 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的数据分开获取。
反正知道原因还是很好解决的。