oracle单行记录长度,[ 丹臣]INNODB与ORACLE单行存储长度对比

众所周知，mysql innodb存储引擎与oracle非常相似,支持事务，row-locking,经过实际测试，innodb与oracle一个比较大的差异点为，在此测试用例下，相同数据，innodb单行存储长度大概是oracle单行存储长度的1.8倍。

测试的表，常见的各种类型都有,number,varchar2,date

–首先在oracle中做测试 , 在oracle数据库中此表单行长度平均为458字节

09:49:45 danchen@ test_oracle>select blocks from dba_extents where segment_name='test_dc' and rownum<2;

BLOCKS

----------

1280

1 row selected.

09:52:55 danchen@ test_oracle>select /*+ rowid(u)*/  count(*) from test_dc u where rowid>=CHARTOROWID('AAADQdAA6AAADcJAAA') and rowid<=CHARTOROWID('AAADQdAA6AAADwICcQ');

COUNT(*)

----------

22852

09:54:55 danchen@ test_oracle>select 1280*8*1024/22852 bytes from dual;

BYTES

----------

458.855242

–在mysql作对比测试

root@test_mysql 09:47:47>select count(*) from test_dc_181;

+----------+

| count(*) |

+----------+

|   128928 |

+----------+

1 row in set (0.08 sec)

root@test_mysql 09:45:57>show table status like 'test_dc_181'\G;

*************************** 1. row ***************************

Name: test_dc_181

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 85668   --统计信息不准确

Avg_row_length: 1122

Data_length: 96141312

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2009-05-27 11:23:55

Update_time: NULL

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment: InnoDB free: 292984832 kB

1 row in set (0.00 sec)

ERROR:

No query specified

–重新分析一下,发现执行分析操作时间非常快，数据也比较准确，估计mysql的分析的算法原理为基于抽样的统计，比如说一个page里有多少记录，总的page数是多少,两者相乘即可得到行数。

root@test_mysql 09:46:01>analyze table test_dc_181;

+---------------------+---------+----------+----------+

| Table               | Op      | Msg_type | Msg_text |

+---------------------+---------+----------+----------+

| test_mysql.test_dc_181 | analyze | status   | OK       |

+---------------------+---------+----------+----------+

1 row in set (0.00 sec)

root@test_mysql 09:46:46>show table status like 'test_dc_181'\G;

*************************** 1. row ***************************

Name: test_dc_181

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 115074

Avg_row_length: 835

Data_length: 96141312

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2009-05-27 11:23:55

Update_time: NULL

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment: InnoDB free: 292984832 kB

1 row in set (0.00 sec)

ERROR:

No query specified

–在mysql innodb的表中平均单行长度为835字节

root@test_mysql 09:46:50>select 96141312/115074;

+-----------------+

| 96141312/115074 |

+-----------------+

|        835.4738 |

+-----------------+

1 row in set (0.00 sec)

–在此测试用例下，同一行记录，在oracle与mysql存储空间对比为1.8:1,在其它大多数情况下，oracle的数据迁移到mysql数据库，所用的空间都要大许多，具体大多少，各位需要经过测试，以使容量估计更加准确。

root@test_mysql 09:48:14>select 835.4738/458.855242 ;

+---------------------+

| 835.4738/458.855242 |

+---------------------+

|          1.82077859 |

+---------------------+

1 row in set (0.00 sec)