数据库性能优化的方法-马海祥博客
新型SEO思维就是从一个全新的层次上提升seo优化的水平，达到网络信息最佳化的展示效果！
> 数据库性能优化的方法
数据库性能优化的方法
时间：&&&文章来源：马海祥博客&&&访问次数：
如今，互联网上关于数据库优化方面的文章很多，但是有的写的似是而非，有的不切实际，对一个数据库来说，只能做到更优，不可能最优，并且由于实际需求不同，优化方案还是有所差异的，根据实际需要关心的方面（速度、存储空间、可维护性、可拓展性）来优化数据库，而这些方面往往又是相互矛盾的。
一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，所以，我按照软件生命周期的不同阶段来总结数据库性能优化相关的方法及注意事项。
一、为什么要优化数据库？
数据库的应用程序优化通常可分为两个方面：源代码和SQL语句。
由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高，而对数据库系统性能的提升收效有限，那么，我们为什么要优化SQL语句呢？
1、SQL语句是对数据库进行操作的惟一途径，对数据库系统的性能起着决定性的作用。
2、SQL语句消耗了70%至90%的数据库资源。
3、SQL语句独立于程序设计逻辑，对SQL语句进行优化不会影响程序逻辑。
4、SQL语句有不同的写法，在性能上的差异非常大。
5、SQL语句易学，但难精通。
优化SQL语句的传统方法是通过手工重写来对SQL语句进行优化，DBA或资深程序员通过对SQL语句执行计划的分析，依靠经验，尝试重写SQL语句，然后对结果和性能进行比较，以试图找到性能较佳的SQL语句。
这种传统上的作法无法找出SQL语句的所有可能写法，且依赖于人的经验，非常耗费时间。
二、分析阶段
一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力。
但是，马海祥必须提醒大家要注意一点，性能是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等，最好能有各种需求的量化的指标。
另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP(联机事务处理系统)和OLAP(联机分析处理系统)。
三、设计阶段
设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程&数据库设计。
在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。
对于性能要求设计阶段，我们需要注意以下几点：
1、数据库逻辑设计的规范化
数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：
第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。
第2规范：每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分，消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。
第3规范：一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。
更高的范式要求这里就不再作介绍了，在马海祥看来，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。
2、合理的冗余
完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。
冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。
冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。
从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。
3、主键的设计
主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引，聚集索引对查询的影响是比较大的，这个在下面索引的叙述。
在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。
4、外键的设计
外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：
外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。
谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作了保留，应该有其可用之处。
马海祥这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。
从性能看级联删除和级联更新是比其他方法更高效的方法。
5、字段的设计
字段是数据库最基本的单位，其设计对性能的影响是很大的，对此，马海祥提醒大家要注意以下几点：
A、数据类型尽量用数字型，数字型的比较比字符型的快很多。
B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。
E、自增字段要慎用，不利于数据迁移。
6、数据库物理存储和环境的设计
在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。
这里需要注意文件组的作用，适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。
7、系统设计
整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。
系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数，用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。
8、索引的设计
在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。
关于索引的选择，马海祥提醒大家要注意以下几点：
A、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
B、根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
C、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
D、一个表不要加太多索引，因为索引影响插入和更新的速度。
马海祥博客点评：
一个数据库系统的生命周期可以分成：设计、开发和成品三个阶段，在设计阶段进行数据库性能优化的成本最低，收益最大，在成品阶段进行数据库性能优化的成本最高，收益最小。
本文为原创文章，如想转载，请注明原文网址摘自于/znseo/820.html，注明出处；否则，禁止转载；谢谢配合！
您可能还会对以下这些文章感兴趣！
百度在2月1日发布了「2013年中国网站发展趋势报告」，报告称去年网站增长数量较大，但优质站点仅占1/4，低……
如今，随着网站的越来越多，互联网每天都产生数以百万计的信息，这其中充斥着太多的“垃圾”信息，这些东西……
百度百家的问题在于，它还基本上是从百度新闻首页倒流给作者，但随着作者越来越多，狼多肉少，显然并非长久……
沙盒效应 沙盒英文单词叫Sandbox，是Google反击垃圾网站的重要措……
锚文本又称锚文本链接，英文名叫anchor text，是链接的一种形式……
301重定向也被称为301永久重定向或页面永久性移走，是一种非常重……
众所周知，网站优化是随着搜索引擎算法的升级不断的探索和寻求效……
在做SEO的行业里有这样一个名词人工词，这个词出现的频率不高，……
本篇文章记录了百度从2011年到2012年中旬百度大更新记录的文章。……
本月热点文章校园网数据库的性能优化技术及其应用--《中南大学》2008年硕士论文
校园网数据库的性能优化技术及其应用
【摘要】：
校园网数据库具有多媒体、异构、查询频繁、数据量大等特点,随着校园网应用的深入,校园网数据库的数据量变得越来越庞大,数据库的访问性能因此而显著降低。如何优化由结构化数据组成的校园网数据库以提升其性能,如何合理设计多媒体数据库的结构以提升其查询效率,如何有针对性地设计校园网数据库的查询算法,对于这些方面的研究,既具有理论意义,更具有实践指导意义。
数据库逻辑设计优化的主要方法是使数据库结构设计尽量满足第三范式。本文在阐述利用规范化设计来优化数据库性能的基础之上,为了减少数据库的多表连接,避免频繁使用聚集函数,优化数据访问频率差别较大的大容量数据库的性能,提出了有针对性的反范式设计方法来优化由结构化数据组成的校园网数据库。同时,通过分析校园网中多媒体数据库的特点,研究了基于数据分类与扩充关系模型来构建多媒体数据库的方法,此方法操作简单,性能良好。
提升数据库的查询性能可以通过建立合适的索引、利用存储过程、优化SQL语句等方法来实现。本文在分析了查询优化方法的基础之上,为解决大容量数据库中统计分析查询十分缓慢的问题,根据校园网数据库的查询重复性强、数据更新时间有规律性等特点,研究了基于语义信息与择机预取的查询优化算法,它通过保存、分析重复性的查询语义信息,择机预取访问频率高的数据到Cache中,达到较好地提升查询速度的目的。
本文以湖南省教育厅资助科研项目为课题来源,在校园网数据库的性能优化实践中,利用性能监控与分析工具分析数据库性能状况,利用规范化与反规范化技术改进数据库的结构设计,利用基于数据分类与扩充关系模型的方法重构多媒体数据库的逻辑设计,利用基于语义信息与择机预取的查询优化算法改善大容量数据库的统计分析查询性能。优化实践后的测试结果表明,校园网数据库性能得到了较大幅度的提升。
【关键词】：
【学位授予单位】：中南大学【学位级别】：硕士【学位授予年份】：2008【分类号】：TP311.13【目录】：
ABSTRACT4-8
第一章 绪论8-12
1.1 研究背景8
1.2 研究现状8-10
1.3 研究意义10
1.4 论文的主要内容10-11
1.5 论文的组织结构11-12
第二章 校园网数据库性能优化概述12-18
2.1 校园网数据库简介12-13
2.2 影响校园网数据库性能的因素13-15
2.3 数据库性能优化的基本途径15-16
2.4 本章小结16-18
第三章 数据库逻辑设计优化18-40
3.1 数据库逻辑设计概述18-19
3.2 数据库逻辑设计规范化的基本途径19-22
3.3 校园网数据库的反范式优化22-31
3.3.1 规范化设计的弊端及反范式设计的优点23-24
3.3.2 针对性的反范式优化方法24-27
3.3.3 反范式设计的注意事项27-29
3.3.4 反范式设计优化前后的查询性能比较29-31
3.4 基于数据分类与扩充关系模型的多媒体数据库构建方法31-39
3.4.1 校园网多媒体数据库的特点31-32
3.4.2 多媒体数据存储的一般方法及存在的问题32-33
3.4.3 校园网多媒体数据库的分类与关系模型扩充方法33-34
3.4.4 多媒体素材库的设计及性能优化34-38
3.4.5 VOD影音库、多媒体课件库的设计及性能优化38-39
3.5 本章小结39-40
第四章 数据库查询性能优化40-59
4.1 查询性能优化的主要方法40-42
4.2 数据库的索引优化42-45
4.2.1 数据库索引概述42-43
4.2.2 数据库的索引设计的方法43-45
4.3 利用存储过程优化查询性能45-47
4.3.1 存储过程概述45-46
4.3.2 利用存储过程优化查询的应用场合及其注意事项46-47
4.4 SQL语句优化47-50
4.4.1 SQL语句优化概述47-48
4.4.2 SQL语句优化的原则48-50
4.5 基于语义信息与择机预取的查询优化算法50-57
4.5.1 查询优化算法概述50-51
4.5.2 算法选择的依据及算法的体系结构图51-52
4.5.3 算法的基本步骤及算法的相关定义52-54
4.5.4 语义信息替换与择机预取算法54-55
4.5.5 基于语义信息与择机预取的查询优化算法及其性能测试55-57
4.6 本章小结57-59
第五章 校园网数据库性能优化的实践59-69
5.1 永州职业技术学院数据库简介59
5.2 数据库性能监控工具简介59-62
5.3 永州职业技术学院数据库性能优化实践62-68
5.4 本章小结68-69
第六章 总结与展望69-71
6.1 论文工作总结69
6.2 展望69-71
参考文献71-75
攻读学位期间主要的研究成果76
欢迎：、、)
支持CAJ、PDF文件格式
【引证文献】
中国期刊全文数据库
孙智军;史淑红;韩杰;;[J];计算机与现代化;2012年03期
【参考文献】
中国期刊全文数据库
陈世坤,王琼英,胡其伟;[J];广东工业大学学报;2005年03期
李霞;王建民;;[J];河南大学学报(自然科学版);2005年04期
刘兵;严和平;段江娇;汪卫;施伯乐;;[J];计算机研究与发展;2006年09期
富宇;杨冬黎;杨彬;;[J];佳木斯大学学报(自然科学版);2008年03期
邹玲,石冰心;[J];计算机工程与应用;2000年03期
郭向勇,吴光斌;[J];计算机工程;2002年05期
杨建武,陈晓鸥;[J];计算机学报;2002年11期
周向东,施伯乐;[J];计算机学报;2004年11期
施智平;胡宏;李清勇;史俊;史忠植;;[J];计算机学报;2007年03期
李用江;[J];计算机应用;2003年11期
【共引文献】
中国期刊全文数据库
李用江,回雁雁;[J];安阳师范学院学报;2004年05期
刘润涛;郝忠孝;;[J];北京工业大学学报;2010年10期
曹玉林;[J];宝鸡文理学院学报(自然科学版);2005年01期
岳俊琳,陈庆伟;[J];保山师专学报;2005年02期
吴浪;;[J];才智;2008年10期
毕硕本;耿焕同;闾国年;;[J];地理信息世界;2008年01期
黄吉宏;王丽;;[J];沧桑;2011年01期
孙悦娟;;[J];常州工学院学报;2007年03期
陈伟;黎祚;;[J];电工技术;2012年02期
郭向勇;[J];电化教育研究;2003年06期
中国重要会议论文全文数据库
周磊;刘强;李小文;;[A];《测绘通报》测绘科学前沿技术论坛摘要集[C];2008年
张朝阳;宁洪;王挺;郭超;;[A];2009年研究生学术交流会通信与信息技术论文集[C];2009年
杨彦明;魏振钢;迟忠惠;;[A];’2004计算机应用技术交流会议论文集[C];2004年
卢朝霞;习捷;王剑;;[A];2006中国控制与决策学术年会论文集[C];2006年
冯化强;万麟瑞;;[A];第四届中国智能计算大会论文集[C];2010年
冯化强;万麟瑞;;[A];中国通信学会第六届学术年会论文集（上）[C];2009年
刘全玺;卢佩;李智辉;;[A];2009全国虚拟仪器大会论文集（二）[C];2009年
Hua ZHOU;;[A];第5届教育教学改革与管理工程学术年会论文集[C];2012年
中国博士学位论文全文数据库
张泽宝;[D];哈尔滨工程大学;2009年
刘润涛;[D];哈尔滨理工大学;2009年
李瑞轩;[D];华中科技大学;2004年
袁贞明;[D];浙江大学;2005年
黄继先;[D];中南大学;2005年
孙海滨;[D];吉林大学;2006年
于红;[D];大连理工大学;2006年
郑骁庆;[D];浙江大学;2007年
周伟;[D];重庆大学;2007年
黄魏;[D];华中农业大学;2007年
中国硕士学位论文全文数据库
张伟;[D];辽宁工程技术大学;2009年
连照亮;[D];辽宁工程技术大学;2010年
张加元;[D];辽宁工程技术大学;2009年
徐欢;[D];苏州大学;2010年
董培军;[D];华东师范大学;2010年
许刚;[D];沈阳理工大学;2010年
赵小凡;[D];华南理工大学;2010年
李琳;[D];华南理工大学;2010年
吴海燕;[D];哈尔滨理工大学;2010年
潘红岩;[D];哈尔滨理工大学;2010年
【同被引文献】
中国期刊全文数据库
赵洁;甘志刚;;[J];广东工业大学学报;2007年04期
王继伟;[J];解放军医院管理杂志;2002年01期
胡乃静;俞新梅;王颖颖;;[J];计算机工程;2004年15期
尹萍;[J];计算机应用与软件;2005年03期
谭定英,方振聪;[J];计算机与现代化;2005年06期
杨小艳;尹明;戴学丰;;[J];计算机与现代化;2008年04期
郝伟;曹代勇;彭宏钊;禹蒲阳;;[J];湖南科技大学学报(自然科学版);2007年02期
王树庆;孟志平;肖潇;刘毅;;[J];医疗装备;2009年11期
中国硕士学位论文全文数据库
李军;[D];北京交通大学;2009年
朱喜梅;[D];哈尔滨理工大学;2009年
宋秀荣;[D];燕山大学;2009年
【二级参考文献】
中国期刊全文数据库
曹菡,陈军,杜道生;[J];测绘学报;2001年02期
武国卫;[J];中国传媒科技;2004年10期
李武韬;[J];常州信息职业技术学院学报;2004年01期
漆华妹,陈志刚,曾碧卿;[J];电脑与信息技术;2005年01期
赵颖,沈金龙;[J];电子工程师;2000年05期
曹会文;[J];大众科技;2005年07期
孙凌,高大利,郝继红;[J];福建电脑;2003年12期
勾建新;[J];湖北汽车工业学院学报;2003年03期
朱虹,冯玉才;[J];计算机研究与发展;1998年01期
车争,夏巨谌,胡国安,张宜生;[J];计算机辅助工程;2002年04期
中国博士学位论文全文数据库
吴婷婷;[D];中国人民解放军国防科学技术大学;2002年
【相似文献】
中国期刊全文数据库
钟克吟;;[J];计算机与数字工程;2008年06期
陈一明;;[J];科学技术与工程;2008年12期
张南平,陈小倩;[J];微机发展;2005年08期
郑华;[J];计算机时代;2005年08期
陈国旗;;[J];中国计量学院学报;2006年03期
范宁;;[J];华南金融电脑;2007年02期
桂友武;桂友超;;[J];企业技术开发;2009年04期
郑小蓉;;[J];中国新技术新产品;2009年22期
邢承杰;宋式斌;林莉;杨旭;;[J];中山大学学报(自然科学版);2009年S1期
范孝良;国秀丽;;[J];上海第二工业大学学报;2006年01期
中国重要会议论文全文数据库
方奇;袁茂森;刘志强;;[A];全国第八届有机固体电子过程暨华人有机光电功能材料学术讨论会摘要集[C];2010年
洪润秋;金文;陈钢;王能斌;;[A];第十一届全国数据库学术会议论文集[C];1993年
刘旭辉;冯建华;洪亲;;[A];第二十四届中国数据库学术会议论文集（技术报告篇）[C];2007年
汪挺;;[A];第13届全国计算机、网络在现代科学技术领域的应用学术会议论文集[C];2007年
于沛;沈宇希;周荣贵;刘怡;;[A];第十八届全国数据库学术会议论文集（研究报告篇）[C];2001年
李蓉梅;张鸿军;茅志华;何守才;;[A];第十五届全国数据库学术会议论文集[C];1998年
张霞;刘积仁;李华天;;[A];第十一届全国数据库学术会议论文集[C];1993年
陈建松;;[A];中国计量协会冶金分会2011年会论文集[C];2011年
秦长贵;;[A];中国图象图形科学技术新进展——第九届全国图象图形科技大会论文集[C];1998年
李碧波;冯玉才;周旋;;[A];数据库研究与进展95——第十三届全国数据库学术会议论文集[C];1995年
中国重要报纸全文数据库
陈翔;[N];中国计算机报;2007年
陈纯;[N];电脑报;2004年
奥创利高级开发工程师 Robert A. Aekins
奥创利高级产品经理 Gregg L[N];计算机世界;2002年
黄药师;[N];电脑报;2004年
北京日讯在线科技有限公司;[N];通信产业报;2007年
西安交大长天软件公司
王隆彦;[N];中国计算机报;2001年
李雪梅;[N];人民邮电;2001年
张旭军;[N];网络世界;2006年
涂桂林;[N];中国新闻出版报;2008年
;[N];人民邮电;2008年
中国博士学位论文全文数据库
徐红波;[D];哈尔滨理工大学;2010年
刘润涛;[D];哈尔滨理工大学;2009年
庄毅;[D];浙江大学;2007年
左琼;[D];华中科技大学;2010年
张雷;[D];电子科技大学;2010年
何倩;[D];北京邮电大学;2010年
吴长泽;[D];重庆大学;2007年
吴钊;[D];武汉大学;2007年
董道国;[D];复旦大学;2005年
朱正林;[D];东南大学;2005年
中国硕士学位论文全文数据库
邓小善;[D];中南大学;2008年
窦凤云;[D];天津大学;2006年
韩天;[D];厦门大学;2009年
冯平;[D];华中科技大学;2005年
林树新;[D];浙江大学;2005年
熊远生;[D];浙江工业大学;2004年
徐慧君;[D];北京工业大学;2004年
吕栗;[D];哈尔滨工程大学;2005年
石鹏飞;[D];浙江大学;2006年
陆琳琳;[D];吉林大学;2006年
&快捷付款方式
&订购知网充值卡
400-819-9993
《中国学术期刊（光盘版）》电子杂志社有限公司
同方知网数字出版技术股份有限公司
地址：北京清华大学 84-48信箱 大众知识服务
出版物经营许可证 新出发京批字第直0595号
订购热线：400-819-82499
服务热线：010--
在线咨询：
传真：010-
京公网安备75号特别说明：
1、& 本文只是面对数据库应用开发的程序员，不适合专业DBA，DBA在数据库性能优化方面需要了解更多的知识；
2、& 本文许多示例及概念是基于Oracle数据库描述，对于其它关系型数据库也可以参考，但许多观点不适合于KV数据库或内存数据库或者是基于SSD技术的数据库；
3、& 本文未深入数据库优化中最核心的执行计划分析技术。
读者对像：
开发人员：如果你是做数据库开发，那本文的内容非常适合，因为本文是从程序员的角度来谈数据库性能优化。
架构师：如果你已经是数据库应用的架构师，那本文的知识你应该清楚90%，否则你可能是一个喜欢折腾的架构师。
DBA（数据库管理员）：大型数据库优化的知识非常复杂，本文只是从程序员的角度来谈性能优化，DBA除了需要了解这些知识外，还需要深入数据库的内部体系架构来解决问题。
在网上有很多文章介绍数据库优化知识，但是大部份文章只是对某个一个方面进行说明，而对于我们程序员来说这种介绍并不能很好的掌握优化知识，因为很多介绍只是对一些特定的场景优化的，所以反而有时会产生误导或让程序员感觉不明白其中的奥妙而对数据库优化感觉很神秘。
很多程序员总是问如何学习数据库优化，有没有好的教材之类的问题。在书店也看到了许多数据库优化的专业书籍，但是感觉更多是面向DBA或者是PL/SQL开发方面的知识，个人感觉不太适合普通程序员。而要想做到数据库优化的高手，不是花几周，几个月就能达到的，这并不是因为数据库优化有多高深，而是因为要做好优化一方面需要有非常好的技术功底，对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基础知识，另一方面是需要花大量时间对特定的数据库进行实践测试与总结。
作为一个程序员，我们也许不清楚线上正式的服务器硬件配置，我们不可能像DBA那样专业的对数据库进行各种实践测试与总结，但我们都应该非常了解我们SQL的业务逻辑，我们清楚SQL中访问表及字段的数据情况，我们其实只关心我们的SQL是否能尽快返回结果。那程序员如何利用已知的知识进行数据库优化？如何能快速定位SQL性能问题并找到正确的优化方向？
面对这些问题，笔者总结了一些面向程序员的基本优化法则，本文将结合实例来坦述数据库开发的优化知识。
要正确的优化SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是2Mbps，硬盘是每分钟7200转等等。因此，为了快速找到SQL的性能瓶颈点，我们也需要了解我们计算机系统的硬件基本性能指标，下图展示的当前主流计算机性能指标数据。
从图上可以看到基本上每种设备都有两个指标：
延时（响应时间）：表示硬件的突发处理能力；
带宽（吞吐量）：代表硬件持续处理能力。
从上图可以看出，计算机系统硬件性能从高到代依次为：
CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘
由于SSD硬盘还处于快速发展阶段，所以本文的内容不涉及SSD相关应用系统。
根据数据库知识，我们可以列出每种硬件主要的工作内容：
CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；
网络：结果数据传输、SQL请求、远程数据库访问（dblink）；
硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。
根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下图所示的性能基本优化法则：
这个优化法则归纳为5个层次：
1、& 减少数据访问（减少磁盘访问）
2、& 返回更少数据（减少网络传输或磁盘访问）
3、& 减少交互次数（减少网络传输）
4、& 减少服务器CPU开销（减少CPU及内存开销）
5、& 利用更多资源（增加资源）
由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。
以下是每个优化法则层级对应优化效果及成本经验参考：
性能提升效果
减少数据访问
返回更少数据
减少交互次数
减少服务器CPU开销
利用更多资源
接下来，我们针对5种优化法则列举常用的优化手段并结合实例分析。
数据库两个基本概念
数据块是数据库中数据在磁盘中存储的最小单位，也是一次IO访问的最小单位，一个数据块通常可以存储多条记录，数据块大小是DBA在创建数据库或表空间时指定，可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构，一个数据库可以包括多个数据文件，一个数据文件内又包含多个数据块；
ROWID是每条记录在数据库中的唯一标识，通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号，如下图所示：
三、数据库访问优化法则详解
、减少数据访问
、创建并使用正确的索引
数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人很少，即使是专业的DBA也不一定能完全做到最优。
索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。
索引常见问题：
索引有哪些种类？
常见的索引有B-TREE索引、位图索引、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少，这里不深入介绍。B-TREE索引包括很多扩展类型，如组合索引、反向索引、函数索引等等，以下是B-TREE索引的简单介绍：
B-TREE索引也称为平衡树索引(Balance Tree)，它是一种按字段排好序的树形目录结构，主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。
叶子节点内容：索引字段内容+表记录ROWID
根节点，分支节点内容：当一个数据块中不能放下所有索引字段数据时，就会形成树形的根节点或分支节点，根节点与分支节点保存了索引树的顺序及各层级间的引用关系。
&&&&&&&& 一个普通的BTREE索引结构示意图如下所示：
如果我们把一个表的内容认为是一本字典，那索引就相当于字典的目录，如下图所示：
图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首+笔划的组合索引。
一个表中可以建多个索引，就如一本字典可以建多个目录一样（按拼音、笔划、部首等等）。
一个索引也可以由多个字段组成，称为组合索引，如上图就是一个按部首+笔划的组合目录。
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（两个表通过索引字段关联）
SQL什么条件不会使用索引？
不能使用索引原因
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'
CHAR_INDEX_COLUMN=12345
Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。
a.INDEX_COLUMN=a.COLUMN_1
给索引查询的值应是已知数据，不能是未知字段值。
经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。
有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引
如：我们company表建了一个id+name的组合索引，以下SQL是不能使用索引的
Select * from company where name=?
Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题，但是通过index skip scan提高性能的条件比较特殊，使用不好反而性能会更差。
我们一般在什么字段上建索引？
这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：
1、字段出现在查询条件中，并且查询条件可以使用索引；
2、语句执行频率高，一天会有几千次以上；
3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？
这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：
小表(记录数小于10000行的表)：筛选比例&10%；
大表：(筛选返回记录数)&(表总记录数*单条记录长度)/10000/16
&&&&& 单条记录长度≈字段平均内容长度之和+字段数*2
以下是一些字段是否需要建B-TREE索引的经验分类：
常见字段名
需要建索引的字段
PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID
有对像或身份标识意义字段
HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO
索引慎用字段,需要进行数据分布及使用场景详细评估
GMT_CREATE,GMT_MODIFIED
YEAR,MONTH
PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG
ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE
COUNTRY,PROVINCE,CITY
CREATOR,AUDITOR
数值
LEVEL,AMOUNT,SCORE
ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT
不适合建索引的字段
DESCRIPTION,REMARK,MEMO,DETAIL
FILE_CONTENT,EMAIL_CONTENT
如何知道SQL是否使用了正确的索引？
简单SQL可以根据索引使用语法规则判断，复杂的SQL不好办，判断SQL的响应时间是一种策略，但是这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用，需要到数据库中查看SQL真实的执行计划，这个话题比较复杂，详见SQL执行计划专题介绍。
索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？
这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：
索引对于Insert性能降低56%
索引对于Update性能降低47%
索引对于Delete性能降低29%
因此对于写IO压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。
、只通过索引访问数据
有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。
如：select id,name from company where type='2';
如果这个SQL经常使用，我们可以在type,id,name上创建组合索引
create index my_comb_index on company(type,id,name);
有了这个组合索引后，SQL就可以直接通过my_comb_index索引返回数据，不需要访问company表。
还是拿字典举例：有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果。如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页，拼音目录有20页，那我们的数据访问成本相当于全表访问的50分之一。
切记，性能优化是无止境的，当性能可以满足需求时即可，不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里，所以这种只通过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询。
、优化SQL执行计划
SQL执行计划是关系型数据库最核心的技术之一，它表示SQL执行时的数据访问算法。由于业务需求越来越复杂，表数据量也越来越大，程序员越来越懒惰，SQL也需要支持非常复杂的业务逻辑，但SQL的性能还需要提高，因此，优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外，还需要有一套优秀的算法库来提高SQL性能。
目前ORACLE有SQL执行计划的算法约300种，而且一直在增加，所以SQL执行计划是一个非常复杂的课题，一个普通DBA能掌握50种就很不错了，就算是资深DBA也不可能把每个执行计划的算法描述清楚。虽然有这么多种算法，但并不表示我们无法优化执行计划，因为我们常用的SQL执行计划算法也就十几个，如果一个程序员能把这十几个算法搞清楚，那就掌握了80%的SQL执行计划调优知识。
由于篇幅的原因，SQL执行计划需要专题介绍，在这里就不多说了。
、返回更少的数据
、数据分页处理
一般数据分页方式有：
2.1.1、客户端(应用程序或浏览器)分页
将数据从应用服务器全部下载到本地应用程序或浏览器，在应用程序或浏览器内部通过本地代码进行分页处理
优点：编码简单，减少客户端与应用服务器网络交互次数
缺点：首次交互时间长，占用客户端内存
适应场景：客户端与应用服务器网络延时较大，但要求后续操作流畅，如手机GPRS，超远程访问（跨国）等等。
2.1.2、应用服务器分页
将数据从数据库服务器全部下载到应用服务器，在应用服务器内部再进行数据筛选。以下是一个应用服务器端Java程序分页的示例：
List list=executeQuery(“select * from employee order by id”);
Int count= list.size();
List subList= list.subList(10, 20);
优点：编码简单，只需要一次SQL交互，总数据与分页数据差不多时性能较好。
缺点：总数据量较多时性能较差。
适应场景：数据库系统不支持分页处理，数据量较小并且可控。
2.1.3、数据库SQL分页
采用数据库SQL分页需要两次SQL完成
一个SQL计算总数量
一个SQL返回分页后的数据
优点：性能好
缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。
oracle数据库一般采用rownum来进行分页，常用分页语法有如下两种：
直接通过rownum分页：
select * from (
&&&&&&&& select a.*,rownum rn from
&&&&&&&&&&&&&&&&&& (select * from product a where company_id=? order by status) a
&&&&&&&& where rownum&=20)
where rn&10;
数据访问开销=索引IO+索引全部记录结果对应的表数据IO
采用rowid分页语法
优化原理是通过纯索引找出分页记录的ROWID，再通过ROWID回表返回数据，要求内层查询和排序字段全在索引里。
create index myindex on product(company_id,status);
select b.* from (
&&&&&&&& select * from (
&&&&&&&&&&&&&&&&&& select a.*,rownum rn from
&&&&&&&&&&&&&&&&&&&&&&&&&&& (select rowid rid,status from product a where company_id=? order by status) a
&&&&&&&&&&&&&&&&&& where rownum&=20)
&&&&&&&& where rn&10) a, product b
where a.rid=b.
数据访问开销=索引IO+索引分页结果对应的表数据IO
一个公司产品有1000条记录，要分页取其中20个产品，假设访问公司索引需要50个IO，2条记录需要1个表数据IO。
那么按第一种ROWNUM分页写法，需要550(50+1000/2)个IO，按第二种ROWID分页写法，只需要60个IO(50+20/2);
、只返回需要的字段
通过去除不必要的返回字段可以提高性能，例：
调整前：select * from product where company_id=?;
调整后：select id,name from product where company_id=?;
1、减少数据在网络上传输开销
2、减少服务器数据处理开销
3、减少客户端内存占用
4、字段变更时提前发现问题，减少程序BUG
5、如果访问的所有字段刚好在一个索引里面，则可以使用纯索引访问提高性能。
缺点：增加编码工作量
由于会增加一些编码工作量，所以一般需求通过开发规范来要求程序员这么做，否则等项目上线后再整改工作量更大。
如果你的查询表中有大字段或内容较多的字段，如备注信息、文件内容等等，那在查询表时一定要注意这方面的问题，否则可能会带来严重的性能问题。如果表经常要查询并且请求大内容字段的概率很低，我们可以采用分表处理，将一个大表分拆成两个一对一的关系表，将不常用的大内容字段放在一张单独的表中。如一张存储上传文件的表：
T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT）
我们可以分拆成两张一对一的关系表：
T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE）
T_FILECONTENT（ID, FILE_CONTENT）
&&&&&&&& 通过这种分拆，可以大大提少T_FILE表的单条记录及总大小，这样在查询T_FILE时性能会更好，当需要查询FILE_CONTENT字段内容时再访问T_FILECONTENT表。
、减少交互次数
数据库访问框架一般都提供了批量提交的接口，jdbc支持batch的提交处理方法，当你一次性要往一个表中插入1000万条数据时，如果采用普通的executeUpdate处理，那么和服务器交互次数为1000万次，按每秒钟可以向数据库服务器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000条提交一次，那么和服务器交互次数为1万次，交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。
假设要向一个普通表插入1000万数据，每条记录大小为1K字节，表上没有任何索引，客户端与数据库服务器网络是100Mbps，以下是根据现在一般计算机能力估算的各种batch大小性能对比值：
　单位：ms
Batch=1000
Batch=10000
服务器事务处理时间
服务器IO处理时间
网络交互发起时间
网络数据传输时间
平均每条记录处理时间
从上可以看出，Insert操作加大Batch可以对性能提高近8倍性能，一般根据主键的Update或Delete操作也可能提高2-3倍性能，但不如Insert明显，因为Update及Delete操作可能有比较大的开销在物理IO访问。以上仅是理论计算值，实际情况需要根据具体环境测量。
很多时候我们需要按一些ID查询数据库记录，我们可以采用一个ID一个请求发给数据库，如下所示：
for :var in ids[] do begin
& select * from mytable where id=:
我们也可以做一个小的优化， 如下所示，用ID INLIST的这种方式写SQL：
select * from mytable where id in(:id1,id2,...,idn);
通过这样处理可以大大减少SQL请求的数量，从而提高性能。那如果有10000个ID，那是不是全部放在一条SQL里处理呢？答案肯定是否定的。首先大部份数据库都会有SQL长度和IN里个数的限制，如ORACLE的IN里就不允许超过1000个值。
另外当前数据库一般都是采用基于成本的优化规则，当IN数量达到一定值时有可能改变SQL执行计划，从索引访问变成全表访问，这将使性能急剧变化。随着SQL中IN的里面的值个数增加，SQL的执行计划会更复杂，占用的内存将会变大，这将会增加服务器CPU及内存成本。
评估在IN里面一次放多少个值还需要考虑应用服务器本地内存的开销，有并发访问时要计算本地数据使用周期内的并发上限，否则可能会导致内存溢出。
综合考虑，一般IN里面的值个数超过20个以后性能基本没什么太大变化，也特别说明不要超过100，超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本，这个需要专业DBA评估。
、设置Fetch
当我们采用select从数据库查询数据时，数据默认并不是一条一条返回给客户端的，也不是一次全部返回客户端的，而是根据客户端fetch_size参数处理，每次只返回fetch_size条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时，可以加大fetch_size，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能。
以下是jdbc测试的代码，采用本地数据库，表缓存在数据库CACHE中，因此没有网络连接及磁盘IO开销，客户端只遍历游标，不做任何处理，这样更能体现fetch参数的影响：
String vsql =&select * from t_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();
while (rs.next()) {
&&&&for (int i = 1; i &= i++) {
&&&&&&&o = rs.getObject(i);
测试示例中的employee表有100000条记录，每条记录平均长度135字节
以下是测试结果，对每种fetchsize测试5次再取平均值：
&elapse_time（s）
Oracle jdbc fetchsize默认值为10，由上测试可以看出fetchsize对性能影响还是比较大的，但是当fetchsize大于100时就基本上没有影响了。fetchsize并不会存在一个最优的固定值，因为整体性能与记录集大小及硬件平台有关。根据测试结果建议当一次性要取大量数据时这个值设置为100左右，不要小于40。注意，fetchsize不能设置太大，如果一次取出的数据大于JVM的内存会导致内存溢出，所以建议不要超过1000，太大了也没什么性能提高，反而可能会增加内存溢出的危险。
注：图中fetchsize在128以后会有一些小的波动，这并不是测试误差，而是由于resultset填充到具体对像时间不同的原因，由于resultset已经到本地内存里了，所以估计是由于CPU的L1,L2 Cache命中率变化造成，由于变化不大，所以笔者也未深入分析原因。
iBatis的SqlMapping配置文件可以对每个SQL语句指定fetchsize大小，如下所示：
&select id=&getAllProduct& resultMap=&HashMap&&fetchSize=&1000&&
select * from employee
、使用存储过程
大型数据库一般都支持存储过程，合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中，但是又不可能一条SQL完成，这时你需要如下3步操作：
a：将A表数据全部取出到客户端；
b：计算出要更新的数据；
c：将计算结果更新到B表。
如果采用存储过程你可以将整个业务逻辑封装在存储过程里，然后在客户端直接调用存储过程处理，这样可以减少网络交互的成本。
当然，存储过程也并不是十全十美，存储过程有以下缺点：
a、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。
b、学习成本高，DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。
c、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。
d、存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。
e、如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。
f、为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于java的class文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。
个人观点：普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。
、优化业务逻辑
要通过优化业务逻辑来提高性能是比较困难的，这需要程序员对所访问的数据及业务流程非常清楚。
举一个案例：
某移动公司推出优惠套参，活动对像为VIP会员并且，3月平均话费20元以上的客户。
那我们的检测逻辑为：
select avg(money) as avg_money from bill where phone_no='' and date between '201001' and '201003';
select vip_flag from member where phone_no='';
if avg_money&20 and vip_flag=true then
& 执行套参();
如果我们修改业务逻辑为:
select avg(money) as& avg_money from bill where phone_no='' and date between '201001' and '201003';
if avg_money&20 then
& select vip_flag from member where phone_no='';
& if vip_flag=true then
&&& 执行套参();
通过这样可以减少一些判断vip_flag的开销，平均话费20元以下的用户就不需要再检测是否VIP了。
如果程序员分析业务，VIP会员比例为1%，平均话费20元以上的用户比例为90%，那我们改成如下：
select vip_flag from member where phone_no='';
if vip_flag=true then
& select avg(money) as avg_money from bill where phone_no='' and date between '201001' and '201003';
& if avg_money&20 then
&&& 执行套参();
这样就只有1%的VIP会员才会做检测平均话费，最终大大减少了SQL的交互次数。
以上只是一个简单的示例，实际的业务总是比这复杂得多，所以一般只是高级程序员更容易做出优化的逻辑，但是我们需要有这样一种成本优化的意识。
、使用ResultSet游标处理记录
现在大部分Java框架都是通过jdbc从数据库取出数据，然后装载到一个list里再处理，list里可能是业务Object，也可能是hashmap。
由于JVM内存一般都小于4G，所以不可能一次通过sql把大量数据装载到list里。为了完成功能，很多程序员喜欢采用分页的方法处理，如一次从数据库取1000条记录，通过多次循环搞定，保证不会引起JVM Out of memory问题。
以下是实现此功能的代码示例，t_employee表有10万条记录，设置分页大小为1000：
d1 = Calendar.getInstance().getTime();
vsql = &select count(*) cnt from t_employee&;
pstmt = conn.prepareStatement(vsql);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
&&&&&&&& cnt = rs.getInt(&cnt&);
Integer lastid=0;
Integer pagesize=1000;
System.out.println(&cnt:& + cnt);
String vsql = &select count(*) cnt from t_employee&;
PreparedStatement pstmt = conn.prepareStatement(vsql);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
&&&&&&&& cnt = rs.getInt(&cnt&);
Integer lastid = 0;
Integer pagesize = 1000;
System.out.println(&cnt:& + cnt);
for (int i = 0; i &= cnt / i++) {
&&&&&&&& vsql = &select * from (select * from t_employee where id&? order by id) where rownum&=?&;
&&&&&&&& pstmt = conn.prepareStatement(vsql);
&&&&&&&& pstmt.setFetchSize(1000);
&&&&&&&& pstmt.setInt(1, lastid);
&&&&&&&& pstmt.setInt(2, pagesize);
&&&&&&&& rs = pstmt.executeQuery();
&&&&&&&& int col_cnt = rs.getMetaData().getColumnCount();
&&&&&&&& O
&&&&&&&& while (rs.next()) {
&&&&&&&&&&&&&&&&&& for (int j = 1; j &= col_ j++) {
&&&&&&&&&&&&&&&&&&&&&&&&&&& o = rs.getObject(j);
&&&&&&&&&&&&&&&&&& }
&&&&&&&&&&&&&&&&&& lastid = rs.getInt(&id&);
&&&&&&&& }
&&&&&&&& rs.close();
&&&&&&&& pstmt.close();
以上代码实际执行时间为6.516秒
很多持久层框架为了尽量让程序员使用方便，封装了jdbc通过statement执行数据返回到resultset的细节，导致程序员会想采用分页的方式处理问题。实际上如果我们采用jdbc原始的resultset游标处理记录，在resultset循环读取的过程中处理记录，这样就可以一次从数据库取出所有记录。显著提高性能。
这里需要注意的是，采用resultset游标处理记录时，应该将游标的打开方式设置为FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)，否则会把结果缓存在JVM里，造成JVM Out of memory问题。
代码示例：
String vsql =&select * from t_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();
while (rs.next()) {
&&&&&&&& for (int j = 1; j &= col_ j++) {
&&&&&&&&&&&&&&&&&& o = rs.getObject(j);
&&&&&&&& }
调整后的代码实际执行时间为3.156秒
从测试结果可以看出性能提高了1倍多，如果采用分页模式数据库每次还需发生磁盘IO的话那性能可以提高更多。
iBatis等持久层框架考虑到会有这种需求，所以也有相应的解决方案，在iBatis里我们不能采用queryForList的方法，而应用该采用queryWithRowHandler加回调事件的方式处理，如下所示：
MyRowHandler myrh=new&MyRowHandler();
sqlmap.queryWithRowHandler(&getAllEmployee&, myrh);
class&MyRowHandler&implements&RowHandler {
&&&&publicvoid&handleRow(Object o) {
&&&&&&&//todo something
iBatis的queryWithRowHandler很好的封装了resultset遍历的事件处理，效果及性能与resultset遍历一样，也不会产生JVM内存溢出。
、减少数据库服务器CPU运算
、使用绑定变量
绑定变量是指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*=400000
100*160=16000
从以上计算可以看出，不使用绑定变量的系统当并发达到8000时会在CPU上产生瓶颈，当使用绑定变量的系统当并行达到16000时会在磁盘IO上产生瓶颈。所以如果你的系统CPU有瓶颈时请先检查是否存在大量的硬解析操作。
使用绑定变量为何会提高SQL解析性能，这个需要从数据库SQL执行原理说明，一条SQL在Oracle数据库中的执行过程如下图所示：
当一条SQL发送给数据库服务器后，系统首先会将SQL字符串进行hash运算，得到hash值后再从服务器内存里的SQL缓存区中进行检索，如果有相同的SQL字符，并且确认是同一逻辑的SQL语句，则从共享池缓存中取出SQL对应的执行计划，根据执行计划读取数据并返回结果给客户端。
如果在共享池中未发现相同的SQL则根据SQL逻辑生成一条新的执行计划并保存在SQL缓存区中，然后根据执行计划读取数据并返回结果给客户端。
为了更快的检索SQL是否在缓存区中，首先进行的是SQL字符串hash值对比，如果未找到则认为没有缓存，如果存在再进行下一步的准确对比，所以要命中SQL缓存区应保证SQL字符是完全一致，中间有大小写或空格都会认为是不同的SQL。
如果我们不采用绑定变量，采用字符串拼接的模式生成SQL,那么每条SQL都会产生执行计划，这样会导致共享池耗尽，缓存命中率也很低。
一些不使用绑定变量的场景：
a、数据仓库应用，这种应用一般并发不高，但是每个SQL执行时间很长，SQL解析的时间相比SQL执行时间比较小，绑定变量对性能提高不明显。数据仓库一般都是内部分析应用，所以也不太会发生SQL注入的安全问题。
b、数据分布不均匀的特殊逻辑，如产品表，记录有1亿，有一产品状态字段，上面建有索引，有审核中，审核通过，审核未通过3种状态，其中审核通过9500万，审核中1万，审核不通过499万。
要做这样一个查询：
select count(*) from product where status=?
采用绑定变量的话，那么只会有一个执行计划，如果走索引访问，那么对于审核中查询很快，对审核通过和审核不通过会很慢；如果不走索引，那么对于审核中与审核通过和审核不通过时间基本一样；
对于这种情况应该不使用绑定变量，而直接采用字符拼接的方式生成SQL，这样可以为每个SQL生成不同的执行计划，如下所示。
select count(*) from product where status='approved'; //不使用索引
select count(*) from product where status='tbd'; //不使用索引
select count(*) from product where status='auditing';//使用索引
、合理使用排序
Oracle的排序算法一直在优化，但是总体时间复杂度约等于nLog(n)。普通OLTP系统排序操作一般都是在内存里进行的，对于数据库来说是一种CPU的消耗，曾在PC机做过测试，单核普通CPU在1秒钟可以完成100万条记录的全内存排序操作，所以说由于现在CPU的性能增强，对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上万条以上时，你需要注意是否一定要这么做了，大记录集排序不仅增加了CPU开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降，这种需求需要与DBA沟通再决定，取决于你的需求和数据，所以只有你自己最清楚，而不要被别人说排序很慢就吓倒。
以下列出了可能会发生排序操作的SQL语法：
Exists子查询
Not Exists子查询
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或者采用全文索引方案来提高性能。
不能使用索引定位的大量In List，如下所示：
a in (:1,:2,:3,…,:n)&& ----n&20
如果这里的a字段不能通过索引比较，那数据库会将字段与in里面的每个值都进行比较运算，如果记录数有上万以上，会明显感觉到SQL的CPU开销加大，这个情况有两种解决方式：
a、& 将in列表里面的数据放入一张中间小表，采用两个表Hash Join关联的方式处理；
b、& 采用str2varList方法将字段串列表转换一个临时表处理，关于str2varList方法可以在网上直接查询，这里不详细介绍。
以上两种解决方案都需要与中间表Hash Join的方式才能提高性能，如果采用了Nested Loop的连接方式性能会更差。
如果发现我们的系统IO没问题但是CPU负载很高，就有可能是上面的原因，这种情况不太常见，如果遇到了最好能和DBA沟通并确认准确的原因。
、大量复杂运算在客户端处理
什么是复杂运算，一般我认为是一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等。
如果有大量这类函数运算，尽量放在客户端处理，一般CPU每秒中也只能处理1万-10万次这样的函数运算，放在数据库内不利于高并发处理。
、利用更多的资源
、客户端多进程并行访问
多进程并行访问是指在客户端创建多个进程(线程)，每个进程建立一个与数据库的连接，然后同时向数据库提交访问请求。当数据库主机资源有空闲时，我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时，采用多进程并行访问性能不会提高，反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。
我们有10000个产品ID，现在需要根据ID取出产品的详细信息，如果单线程访问，按每个IO要5ms计算，忽略主机CPU运算及网络传输时间，我们需要50s才能完成任务。如果采用5个并行访问，每个进程访问2000个ID，那么10s就有可能完成任务。
那是不是并行数越多越好呢，开1000个并行是否只要50ms就搞定，答案肯定是否定的，当并行数超过服务器主机资源的上限时性能就不会再提高，如果再增加反而会增加主机的进程间调度成本和进程冲突机率。
以下是一些如何设置并行数的基本建议：
如果瓶颈在服务器主机，但是主机还有空闲资源，那么最大并行数取主机CPU核数和主机提供数据服务的磁盘数两个参数中的最小值，同时要保证主机有资源做其它任务。
如果瓶颈在客户端处理，但是客户端还有空闲资源，那建议不要增加SQL的并行，而是用一个进程取回数据后在客户端起多个进程处理即可，进程数根据客户端CPU核数计算。
如果瓶颈在客户端网络，那建议做数据压缩或者增加多个客户端，采用map reduce的架构处理。
如果瓶颈在服务器网络，那需要增加服务器的网络带宽或者在服务端将数据压缩后再处理了。
、数据库并行处理
数据库并行处理是指客户端一条SQL的请求，数据库内部自动分解成多个进程并行处理，如下图所示：
并不是所有的SQL都可以使用并行处理，一般只有对表或索引进行全部访问时才可以使用并行。数据库表默认是不打开并行访问，所以需要指定SQL并行的提示，如下所示：
select&/*+parallel(a,4)*/&*
并行的优点：
使用多进程处理，充分利用数据库主机资源（CPU,IO），提高性能。
并行的缺点：
1、单个会话占用大量资源，影响其它会话，所以只适合在主机负载低时期使用；
2、只能采用直接IO访问，不能利用缓存数据，所以执行前会触发将脏缓存数据写入磁盘操作。
1、并行处理在OLTP类系统中慎用，使用不当会导致一个会话把主机资源全部占用，而正常事务得不到及时响应，所以一般只是用于数据仓库平台。
2、一般对于百万级记录以下的小表采用并行访问性能并不能提高，反而可能会让性能更差。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：15466次
排名：千里之外
原创：37篇
转载：15篇
(1)(1)(1)(1)(1)(2)(4)(3)(7)(4)(1)(17)(1)(1)(2)(1)(3)(1)}