文档章节

程序员数据库访问的优化的一些思考

BravoZu
 BravoZu
发布于 2014/03/04 21:37
字数 3031
阅读 186
收藏 5

一、数据库访问优化的五个法则

    在实际开发,我们主要是需要对SQL语句进行优化,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?根据木桶原理可以知道,最慢的设备往往是性能瓶颈。例如:互联网运用中的带宽,本地数据复制时的硬盘的访问速度。

    根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容,可以整理出如下五条性能基本优化法则:

      (1)减少数据访问(减少磁盘访问)

     (2)返回更少数据(减少网络传输或磁盘访问)

   (3)减少交互次数(减少网络传输)

   (4)减少服务器CPU开销(减少CPU及内存开销)

   (5) 利用更多资源(增加资源)

由于每一层优化法则都是解决其对应硬件的性能问题,所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法,因此针对低速设备问题的可优化手段也更多,优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案,而不应该首先想到的是增加资源解决问题。

以下是每个优化法则层级对应优化效果及成本经验参考:

优化法则

性能提升效果

优化成本

减少数据访问

1~1000

返回更少数据

1~100

减少交互次数

1~20

减少服务器CPU开销

1~5

利用更多资源

@~10

二、数据库访问优化法则详解

2.1、减少数据访问

(1)正确创建索引

索引有哪些种类?

常见的索引有B-TREE索引、位图索引、全文索引,位图索引一般用于数据仓库应用,全文索引由于使用较少,这里不深入介绍。B-TREE索引包括很多扩展类型,如组合索引、反向索引、函数索引等等,以下是B-TREE索引的简单介绍:

B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。

我们一般在什么字段上建索引?

这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:

        1、字段出现在查询条件中,并且查询条件可以使用索引;

        2、语句执行频率高,一天会有几千次以上;

        3、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?

这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:

小表(记录数小于10000行的表):筛选比例<10%

大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

 单条记录长度≈字段平均内容长度之和+字段数*2

索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少?

 这个没有固定的比例,与每个表记录的大小及索引字段大小密切相关,以下是一个普通表测试数据,仅供参考:

索引对于Insert性能降低56%

索引对于Update性能降低47%

索引对于Delete性能降低29%

因此对于写IO压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。

(2)只通过索引访问数据

有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个组合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的磁盘空间比表小很多,所以这种方式可以大大减少磁盘IO开销。

(3)优化SQL执行计划

SQL执行计划是关系型数据库最核心的技术之一,它表示SQL执行时的数据访问算法。由于业务需求越来越复杂,表数据量也越来越大,程序员越来越懒惰,SQL也需要支持非常复杂的业务逻辑,但SQL的性能还需要提高,因此,优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外,还需要有一套优秀的算法库来提高SQL性能。

2.2、返回更少的数据

减少数据的返回也是优化的重要手段,主要有两种方法 分页 和 只返回需要的字段。

(1)分页

分页总共包括三种分页方式:客户端分页、服务端分页、数据库分页。

客服端分页将数据从应用服务器全部下载到本地应用程序或浏览器,在应用程序或浏览器内部通过本地代码进行分页处理。

优点:编码简单,减少客户端与应用服务器网络交互次数

缺点:首次交互时间长,占用客户端内存

适应场景:客户端与应用服务器网络延时较大,但要求后续操作流畅,如手机GPRS,超远程访问(跨国)等等。

服务端分页:将数据从数据库服务器全部下载到应用服务器,在应用服务器内部再进行数据筛选。

优点:编码简单,只需要一次SQL交互,总数据与分页数据差不多时性能较好。

缺点:总数据量较多时性能较差。

适应场景:数据库系统不支持分页处理,数据量较小并且可控。

数据库分页:采用数据库SQL分页需要两次SQL完成,一个SQL计算总数量,一个SQL返回分页后的数据。

优点:性能好

缺点:编码复杂,各种数据库语法不同,需要两次SQL交互。

ps:oracle数据库一般采用rownum来进行分页。常用分页方式如下

rownum分页:

select * from (
         select a.*,rownum rn from
                   (select * from student a where id=? order by status) a
         where rownum<=20)
where rn>10;

rowid分页语法:

select b.* from (
         select * from (
                   select a.*,rownum rn from
                            (select rowid rid,status from student a where id=? order by status) a
                   where rownum<=20)
         where rn>10) a, student b
where a.rid=b.rowid;

(2)只返回需要的字段

通过去除不必要的返回字段可以提高性能。

优点:

1、减少数据在网络上传输开销

2、减少服务器数据处理开销

3、减少客户端内存占用

4、字段变更时提前发现问题,减少程序BUG

5、如果访问的所有字段刚好在一个索引里面,则可以使用纯索引访问提高性能。

缺点:增加编码工作量

2.3、减少交互的次数

(1) batch 操作

数据库访问框架一般都提供了批量提交的接口,jdbc支持batch的提交处理方法,我们可以批量插入、更新和删除数据。这样交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO,但是会大大减少客户端与服务端的交互次数,从而减少了多次发起的网络延时开销,同时也会降低数据库的CPU开销。

(2)in

