As we all know, the MySQL InnoDB Storage engine is very similar to Oracle and supports transactions and row-locking. After actual tests, a big difference between innodb and oracle is: the same data, the length of innodb single-row storage is approximately 1.8 times that of oracle single-row storage.
Test Tables. Common types include: number, varchar2, and date.
-- First, perform a test in Oracle. in Oracle Database, the average length of the form row is 458 bytes.
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 ('aaadqdaa6aaadcjaa ') and rowid <= CHARTOROWID ('aaadqdaa6aaadwiccq ');
COUNT (*)
----------
22852
09:54:55 danchen @ test_oracle> select 1280*8*1024/22852 bytes from dual;
BYTES
----------
458.855242
-- Compare and test mysql
Root @ test_mysql 09:47:47> select count (*) from test_dc_181;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 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 -- Inaccurate statistics
Avg_row_length: 1122
Data_length: 96141312
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 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
-- Re-analyze and find that the analysis operation is very fast and the data is accurate. It is estimated that the mysql analysis algorithm is based on sampling statistics, for example, how many records are in a page, the total number of pages is multiplied to obtain the number of rows.
Root @ test_mysql 09:46:01> analytic 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: 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
-- The average length of a single row in a mysql innodb table is 835 bytes.
Root @ test_mysql 09:46:50> select 96141312/115074;
+ ----------------- +
| 1, 96141312/115074 |
+ ----------------- +
| 1, 835.4738 |
+ ----------------- +
1 row in set (0.00 sec)
-- For the same row of records, the storage space in oracle and mysql is 1.8: 1. Therefore, pay attention to the Capacity estimation.
Root @ test_mysql 09:48:14> select 835.4738/458.855242;
+ --------------------- +
| 835.4738/458.855242 |
+ --------------------- +
| 1, 1.82077859 |
+ --------------------- +
1 row in set (0.00 sec)