InnoDB存储引擎介绍-(6) 一. Innodb Antelope 和Barracuda区别

分类

 

 Antelope是innodb-base的文件格式，Barracude是innodb-plugin后引入的文件格式，同时Barracude也支持Antelope文件格式。两者区别在于：

文件格式	支持行格式	特性
Antelope（Innodb-base）	ROW_FORMAT=COMPACT ROW_FORMAT=REDUNDANT	Compact和redumdant的区别在就是在于首部的存存内容区别。 compact的存储格式为首部为一个非NULL的变长字段长度列表 redundant的存储格式为首部是一个字段长度偏移列表（每个字段占用的字节长度及其相应的位移）。 在Antelope中对于变长字段，低于768字节的，不会进行overflow page存储，某些情况下会减少结果集IO.
Barracuda(innodb-plugin)	ROW_FORMAT=DYNAMIC ROW_FORMAT=COMPRESSED	这两者主要是功能上的区别功能上的。　另外在行里的变长字段和Antelope的区别是只存20个字节，其它的overflow page存储。 另外这两都需要开启innodb_file_per_table=1 （这个特性对一些优化还是很有用的）

备注：

这里有一点需要注意，如果要使用压缩，一定需要先使用innodb_file_format =Barracuda格式，不然没作用。

下面我们看一下区别：

(none)>SHOW VARIABLES LIKE 'innodb_file_format%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
+--------------------------+----------+
3 rows in set (0.01 sec)

(none)>use wubx;
Database changed
wubx> CREATE TABLE t1
    -> (c1 INT PRIMARY KEY)
    -> ROW_FORMAT=COMPRESSED
    -> KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 4 warnings (0.32 sec)

 

报出来4个warnings查看一下报错：

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)

 

从以上报错可以看出来不支持压缩。但看一下表结构如下：

wubx>show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

wubx>show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-08-19 16:41:17
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8
        Comment: 
1 row in set (0.00 sec)

 

 

这个是比较坑的地方，所以在使用压缩需要注意。

wubx>create table t2 ( c1 int(11) NOT NULL, primary key(c1));
Query OK, 0 rows affected (0.21 sec)

wubx>insert into t2 select * from t1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建支持压缩的表：

wubx>SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

wubx>CREATE TABLE t3 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.13 sec)

wubx>show table status like 't3'\G
*************************** 1. row ***************************
           Name: t3
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 0
 Avg_row_length: 0
    Data_length: 8192
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-08-19 16:50:09
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8
        Comment: 
1 row in set (0.00 sec)