关于数据库编码优化的一些看法的讨论

　　有关数据库的优化这个问题，平常一般我们接触的比较少，所以有时没有引起足够的重视，但是在某些时候，这将关系到整个软件的成败。
　　
　　一般来说，我们考虑要做数据库的优化的时候，是建立在设计已经定型的基础上的，往往设计此时已经很难更改或者根本无法更改，所以对于太糟糕的设计，我们往往是无能为力的。比如需要提取的某个信息的时候，需要辗转多个大表，且返回记录数较多，那么这种情况在设计的时候我们希望能予以尽可能的关注。数据库的设计应该和实际业务（功能）结合起来选择一个设计的平衡点，不能一味的追求范式级别是怎么的高，有时甚至需要通过一些数据冗余来提高查询效率。那么在所有这些成为事实，我们已经进入编程阶段的时期之后，对于我们具体的数据库编程人员来讲，要考虑的就是以下几类问题：
　　一：网络流量
　　二：磁盘I/O
　　三：资源的使用
　　四：代码效率
　　
　　网络流量
　　通常我们在两种情况下要考虑这个问题：
　　
　　1． 繁发送的SQL请求
　　比如web数据库的访问，这种情况下sql语句的传送都有可能给网络造成沉重的负担，可以考虑采用存储过程或者函数的方式来缓解问题。一般来说，这种情况下应用服务器应该提供pool和数据库建立常连接供调用，因为对数据库的连接请求和数据库的响应是一个比较耗时的过程，这往往成为数据库瓶颈。
　　
　　2．一次性返回记录过多
　　使用者未必需要看所有记录，或者他一次可以看一部分记录，这时就可以通过前台采用翻页等措施，来缓解网络的压力。而后台则可以通过多种方式来实现，比如游标，比如利用中间结果集一次性返回的方式等。
　　
　　总之，对于网络流量的考虑，也是一个根据具体需求和环境衡量的结果，如果没有必要让我们来考虑这个问题，那么，你就大胆的做吧，在LAN中一次返回500k的数据大家都还可以接受。顺便提一下，有时可能要网络传送的包的大小问题，因为包总有一些固定大小的标记，所以包太小则对于业务无用的信息太多，包太大而又可能造成经常发送失败。
　　
　　磁盘I/O一般来说这个问题跟数据库管理员或者设计人员的关系更密切，但我们需要做的是，尽可能的利用索引，对于太大的表，利用表数据分区存储的特点（当然这需要你首先明白什么是表分区和它可以带来什么好处）。利用索引能提高速度是因为两个原因，一是索引通常比数据占用的空间小，每次从磁盘读入内存的时候更快；二是索引的存储结构是一种平衡的树的形式，它不是顺序的查找的，根据树的特点，我们可以估算出其查找次数大致应该为n=logn(x), x为记录数，若不利用索引则许查找x次（当然这好象跟磁盘I/O已经没有关系了）。再提醒一点，若记录返回数大于20%，则数据库将可能做全表扫描，此时索引反而降低了数据库的效率。
　　
　　资源的使用其实我们最关心的资源，除了网络，就是内存和cpu。要在这方面作出优化，必须明白数据库的体系结构和数据库对于sql到底是怎么解析执行的。对于发送的sql语句，数据库先解析若发现有完全和该语句相同的已经解析好的执行代码存在于缓冲区中，则直接执行缓冲区中代码。
　　
　　要利用这点，要求我们编写代码具有良好的风格和习惯，所谓完全相同的sql语句的意思是该语句所有字符甚至空格和换行都完全一样。想想存储过程的快主要就是因为它是解析好的代码存储在数据库中而可以直接调用。关于cpu的利用的明显的地方就是多处理器的情况下，应该在查询中指定并行查询，利用多个cpu并行的能力。关于内存的使用，这是数据库管理员和我们编程人员应该共同关心的问题。因为数据库缓冲区是为了保存已经执行或者使用过的数据留下的信息以为下次执行使用。这样既降低了磁盘I/O，也减少了一些重复的执行步骤。
　　
　　首先要明白数据库缓冲区采用的是先淘汰最近使用最少的数据的策略。当然，其中还有优先级别等问题。对于通过索引读入的数据和索引本身，在数据库中保留的时间总是比较长，若这样的数据充斥了缓冲区，那带来的结果是我们不愿意看到的。还有一点，若临时表或者中间结果过大，也会有类似的影响。这些在编程的时候可能会遇到。代码效率大多数情况下，这才是编程人员关心的重点。
　　
　　要使代码效率高，我们当然希望它占用的内存小，耗用的时间短。但某些情况下，这2点可能成为一对矛盾。比如，我们要提取符合条件的从100000条到100050条的记录，我们决定采用游标的方式。那么一种可能，我们打开一个游标，然后顺序移动到第100000条记录处，开始提取数据，结束后关闭游标。一种可能，我们采用中间结果集或者临时表等方式，先找出符合条件的记录中的100000条到100050条，然后在这个基础上定义游标，那么这样我们的游标就避免了100000次的移动。但是代价是这个过程中利用了更多的内存。不过，我们编程，本就没有一个完美的解决方案，总是根据具体情况做出一个比较合适的选择的。
　　
　　其实，更多的，我们应该关心SQL语句在数据库中到底是怎么解析并执行的。从这个层次
　　
　　上明白了，写出的sql语句就至少不会太差了。当然，一开始，我们可以记住一些通用规则，比如查询中尽可能的利用索引，能过滤掉最多数据的条件写在where子句的末尾，在使用子查询的时候要慎重考虑，对于in要仔细衡量，not in要尽量避免使用。表连接的时候要尽量利用索引字段，多表连接的时候要注意连接顺序。还有比如like的使用，不要轻易使用like%s%这样的条件（like s%是可以利用索引的），这种匹配的算法再快也有影响。
　　
　　多了解sql编码中的一些技巧。多积累经验、多学习，在已经知道的东西中根据需要灵活的创造出自己的用法，这时你心里就应该清楚自己的代码的执行效率了，不至于N个月后运行的时候突然发现需要返工，那将是一件很痛苦的事情。
　　
　　最后再强调一点，要做到对自己编写的代码心中有数，很明白它有多大的承受能力，你
　　
　　就必须理解数据库的体系结构，明白SQL代码的执行过程，知道这个执行过程中大约要消耗多少内存，有那些更详细的步骤，它会给数据库带来什么样的影响，这个影响可以忽略么？只要咱们朝着这个方向努力，最终会玩转这个内容并不太多的东东的。(<b>biti_rainy</b>)
　　
　　对于1的看法：
　　包、存储过程函数等是针对计算密集型和后台数据处理密集型的业务逻辑。而一点从 App Server 层传的 sql 就能造成网络沉重负担？提交的表单数据都比 SQL 字节多！而对于连接，一般都用 connection pool，即使用 tomcat 也自己写 pool 了，况且连接请求和SQL请求完全是两码事嘛。
　　
　　对于2的看法：
　　分页是理所当然的，可是没有必要用什么游标吧？（我想你指的是服务端游标吧，其实客户端结果集本身就打开了客户端游标），难道为一个分页就写一个存储过程？利用两次 rownum 结合就搞定了。
　　
　　其实网络流量的问题，只要程序中不出现返回几千、万行记录的“地雷”程序就没问题，除非你用10M或100M网卡，服务器都应该用 1000M 以上的网络设备了。
　　
　　对于磁盘 I/O
　　最有效的办法是对数据库分区：对表分区、对索引分区，以及结合二者，然后对 SQL 进行优化，使之利用分区。SQL优化比较烦人，需要花一些时间，而且还要用到 hint，Oracle 有一个文档，什么 performance 什么里面讲很清楚。如何写 SQL 都很有学问，读清楚那本书就 ok 了如果分区和SQL优化做得好，CPU利用率不会太高。相对而言数据库服务器吃内存比较狠。应用服务器用 CPU 比内存重要些。(<b>singledream</b>)

原文转自：http://www.ltesting.net