資料庫訪問最佳化法則詳解之返回更少的資料

資料庫訪問最佳化法則詳解之返回更少的資料

返回更少的資料

如果返回的資料更少，則可以減少網路傳輸或磁碟訪問。可透過分頁處理等方式減少返回資料。

資料分頁處理

常用資料分頁方式有以下3 種。

1．客戶端（應用程式或瀏覽器）分頁

將資料從伺服器全部下載到本地應用程式或瀏覽器，在應用程式或瀏覽器內部透過原生程式碼進行分頁處理。

優點：編碼簡單，減少客戶端與應用伺服器網路的互動次數。

缺點：首次互動時間長，佔用客戶端記憶體。

適應場景：客戶端與伺服器網路延時較大，但要求後續操作流暢，如手機GPRS 應用、超遠端訪問（跨國）等等。

2．應用伺服器分頁

將資料從資料庫伺服器全部下載到應用伺服器，在應用伺服器內部再進行資料篩選。以下是一個應用伺服器端Java 程式分頁的示例：

List list=executeQuery("select * from employee 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 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（50+20/2）個IO。

只返回需要的欄位

透過去除不必要的返回欄位可以提高效能，如下所示。

調整前：

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

本文選自《Oracle DBA手記3：資料庫效能最佳化與內部原理解析》一書

本書詳細資訊:http://space.itpub.net/13164110/viewspace-709596