数据库（五）数据库访问性能优化

       一个优良的系统，性能问题是我们必须考虑的问题，而数据库的性能瓶颈也是我们不得不面对的问题，本章我们将探讨一下关于数据库访问性能优化方面的问题。
.1)数据库访问涉及到的层面
        CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算。
        网            络：结果数据传输、SQL请求、远程数据库访问（dblink）。
        硬            盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。
.2)数据库优化基本法则

这个优化归纳为5个层次：
（a） 减少数据访问（减少磁盘访问）
（b）返回更少数据（减少网络传输或磁盘访问）
（c） 减少交互次数（减少网络传输）
（d）减少服务器CPU开销（减少CPU及内存开销）
（e）利用更多资源（增加资源）

下面我们针对上面的优化法则一一说明。

（a）减少数据库访问

         减少数据库访问，简而言之，就是尽量减少系统与数据库的交互，因为对数据库的访问必然存在性能的消耗，我们可以从以下几个方面来思考。

（a.1）使用数据库索引

         关于数据库索引的使用，请参考 数据库（六）数据库索引

（a.2）优化SQL执行计划

        SQL执行计划是关系型数据库最核心的技术之一，它表示SQL执行时的数据访问算法。优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外，还需要有一套优秀的算法库来提高SQL性能。由于SQL执行计划是一个比较专业的话题，本章节就不多加叙说，后续有计划再讨论

（b） 返回更少的数据

（b.1）数据分页处理

         对需要获取的数据，进行分页处理，避免一次获取数据量过大,请参考：数据库（七）数据库分页处理

（b.2）只返回需要的字段

        查询数据库表数据时，返回必须的字段，避免采用select * 模式。

（c）减少交互次数

（c.1）采用批处理

         数据库访问框架一般都提供了批量提交的接口，jdbc支持batch的提交处理方法，当你一次性要往一个表中插入1000万条数据时，如果采用普通的executeUpdate处理，那么和服务器交互次数为1000万次，按每秒钟可以向数据库服务器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000条提交一次，那么和服务器交互次数为1万次，交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。

（c.2）in查询

        数据库查询中，有时候会遇见多个字段查询，这时建议采用in（id1,id2,id3）,采用这种方式可以减少SQL请求次数，以提高性能。但是需要注意的时，采用in查询时，id的数量会有限制（各个数据库都会有SQL长度及IN个数的限制），当id个数过多时，采用in查询，性能基本上没有什么提高，比如说，如果id的个数达到1000以上，这时就建议采用全表查询了，因为随着SQL中IN的里面的值个数增加，SQL的执行计划会更复杂，占用的内存将会变大，这将会增加服务器CPU及内存成本。

（c.3）设置Fetch Size

       当我们采用select从数据库查询数据时，数据默认并不是一条一条返回给客户端的，也不是一次全部返回客户端的，而是根据客户端fetch_size参数处理，每次只返回fetch_size条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时，可以加大fetch_size，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能。

（c.4）使用存储过程、视图、函数

大型数据库一般都支持存储过程、视图、函数，,采用存储过程、视图、函数你可以将一些复杂业务逻辑封装，然后在客户端直接调用存储过程、视图、函数处理，这样可以减少网络交互的成本。
虽然存储过程可以提高数据库性能，但是也存在一些缺陷，使用时要慎用。
a、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。
b、学习成本高，DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。
c、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。
d、存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。
e、如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。
f、为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于java的class文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。
（c.5）使用游标处理结果

前面我们讨论过分页处理，其实在数据库中还有一种方式处理返回结果，那就是ResultSet游标，后续详细讨论数据库游标相关知识。

（d）减少服务器CPU开销（减少CPU及内存开销）

（d.1）使用绑定变量

在解释使用绑定变量之前，我们先来看看SQL执行原理，一条SQL在Oracle数据库中的执行过程如下图所示：

（1）当一条SQL发送给数据库服务器后，系统首先会将SQL字符串进行hash运算，得到hash值后再从服务器内存里的SQL缓存区中进行检索，如果有相同的SQL字符，并且确认是同一逻辑的SQL语句，则从共享池缓存中取出SQL对应的执行计划，根据执行计划读取数据并返回结果给客户端。
（2）如果在共享池中未发现相同的SQL则根据SQL逻辑生成一条新的执行计划并保存在SQL缓存区中，然后根据执行计划读取数据并返回结果给客户端。
       为了更快的检索SQL是否在缓存区中，首先进行的是SQL字符串hash值对比，如果未找到则认为没有缓存，如果存在再进行下一步的准确对比，所以要命中SQL缓存区应保证SQL字符是完全一致，中间有大小写或空格都会认为是不同的SQL。如果我们不采用绑定变量，采用字符串拼接的模式生成SQL,那么每条SQL都会产生执行计划，这样会导致共享池耗尽，缓存命中率也很低。

知道了sql执行原理，我们再看绑定变量，绑定变量是指SQL中对变化的值采用变量参数的形式提交，而不是在SQL中直接拼写对应的值。

非绑定变量写法：Select * from employee where id=1
绑定变量写法：
Select * from employee where id=?
Preparestatement.setInt(1,1)

（e）利用更多资源（增加资源）

增加资源来提高数据库性能，可以从多个方面考虑，比如采用负载均衡、数据库分库分表，采用数据库代理及通过读写分离等，这些会在数据库章节一一介绍。

参考：

http://www.cnblogs.com/easypass/archive/2010/12/08/1900127.html