众所周知,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
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
+----------+
| 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
+---------------------+---------+----------+----------+
| 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)
+-----------------+
| 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)
+---------------------+
| 835.4738/458.855242 |
+---------------------+
| 1.82077859 |
+---------------------+
1 row in set (0.00 sec)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/1384/viewspace-611328/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/1384/viewspace-611328/