The difference analysis of MySQL antelope and Barracuda _mysql

Antelope is a innodb-base file format, Barracude is a file format introduced after Innodb-plugin, and Barracude also supports Antelope file formats. The difference between the two is:

File format	Support Row format	Characteristics
Antelope (innodb-base)	Row_format=compact Row_format=redundant	The difference between compact and redumdant is that the contents of the first deposit are different. The compact's storage format is a non-null variable long field length list The redundant storage format is the head of a field length offset list (the length of bytes occupied by each field and its corresponding offset). In antelope, for variable-length fields, less than 768 bytes are not overflow page stores, and in some cases result set IO is reduced.
Barracuda (Innodb-plugin)	Row_format=dynamic Row_format=compressed	The two are mainly functional differences in function. The difference between a variable length field and a antelope in a row is only 20 bytes, and the other overflow page store. In addition, both need to open the innodb_file_per_table=1 (This feature is useful for some optimizations)

Note:

Here is a point to note that if you want to use compression, you must first use the Innodb_file_format =barracuda format, otherwise no effect.

Here's a look at the difference:

Copy Code code as follows:

(testing) Root@localhost [(none)]> use WUBX;

Database changed

(testing) root@localhost [wubx]> CREATE TABLE T1

-> (C1 INT PRIMARY KEY)

-> row_format=compressed

-> key_block_size=8;

Query OK, 0 rows affected, 4 warnings (0.01 sec)

Report out 4 Warnings check the error:

Copy Code code as follows:

(testing) Root@localhost [wubx]> show warnings;

+ ——— +--+ ——————————————————————— –+

| Level | Code | message |

+ ——— +--+ ——————————————————————— –+

| Warning | 1478 |        Innodb:key_block_size requires Innodb_file_format > antelope. |

| Warning | 1478 |                                    Innodb:ignoring key_block_size=8. |

| Warning | 1478 | innodb:row_format=compressed requires Innodb_file_format > antelope. |

| Warning | 1478 |                                  Innodb:assuming row_format=compact. |

+ ——— +--+ ——————————————————————— –+

4 rows in Set (0.00 sec)

From the above error can be seen to not support compression. But look at the table structure as follows:

Copy Code code as follows:

(testing) Root@localhost [wubx]> show create table T1;

+ ——-+ ——————————————————————————————————————————————— –+

| Table |                                                                                                                                  Create Table |

+ ——-+ ——————————————————————————————————————————————— –+

| T1 | CREATE TABLE T1 (

C1 int (one) not NULL,

PRIMARY KEY (C1)

) Engine=innodb DEFAULT Charset=utf8 row_format=compressed key_block_size=8 |

+ ——-+ ——————————————————————————————————————————————— –+

1 row in Set (0.00 sec)

This is the place to compare pits, so in the use of compression requires attention.

Copy Code code as follows:

(testing) root@localhost [wubx]>create table T2 (c1 int (one) not NULL, primary key (C1));

(testing) Root@localhost [wubx]> INSERT into the t2 select * from T1;

Query OK, 5417760 rows Affected (37.12 sec)

records:5417760 duplicates:0 warnings:0

To create a table that supports compression:

Copy Code code as follows:

(testing) Root@localhost [Wubx]>set GLOBAL innodb_file_per_table=1

(testing) Root@localhost [Wubx]>set GLOBAL Innodb_file_format=barracuda;

(testing) Root@localhost [wubx]>create TABLE T3

(C1 INT PRIMARY KEY)

Row_format=compressed

key_block_size=8;

(testing) Root@localhost [wubx]> INSERT into the T3 select * from T1;

Query OK, 5417760 rows affected (1 min 10.98 sec)

records:5417760 duplicates:0 warnings:0

Look at the physical size of the table as follows:

Copy Code code as follows:

-rw-rw--1 MySQL MySQL 8.4K June 5 16:58 T1.frm

-rw-rw--1 MySQL MySQL 136M June 5 19:40 T1.IBD

-rw-rw--1 MySQL MySQL 8.4K June 5 19:43 T2.frm

-rw-rw--1 MySQL MySQL 136M June 5 19:44 T2.IBD

-rw-rw--1 MySQL MySQL 8.4K June 5 19:46 T3.frm

-rw-rw--1 MySQL MySQL 96M June 5 19:47 T3.IBD

Visible T1, T2 are not compressed, T3 is supported compression.