对数据表中大字段的处理方式

　　在数据库中，经常需要用到大字段类型，如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){}

评论

20 楼 javadev 2007-04-29  

上面怎么只列举了Oracle和MySql，DB2是否也存在这种问题。

19 楼 maxima 2007-03-23  

试过hibernate 的话大字段的属性都设lazy＝true，能提高查询速度。具体能提高多少不知道了

18 楼 zaife 2006-12-21  

能不能说说INDEX表的具体做法。

17 楼 myreligion 2006-12-07  

我觉得放到文件系统的优势是对于可以公开访问的大数据，如个人头像的图片，帖子的正文等可以外部访问的东西，可以通过Apache直接读取，完全不用经过App和DB层，这对性能优化是非常有用的。

cookoo 写道

放在数据库里可以直接利用数据库的安全性，完整性，备份，集群，远程可访问性。当然设计不好的话对性能会有影响。

如果放在文件系统，数据库里只保留一个path的话，虽然本地访问直观，但是同步检查（检查path所指文件是否真的存在），加上上述各种数据库特性就都需要自己来处理了。

16 楼 geradle 2006-12-07  

能不能说说INDEX表的具体做法。

15 楼 fight_bird 2006-12-04  

大数据量下的LOB处理没有捷径，目前唯一合理的方法是：
专用文件服务器＋基于数据库的目录管理

希望改个Pojo细节，躲开LOB字段的映射就能解决问题是不现实的，问题的瓶颈就是数据库本身。

14 楼 oboaix 2006-11-17  

需要高手支持,需要反复考察验证方可定论,支持讨论...

13 楼 糖人GG 2006-11-16  

好贴子

12 楼 JackTree365 2006-11-09  

向高手学习

11 楼 cryolite 2006-11-09  

zelsa 写道

我公司的有一套系统，近1年的附件已达到70+G，如果放到库里那只有找死。所以还要具体问题具体分析，附件数据量小的，可以考虑入库。

如果这些附件都放在一个单独的表里,通过外键关联到其他表,这样又不影响其他表,怎么会是找死?
难道70G的文件系统就不是找死?

10 楼 zelsa 2006-11-01  

我公司的有一套系统，近1年的附件已达到70+G，如果放到库里那只有找死。所以还要具体问题具体分析，附件数据量小的，可以考虑入库。

9 楼 jsszwangwei 2006-11-01  

这样也好，毕竟是“深度论坛”，呵呵

8 楼 cxd110 2006-10-25  

这里似乎对总结性的帖子很关照，只要是做一点总结的都是精华了，而如果谁问一些具体程序的，比如一个算法什么的，大多数被隐藏掉的。

7 楼 eason007 2006-10-23  

叶子 写道

文本存text尚可接受

image存2进制文件觉得蛮那个的...几千张图片存下,就很恐怖了.

恩，如果是考虑防盗链的话，可以通过其他服务器端技术解决。

6 楼 叶子 2006-10-23  

文本存text尚可接受

image存2进制文件觉得蛮那个的...几千张图片存下,就很恐怖了.

5 楼 darkjune 2006-10-22  

没有特殊要求，还是只放一个URL比较好

4 楼 cookoo 2006-10-22  

放在数据库里可以直接利用数据库的安全性，完整性，备份，集群，远程可访问性。当然设计不好的话对性能会有影响。

如果放在文件系统，数据库里只保留一个path的话，虽然本地访问直观，但是同步检查（检查path所指文件是否真的存在），加上上述各种数据库特性就都需要自己来处理了。

3 楼 myronyxl 2006-10-22  

干吗一定要把大字段放在数据库，我个人认为把文本和图像等之类需要用大字段处理的东东放在应用服务器上用文件保存，而在数据库里只放个路径就可以了，这样还可以省去应用服务器到数据库服务器的时间，更不会有类似楼上的事情发生了。

2 楼 eric_chan 2006-10-21  

问题一

引用

不能完全跨数据库

在oracle上也存在问题，我现在一般都这样写

select distinct d.id from document as d

1 楼 robbin 2006-10-16  

好贴！