4、减少数据库服务器CPU运算
4.1、使用绑定变量
绑定变量是指SQL中对变化的值采用变量参数的形式提交,而不是在SQL中直接拼写对应的值。
非绑定变量写法:Select * from employee where id=1234567
绑定变量写法:
Select * from employee where id=? Preparestatement.setInt(1,1234567) |
Java中Preparestatement就是为处理绑定变量提供的对像,绑定变量有以下优点:
1、防止SQL注入
2、提高SQL可读性
3、提高SQL解析性能,不使用绑定变更我们一般称为硬解析,使用绑定变量我们称为软解析。
第1和第2点很好理解,做编码的人应该都清楚,这里不详细说明。关于第3点,到底能提高多少性能呢,下面举一个例子说明:
假设有这个这样的一个数据库主机:
2个4核CPU
100块磁盘,每个磁盘支持IOPS为160
业务应用的SQL如下:
select * from table where pk=?
这个SQL平均4个IO(3个索引IO+1个数据IO)
IO缓存命中率75%(索引全在内存中,数据需要访问磁盘)
SQL硬解析CPU消耗:1ms (常用经验值)
SQL软解析CPU消耗:0.02ms(常用经验值)
假设CPU每核性能是线性增长,访问内存Cache中的IO时间忽略,要求计算系统对如上应用采用硬解析与采用软解析支持的每秒最大并发数:
是否使用绑定变量 | CPU支持最大并发数 | 磁盘IO支持最大并发数 |
不使用 | 2*4*1000=8000 | 100*160=16000 |
使用 | 2*4*1000/0.02=400000 | 100*160=16000 |
从以上计算可以看出,不使用绑定变量的系统当并发达到8000时会在CPU上产生瓶颈,当使用绑定变量的系统当并行达到16000时会在磁盘IO上产生瓶颈。所以如果你的系统CPU有瓶颈时请先检查是否存在大量的硬解析操作。
使用绑定变量为何会提高SQL解析性能,这个需要从数据库SQL执行原理说明,一条SQL在Oracle数据库中的执行过程如下图所示: