数据库学习篇之数据库大字段的理解

前言

Oracle数据库大字段问题

问题描述：字段内容长度超4000
项目中存在用某些字段存预处理sql语句，最初，项目此类型字段用varchar2(4000)，但随着项目跟进到二期，牵连的表越来越多，存在预处理索引，中间临时表的问题，预处理SQL字段长度超过4000。

oracel默认varchar2类型是不能超过4000的，如果类型超过4000的数据插入会自动转为long型数据插入，所以问题来了。于是我将表中需要超过4000内容的字段改为long型，接下来问题又来了。

oracle一个表中最多只能有一个long型字段，所以这种解决方案又不适合解决业务需求了，经查询oracle推荐用clob字段类型存储。

clob和blob：CLOB(Character Large Object) 字符大对象，Blob（Binary Large Object）二进制大对象。根据字面意思可以简单的辨别、选择不同的对应类型，项目中这个字段全是文字形式的预处理sql，所以就选择了clob,如果在遇到文件、视频、音频等可以选择blob。

那么问题又来了：这是一种对象存储方式，如何存取呢？举个例子：

为了使用方便肯定要进行数据封装，简单写个类以及对应的set和get方法。在遍历查询结果集ResultSet的时候将对应的结果user.setClob(rs.getClob(“sqla”))，因为要用clob里面的内容信息，将Clob转为String使用。

目前已经实现了在user对象能存储进sqla这个clob对象了吧，那怎么取出来使用呢，简单测试一下System.out.println(user.getSqla().toString())，这打印出来是对象地址。在用的时候将clob转为String使用，写一个工具类，方便使用的时候调用如下：

public static String clobToString(CLOB clob) throws SQLException, IOException { 
 
 result clob != null ? clob.getSubString(1, (int) clob.length()): null;

}

深入理解

什么是数据库大字段？

字段之字符串类型

char(N) vs varchar(N)

不管是char，还是varchar，在compact row-format格式下，NULL都不占用任何存储空间
在多字节字符集的情况下，CHAR vs VARCHAR 的实际行存储基本没区别
CHAR不管是否是多字符集，对未能占满长度的字符还是会填充0x20

varchar(N) : 255 vs 256

当实际长度大于255的时候，变长字段长度列表需要用两个字节存储，也就意味着每一行数据都会增加1个字节
实测下来存储空间增长并不算大，且性能影响也不大，所以，尽量在256之内吧

varchar（N） & char（N） 的最大限制

char的最大限制是： N<=255
varchar 的最大限制是： N<=65535 , 官方文档说的是N是字节，并且说的是一行的所有字段的总和小于65535，而varchar(N)中的N表示的是字符。
测试后发现，65535并不是最大限制，最大的限制是65532

off-page： 行溢出

行溢出off-page概念

假设创建了一张表，里面有一个字段是a varchar(30000) , innoDB的页才16384个字节，如何存储的下呢？所以行溢出就来了嘛

溢出有什么危害

溢出的数据不再存储在B+tree中
溢出的数据使用的是uncompress BLOB page，并且存储独享，这就是存储越来越大的真正原因

通过下面例子可以看到，t_long 插入的数据仅仅比 t_short 多了几个字节，但是最终的存储却是2~3倍的差距 

