Differences between MySQLAntelope and Barracuda _ MySQL

This article mainly introduces the differences between MySQLAntelope and Barracuda. Antelope and Barracude are both in a file format. if you need them, refer to the document that Antelope is an innodb-base file format, barracude is the file format introduced after innodb-plugin, and also supports Antelope file format. The difference is:

File format	Supports row format	Features
Antelope (Innodb-base)	ROW_FORMAT = COMPACT ROW_FORMAT = REDUNDANT	The difference between Compact and redumdant lies in the difference between the first storage content. The storage format of compact is a list of non-NULL variable length fields. The redundant storage format is the header of a field length offset list (each field occupies the byte length and its corresponding displacement ). In Antelope, if the variable length field is smaller than 768 bytes, the overflow page is not stored. in some cases, the result set IO is reduced.
Barracuda (Innodb-plugin)	ROW_FORMAT = DYNAMIC ROW_FORMAT = COMPRESSED	The two are mainly functional differences. In addition, the variable-length field in a row differs from Antelope in 20 bytes, while other overflow pages store data. In addition, innodb_file_per_table = 1 must be enabled. (This feature is useful for some optimizations)

Note:

Note that to use compression, innodb_file_format = Barracuda must be used first. Otherwise, it will not work.

Let's take a look at the differences below:

The code is 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)

Four warnings are reported. check whether the error is returned:

The code is as follows:

(Testing) root @ localhost [wubx]> show warnings;

+ --- + -- + ------------------------ +

| Level | Code | Message |

+ --- + -- + ------------------------ +

| Waring | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format> Antelope. |

| Waring | 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)

We can see from the above error that compression is not supported. But let's take a look at the table structure as follows:

The code is as follows:

(Testing) root @ localhost [wubx]> show create table t1;

+ --- + ------------------------------------------------ +

| Table | Create Table |

+ --- + ------------------------------------------------ +

| T1 | create table t1 (

C1 int (11) 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 a pitfall, so pay attention to the use of compression.

The code is as follows:

(Testing) root @ localhost [wubx]> create table t2 (c1 int (11) not null, primary key (c1 ));

(Testing) root @ localhost [wubx]> insert into t2 select * from t1;

Query OK, 5417760 rows affected (37.12 sec)

Records: 5417760 Duplicates: 0 Warnings: 0

Create tables that support compression:

The code is 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 t3 select * from t1;

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

Records: 5417760 Duplicates: 0 Warnings: 0

The physical size of the table is as follows:

The code is as follows:

-Rw -- 1 mysql 8.4 K Jul 5 t1.frm

-Rw -- 1 mysql 136 M Jul 5 t1.ibd

-Rw -- 1 mysql 8.4 K Jul 5 t2.frm

-Rw -- 1 mysql 136 M Jul 5 t2.ibd

-Rw -- 1 mysql 8.4 K Jul 5 t3.frm

-Rw -- 1 mysql 96 M Jul 5 :47 t3.ibd

It can be seen that t1 and t2 are not compressed, and t3 supports compression.