还剩44页未读，继续阅读
本资源只提供10页预览，全部文档请下载后查看！喜欢就下载吧，查找使用更方便
文本内容:
年版百万级数据库优化方案2023性能优化总结Oracle
一、百万级数据库优化方案.对查询优化，尽量避免全表扫描，首先应考虑在及涉及where0rder by1的列上建立索引.应尽量避免在子句中对字段进行值判断，否则将导致where null2引擎放弃使用索引而进行全表扫描，如select idfrom t where numis null复制最好不要给数据库留尽可能的使用填充数据库.NULL,NOT NULL备注、描述、评论之类的可以设置为其他的，最好不要使用NULL,NULLO不要以为不需要空间,比如型，在字段建立时，空间就固NULL charlOO定了，不管是否插入值也包含在内，都是占用个字符的空间的如NULL100果是这样的变长字段，不占用空间varchar null可以在上设置默认值,确保表中列没有值，然后这样查询num0num null从图上可以看到基本上每种设备都有两个指标:延时（响应时间）表示硬件的突发处理能力；带宽（吞吐量）代表硬件持续处理能力从上图可以看出，计算机系统硬件性能从高到代依次为（）内存——硬盘——网络——硬盘CPU——Cache Ll-L2-L3——SSD由于硬盘还处于快速发展阶段，所以本文的内容不涉及相关应用SSD SSD系统根据数据库知识，我们可以列出每种硬件主要的工作内容及内存缓存数据访问、比较、排序、事务检测、解析、函数或逻CPU SQL辑运算；网络结果数据传输、请求、远程数据库访问（）;SQL dblink硬盘数据访问、数据写入、日志记录、大数据量排序、大表连接根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下图所示的性能基本优化法则这个优化法则归纳为个层次:
5、减少数据访问（减少磁盘访问）、返回更少数据（减少网络传输或磁盘12访问）、减少交互次数（减少网络传输）
3、减少服务器开销（减少及内存开销）4CPU CPU、利用更多资源（增加资源）5由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低我们任何一个的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，SQL而不应该首先想到的是增加资源解决问题以下是每个优化法则层级对应优化效果及成本经验参考优化法则性能提升效果优化成本减少数据访问1^1000低1^100低返回更少数据减少交互次数V20低减少服务器CPU开销1~5低利用更多资源@~10高接下来，我们针对种优化法则列举常用的优化手段并结合实例分析5数据块是数据库中数据在磁盘中存储的最小单位，也是一次访问的最小单10位，一个数据块通常可以存储多条记录，数据块大小是在创建数据库或DBA表空间时指定,可指定为、、、或字节下图是一个2K4K8K16K32K Oracle数据库典型的物理结构，一个数据库可以包括多个数据文件，一个数据文件内又包含多个数据块；是每条记录在数据库中的唯一标识，通过可以直接定位记录R0WID R0WID到对应的文件号及数据块位置内容包括文件号、对像号、数据块号、R0WID记录槽号，如下图所示
三、数据库访问优化法则详解减少数据访问创建并使用正确的索引数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人很少，即使是专业的也不一定能完全做到最优DBA索引会大大增加表记录的）开销，正确DMLQNSERT,UPDATE,DELETE的索引可以让性能提升倍以上，不合理的索引也可能会让100,1000性能下降倍，因此在一个表中创建什么样的索引需要平衡各种业务100索引常见问题:索引有哪些种类？常见的索引有索引、位图索引、B-TREE全文索引，位图索引一般用于数据仓库应用,全文索引由于使用较少，这里不深入介绍索引包括很多扩展类型，如组合索引、反向索引、函数B-TREE索引等等，以下是索引的简单介绍B-TREE索引也称为平衡树索引它是一种按字段排好序的树B-TREE BalanceTree,形目录结构,主要用于提升查询性能和唯一约束支持索引的内容包B-TREE括根节点、分支节点、叶子节点叶子节点内容索引字段内容+表记录根节点，分支节点内容当一ROWID个数据块中不能放下所有索引字段数据时,就会形成树形的根节点或分支节点，根节点与分支节点保存了索引树的顺序及各层级间的引用关系一个普通的索引结构示意图如下所示:BTREE如果我们把一个表的内容认为是一本字典，那索引就相当于字典的目录,如下图所示图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首十笔划的组合索引一个表中可以建多个索引，就如一本字典可以建多个目录一样（按拼音、笔划、部首等等）一个索引也可以由多个字段组成，称为组合索引，如上图就是一个按部首+笔划的组合目录什么条件会使用索引？SQL当字段上建有索引时，通常以下情况会使用索引INDEX_COLUMN=INDEX_COLUMNINDEX_COLUMN=INDEX COLUMNINDEX COLUMN=INDEX_COLUMN betweenand・INDEX_COLUMN in,,..,后导模糊查询INDEX_COLUMN like||%两个表通过索引字段关联TL INDEX_COLUMN=T
2.COLUMN1什么条件不会使用索引？SQL不能使用索查询条件引原因不等于操作INDEX_COLUMNINDEX_COLUMN notin不能使用索引经过普通运functionINDEX_COLUMN=INDEX_COLUMN+1算或函数运=INDEX_COLUMN|Ia=算后的索引字段不能使用索引含前导模糊INDEX_COLUMN like||INDEX_COLUMN like查询的Like语法不能使用索引INDEX_COLUMN isnull B-TREE索弓I里不保存字段为NULL值记录，因此IS NULL不能使用索引NUMBER」NDEX_COLUMN=12345CHAR_INDEXJ OLUMN=12345Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引a.lNDEX_COLUMN=a.COLUMN_l给索引查询的值应是已知数据，不能是未知字段值注经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不，能使用索引如我们company表建了一个id+name的组合索引以下SQL是不能使用索引的Select*from company where name=Oracle9i后引入了一种index skipscan的索引方式来解决类似的问题，但是通过index skipscan提高性能的条件比较特殊，使用不好反而性能会更差我们一般在什么字段上建索引？这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件、字段出现在查询条件中，并且查询条件可以使用索引；
1、语句执行频率高，一天会有几千次以上；2建索、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？3引的字这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快段速评估小表（记录数小于行的表）:筛选比例10000V10%;大表（筛选返回记录数）〈（表总记录数*单条记录长度）单/10000/16条记录长度，字段平均内容长度之和+字段数以下是一些字段是否需要*2建索引的经验分类:B-TREE字常见字段名段类索型引需主ID.PK慎用外PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER」D,TRADE_ID,PAY」D字键段,需有HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO要对进像行或数身据份分标布识及忌、使义用字段日GMT_CREATE,GMT_MODIFIED期年YEAR,MONTH月状PRODU CT_STATUS,ORDE R_STATU S,IS_DELETE VIP_FLAGZ态标志类ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE型区COUNTRY,PROVINCE,CITY域操CREATOR,AUDITOR作场人景员详,e数LEVEL,AMOUNT.SCORE细值评,估长ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT字符不描DESCRIPTION,REMARK.MEMO,DETAIL适述合备建注索大FILE_CONTENT,EMAIL_CONTENT引字的段字段如何知道是否使用了正确的索引？简单可以根据索引使用语法规则SQL SQL判断，复杂的不好力、，判断的响应时间是一种策略，但是这会受SQL SQL到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里,可能全表访问的时间比索引访问时间还少要准确知道索引是否正确使用，需要到数据库中查看真实的执行计划，这个话题比较复杂，详见执行计划专题介SQL SQL绍索引对附加的开销有多少？DMLINSERT,UPDATE,DELETEselect idfrom twhere num=0复制复制.应尽量避免在子句中使用上或操作符，否则将引擎放弃使用索where3引而进行全表扫描.应尽量避免在子句中使用来连接条件，如果一个字段有索引，where or4一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描,如!1select idfrom twhere num=10or Name=admin可以这样查询㊀㊀㊀㊀s1ct idfrom twhere num=lOunion alls1ct idfrom twhere1Name=*admin复制和也要慎用，否则会导致全表扫描，如
5.in notin复制select idfrom twhere num in1,2,3复制对于连续的数值，能用就不要用了between in㊀㊀㊀select idfrom twhere numb twn1and3复制这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关,以下是一个普通表测试数据，仅供参考索引对于性能降低Insert56%索引对于性能降低Update47%索引对于性能降低Delete29%因此对于写压力比较大的系统，表的索引需要仔细评估必要性，另外索引10也会占用一定的存储空间、只通过索引访问数据
1.2有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘10开销如,select id,name fromcompanywheretype=*2;复制如果这个经常使用，我们可以在上创建组合索引SQL type,id,namecreate indexmy_comb_index oncompanytype,id,name;复制有了这个组合索引后，就可以直接通过索引返回SQL my_comb_index数据，不需要访问表company还是拿字典举例有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数如果一本字典有页，拼音目录有页，那我们的数据访问成本相100020当于全表访问的分之一50切记，性能优化是无止境的，当性能可以满足需求时即可，不要过度优化在实际数据库中我们不可能把每个请求的字段都建在索引里,所以这种只通SQL过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询、优化执行计划L3SQL执行计划是关系型数据库最核心的技术之一，它表示执行时的数据SQL SQL访问算法由于业务需求越来越复杂，表数据量也越来越大，程序员越来越懒惰，也需要支持非常复杂的业务逻辑，但的性能还需要提高，因此，SQL SQL优秀的关系型数据库除了需要支持复杂的语法及更多函数外，还需要有一SQL套优秀的算法库来提高性能SQL目前有执行计划的算法约种，而且一直在增加，所以ORACLE SQL300SQL执行计划是一个非常复杂的课题，一个普通能掌握种就很不错了，就DBA50算是资深也不可能把每个执行计划的算法描述清楚虽然有这么多种算DBA法，但并不表示我们无法优化执行计划，因为我们常用的执行计划算法SQL也就十几个，如果一个程序员能把这十几个算法搞清楚，那就掌握了的80%执行计划调优知识SQL由于篇幅的原因，执行计划需要专题介绍，在这里就不多说了SQL、返回更少的数据
2、数据分页处理
2.1一般数据分页方式有、客户端（应用程序或浏览器）分页
2.
1.1将数据从应用服务器全部下载到本地应用程序或浏览器，在应用程序或浏览器内部通过本地代码进行分页处理优点编码简单，减少客户端与应用服务器网络交互次数缺点首次交互时间长，占用客户端内存适应场景客户端与应用服务器网络延时较大，但要求后续操作流畅,如手机,超远程访问（跨国）等等GPRS、应用服务器分页
2.
1.2将数据从数据库服务器全部下载到应用服务器，在应用服务器内部再进行数据筛选以下是一个应用服务器端程序分页的示例Java〃；List list=executeQuery select*from employeeorder byid Intcount=list.size;List subList=list.subList10,20;复制优点编码简单，只需要一次交互，总数据与分页数据差不多时性能较SQL好缺点总数据量较多时性能较差适应场景数据库系统不支持分页处理，数据量较小并且可控、数据库分页
2.
1.3SQL采用数据库分页需要两次完成SQL SQL一个计算总数量SQL一个返回分页后的数据SQL优点性能好缺点编码复杂，各种数据库语法不同，需要两次交互SQL数据库一般采用来进行分页，常用分页语法有如下两oracle rownum种【接通过分页rownumselect*from selecta.*,rownum rnfromselect*from product a where company_id=or derby statusawhere rownum=20where rn10;复制数据访问开销二索引索引全部记录结果对应的表数据10+10采用分页语法rowid优化原理是通过纯索引找出分页记录的,再通过回表返回数ROWID ROWID据，要求内层查询和排序字段全在索引里create indexmyindex onproductcompany_id status;select b.*fro mzselect*from selecta.*,rownum rnfromselect rowidrid,status from producta wherecompany_id=order bystatusawhere rownum=20where rn10a,product bwherea.rid=b.rowid;复制数据访问开销二索引索引分页结果对应的表数据10+10实例一个公司产品有条记录,要分页取其中个产品，假设访问公100020司索引需要个条记录需要个表数据5010,21I0o那么按第一种分页写法，需要个,按ROWNUM55050+1000/210第二种分页写法,只需要个ROWID601050+20/2;、只返回需要的字段
2.2通过去除不必要的返回字段可以提高性能，例调整前㊀㊀㊀sei ct*from productwh rcompany_id=;调整后select id,name from product wherecompany_id=;复制优点、减少数据在网络上传输开销
1、减少服务器数据处理开销、减少客户端内存占用
23、字段变更时提前发现问题,减少程序4BUG、如果访问的所有字段刚好在一个索引里面,则可以使用纯索引访问提高性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减少交互次数
3.、
3.1batch DML数据库访问框架一般都提供了批量提交的接口，支持的提交处理jdbc batch方法，当你一次性要往一个表中插入万条数据时，如果采用普通的1000处理那么和服务器交互次数为万次，按每秒钟可以executellpdate1000向数据库服务器提交次估算，要完成所有工作需要秒如果采100001000用批量提交模式，条提交一次，那么和服务器交互次数为万次，交10001互次数大大减少采用操作一般不会减少很多数据库服务器的物理，batch10但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的开销CPU假设要向一个普通表插入万数据，每条记录大小为字节，表上没有10001K任何索引，客户端与数据库服务器网络是以下是根100Mbps,据现在一般计算机能力估算的各种大小性能对比值batchNo单位ms Batch=10Batch=100Batch=1000Batch=10000batch服务器事务处理
0.
10.
10.
10.
10.1时间服务器10处理
0.
020.2220200时间网络交互发
0.
10.
10.
10.
10.1起时间
0.
010.1110100网络数据传输时间小计
0.
230.
53.
230.
2300.2平均每条记
0.
230.
050.
0320.
03020.03002录处理时间从上可以看出操作加大可以对性能提高近倍性能般根据主,Insert Batch8键的或操作也可能提高倍性能，但不如明显，Update Delete2-3Insert因为及操作可能有比较大的开销在物理访问以上仅是Update Delete10理论计算值，实际情况需要根据具体环境测量、
3.2In List很多时候我们需要按一些查询数据库记录，我们可以采用一个个请求ID ID-发给数据库，如下所示㊀for:var inids[]do bginselect*from mytablewhere id=:var;end;复制我们也可以做一个小的优化，如下所示，用的这种方式写ID INLIST SQL:㊀㊀s1ct*from mytablewhere idin:idl,id2,...,idn;复制通过这样处理可以大大减少请求的数量，从而提高性能那如果有SQL个，那是不是全部放在一条里处理呢？答案肯定是否定的首10000ID SQL先大部份数据库都会有长度和里个数的限制，如的里SQL INORACLE IN就不允许超过个值1000另外当前数据库一般都是采用基于成本的优化规则，当数量达到一定值时IN有可能改变执行计划，从索引访问变成全表访问，这将使性能急剧变化SQL随着中的里面的值个数增加，的执行计划会更复杂，占用的内存SQL INSQL将会变大，这将会增加服务器及内存成本CPU评估在里面一次放多少个值还需要考虑应用服务器本地内存的开销,有并发IN访问时要计算本地数据使用周期内的并发上限，否则可能会导致内存溢出综合考虑，一般里面的值个数超过个以后性能基本没什么太大变化，也IN20特别说明不要超过,超过后可能会引起执行计划的不稳定性及增加数据库100及内存成本，这个需要专业评估CPU DBA、设置
3.3Fetch Size当我们采用从数据库查询数据时，数据默认并不是一条一条返回给客户select端的，也不是一次全部返回客户端的，而是根据客户端参数处理,fetch_size每次只返回条记录，当客户端游标遍fetch_size复制很多时候用代替是一个好的选择exists inselect num from awhere numinselect numfrom b复制用下面的语句替换selectnumfromawhere existsselect1from bwhere num=a.num复制.下面的查询也将导致全表扫描
6、％select idfrom twhere namelike abc%复制若要提高效率，可以考虑全文检索.如果在子句中使用参数，也会导致全表扫描因为只有在运7where SQL行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项应尽量避免在子句中对字段where进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描如㊀select idfrom twher num/2=100历到尾部时再从服务端取数据，直到最后全部传送完成所以如果我们要从服务端一次取大量数据时，可以加大这样可以减少结果数据传输的fetch_size,交互次数及服务器数据准备时间，提高性能以下是测试的代码，采用本地数据库，表缓存在数据库中，因此jdbc CACHE没有网络连接及磁盘开销，客户端只遍历游标，不做任何处理，这样更能10体现参数的影响:fetch“；㊀㊀㊀㊀㊀㊀㊀nString vsql=select*from mployPr pardStat mnt pstmt=conn.prepareStatementvsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY;pstmt.setFetchSize1000;ResultSet rs=pstmt.executeQueryvsql;int ent=rs.getMetaData.getColumnCount;Obj ecto;whilers.next{for inti=1;i=ent;i++{o=rs.getObject i;}复制测试示例中的表有条记录，每条记录平均长度employee100000135字节以下是测试结果，对每种测试次再取平均值:fetchsize5fetchsize elapse_time s
120.
516211.
3446.
89484.
65163.
584322.
865642.
6561282.
442562.
7655123.
07510242.
86220482.
72240962.
68181922.715默认值为由上测试可以看出对性能影Oracle jdbcfetchsize10,fetchsize响还是比较大的，但是当大于时就基本上没有影响了fetchsize100fetchsize并不会存在一个最优的固定值，因为整体性能与记录集大小及硬件平台有关根据测试结果建议当一次性要取大量数据时这个值设置为左右，不要小于100注意，不能设置太大,如果一次取出的数据大于的内存会导40fetchsize JVMo致内存溢出，所以建议不要超过太大了也没什么性能提高，反而可能会1000,增加内存溢出的危险注图中在以后会有一些小的波动这并不是测试误差，而是由于fetchsize128，填充到具体对像时间不同的原因由于已经到本地内存里resultset resultset了，所以估计是由于的命中率变化造成，由于变化不大，CPU L1,L2Cache所以笔者也未深入分析原因的配置文件可以对每个语句指定大小，如iBatis SqlMappingSQL fetchsize下所示n Hn nnselect id=getAllProduct resu1tMap=HashMap fetchSize=1000s elect*fromemployee/select复制、使用存储过程
3.4大型数据库一般都支持存储过程，合理的利用存储过程也可以提高系统性能如你有一个业务需要将表的数据做一些加工然后更新到表中，但是又不可A B能一条完成，这时你需要如下步操作SQL3:将表数据全部取出到客户端;a A:计算出要更新的数据；:将计算结果更新到表b cB如果采用存储过程你可以将整个业务逻辑封装在存储过程里，然后在客户端直接调用存储过程处理，这样可以减少网络交互的成本当然，存储过程也并不是十全十美，存储过程有以下缺点、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼a容多种数据库时最好不要用存储过程、学习成本高，一般都擅长写存储过程，但并不是每个程序员都能写好b DBA存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑c不是在应用程序里处理,这种架构会增加一些系统维护和调试成本、存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满d足需求，有些逻辑就只能通过应用程序处理、如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，e对于集中式数据库可能会导致系统可扩展性问题、为了提高性能,数据库会把存储过程代码编译成中间运行代码（类似于f java的文件）所以更像静态语言当存储过程引用的对像俵、视图等等）结class构改变后，存储过程需要重新编译才能生效，在高并发应用场景，一般24*7都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障（数据库Oracle就存在这样的问题）个人观点普通业务逻辑尽量不要使用存储过程，定时性的任务或报表统ETL计函数可以根据团队资源情况采用存储过程处理、优化业务逻辑
3.5要通过优化业务逻辑来提高性能是比较困难的，这需要程序员对所访问的数据及业务流程非常清楚举一个案例某移动公司推出优惠套参，活动对像为会员并且年VIP20101,2,3月平均话费元以上的客户20那我们的检测逻辑为（）Tselect avgmoney asavg_money frombill wherephone_no=*13988888888and!1T fdate between201001and201003;select vip_flag frommember where!fphone_no=;if avg_money20and vip_flag=true thenbegin执行套参（）；end;复制如果我们修改业务逻辑为:㊀sei ctavgmoney asavg_money frombill whereT1!1!phone_no=13988888888*and datebetween201001and201003;if avgmoney20then begin1select vip_flag frommember wherephone_no=if vip_flag=true thenbegin执行套参;end;end;复制通过这样可以减少一些判断的开销，平均话费元以下的用户就不vip_flag20需要再检测是否了VIP如果程序员分析业务会员比例为，平均话费元以上的用户比例为,VIP1%20那我们改成如下90%,1select vip_flag frommember wherephone_no=;if vip_f lag=true thenbeginselectavgmoney asavg_money frombill wherephone_no=*13988888888*and11datebetween1201001and,201003;if avg_money20thenbegin执行套参0;end;end;复制这样就只有的会员才会做检测平均话费，最终大大减少了的交1%VIP SQL互次数以上只是一个简单的示例，实际的业务总是比这复杂得多，所以一般只是高级程序员更容易做出优化的逻辑，但是我们需要有这样一种成本优化的意识、使用游标处理记录
3.6ResultSet现在大部分框架都是通过从数据库取出数据，然后装载到一个Java jdbclist里再处理，里可能是业务也可能是由于内存一般list Object,hashmap JVM0都小于，所以不可能一次通过把大量数据装载到里为了完成功能，4G sqllist很多程序员喜欢采用分页的方法处理，如一次从数据库取条记录,通过1000多次循环搞定，保证不会引起问题JVM Out of memory以下是实现此功能的代码示例，表有万条记录，设置分页大t.employee10小为1000:ndl=Calendar.getInstance.getTime;vsql=select count*ent fromnt_employee;pstmt=conn.prepareStatementvsql;ResultSet r工㊀㊀㊀㊀㊀㊀s=pstmt.x cutQu ry;nt gr ent=0;while rs.next{nent=rs.getlntcnt;}Integer lastid=0;Integer pagesiznne=1000;System.out.printIn cnt:+ent;String vsql=select count*ent fromHt_employee;PreparedStatement pstmt=conn.prepare Statementvsql;ResultSetrs=pstmt.executeQuery;Integer ent=0;while rs.next{Hent=rs.getlnt ent;}Integer lastid=0;Integer Inent:+ent;for inti=0;i=ent/pagesize;i++{工㊀㊀vsql=select*from sct*from t_employee wherei dordernby idwhere rownum=;・㊀㊀㊀㊀pstmt=conn prpar Statm ntvsql;pstmt.setFetchSize1000;pstmt.setlnt1,lastid;pstmt.setlnt2,pagesize;rs=pstmt.executeQuery;int col_cnt=rs.getMetaData.getColumnCount;Object o;while rs.next{for intj=1;j=col_cnt;j++{o=rs.getObject j;lastid=rs.getlntid;rs,close;pstmt.close;}复制以上代码实际执行时间为秒很多持久层框架为了尽量让程序员使用方便，
6.516封装了通过执行数据返回到的细节，导致程序员jdbc statementresultset会想采用分页的方式处理问题实际上如果我们采用原始的jdbc resultset游标处理记录，在循环读取的过程中处理记录，这样就可以一次从数resultset据库取出所有记录显著提高性能这里需要注意的是，采用游标处理记录时，应该将游标的打开方式resultset设置为模式（FORWARD_READONLYResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ON否则会把结果缓存在里，造成问题LY,JVM JVMOutofmemory代码示例n nStringvsql=select*from t_employee;PreparedStatement pstmt=conn,prepareStatementvsql ResuitSet.TYPE_FORWARD_ONLY ResultSet.A Z・CONCUR_READ_ONLY;pstmt.setFetchSize100;ResultSet rs=pstmt ex・㊀ecuteQueryvsql;int col_cnt=rs getMetaData.getColumnCount;Obj cto;while rs.next{for intj=1;j=col_cnt;j++{o=rs.getObject j;}复制调整后的代码实际执行时间为秒
3.156从测试结果可以看出性能提高了倍多，如果采用分页模式数据库每次还需发1生磁盘的话那性能可以提高更多10等持久层框架考虑到会有这种需求，所以也有相应的解决方案,在iBatis iBatis里我们不能采用的方法,而应用该采用queryForList queryWithRowHandler力口回调事件的方式处理，如下所示MyRowHandler myrh=new MyRowHandler;n nsqlmap.queryWithRowHandler getAllEmployee,myrh;class MyRowHandlerimplements RowHandler{public voidhandleRowObject o{//todo something复制的很好的封装了遍历的事件处理，iBatis queryWithRowHandlerresultset效果及性能与遍历一样，也不会产生内存溢出resultset JVM、减少数据库服务器运算4CPU、使用绑定变量
4.1绑定变量是指中对变化的值采用变量参数的形式提交，而不是在中SQL SQL直接拼写对应的值应改为select idfrom twherenum=100*2复制.应尽量避免在子句中对字段进行函数操作这将导致引擎放弃使用索9where引而进行全表扫描不要在子句中的一左边进行函数、算术运算或其他表达式运算，
10.where否则系统将可能无法正确使用索引.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该11索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致.不要写一些没有意义的查询，如需要生成一个空表结构12select coll,col2into#t from twhere1=0复制这类代码不会返回任何结果集，但是会消耗系统资源的语句，如果只更改、个字段，不要全部字段,否则频
13.Update12Update繁调用会引起明显的性能消耗，同时带来大量日志.对于多张大数据量（这里几百条就算大了）的表,要先分页再14JOIN JOIN,否则逻辑读会很高，性能很差非绑定变量写法:Select*from employeewhere id=1234567复制绑定变量写法Select*from employwhere id=㊀㊀㊀㊀Pr parstat mnt.setlnt1,1234567复制中就是为处理绑定变量提供的对像，绑定变量有以Java Preparestatement下优点、防止注入1SQL、提高可读性2SQL、提高解析性能，不使用绑定变更我们一般称为硬解析，使用绑定变量3SQL我们称为软解析第和第点很好理解，做编码的人应该都清楚，这里不详细说明关于第123点，到底能提高多少性能呢，下面举一个例子说明假设有这个这样的一个数据库主机个核24CPU块磁盘,每个磁盘支持为100IOPS160业务应用的如下SQLselect*from tablewhere pk=这个平均个（个索引个数据）SQL4IO3IO+1IO（缓存命中率索引全在内存中，数据需要访问磁盘）IO75%硬解析消耗（常用经验值）SQL CPU1ms软解析消耗（常用经验值）SQL CPU
0.02ms假设每核性能是线性增长，访问内存中的时间忽略,要求计算CPU CacheIO系统对如上应用采用硬解析与采用软解析支持的每秒最大并发数是否使用绑定变量CPU支持最大并发数磁盘10支持最大并发数不使用2*4*1000=8000100*160=16000使用2*4*1000/
0.02=400000100*160=16000从以上计算可以看出，不使用绑定变量的系统当并发达到时会在上8000CPU产生瓶颈，当使用绑定变量的系统当并行达到时会在磁盘上产生瓶16000IO颈所以如果你的系统有瓶颈时请先检查是否存在大量的硬解析操作CPU使用绑定变量为何会提高解析性能，这个需要从数据库执行原理说SQL SQL明，一条在数据库中的执行过程如下图所示SQL Oracle当一条发送给数据库服务器后，系统首先会将字符串进行运SQL SQLhash算，得到值后再从服务器内存里的缓存区中进行检索，如果有相同hash SQL的字符，并且确认是同一逻辑的语句，则从共享池缓存中取出SQL SQL SQL对应的执行计划，根据执行计划读取数据并返回结果给客户端如果在共享池中未发现相同的则根据逻辑生成一条新的执行计划并SQL SQL保存在缓存区中，然后根据执行计划读取数据并返回结果给客户端SQL为了更快的检索是否在缓存区中首先进行的是字符串值对比，SQL SQLhash如果未找到则认为没有缓存，如果存在再进行下一步的准确对比，所以要命中缓存区应保证字符是完全一致，中间有大小写或空格都会认为是不SQL SQL同的SQL如果我们不采用绑定变量，采用字符串拼接的模式生成那么每条都SQL,SQL会产生执行计划，这样会导致共享池耗尽，缓存命中率也很低一些不使用绑定变量的场景、数据仓库应用,这种应用一般并发不高，但是每个执行时间很长，a SQLSQL解析的时间相比执行时间比较小，绑定变量对性能提高不明显数据仓SQL库一般都是内部分析应用，所以也不太会发生注入的安全问题SQL、数据分布不均匀的特殊逻辑，如产品表，记录有亿，有一产品状态字段，b1上面建有索引，有审核中，审核通过，审核未通过种状态,其中审核通过39500万,审核中万,审核不通过万1499要做这样一个查询select count*from product where status=采用绑定变量的话，那么只会有一个执行计划，如果走索引访问，那么对于审核中查询很快，对审核通过和审核不通过会很慢；如果不走索引，那么对于审核中与审核通过和审核不通过时间基本一样；对于这种情况应该不使用绑定变量，而直接采用字符拼接的方式生成这样可以为每个生成不同的执行计划，如下所示SQL,SQL//不使用索弓11select count*fromproductwhere status=approved;I select//不使用索弓T1count*fromproductwhere status=tbd;I select count*from使用索弓，productwherestatus=auditing//I复制的排序算法一直在优化但是总体时间复杂度约等于Oracle nLogno普通系统排序操作一般都是在内存里进行的，对于数据库来说是一种OLTP的消耗，曾在机做过测试，单核普通在秒钟可以完成CPU PCCPU1100万条记录的全内存排序操作，所以说由于现在的性能增强，对于普通的CPU几十条或上百条记录排序对系统的影响也不会很大但是当你的记录集增加到上万条以上时，你需要注意是否一定要这么做了,大记录集排序不仅增加了开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘CPU排序时性能会急剧下降，这种需求需要与沟通再决定，取决于你的需求DBA和数据，所以只有你自己最清楚，而不要被别人说排序很慢就吓倒以下列出了可能会发生排序操作的语法:SQLOrder byGroupbyDistinct子查询Exists子查询Not Exists子查询In子查询Not In（并集），也是一种并集操作，但是不会发生排序，如果Union UnionAll你确认两个数据集不需要执行去除重复数据操作，那请使用代替Union AllUniono（）差集Minus（交集）IntersectCreate Index这是一种两个表连接的内部算法，执行时会把两个表先排序好Merge Join,再连接，应用于两个大表连接的操作如果你的两个表连接的条件都是等值运算，那可以采用来提高性能，因为使用运算Hash JoinHash JoinHash来代替排序的操作具体原理及设置参考执行计划优化专题SQL、减少比较操作
4.3我们的业务逻辑经常会包含一些比较操作，如之类的操作，SQL a=b,ab对于这些比较操作数据库都体现得很好，但是如果有以下操作,我们需要保持警惕模糊直询，如下所示Likea like%abc%模糊查询对于数据库来说不是很擅长，特别是你需要模糊检查的记录有Like上万条以上时，性能比较糟糕，这种情况一般可以采用专用或者采用Search全文索引方案来提高性能不能使用索引定位的大量如下所示In List,a inL
23...,:n——n20如果这里的字段不能通过索引比较，那数据库会将字段与里面的每个值a in都进行比较运算，如果记录数有上万以上，会明显感觉到的开销加SQL CPU大,这个情况有两种解决方式、将列表里面的数据放入一张中间小表,采用两个表关联的方a inHash Join式处理；、采用方法将字段串列表转换一个临时表处理，关于b str2varList方法可以在网上直接查询，这里不详细介绍str2varList以上两种解决方案都需要与中间表的方式才能提高性能，如果采Hash Join用了的连接方式性能会更差Nested Loop如果发现我们的系统没问题但是负载很高，就有可能是上面的原因，I CPU这种情况不太常见，如果遇到了最好能和沟通并确认准确的原因DBA什么是复杂运算，一般我认为是一秒钟只能做万次以内的运算如CPU10含小数的对数及指数运算、三角函数、及数据加密算;士卒生3DES BASE64如果有大量这类函数运算，尽量放在客户端处理，一般每秒中也只能处CPU理万万次这样的函数运算，放在数据库内不利于高并发处理1-
10、利用更多的资源
5、客户端多进程并行访问
5.1多进程并行访问是指在客户端创建多个进程（线程），每个进程建立一个与数据库的连接，然后同时向数据库提交访问请求当数据库主机资源有空闲时，我们可以采用客户端多进程并行访问的方法来提高性能如果数据库主机已经很忙时，采用多进程并行访问性能不会提高，反而可能会更慢所以使用这种方式最好与或系统管理员进行沟通后再决定是否米用DBA例如我们有个产品现在需要根据取出产品的详细信息，如10000ID,ID果单线程访问，按每个要计算，忽略主机运算及网络传输时105ms CPU间，我们需要才能完成任务如果采用个并行访问，每个进程访问50s52000个那么就有可能完成任务ID,10s那是不是并行数越多越好呢,开个并行是否只要就搞定,答案肯100050ms定是否定的，当并行数超过服务器主机资源的上限时性能就不会再提高，如果再增加反而会增加主机的进程间调度成本和进程冲突机率以下是一些如何设置并行数的基本建议:如果瓶颈在服务器主机，但是主机还有空闲资源，那么最大并行数取主机核数和主机提供数据服务的磁盘数CPU两个参数中的最小值，同时要保证主机有资源做其它任务如果瓶颈在客户端处理，但是客户端还有空闲资源，那建议不要增加的SQL并行，而是用一个进程取回数据后在客户端起多个进程处理即可,进程数根据客户端核数计算CPU如果瓶颈在客户端网络,那建议做数据压缩或者增加多个客户端，采用map的架构处理reduce如果瓶颈在服务器网络，那需要增加服务器的网络带宽或者在服务端将数据压缩后再处理了、数据库并行处理
5.2数据库并行处理是指客户端一条的请求，数据库内部自动分解成多个进SQL程并行处理，如下图所示并不是所有的都可以使用并行处理，一般只有对表或索引进行全部访问SQL时才可以使用并行数据库表默认是不打开并行访问，所以需要指定并SQL行的提示，如下所示select/*+parallela4*/*from employee;f并行的优点使用多进程处理，充分利用数据库主机资源，提高性能CPUJO并行的缺点、单个会话占用大量资源，影响其它会话，所以只适合在主机负载低时期使1用；、只能采用直接访问，不能利用缓存数据，所以执行前会触发将脏缓存210数据写入磁盘操作注、并行处理在类系统中慎用，使用不当会导致一个会话把主机资源1OLTP全部占用，而正常事务得不到及时响应，所以一般只是用于数据仓库平台、一般对于百万级记录以下的小表采用并行访问性能并不能提高，反而可能2会让性能更差;这样不带任何条件的会引起全表
15.selectcount*from tablecount扫描，并且没有任何业务意义，是一定要杜绝的.索引并不是越多越好，索引固然可以提高相应的的效率，但同时16select也降低了及的效率，因为或时有可能会重建insert updateinsert update索引，所以怎样建索引需要慎重考虑，视具体情况而定一个表的索引数最好不要超过个，若太多则应考虑一些不常使用到的列上建的索引是否有必6要.应尽可能的避免更新索引数据列，因为索引数据17clustered clustered列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源若应用系统需要频繁更新索clustered引数据列，那么需要考虑是否应将该索引建为索引clustered.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，18这会降低查询和连接的性能，并会增加存储开销这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了.尽可能的使用代替,因为首先变长字段19varchar/nvarchar char/nchar存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些任何地方都不要使用，用具体的字段列表代替“’，不
20.select*fromt要返回用不到的任何字段.尽量使用表变量来代替临时表如果表变量包含大量数据，请注意索引21非常有限（只有主键索引）避免频繁创建和删除临时表，以减少系统表资源的消耗临时表并不是
22.不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时但是，对于一次性事件,最好使用导出表.在新建临时表时,如果一次性插入数据量很大，那么可以使用23select into代替,避免造成大量,以提高速度；如果数据量不大,为了create tablelog缓和系统表的资源,应先,然后create tableinserto.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，24先,然后,这样可以避免系统表的较长时间锁定truncate tabledrop table.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过251万行，那么就应该考虑改写.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来26解决问题，基于集的方法通常更有效.与临时表一样，游标并不是不可使用对小型数据集使用27游标通常要优于其他逐行处理方法，尤其是在必须引用几FAST_FORWARD个表才能获得所需的数据时在结果集中包括“合计”的例程通常要比使用游标执行的速度快如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好.在所有的存储过程和触发器的开始处设置28SET NOCOUNTON,在结束时设置SET NOCOUNTOFF无需在执行存储过程和触发器o的每个语句后向客户端发送」消息DONE N_PROC.尽量避免大事务操作，提高系统并发能力
29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是30否合理
二、数据库访问性能优化特别说明、本文只是面对数据库应用开发的程序员，不适合专业在数据1DBA,DBA库性能优化方面需要了解更多的知识；、本文许多示例及概念是基于数据库描述，对于其它关系型数据库2Oracle也可以参考，但许多观点不适合于数据库或内存数据库或者是基于KV SSD技术的数据库；、本文未深入数据库优化中最核心的执行计划分析技术3读者对像开发人员如果你是做数据库开发，那本文的内容非常适合,因为本文是从程序员的角度来谈数据库性能优化架构师如果你已经是数据库应用的架构师，那本文的知识你应该清楚90%,否则你可能是一个喜欢折腾的架构师（数据库管理员）大型数据库优化的知识非常复杂，本文只是从程序DBA员的角度来谈性能优化，除了需要了解这些知识外，还需要深入数据库DBA的内部体系架构来解决问题在网上有很多文章介绍数据库优化知识，但是大部份文章只是对某个一个方面进行说明，而对于我们程序员来说这种介绍并不能很好的掌握优化知识，因为很多介绍只是对一些特定的场景优化的，所以反而有时会产生误导或让程序员感觉不明白其中的奥妙而对数据库优化感觉很神秘很多程序员总是问如何学习数据库优化，有没有好的教材之类的问题在书店也看到了许多数据库优化的专业书籍但是感觉更多是面向或者是DBA开发方面的知识，个人感觉不太适合普通程序员而要想做到数据PL/SQL库优化的高手，不是花几周，几个月就能达到的，这并不是因为数据库优化有多高深，而是因为要做好优化一方面需要有非常好的技术功底，对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基础知识，另一方面是需要花大量时间对特定的数据库进行实践测试作为一个程序员，我们也许不清楚线上正式的服务器硬件配置，我们不可能像那样专业的对数据库进DBA行各种实践测试与总结，但我们都应该非常了解我们的业务逻辑，我们SQL清楚中访问表及字段的数据情况，我们其实只关心我们的是否能SQLSQL尽快返回结果那程序员如何利用已知的知识进行数据库优化？如何能快速定位性能问题并找到正确的优化方向？面对这些问题，笔者总结了一SQL些面向程序员的基本优化法则，本文将结合实例来坦述数据库开发的优化知识要正确的优化我们需要快速定位能性的瓶颈点，也就是说快速找到我SQL,们主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下SQL载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是硬盘是每分钟转等2Mbps,7200等因此,为了快速找到的性能瓶颈点,我们也需要了解我们计算机系统SQL的硬件基本性能指标，下图展示的当前主流计算机性能指标数据。
个人认证
优秀文档
获得点赞 0
