SQL优化

原创 2012年03月31日 10:29:03

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性能优化大全

  • 2015年06月11日 16:18
  • 68KB
  • 下载

SQL优化秘籍

  • 2015年05月28日 15:43
  • 31KB
  • 下载

Oracle SQL优化 总结

之前的blog中零零散散的整理了一些优化相关的内容,找起来比较麻烦,所以总结一下,查看的时候方便一点。这篇BLog只看SQL 优化的相关的注意事项,数据库优化部分以后有空在整理。 SQL 的优化主要涉...

JAVA(SQL语句的优化)

  • 2017年10月20日 18:05
  • 2.29MB
  • 下载

oracle_sql性能优化

  • 2015年11月24日 16:20
  • 2.32MB
  • 下载

Sql语句优化-查询两表不同行NOT IN、NOT EXISTS、连接查询Left Join

在实际开发中,我们往往需要比较两个或多个表数据的差别,比较那些数据相同那些数据不相同,这时我们有一下三种方法可以使用:1. IN或NOT IN,2. EXIST或NOTEXIST,3.使用连接查询(i...

SQL-Server优化指导

  • 2015年10月11日 18:10
  • 354KB
  • 下载

sql server优化

  • 2015年04月28日 10:13
  • 2KB
  • 下载

sql优化--in和exists效率

in 和exists in是把外表和内表作hash 连接,而exists 是对外表作loop 循环,每次loop 循环再对内表进行查询。 一直以来认为exists 比in 效率高的说法是不...

优化大全sql(绝对全)

  • 2016年04月28日 12:00
  • 5KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL优化
举报原因:
原因补充:

(最多只允许输入30个字)