Length comparison between InnoDB and Oracle single-row Storage

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.