1.SQL优化
SQL什么条件会使用索引?
当字段上建有索引时,通常以下情况会使用索引:
INDEX_COLUMN = ?
INDEX_COLUMN > ?
INDEX_COLUMN >= ?
INDEX_COLUMN < ?
INDEX_COLUMN <= ?
INDEX_COLUMN between ?and ?
INDEX_COLUMN in (?,?,...,?)
INDEX_COLUMN like ?||'%'(后导模糊查询)
T1. INDEX_COLUMN=T2. COLUMN1(两个表通过索引字段关联)
什么条件不使用索引?
INDEX_COLUMN <> ?
INDEX_COLUMN not in (?,?,...,?) 不等于操作不能使用索引
function(INDEX_COLUMN) = ?
INDEX_COLUMN + 1 = ?
INDEX_COLUMN || 'a' = ? 经过普通运算或函数运算后的索引字段不能使用索引
INDEX_COLUMN like '%'||?
INDEX_COLUMN like '%'||?||'%' 含前导模糊查询的Like语法不能使用索引
INDEX_COLUMN is null B-TREE索引里不保存字段为NULL值记录,因此IS NULL不能使用索引
NUMBER_INDEX_COLUMN='12345' 右边的值不是同类型的常量值,不能用
索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少?
这个没有固定的比例,与每个表记录的大小及索引字段大小密切相关,以下是一个普通表测试数据,仅供参考:
索引对于Insert性能降低56%
索引对于Update性能降低47%
索引对于Delete性能降低29%
2.选择合适SQL执行计划
3.返回更少的数据
3.1数据分页处理
4.只返回需要的字段
5.减少交互次数
5.1batch DML
6.In List
首先大部份数据库都会有SQL长度和IN里个数的限制,如ORACLE的IN里就不允许超过1000个值。
一般IN里面的值个数超过20个以后性能基本没什么太大变化,也特别说明不要超过100,
7.设置Fetch Size
当我们采用select从数据库查询数据时,数据默认并不是一条一条返回给客户端的,也不是一次全部返回客户端的,而是根据客户端fetch_size参数处理,每次只返回fetch_size条记录,当客户端游标遍历到尾部时再从服务端取数据,直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时,可以加大fetch_size,这样可以减少结果数据传输的交互次数及服务器数据准备时间,提高性能。
以下是jdbc测试的代码,采用本地数据库,表缓存在数据库CACHE中,因此没有网络连接及磁盘IO开销,客户端只遍历游标,不做任何处理,这样更能体现fetch参数的影响:
String vsql ="select * fromt_employee";
PreparedStatement pstmt =conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(1000);
ResultSet rs = pstmt.executeQuery(vsql);
int cnt =rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int i = 1; i <= cnt; i++) {
o = rs.getObject(i);
}
}
据测试结果建议当一次性要取大量数据时这个值设置为100左右,不要小于40。注意,fetchsize不能设置太大,如果一次取出的数据大于JVM的内存会导致内存溢出,所以建议不要超过1000,太大了也没什么性能提高,反而可能会增加内存溢出的危险。
7.存储过程过程引用的对像(表、视图等等)结构改变后,存储过程需要重新编译才能生效,在24*7高并发应用场景,一般都是在线变更结构的,所以在变更的瞬间要同时编译存储过程,这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。
8.ResultSet
如果我们采用jdbc原始的resultset游标处理记录,在resultset循环读取的过程中处理记录,这样就可以一次从数据库取出所有记录。显著提高性能。
这里需要注意的是,采用resultset游标处理记录时,应该将游标的打开方式设置为FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY),否则会把结果缓存在JVM里,造成JVM Out of memory问题。
代码示例:
String vsql ="select * fromt_employee";
PreparedStatement pstmt =conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(100);
ResultSet rs = pstmt.executeQuery(vsql);
int col_cnt =rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int j = 1; j <= col_cnt; j++) {
o = rs.getObject(j);
}
}
iBatis等持久层框架考虑到会有这种需求,所以也有相应的解决方案,在iBatis里我们不能采用queryForList的方法,而应用该采用queryWithRowHandler加回调事件的方式处理,如下所示:
MyRowHandler myrh=newMyRowHandler();
sqlmap.queryWithRowHandler("getAllEmployee", myrh);
class MyRowHandler implements RowHandler {
publicvoid handleRow(Object o) {
//todo something
}
}
iBatis的queryWithRowHandler很好的封装了resultset遍历的事件处理,效果及性能与resultset遍历一样,也不会产生JVM内存溢出。
9.服务器CPU运算使用绑定变量
绑定变量是指SQL中对变化的值采用变量参数的形式提交,而不是在SQL中直接拼写对应的值。
非绑定变量写法:Select * fromemployee where id=1234567
绑定变量写法:
Select * from employee where id=?
Preparestatement.setInt(1,1234567)
对于这种情况应该不使用绑定变量,而直接采用字符拼接的方式生成SQL,这样可以为每个SQL生成不同的执行计划,如下所示。
select count(*) from product wherestatus='approved'; //不使用索引
select count(*) from product wherestatus='tbd'; //不使用索引
select count(*) from product wherestatus='auditing';//使用索引
10.排序
以下列出了可能会发生排序操作的SQL语法:
Order by
Group by
Distinct
Exists子查询
Not Exists子查询
In子查询
Not In子查询
Union(并集),Union All也是一种并集操作,但是不会发生排序,如果你确认两个数据集不需要执行去除重复数据操作,那请使用Union All 代替Union。
Minus(差集)
Intersect(交集)
Create Index
Merge Join,这是一种两个表连接的内部算法,执行时会把两个表先排序好再连接,应用于两个大表连接的操作。如果你的两个表连接的条件都是等值运算,那可以采用Hash Join来提高性能,因为Hash Join使用Hash 运算来代替排序的操作。具体原理及设置参考SQL执行计划优化专题。
减少比较操作
我们SQL的业务逻辑经常会包含一些比较操作,如a=b,a<b之类的操作,对于这些比较操作数据库都体现得很好,但是如果有以下操作,我们需要保持警惕:Like模糊查询,如下所示:
a like ‘%abc%’
Like模糊查询对于数据库来说不是很擅长,特别是你需要模糊检查的记录有上万条以上时,性能比较糟糕,这种情况一般可以采用专用Search或者采用全文索引方案来提高性能。
11.并行处理
数据库并行处理是指客户端一条SQL的请求,数据库内部自动分解成多个进程并行处理,如下图所示:
并不是所有的SQL都可以使用并行处理,一般只有对表或索引进行全部访问时才可以使用并行。数据库表默认是不打开并行访问,所以需要指定SQL并行的提示,如下所示:
select/*+parallel(a,4)*/ * fromemployee;
并行的优点:
使用多进程处理,充分利用数据库主机资源(CPU,IO),提高性能。
并行的缺点:
1、单个会话占用大量资源,影响其它会话,所以只适合在主机负载低时期使用;
2、只能采用直接IO访问,不能利用缓存数据,所以执行前会触发将脏缓存数据写入磁盘操作。
注:
1、并行处理在OLTP类系统中慎用,使用不当会导致一个会话把主机资源全部占用,而正常事务得不到及时响应,所以一般只是用于数据仓库平台。
2、一般对于百万级记录以下的小表采用并行访问性能并不能提高,反而可能会让性能更差。