* 表结构
root:test> show create table t_long;
+--------+---------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                            |
+--------+---------------------------------------------------------------------------------------------------------+
| t_long | CREATE TABLE `t_long` (
  `id` int(11) DEFAULT NULL,
  `col1` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root:test> show create table t_short;
+---------+----------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                             |
+---------+----------------------------------------------------------------------------------------------------------+
| t_short | CREATE TABLE `t_short` (
  `id` int(11) DEFAULT NULL,
  `col1` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


* 测试案例

foreach $num (1 .. 48849){

        $sql_1 = "insert into $table_short select $num,repeat('a',8090)";
        $sql_2 = "insert into $table_long select $num,repeat('a',8098)";
        `$cmd -e " $sql_1 "`;
        `$cmd -e " $sql_2 "`;
}


* 最终的记录数

root:test> select count(*) from t_short;
+----------+
| count(*) |
+----------+
|    48849 |
+----------+
1 row in set (0.03 sec)

root:test> select count(*) from t_long;
+----------+
| count(*) |
+----------+
|    48849 |
+----------+
1 row in set (0.02 sec)


* 页类型的比较

[root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_short.ibd
Total number of page: 25344:
Insert Buffer Bitmap: 2
Freshly Allocated Page: 887
File Segment inode: 1
B-tree Node: 24452
File Space Header: 1
扩展描述页: 1


[root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_long.ibd
Total number of page: 60160:
Insert Buffer Bitmap: 4
Freshly Allocated Page: 8582
File Segment inode: 1
B-tree Node: 2720
File Space Header: 1
扩展描述页: 3
Uncompressed BLOB Page: 48849


* 最终大小的对比

[root()@xx test]# du -sh * | grep 'long\|short' | grep ibd
941M    t_long.ibd
397M    t_short.ibd

* 结论

t_short 的表，在400M左右可以理解，因为 8k * 48849 = 400M

t_long 的表，由于独享48849个Uncompressed BLOB Page，严重浪费空间

什么情况下会溢出

原则：只要一行记录的总和超过8k，就会溢出。
所以：varchar(9000) 或者 varchar(3000) + varchar(3000) + varchar(3000)，当实际长度大于8k的时候，就会溢出
所以：Blob，text，一行数据如果实际长度大于8k会溢出，如果实际长度小于8k则不会溢出，并非所有的blob，text都会溢出

多列总和大字段 vs 一列大字段

多个大字段会导致多次off-page

如何对大字段进行优化

1、如果有多个大字段，尽量序列化后，存储在同一列中,避免多次off-page
2、将text等大字段从主表中拆分出来：a）存储到key-value中 b）存储在单独的一张子表中，并且压缩
3、必须保证一行记录小于8k

        在数据库中，经常需要用到大字段类型，如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类的设计来讲，都是没有问题的。但实际上，这里面隐藏着两个比较严重的问题！

1、不能完全跨数据库
　　为什么？问题出在需要查重(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上部署时，当需要查重时则会出错。当然如果你用不到查重语句，是一点不受影响的。
2、严重影响列表显示和统计的效率
　　影响一张表的查询速度的，除了行数，还包括表所占的物理空间的大小。此表在数据量较小时，在查询方面感觉不到明显的差异。但是如果document_context字段所存储的数据都是大段文本或较大的文件时，会导致表的物理空间迅速变大，该字段所占用的空间有可能达到整表所占空间的90%以上。在此基础上，如果行数再增加到数十万、上百万级时，整个表所占的空间将达到一个惊人的数字。
　　保守估计，一条记录占用的空间平均为10K的话，一万条记录将占用100M的空间，一百万条记录将占用10G！在此表上的CRUD操作，亦将变慢，查询的速度亦会受到非常大的影响。当然通过提高服务器本身的硬件性能和优化索引，可以提高查询速度，但面对无法预知的巨大洪水，单纯加固堤坝是不保险的。

解决的方式

        一个系统表的行数达到十万左右，由于采用上面的设计方式，虽然已经尽可能优化了索引，但查询分页时，仍然需要十秒左右。单独建了一个新表，将document_context这个字段移到新表中，在原表中加一个对应的外键列，经过处理后，分页显示响应时间降到毫秒级以内。（二进制数据的转移是无法使用普通的数据导入导出方式的，我的方法是复制该表，然后再修改复制后的表结构）

        因为这个大字段，在最常用的列表显示中是根本不需要关心的，仅当用户需要查看某一记录的具体信息时，才需要调入该字段信息。因此分表后，显著提高了分页性能。
在我现在开发的所有的系统中，我都采用了上述的方式，这样做属于未雨绸缪，一旦系统部署后再修改，可能就来不及了。