面向程序员的数据库拜访功能优化规律tengxun - 凯发娱乐

面向程序员的数据库拜访功能优化规律tengxun

2019-02-13 09:58:58 | 作者: 梦秋 | 标签: 数据库,数据,优化 | 浏览: 7138

特别阐明:

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的功用优化都应该按这个规矩由上到下来确诊问题并提出处理计划,而不应该首要想到的是添加资源处理问题。

以下是每个优化规律层级对应优化作用及本钱经历参阅:



优化规律


功用进步作用


优化本钱

削减数据拜访


1~1000




回来更少数据


1~100




削减交互次数


1~20




削减服务器CPU开支


1~5




运用更多资源


@~10






接下来,咱们针对5种优化规律罗列常用的优化手法并结合实例剖析。


二、Oracle数据库两个根本概念
数据块(Block)

数据块是数据库中数据在磁盘中存储的最小单位,也是一次IO拜访的最小单位,一个数据块一般能够存储多条记载,数据块巨细是DBA在创立数据库或表空间时指定,可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构,一个数据库能够包括多个数据文件,一个数据文件内又包括多个数据块;




ROWID

ROWID是每条记载在数据库中的仅有标识,经过ROWID能够直接定位记载到对应的文件号及数据块方位。ROWID内容包括文件号、对像号、数据块号、记载槽号,如下图所示:


三、数据库拜访优化规律详解
1、削减数据拜访
1.1、创立并运用正确的索引

数据库索引的原理十分简略,但在杂乱的表中实在能正确运用索引的人很少,即使是专业的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索引的经历分类:






字段类型


常见字段名

需求建索引的字段


主键


ID,PK

外键


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压力比较大的体系,表的索引需求细心评价必要性,别的索引也会占用必定的存储空间。


1.2、只经过索引拜访数据

有些时分,咱们仅仅拜访表中的几个字段,并且字段内容较少,咱们能够为这几个字段独自树立一个组合索引,这样就能够直接只经过拜访索引就能得到数据,一般索引占用的磁盘空间比表小许多,所以这种办法能够大大削减磁盘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恳求的字段都建在索引里,所以这种只经过索引拜访数据的办法一般只用于中心运用,也就是那种对中心表拜访量最高且查询字段数据量很少的查询。
1.3、优化SQL履行计划

SQL履行计划是联系型数据库最中心的技能之一,它表明SQL履行时的数据拜访算法。由于业务需求越来越杂乱,表数据量也越来越大,程序员越来越懒散,SQL也需求支撑十分杂乱的业务逻辑,但SQL的功用还需求进步,因而,优异的联系型数据库除了需求支撑杂乱的SQL语法及更多函数外,还需求有一套优异的算法库来进步SQL功用。

现在ORACLE有SQL履行计划的算法约300种,并且一直在添加,所以SQL履行计划是一个十分杂乱的课题,一个一般DBA能把握50种就很不错了,就算是资深DBA也不或许把每个履行计划的算法描绘清楚。虽然有这么多种算法,但并不表明咱们无法优化履行计划,由于咱们常用的SQL履行计划算法也就十几个,假如一个程序员能把这十几个算法搞清楚,那就把握了80%的SQL履行计划调优常识。

由于篇幅的原因,SQL履行计划需求专题介绍,在这儿就不多说了。


2、回来更少的数据
2.1、数据分页处理

一般数据分页办法有:
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

数据拜访开支=索引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.rowid;

数据拜访开支=索引IO+索引分页成果对应的表数据IO



实例:

一个公司产品有1000条记载,要分页取其间20个产品,假定拜访公司索引需求50个IO,2条记载需求1个表数据IO。

那么按第一种ROWNUM分页写法,需求550(50+1000/2)个IO,按第二种ROWID分页写法,只需求60个IO(50+20/2);


2.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表。


3、削减交互次数
3.1、batch DML

