資料庫中大字段的處理方式

在資料庫中，經常需要用到大字段類型，如oracle中long/blob/clob,sqlserver中text/image，mysql中的text/longtext/clob/blob。 

　　存儲的資訊大概主要是兩類，一類是長文本，如大段的文字，普通的varchar最長隻能存儲4000個漢字，已經不能滿足要求；另一類是存儲二進制資訊，如上傳的檔案等。 

　　那麼假如現在有一個表，記錄某人釋出的文檔資訊，字段包括：釋出人，釋出時間，文檔标題，文檔内容(實際中還會有其它字段)，一般建表如下(sqlserver)： 

create table document( 

id int identity(1,1) not null, 

createuser_id int, 

document_title varchar(255), 

document_context text); 

這張表的結構，表面上看起來，從資料庫設計角度和對應的JAVA類的設計來講，都是沒有問題的。 

但實際上，這裡面隐藏着兩個比較嚴重的問題！ 

一、不能完全跨資料庫 

　　why?問題出在需要查重(distinct)的時候。 

　　在需要查重時，采用純jdbc技術，則可以自定義要查重的字段，如select distinct id,createuser_id,document_title from document。而當采用hibernate時，若不想自已建立若幹個新的Pojo或者使用Object[]方式來處理資料，則隻能使用select distinct d from document as d這樣的語句，而hibernate會将其解析為類似：select distinct id,createuser_id,document_title,document_context from document。 

　　問題就出在這個document_context字段上！ 

　　對于mysql來講，hibernate生成的sql是可以執行的。但對于sqlserver來講，是不允許在text/image列上進行distinct查詢的！oracle中同樣不可以對clob/blob進行distinct查詢。 

　　是以系統在sqlserver/oracle上部署時，當需要查重時則會出錯。當然如果你用不到查重語句，是一點不受影響的。 

二、嚴重影響清單顯示和統計的效率 

　　影響一張表的查詢速度的，除了行數，還包括表所占的實體空間的大小。此表在資料量較小時，在查詢方面感覺不到明顯的差異。但是如果document_context字段所存儲的資料都是大段文本或較大的檔案時，會導緻表的實體空間迅速變大，該字段所占用的空間有可能達到整表所占空間的90%以上。在此基礎上，如果行數再增加到數十萬、上百萬級時，整個表所占的空間将達到一個驚人的數字。 

　　保守估計，一條記錄占用的空間平均為10K的話，一萬條記錄将占用100M的空間，一百萬條記錄将占用10G！在此表上的CRUD操作，亦将變慢，查詢的速度亦會受到非常大的影響 。當然通過提高伺服器本身的硬體性能和優化索引，可以提高查詢速度，但面對無法預知的巨大洪水，單純加強堤壩是不保險的。 

解決的方式？ 

　　曾經處理過公司内的一個老系統，表的行數達到十萬左右，由于采用上面的設計方式，雖然已經盡可能優化了索引，但查詢分頁時，仍然需要十秒左右。我單獨建了一個新表，将document_context這個字段移到新表中，在原表中加一個對應的外鍵列，經過處理後，分頁顯示響應時間降到毫秒級以内。（二進制資料的轉移是無法使用普通 的資料導入導出方式的，我的方法是複制該表，然後再修改複制後的表結構） 

　　因為這個大字段，在最常用的清單顯示中是根本不需要關心的，僅當使用者需要檢視某一記錄的具體資訊時，才需要調入該字段資訊。是以分表後，顯著提高了分頁性能。 

在我現在開發的所有的系統中，我都采用了上述的方式，這樣做屬于未雨綢缪，一旦系統部署後再修改，可能就來不及了。 

補充：近日公司的另一套CMS系統，已經出現 了上述問題。clob字段直接置于業務表中，現業務表記錄已達20餘萬，查詢的速度非常緩慢，被迫采用各種方式來解決。如果當初設計時就考慮到這方面就不會有這樣的問題了。 

PS：解決方案之一是，可以在Pojo中加入構造函數，參數中包含除clob字段外的所有其它字段，通過select new Pojo(field1,field2,.....) from Pojo的方式來處理。但要注意，fieldx不能為集合類型，隻能為基本資料類型或Po類型。如public Pojo(Long id,String name,User usr,Date createDate){}