在数据库中,经常需要用到大字段类型,如oracle中long/blob/clob,sqlserver中text/image,mysql中的text/longtext/clob/blob。
那么假如现在有一个表,记录某人发布的文档信息,字段包括:发布人,发布时间,文档标题,文档内容(实际中还会有其它字段),一般建表如下(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这个字段移到新表中,在原表中加一个对应的外键列,经过处理后,分页显示响应时间降到毫秒级以内。(二进制数据的转移是无法使用普通 的数据导入导出方式的,我的方法是复制该表,然后再修改复制后的表结构)
因为这个大字段,在最常用的列表显示中是根本不需要关心的,仅当用户需要查看某一记录的具体信息时,才需要调入该字段信息。因此分表后,显著提高了分页性能。
在我现在开发的所有的系统中,我都采用了上述的方式,这样做属于未雨绸缪,一旦系统部署后再修改,可能就来不及了。
分享到:
相关推荐
MySQL数据库在处理大规模数据时,为了提升查询性能和管理效率,常常采用水平分表和垂直分表两种策略。这两种分表方法是数据库优化的重要手段,旨在解决单一表中数据量过大导致的性能瓶颈。 **水平分表(Horizontal ...
MySQL数据库在处理大规模数据时,可能会遇到性能瓶颈,这时就需要采用分表和分区技术来优化。这两种技术的主要目的是提高查询效率、减少锁竞争,并降低单个表的数据量,从而提升系统的整体性能。 **垂直分表**是将...
3. **表记录数量**:尽量避免单表拥有过多记录,可以采用分表策略,如垂直分割或水平分割,以分散负载并提高查询效率。 4. **字段类型选择**:尽可能使用占用空间较小的数据类型,如使用TINYINT代替INT,这不仅可以...
按日期时间分表时,建议符合YYYY[MM][DD][HH]的格式,并采用合适的分库分表策略,例如千库十表、十库百表等。 字段设计规范建议尽可能避免使用TEXT和BLOB类型,以减少数据存储空间,并提高查询效率。推荐使用...
- **拆分大字段**:将访问频率低的大字段拆分到单独的表中,有助于数据库的优化和数据管理。 - **散表策略**:推荐使用HASH散表策略,并且表名后缀使用十进制数,从0开始编号,便于管理。 - **日期时间分表**:按照...
MySQL的巡检是数据库管理中的重要环节,它...可以考虑采用客户端Agent的方式,实时采集数据并存储到专门的数据库,以提高数据处理能力和响应速度。这种方法能更好地应对大规模实例的管理需求,同时减轻服务器端的压力。
日期时间分表应符合特定格式,并且要采用合理的分库分表策略,如千库十表、十库百表等。 在字段设计上,建议使用UNSIGNED存储非负数值,使用INT UNSIGNED存储IPv4地址,用DECIMAL代替FLOAT和DOUBLE存储精确的浮点数...
- **字段长度控制**:避免使用过长或过大字段,以防止性能下降。 - **全文索引限制**:禁止使用全文索引,尤其是处理中文时可能遇到未知错误。 ##### 5. **表结构优化** - **表规模**:设计时需考虑分库分表策略...