数据库访问优化法则详解之返回更少的数据

数据库访问优化法则详解之返回更少的数据

返回更少的数据

如果返回的数据更少，则可以减少网络传输或磁盘访问。可通过分页处理等方式减少返回数据。

数据分页处理

常用数据分页方式有以下3 种。

1．客户端（应用程序或浏览器）分页

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

优点：编码简单，减少客户端与应用服务器网络的交互次数。

缺点：首次交互时间长，占用客户端内存。

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

2．应用服务器分页

将数据从数据库服务器全部下载到应用服务器，在应用服务器内部再进行数据筛选。以下是一个应用服务器端Java 程序分页的示例：

List list=executeQuery("select * fromemployee order by id");

Int count= list.size();

List subList= list.subList(10, 20);

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

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

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

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 onproduct(company_id,status);

select b.* from (

select * from (

select a.*,rownum rn from

(select rowid rid,status from product awhere 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（50+20/2）个IO。

只返回需要的字段

通过去除不必要的返回字段可以提高性能，如下所示。

调整前：

select * from product where company_id=?;

调整后：

select id,name from product wherecompany_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 表。

本文选自《Oracle DBA手记3：数据库性能优化与内部原理解析》一书

本书详细信息:http://blog.csdn.net/broadview2006/article/details/6905948