“趣说“Sybase ASE中有关字段空间大小计算

前言

如果您看过我以前写的一些有关Sybase ASE数据库避免表膨胀的短文，可能还会有一些印象。比如：也说说Sybase ASE中的空间膨胀及应对方案。

实际上，如果不小心使用的话，仍然会遇到一些你不曾经预料的问题。

详细说说

预定配置参数

你手头现在有两套ASE数据库服务器，都是Sybase ASE 16 SP03。一台你配的页大小是4K，另一台你配的是页大小是16K。这个跟PostgreSQL当中是类似的哈，就是数据文件的页大小。另外一个默认的配置项，将它们都采用默认的DOL (Data Only Lock)

使用下边的SQL可以得到具体的锁方案:

sp_configure 'lock scheme'

这里头看到的就是"datarows"方式，即默认锁定的是数据行方式，也是最常用的锁定方式。与SQLServer中概念基本一样。

页大小的值：

select @@maxpagesize
16384

select @@maxpagesize
4096

页大小在安装的时候就需要指定，一旦设定，就不能再更改了。这个也容易理解，相当于数据文件分页分块的物理配置参数。与PG完全类似。

有了上边这些设定，我们接着往下说。

各列类型占用空间大小

这里直接将常用类型占用的空间大小列举如下：

如果只看上边这张表，也没什么特别要注意的地方。对于text, image类型，需要注意的是，有一个额外的16字节。至于后边的2K*页数，实际上是不太准确的。应该是@@maxpagesize * {页数}。2K是默认的页大小 （安装时不指定)。

数字或十进制列的存储大小取决于其精度。对于1位或2位的列，最小存储需求是2字节。每增加2位精度，存储容量增加1字节，最大可达17字节。

数据行大小的”计算“

如果是固定列，Sybase ASE有下边的计算公式：就是各固定长度列总长之和再加上6。

 6 (Overhead) 
+     Sum of bytes in all fixed-length columns
____________________
      Data row size  

Data-only-locked tables must allow room for each row to store a 6-byte forwarded row ID. If a data-only-locked table has rows shorter than 10 bytes, each row is padded to 10 bytes when it is inserted. This affects only data pages, and not indexes, and does not affect allpages-locked tables.

DOL的表必须允许每行存储一个6字节的行ID。如果数据锁定表的行长度小于10字节，则插入时将每行填充为10字节。这只影响数据页，而不影响索引，并且不影响所有页锁定的表。

含变长列或者允许为NULL的列的长度计算：

 8 (Overhead)
+   Sum of bytes in all fixed-length columns
+   Sum of bytes in all variable-length columns
+   Number of variable-length columns * 2
__________________
    Data row size

    8
+   100
+   50
+   4    (两列)
________
    162    

再对照下表中的限制，就有点儿意思了：

上表，16298的值，疑似应该为16300 (右栏)

现在我们就来看看4K页大小情况下的行大小.

定长非NULL列

select @@maxpagesize
4096

create table t1(id char(4006) not null);
-- 可以成功创建

create table t2(id char(4006) not null);
-- 会出下边的错
SQL Error [1701] [ZZZZZ]: Creating table 't2' failed because the minimum row size would be 4013 bytes. This exceeds the maximum allowable size of a row for this table, 4012 bytes.

-- 会成功, 总长刚好 6 + 4002 + 4 = 4012
create table t2(id char(4002) not null, col2 int not null);

-- 会失败, 总长超长：6 + 4003 + 4 = 4013
-- SQL Error [1701] [ZZZZZ]: Creating table 't3' failed because the minimum row size would be 4013 bytes. This exceeds the maximum allowable size of a row for this table, 4012 bytes.
create table t3(id char(4003) not null, col2 int not null);

非定长或允许为NULL列

-- 默认允许为NULL
-- 8 + 4006 + 2 = 4016，最大长度会超
create table t1(id char(4006)); 
建表成功，但有提示：
Warning: Row size (4016 bytes) could exceed row size limit, which is 4012 bytes.

针对上表，插入一些值试一下：

insert into t1 values (replicate('a', 4002));
insert into t1 values (replicate('a', 4003)); -- 报错
-- SQL Error [511] [ZZZZZ]: Attempt to update or insert row failed because resultant row of size 4013 bytes is larger than the maximum size (4012 bytes) allowed for this table.

这只是用一个允许为NULL的字段来模拟。它与varchar(4006)的长度计算是类似的。每添加一个变长字段或允许为NULL字段，就会加上2.