数据库拜访结构一般都供给了批量提交的接口,jdbc支撑batch的提交处理办法,当你一次性要往一个表中刺进1000万条数据时,假如选用一般的executeUpdate处理,那么和服务器交互次数为1000万次,按每秒钟能够向数据库服务器提交10000次预算,要完结一切作业需求 1000秒。假如选用批量提交办法,1000条提交一次,那么和服务器交互次数为1万次,交互次数大大削减。选用batch操作一般不会削减许多数据库服务器的物理IO,可是会大大削减客户端与服务端的交互次数,然后削减了屡次主张的网络延时开支,一起也会下降数据库的CPU开支。



假定要向一个一般表刺进1000万数据,每条记载巨细为1K字节,表上没有任何索引,客户端与数据库服务器网络是100Mbps,以下是依据现在一般核算机才干预算的各种batch巨细功用比照值:



单位:ms


No batch


Batch=10


Batch=100


Batch=1000


Batch=10000

服务器业务处理时刻


0.1


0.1


0.1


0.1


0.1

服务器IO处理时刻


0.02


0.2


2


20


200

网络交互主张时刻


0.1


0.1


0.1


0.1


0.1

网络数据传输时刻


0.01


0.1


1


10


100

小计


0.23


0.5


3.2


30.2


300.2

均匀每条记载处理时刻


0.23


0.05


0.032


0.0302


0.03002



从上能够看出,Insert操作加大Batch能够对功用进步近8倍功用,一般依据主键的Update或Delete操作也或许进步2-3倍功用,但不如Insert显着,由于Update及Delete操作或许有比较大的开支在物理IO拜访。以上仅是理论核算值,实践状况需求依据具体环境丈量。


3.2、In List

许多时分咱们需求按一些ID查询数据库记载,咱们能够选用一个ID一个恳求发给数据库,如下所示:

for :var in ids[] do begin

  select * from mytable where id=:var;

end;



咱们也能够做一个小的优化, 如下所示,用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评价。


3.3、设置Fetch Size

当咱们选用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();

Object o;

while (rs.next()) {

  for (int i = 1; i = cnt; i++) {

  o = rs.getObject(i);

  }

}



测验示例中的employee表有100000条记载,每条记载均匀长度135字节



以下是测验成果,对每种fetchsize测验5次再取均匀值:

fetchsize


elapse_time(s)

1


20.516

2


11.34

4


6.894

8


4.65

16


3.584

32


2.865

64


2.656

128


2.44

256


2.765

512


3.075

1024


2.862

2048


2.722

4096


2.681

8192


2.715







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

/select


3.4、运用存储进程

大型数据库一般都支撑存储进程,合理的运用存储进程也能够进步体系功用。如你有一个业务需求将A表的数据做一些加工然后更新到B表中,可是又不或许一条SQL完结,这时你需求如下3步操作:

a:将A表数据悉数取出到客户端;

b:核算出要更新的数据;

c:将核算成果更新到B表。



假如选用存储进程你能够将整个业务逻辑封装在存储进程里,然后在客户端直接调用存储进程处理,这样能够削减网络交互的本钱。

当然,存储进程也并不是完美无瑕,存储进程有以下缺陷:

a、不行移植性,每种数据库的内部编程语法都不太相同,当你的体系需求兼容多种数据库时最好不要用存储进程。

b、学习本钱高,DBA一般都拿手写存储进程,但并不是每个程序员都能写好存储进程,除非你的团队有较多的开发人员了解写存储进程,不然后期体系维护会发作问题。

c、业务逻辑多处存在,选用存储进程后也就意味着你的体系有一些业务逻辑不是在运用程序里处理,这种架构会添加一些体系维护和调试本钱。

d、存储进程和常用运用程序言语纷歧样,它支撑的函数及语法有或许不能满意需求,有些逻辑就只能经过运用程序处理。

e、假如存储进程中有杂乱运算的话,会添加一些数据库服务端的处理本钱,关于集中式数据库或许会导致体系可扩展性问题。

