InnoDB與Oracle單行存儲(chǔ)長(zhǎng)度對(duì)比

眾所周知，MySQL InnoDB存儲(chǔ)引擎與Oracle非常相似,支持事務(wù)，row-locking,經(jīng)過(guò)實(shí)際測(cè)試，innodb與oracle一個(gè)比較大的差異點(diǎn)為，相同數(shù)據(jù)，innodb單行存儲(chǔ)長(zhǎng)度大概是oracle單行存儲(chǔ)長(zhǎng)度的1.8倍。
測(cè)試的表，常見(jiàn)的各種類型都有,number,varchar2,date

--首先在Oracle中做測(cè)試 , 在Oracle數(shù)據(jù)庫(kù)中此表單行長(zhǎng)度平均為458字節(jié)
-
 

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作對(duì)比測(cè)試
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   --統(tǒng)計(jì)信息不準(zhǔn)確
 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

--重新分析一下,發(fā)現(xiàn)執(zhí)行分析操作時(shí)間非�？�，數(shù)據(jù)也比較準(zhǔn)確，估計(jì)mysql的分析的算法原理為基于抽樣的統(tǒng)計(jì)，比如說(shuō)一個(gè)page里有多少記錄，總的page數(shù)是多少,兩者相乘即可得到行數(shù)。
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的表中平均單行長(zhǎng)度為835字節(jié)

root@test_mysql 09:46:50>select 96141312/115074;
+-----------------+
| 96141312/115074 |
+-----------------+
|        835.4738 |
+-----------------+
1 row in set (0.00 sec)

--同一行記錄，在Oracle與mysql存儲(chǔ)空間對(duì)比為1.8:1,所以在做容量估計(jì)的時(shí)候要注意。
root@test_mysql 09:48:14>select 835.4738/458.855242 ;
+---------------------+
| 835.4738/458.855242 |
+---------------------+
|          1.82077859 |
+---------------------+
1 row in set (0.00 sec)

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]