create table t2(id char(1000) not null, col2 int not null, col3 int, col4 varchar(4000) not null);
-- 建表成功，但有warning:
-- Warning: Row size (5020 bytes) could exceed row size limit, which is 4012 bytes.

我们看看这5020是如何算出来的。

5020 = 8 + 2*2 + 1000 + 4 + 4 + 4000.

这里varchar(4000)虽然是not null，但因为它是变长，所以按变长的方式计算。

insert into t2 values('abc', 4, 2, replicate('a', 4012 - 1020))

insert into t2 values('abc', 4, 2, replicate('a', 4012 - 1019))
-- SQL Error [511] [ZZZZZ]: Attempt to update or insert row failed because resultant row of size 4013 bytes is larger than the maximum size (4012 bytes) allowed for this table.

原因：'abc'会自动扩充至1000字符宽，而最后的col4长度如果太长，就会导致行长超标，从而出错。

以上示例都以字长符串或者整型作为验证手段，只是为了方便说明问题。

Data In Row

又提到这个话题了，它是针对TEXT, IMAGE这类LOB字段类型的。默认情况下，它的每一个值都会新启一页来存储。当页大小为16K时，那相当浪费（如果字段本身可能平均长度不太长的时候)。于是就有了Data In Row。

结合上边的字段长度大小计算，我们来看一两个示例，就以16K页大小为背景：

select @@maxpagesize
16384

CREATE TABLE TBig16K (id int not null, col2 text in row(16296));
-- warning:
-- Warning: Row size (16330 bytes) could exceed row size limit, which is 16300 bytes.

怎么算出来的？

8 + 4 + 2 + 16 + 16296 + "4"

CREATE TABLE TBig16K4 (col1 text in row(8148), col2 text in row(8148));
Warning: Row size (16348 bytes) could exceed row size limit, which is 16300 bytes.

8 + (16 + 8148) + (16 + 8148) + 2*2 + "4" + "4"

单字段的：

CREATE TABLE TBig16K4(col2 text in row(16300));
--
Warning: Row size (16330 bytes) could exceed row size limit, which is 16300 bytes.

计算：8 + (16 + 16300) + 2 + "4"

最后那个"4"，是LOB页的指针，相当于，每一个LOB字段，都会有一个。

Data In Row中的反常例子

正常情况下，你只要使用data in row, 数据插入进去以后，都能正常读取。也能节省不少空间。但是如果你取值的时候有order by的时候，它会另外在每行有另外的字节要求，有时候容易出界，从而取不到值。

for in row lob table, it should be carefully designed, order by will have extra 12 bytes for each in-row-lob column

每个in-row的lob列，在order by的查询时，会有另外12个字节的空间需求

如果这12个字节，叠加已经计算的in row的总宽度，超过16300, SELECT就会完蛋(出错).

看看下边的实例：

CREATE TABLE TBig16K4(id int not null, col2 text in row(16266));

insert into TBig16K4 values(1, replicate('a', 16266));

select * from TBig16K4 where id = 1;
-- 正常拿到结果

select * from TBig16K4 order by id;
-- SQL Error [511] [ZZZZZ]: Attempt to update or insert row failed because resultant row of size 16310 bytes is larger than the maximum size (16300 bytes) allowed for this table.

下面我们分析上边的16310是怎么得到的：

8 + 4 + 2*2  + (16266 + 16) = 16298,  它加上order by的12个字节，刚好就超了。

像这种情况，不是说超了，就一定会报错。而是说，它首先是符合in row的条件，没有新启一页，但是加上这12个字节，它就超出了16300的最大限制。也就是说16288的总长到16300的总长之间的插入操作，都会导致那个问题。

解决这个问题的办法，要么：对于带有data in row字段的查询，尽量不要用order by （这个比较难)

要么，在设计阶段,  提前把 in row( {value}) 的值规划好，不要直接上来就用最大值，最后有可能陷入万劫不复。

比如，上表，我们可就可以把in row的值调的小点，让它一旦加上了另外12个字节，最大允许长度就让该行新启一页，这样就永远不会出问题了。实际上就是把16300 的范围再缩小12个字节。

-- 将上例中的16266 弄小些，弄成比16254或更小一些，就不会出问题了。
CREATE TABLE TBig16K5(id int not null, col2 text in row(16254));
insert into TBig16K5 values(1, replicate('a', 16254));
select * from TBig16K5 order by id;

这样，插入的值再长哪怕一个字节，就会新启一页，从而避免出错。

小结：

各种数据库中关于字段类型存储长度的计算，永远是一个有意思的话题。量体裁衣，无论什么时候都有用。