f、为了进步功用,数据库会把存储进程代码编译成中心运转代码(相似于java的class文件),所以更像静态言语。当存储进程引证的对像 (表、视图等等)结构改动后,存储进程需求从头编译才干收效,在24*7高并发运用场景,一般都是在线改动结构的,所以在改动的瞬间要一起编译存储进程,这或许会导致数据库瞬间压力上升引起毛病(Oracle数据库就存在这样的问题)。



个人观念:一般业务逻辑尽量不要运用存储进程,守时性的ETL使命或报表核算函数能够依据团队资源状况选用存储进程处理。


3.5、优化业务逻辑

要经过优化业务逻辑来进步功用是比较困难的,这需求程序员对所拜访的数据及业务流程十分清楚。

举一个事例:

某移动公司推出优惠套参,活动对像为VIP会员并且2010年1,2,3月均匀话费20元以上的客户。

那咱们的检测逻辑为:

select avg(money) as avg_money from bill where phone_no=13988888888 and date between 201001 and 201003;

select vip_flag from member where phone_no=13988888888;

if avg_money 20 and vip_flag=true then

begin

  履行套参();

end;



假如咱们修正业务逻辑为:

select avg(money) as  avg_money from bill where phone_no=13988888888 and date between 201001 and 201003;

if avg_money 20 then

begin

  select vip_flag from member where phone_no=13988888888;

  if vip_flag=true then

  begin

  履行套参();

  end;

end;

经过这样能够削减一些判别vip_flag的开支,均匀话费20元以下的用户就不需求再检测是否VIP了。



假如程序员剖析业务,VIP会员份额为1%,均匀话费20元以上的用户份额为90%,那咱们改成如下:

select vip_flag from member where phone_no=13988888888;

if vip_flag=true then

begin

  select avg(money) as avg_money from bill where phone_no=13988888888 and date between 201001 and 201003;

  if avg_money 20 then

  begin

  履行套参();

  end;

end;

这样就只需1%的VIP会员才会做检测均匀话费,终究大大削减了SQL的交互次数。



以上仅仅一个简略的示例,实践的业务总是比这杂乱得多,所以一般仅仅高档程序员更简单做出优化的逻辑,可是咱们需求有这样一种本钱优化的认识。


