SQL优化

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、一般对于百万级记录以下的小表采用并行访问性能并不能提高,反而可能会让性能更差。

 

 

 

阅读更多
想对作者说点什么? 我来说一句

sql优化

2011年06月14日 31KB 下载

SQL优化 SQL 优化

2010年03月10日 1.2MB 下载

海量数据优化查询SQL

2011年04月02日 80KB 下载

SQL常见的优化

2018年03月30日 4KB 下载

SQL优化 SQL优化

2010年03月06日 1.48MB 下载

没有更多推荐了,返回首页

不良信息举报

SQL优化

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