分类
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 (这个特性对一些优化还是很有用的) |
备注:
这里有一点需要注意,如果要使用压缩,一定需要先使用 =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 changedwubx> 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: 16384Max_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: 8192Max_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)