3.6、运用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 / pagesize; 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();

  Object o;

  while (rs.next()) {

  for (int j = 1; j = col_cnt; 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();

Object o;

while (rs.next()) {

  for (int j = 1; j = col_cnt; 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 {

  public void handleRow(Object o) {

  //todo something

  }

}



iBatis的queryWithRowHandler很好的封装了resultset遍历的事情处理,作用及功用与resultset遍历相同,也不会发作JVM内存溢出。


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数据库中的履行进程如下图所示:







当一条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;//运用索引


4.2、合理运用排序

Oracle的排序算法一直在优化,可是整体时刻杂乱度约等于nLog(n)。一般OLTP体系排序操作一般都是在内存里进行的,关于数据库来说是一种CPU的耗费,曾在PC机做过测验,单核一般CPU在1秒钟能够完结100万条记载的全内存排序操作,所以说由于现在CPU的功用增强,关于一般的几十条或上百条记载排序对体系的影响也不会很大。可是当你的记载集添加到上万条以上时,你需求留意是否必定要这么做了,大记载集排序不只添加了CPU开支,并且或许会由于内存不足发作硬盘排序的现象,当发作硬盘排序时功用会急剧下降,这种需求需求与DBA交流再决议,取决于你的需求和数据,所以只需你自己最清楚,而不要被他人说排序很慢就吓倒。

以下列出了或许会发作排序操作的SQL语法:

Order by

Group by

Distinct

Exists子查询

Not Exists子查询

In子查询

Not In子查询

Union(并集),Union All也是一种并集操作,可是不会发作排序,假如你承认两个数据集不需求履行去除重复数据操作,那请运用Union All 替代Union。

Minus(差集)

Intersect(交集)

Create Index

Merge Join,这是一种两个表衔接的内部算法,履行时会把两个表先排序好再衔接,运用于两个大表衔接的操作。假如你的两个表衔接的条件都是等值运算,那能够选用Hash Join来进步功用,由于Hash Join运用Hash 运算来替代排序的操作。具体原理及设置参阅SQL履行计划优化专题。


4.3、削减比较操作

咱们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交流并承认精确的原因。


4.4、许多杂乱运算在客户端处理

什么是杂乱运算,一般我认为是一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等。

假如有许多这类函数运算,尽量放在客户端处理,一般CPU每秒中也只能处理1万-10万次这样的函数运算,放在数据库内不利于高并发处理。


5、运用更多的资源
5.1、客户端多进程并行拜访

多进程并行拜访是指在客户端创立多个进程(线程),每个进程树立一个与数据库的衔接,然后一起向数据库提交拜访恳求。当数据库主机资源有闲暇时,咱们能够选用客户端多进程并行拜访的办法来进步功用。假如数据库主机现已很忙时,选用多进程并行拜访功用不会进步,反而或许会更慢。所以运用这种办法最好与DBA或体系管理员进行交流后再决议是否选用。



例如:

咱们有10000个产品ID,现在需求依据ID取出产品的具体信息,假如单线程拜访,按每个IO要5ms核算,疏忽主机CPU运算及网络传输时刻,咱们需求50s才干完结使命。假如选用5个并行拜访,每个进程拜访2000个ID,那么10s就有或许完结使命。

那是不是并行数越多越好呢,开1000个并行是否只需50ms就搞定,答案肯定是否定的,当并行数超越服务器主机资源的上限时功用就不会再进步,假如再添加反而会添加主机的进程间调度本钱和进程抵触机率。



以下是一些怎么设置并行数的根本主张:

假如瓶颈在服务器主机,可是主机还有闲暇资源,那么最大并行数取主机CPU核数和主机供给数据服务的磁盘数两个参数中的最小值,一起要确保主机有资源做其它使命。

假如瓶颈在客户端处理,可是客户端还有闲暇资源,那主张不要添加SQL的并行,而是用一个进程取回数据后在客户端起多个进程处理即可,进程数依据客户端CPU核数核算。

假如瓶颈在客户端网络,那主张做数据压缩或许添加多个客户端,选用map reduce的架构处理。

假如瓶颈在服务器网络,那需求添加服务器的网络带宽或许在服务端将数据压缩后再处理了。


5.2、数据库并行处理

数据库并行处理是指客户端一条SQL的恳求,数据库内部主动分解成多个进程并行处理,如下图所示:





并不是一切的SQL都能够运用并行处理,一般只需对表或索引进行悉数拜访时才干够运用并行。数据库表默许是不翻开并行拜访,所以需求指定SQL并行的提示,如下所示:

select /*+parallel(a,4)*/ * from employee;



并行的长处:

运用多进程处理,充沛运用数据库主机资源(CPU,IO),进步功用。

并行的缺陷:

1、单个会话占用许多资源,影响其它会话,所以只合适在主机负载低时期运用;

2、只能选用直接IO拜访,不能运用缓存数据,所以履行前会触发将脏缓存数据写入磁盘操作。



注:

1、并行处理在OLTP类体系中慎用,运用不当会导致一个会话把主机资源悉数占用,而正常业务得不到及时呼应,所以一般仅仅用于数据仓库渠道。

2、一般关于百万级记载以下的小表选用并行拜访功用并不能进步,反而或许会让功用更差。
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2
  • 3
  • 4

    union联合查询guojizaixian

    成果,数据,排序
  • 5

    TIME快报

    效劳,状况,修正
  • 6

    SQL试题环球

    用户,时刻,信息
  • 7
  • 8
  • 9

    asdfasdfITeye头条

    用户,权限,指令
  • 10

    SQL一招ITeyesohu

    字符串,标题,检索