Analysis of the difference between MySQL Antelope and Barracuda

2020-06-23 02:07:47
OfStack

Antelope is the file format of ES2en-ES3en, Barracude is the file format introduced after ES5en-ES6en, and Barracude also supports Antelope file format. The difference is:

文件格式

支持行格式

特性

Antelope

（Innodb-base）

ROW_FORMAT=COMPACT

ROW_FORMAT=REDUNDANT

Compact和redumdant的区别在就是在于首部的存存内容区别。

compact的存储格式为首部为1个非NULL的变长字段长度列表

redundant的存储格式为首部是1个字段长度偏移列表（每个字段占用的字节长度及其相应的位移）。

在Antelope中对于变长字段，低于768字节的，不会进行overflow page存储，某些情况下会减少结果集IO.

Barracuda

(innodb-plugin)

ROW_FORMAT=DYNAMIC

ROW_FORMAT=COMPRESSED

这两者主要是功能上的区别功能上的。　另外在行里的变长字段和Antelope的区别是只存20个字节，其它的overflow page存储。

另外这两都需要开启innodb_file_per_table=1

（这个特性对1些优化还是很有用的）

Remark:

There is one thing to note here. If you want to use compression, you must first use innodb_file_format =Barracuda format, otherwise it will not work.

Here's the difference:



(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 4 warnings to check 1 error:



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

You can see from the above error that compression is not supported. However, see the structure of table 1 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 where the pit is, so be careful when using compression.



(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:



(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

See the physical size of table 1 as follows:



-rw-rw - - 1 mysql mysql 8.4K Jul  5 16:58 t1.frm
-rw-rw - - 1 mysql mysql 136M Jul  5 19:40 t1.ibd
-rw-rw - - 1 mysql mysql 8.4K Jul  5 19:43 t2.frm
-rw-rw - - 1 mysql mysql 136M Jul  5 19:44 t2.ibd
-rw-rw - - 1 mysql mysql 8.4K Jul  5 19:46 t3.frm
-rw-rw - - 1 mysql mysql  96M Jul  5 19:47 t3.ibd

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