很多时候我们需要按一些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请求的数量,从而提高性能。那如果有10000ID,那是不是全部放在一条SQL里处理呢?答案肯定是否定的。首先大部份数据库都会有SQL长度和IN里个数的限制,如ORACLEIN里就不允许超过1000个值

另外当前数据库一般都是采用基于成本的优化规则,当IN数量达到一定值时有可能改变SQL执行计划,从索引访问变成全表访问,这将使性能急剧变化。随着SQLIN的里面的值个数增加,SQL的执行计划会更复杂,占用的内存将会变大,这将会增加服务器CPU及内存成本。

评估在IN里面一次放多少个值还需要考虑应用服务器本地内存的开销,有并发访问时要计算本地数据使用周期内的并发上限,否则可能会导致内存溢出。

综合考虑,一般IN里面的值个数超过20个以后性能基本没什么太大变化,也特别说明不要超过100,超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本.

(3)设置Fetch Size

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

2.4、减少数据库服务器CPU运算

(1) 使用绑定变量

JavaPreparestatement就是为处理绑定变量提供的对像,绑定变量有以下优点:

1、防止SQL注入

2、提高SQL可读性

3、提高SQL解析性能,不使用绑定变更我们一般称为硬解析,使用绑定变量我们称为软解析。

(2)合理使用排序

(3)减少比较操作

(4)大量复杂运算在客户端处理

2.5、利用更多的资源

(1)客户端多进程并行访问

    多进程并行访问是指在客户端创建多个进程(线程),每个进程建立一个与数据库的连接,然后同时向数据库提交访问请求。当数据库主机资源有空闲时,我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时,采用多进程并行访问性能不会提高,反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。

(2)数据库并行处理






© 著作权归作者所有

BravoZu
粉丝 13
博文 54
码字总数 34332
作品 0
广州
程序员
私信 提问
ORM知多少

公司的项目中都涉及到ORM(Object Relational Mapping)框架,EF映射、Hibernate、Mybatis、JPA……不断涌现的新鲜词语,面试的时候面试官还在说EFframework(有木有很奇怪),那就来一探究竟吧...

zt15732625878
2017/10/12
0
0
纪念老青橘子电影

一、以前有个网站,叫青橘子电影 2016年,当时想做一个资源聚合网站,由网友提供资源线索信息,青橘子资源网站诞生了, QJuzi.com 域名第一次被使用。起初,青橘子被设计为多个板块,包括电影...

花有信
2017/11/03
0
0
读《面向程序员的数据库访问性能优化法则》

刚刚看了一下2014年中国数据库技术大会(DTCC)PPT,网易杭州研究院的一位同学介绍了一下数据库的优化与调优。 网易的Mysql优化做的很好,而且还写了本书和大家分享这些经验《深入浅出MySQL...

ifeixiang
2015/01/04
93
0
Java程序员如何成为内功深厚的架构师

一听到架构师,首先便想到的是在一间宽敞的房间中间坐着一位衣着得体的中年男人,望着落地窗外的风景凝思,万千思绪在脑海里翻腾,颇有运筹帷幄千里外的气势。程序员究竟是做架构师还是项目经...

Theriseof
2017/10/18
0
0
MyBatis入门~教你如何与数据库更好地交互

这几天回顾了下以前学的mybatis,特写这篇文章来总结一下,非技术人员请绕道~如果有什么疑问,可以关注我的公众号:多一点思考。 先来介绍下Mybatis,它是appache下开源的一款持久层框架,通...

一只大黑猫
2017/12/15
0
0

没有更多内容

加载失败,请刷新页面

加载更多

最简单的获取相机拍照的图片

  import android.content.Intent;import android.graphics.Bitmap;import android.os.Bundle;import android.os.Environment;import android.provider.MediaStore;import andr......

MrLins
今天
6
0
说好不哭!数据可视化深度干货,前端开发下一个涨薪点在这里~

随着互联网在各行各业的影响不断深入,数据规模越来越大,各企业也越来越重视数据的价值。作为一家专业的数据智能公司,个推从消息推送服务起家,经过多年的持续耕耘,积累沉淀了海量数据,在...

个推
今天
10
0
第三方支付-返回与回调注意事项

不管是支付宝,微信,还是其它第三方支付,第四方支付,支付机构服务商只要涉及到钱的交易都要进行如下校验,全部成功了才视为成功订单 1.http请求是否成功 2.校验商户号 3.校验订单号及状态...

Shingfi
今天
5
0
简述Java内存分配和回收策略以及Minor GC 和 Major GC(Full GC)

内存分配: 1. 栈区:栈可分为Java虚拟机和本地方法栈 2. 堆区:堆被所有线程共享,在虚拟机启动时创建,是唯一的目的是存放对象实例,是gc的主要区域。通常可分为两个区块年轻代和年老代。更...

DustinChan
今天
7
0
Excel插入批注:可在批注插入文字、形状、图片

1.批注一直显示:审阅选项卡-------->勾选显示批注选项: 2.插入批注快捷键:Shift+F2 组合键 3.在批注中插入图片:鼠标右键点击批注框的小圆点【重点不可以在批注文本框内点击】----->调出批...

东方墨天
今天
7
1

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部